STATSPACK’S IMPORTANCE IN ORACLE DATABASE STANDARD EDITION

How to identify the historical statistics of the database performance during below two circumstances?

  • When using standard edition
  • When there is no license for diagnostics pack, even though database is enterprise

This is where statspack gives a helping hand

Enable stats pack snap immediately in the database, if the database is exposed to non diagnostics pack

kIsH@Xhydra<>@?/rdbms/admin/spcreate

Session altered.


Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password:

There are different tables and views with STATS$ which helps in historical analysis of the database

kIsH@Xhydra<>select view_name from dba_views where view_name like '%STATS$%';

VIEW_NAME
--------------------------------------------------------------------------------
STATS$X_$KCBFWAIT
STATS$X_$KSPPSV
STATS$X_$KSPPI
STATS$X_$KSXPPING
STATS$V_$FILESTATXS
STATS$V_$TEMPSTATXS
STATS$V_$SQLXS
STATS$V_$SQLSTATS_SUMMARY
STATS$BG_EVENT_SUMMARY

To check the sql fulltext of a sql after sql is removed from memory

set long 10000000
set numwidth 50
set lines 200 pages 1000
col SQL_ID for a20
col SQL_TEXT for a80
select sql_id,sql_text from STATS$V_$SQLXS where rownum < 2;

To check the sorting details of sql

kIsH@Xhydra<>col sorts for 9999999999
kIsH@Xhydra<>col rows_processed for 999999999999
kIsH@Xhydra<>select sql_id,sorts,rows_processed from STATS$V_$SQLXS order by sorts desc fetch first rows only;

SQL_ID                     SORTS ROWS_PROCESSED
-------------------- ----------- --------------
2sxqgx5hx76qr              11647         175819

Identify the expensive SQL

kIsH@Xhydra<>col PARSE_CALLS for 999999999999
kIsH@Xhydra<>col DISK_READS for 999999999999
kIsH@Xhydra<>col BUFFER_GETS for 99999999999
kIsH@Xhydra<>col EXECUTIONS for 99999999999
kIsH@Xhydra<>col VERSION_COUNT for 9999999999
kIsH@Xhydra<>select SQL_ID,PARSE_CALLS,DISK_READS,BUFFER_GETS,EXECUTIONS,VERSION_COUNT,ELAPSED_TIME from STATS$V_$SQLSTATS_SUMMARY order by ELAPSED_TIME desc fetch first rows only;

SQL_ID                 PARSE_CALLS    DISK_READS  BUFFER_GETS   EXECUTIONS VERSION_COUNT ELAPSED_TIME
-------------------- ------------- ------------- ------------ ------------ ------------- ------------
f4v56bu3c2vjy                    3         12014        22533            3             1    114557171

Leave a Reply