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