Oracle Dictionary Scripts

In the real world, most Oracle experts use custom data dictionary scripts to investigate Oracle performance problems. One of the reasons that third-party tools cannot replace native scripts is that the Oracle data dictionary is very sophisticated, and there are hundreds of thousands of ways that Oracle metrics can be combined and displayed.

Oracle tuning scripts can also be very sophisticated in structure, especially when performing advanced tuning activities such as hypothesis testing and multivariate analysis.

Even simple tasks can be far too complex for a GUI tool. For example, this simple script to display full-table scans would be nearly impossible to build from a GUI menu:

select

to_char(sn.end_interval_time,'mm/dd/rr hh24') time,

p.owner,

p.name,

t.num_rows,

decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,

s.blocks blocks,

sum(a.executions_delta) nbr_FTS

from

dba_tables t,

dba_segments s,

dba_hist_sqlstat X "dba_hist_sqlstat" a,

dba_hist_snapshot sn,

(select distinct

pl.sql_id,

object_owner owner,

object_name name

from

dba_hist_sql_plan X "dba_hist_sql_plan" pl

where

operation = 'TABLE ACCESS'

and

options = 'FULL') p

where

a.snap_id = sn.snap_id

and

a.sql_id = p.sql_id

and

t.owner = s.owner

and

t.table_name = s.segment_name

and

t.table_name = p.name

and

t.owner = p.owner

and

t.owner not in ('SYS','SYSTEM')

having

sum(a.executions_delta) > 1

group by

to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks

order by

1 asc;

Native Oracle dictionary scripts also allow information to be displayed in novel ways and the creation of easy exception reports. To confound matters, every release of Oracle has changes to the underlying x$ tables and v$ views, and new scripts may need to be created for Oracle9i and Oracle10g.

With hundreds of thousands of possible tuning queries, how does the DBA manage to find the best possible solution? Most professional Oracle tuning professionals build collections of their top 500 scripts and organize them so that they can find the right script for desired outcome.

One extremely popular script collection is the Mike Ault collection available at www.oracle-script.com. This collection is organized with precise naming conventions to clue the DBA in about the functions of each script. Within each script, all possible column values are displayed and the DBA can comment out those that they are not interested in observing.

To a knowledgeable DBA, finding the right script is simple. For example, to find all scripts referencing physical disk reads, the following grep command can be issued:

Most hardcore Oracle tuning professionals do not like being forced to tune within the confines of the tool, and they enjoy the flexibility of being able to get any metric that they want.

The exception to this rule is graphing time-series data and many Oracle DBAs cut-and-paste output data into MS-Excel spreadsheets for visual plotting. The visualization of this data is one area where a third-party tool can assist the DBA.

The following section will provide a look at a third-party tool that is specifically designed to assist with this visualization problem.