Oracle Scratchpad

December 18, 2009

Simple scripts

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 5:24 pm GMT Dec 18,2009

Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs or getting my laptop onto the local network or downloading from the Internet, so I’ve often had to type up ad-hoc queries whenever I’ve had a thought about some useful piece of information that I want to pull out of the database.

On the plus side this means that I usually manage to keep up to date with the information that’s available in the data dictionary and dynamic performance views – because I have to check it from time to time. On the minus side I’ve never really got around to creating a tidy library of useful scripts – after all it only takes a few minutes to create an ad hoc query but it takes a couple of hours to write the sort of “production quality” that I would normally feel happy about publishing.

However, I’ve been asked for ideas and scraps so often that I’ve decided to start supplying some of the quick and dirty solutions that I’ve hacked together in a few minutes. Even though they’re very basic scripts, and not intended to cover all situations, you may find some of them useful.

Extracts from the AWR

trend_awr_stat.sql: Report a single statistic across time from the AWR history of v$sysstat.
trend_awr_os_stat.sql: Report a single statistic across time from the AWR history of v$osstat.
trend_cpu_stat.sql: Demonstration of a method of correlating two statistics from AWR history

Simple diagnostic probes

Pga Leaks: an article that includes three queries to check memory usage for a session.
Recent SQL: an article that describes a check for SQL that has recently appeared in the SGA heap
Segment Scans: a simple script for finding objects subject to a large number of tablescans or index fast full scans
Log file switches: a script (for single instance) to report the time of, and time between, log file switches
Time to collect stats: a script to report the start time and run time of the automatic stats collection job
SGA Resizing: a script to report resizing operations (v$sga_resize_ops)
What have I done: a script to report the work done, time spent, and wait events for my session
Tracking one statement: an article describing a way of watching the work done by a critical statement as time passes
Snapshot my workload: an example of creating a package in the SYS schema to take snapshot of dynamic performance views
Hidden Parameters: Very old queries (2001) to report all parameters, including the hidden ones – session and system level
Parallel Workload: a query to run immediately after a parallel operation in the same session to report the distribution of work
Optimizer Operations: How much time was spent on calls to dbms_stats over the stats retention period.
System stats: report current values
SQL Hints:  simple listing of v$sql_hint showing all the hints, their inverses, and the level(s) for which they’re relevant.
Parallel Wait Events: simple listing for the wait events for all the processes involved in a parallel query.
Parallel Session Activity: simple listing for the session stativity stats for all the processes involved in a parallel query.

Data Diagnostics

Partition Count: an article with a script to count the number of rows in each partition of a partitioned table
tablespace usage: a script to list the extents and free space chunks in a tablespace in file and block order
Index definitions: a script to describe the indexes on a single table – column and statistical information
Oversized Indexes: a data dictionary scan for simple B-tree indexes that may be unreasonably large for the data held
Index Leaf Block scanner: a labour-intensive analysis of leaf block usage for a simple B-tree index.
Drawing an Index: An example of reading a treedump and using an analytic function to “draw a picture” of an index.
Reporting Branch blocks: Similar to “drawing an index”, but listing just the branch blocks in index order.
Stale Stats: A simple piece of pl/sql to report objects with stale or missing stats
Hakan Factor: A procedure – owned by SYS – that reports the Hakan factor for an object.
Segment Space: A script to report on space usage within generai segment types
Securefile Space: A variant on the space reporting script to report space usage inside Securefile LOBs
SYSAUX: A report of which Oracle components are in the tablespace, and what procedure should be used to move them

Technical Demonstrations

ora_hash(): a script demonstrating that ora_hash works with numeric partition keys, published in response to this comment.

 

2 Comments

  1. I don’t call a script “simple” when it contains more then 10 lines ;)
    Two scripts I use most heavily are getting an explain plan output & default trace file name:

    set linesize 150 pagesize 999
col plan_table_output format a150
select * from table(dbms_xplan.display);

    select value || '/' || (select lower(instance_name) from v$instance) || '_ora_' ||
       (select spid
          from v$process
         where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1))) || '.trc'
  from v$parameter
 where name = 'user_dump_dest';

    BTW, referencing dba_hist_* requires Diagnostic Pack license only; correct name of the Performance pack is “Oracle Tuning Pack”.

    Comment by Timur Akhmadeev — December 21, 2009 @ 9:36 am GMT Dec 21,2009

    • Timur,
      It depends so much on where you put the line-breaks. Here’s your second query formatted properly (i.e. MY way) ;)

      select 
	value || '/' || 
	(select lower(instance_name) from v$instance) || 
	'_ora_' ||
	(
	select 	spid
	from	v$process
	where	addr = (
			select	paddr 
			from	v$session 
			where	sid = (
					select	sid 
					from	v$mystat 
					where	rownum = 1
				)
			)
	) || 
	'.trc'
from 
	v$parameter
where
	name = 'user_dump_dest'
;

      Twenty-three lines !

      Comment by Jonathan Lewis — December 21, 2009 @ 1:55 pm GMT Dec 21,2009


RSS feed for comments on this post.

Website Powered by WordPress.com.

%d bloggers like this: