The SQL statistics summary is one of the most important
tables within the STATSPACK facility. As we know, tuning of SQL can
often have a profound impact on the performance of your Oracle system,
and the stats$sql_summary table provides the text of each SQL
statement and a detailed description of the resources used by each and
every SQL statement that meets the necessary thresholds conditions to
be captured in a snapshot.
For now, all we need to know is that this table
tracks the number of executions, the number of parse calls, and the
number of data blocks read and written for each SQL statement. This
information can be an invaluable tool when it comes time to tune the
SQL within your Oracle database.
It also bears mentioning that the
stats$sql_summary table is the most highly populated of all of the
STATSPACK tables. If your threshold values are set very low and you
have a busy database, it's not uncommon to get 300 to 500 rows added
to the stats$sql_summary table each and every time STATSPACK requests
a snapshot. Hence, it is very important that the DBA remove unwanted
rows from the stats$sql_summary table once they are no longer used for
SQL tuning.
Here is the version of stats$sql_summary for
Oracle8 and Oracle8i.
SQL> desc
STATS$SQL_SUMMARY;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
SORTS NUMBER
MODULE VARCHAR2(64)
LOADED_VERSIONS NUMBER
EXECUTIONS NUMBER
LOADS NUMBER
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
ADDRESS NOT NULL RAW(8)
HASH_VALUE NOT NULL NUMBER
VERSION_COUNT NUMBER
Oracle9i has numerous additional columns for
additional details about the SQL. Here is the table description for
Oracle9i.
SQL> desc
STATS$SQL_SUMMARY
Name Null? Type
----------------------------------------- -------- --------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
TEXT_SUBSET NOT NULL VARCHAR2(31)
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
SORTS NUMBER
MODULE VARCHAR2(64)
LOADED_VERSIONS NUMBER
EXECUTIONS NUMBER
LOADS NUMBER
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
ADDRESS RAW(8)
HASH_VALUE NOT NULL NUMBER
VERSION_COUNT NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
and include the URL for the page.
