Treasure Chest / Scripts
  Treasure Chest  

You will find hereafter a number of scripts which we find useful when studying performance problems.
Most of them access tables (or views) an ordinary user cannot query, so you may have to negotiate in order to run them.

Actually, you may even need the right to query the tables in the SYS schema, and not only the catalog. When you work on performance you become impatient and some of the queries would be desperately slow if run against the dictionary view. After all, when you go to the surgeon you don’t expect to keep buttoned up ... if you want any result, that is.

All those scripts are published as is, without any guarantee, and under the GPL license.

  • what_hurts.sql checks in SGA the most costly statements; many scripts do this kind of thing, but this one tries to correctly count together queries which are almost identical.
  • rsdiag.ksh is a useful script for checking what is going on on all the Oracle instances running on a server at once.
  • idx.sql is extremely convenient for checking how a table is indexed.
  • rowcnt.sql is a tricky SQL script for getting quickly the sizes of several tables involved in a complex query.
  • parttab.sql will tell you how tables are partitioned, which is not always easy to check. Requires high privileges.
  • viewdep.sql shows whether a view is built atop some other views, a frequent cause for dismal performance in complex queries.
  • repeat.sql automatically runs another script every 5 seconds a specified number of times.
  • stat_sanity.sql tells what the cost-based optimizer knows about the data in all the columns of a given table.