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.
For future reference, I’ve also added a link to this page near the top of the panel to the right.
Extracts from the AWR
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.
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