http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
DBMS_STATS.GATHER_TABLE_STATS
exec dbms_stats.gather_table_stats( -
ownname => 'PERFSTAT', -
tabname => ’STATS$SNAPSHOT’ -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size skewonly', -
cascade => true, -
degree => 7 -
)
Options: (for gather_schema_stats)
gather —Reanalyzes the whole schema
gather empty —Only analyzes tables that have no existing statistics
gather stale —Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto —Reanalyzes objects which currently have no statistics and objects with stale statistics
method_opt:
'for all indexed columns size skewonly' - examines the distribution of values for every column within every index for histogram
'for all columns size repeat' - only reanalyze indexes with existing histograms
'for columns size auto' - used when invoked using alter table xxx monitoring; data distribution and the access manner / load on column
DBMS_STATS.GATHER_SYSTEM_STATS
execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');
The statistics collected are (sys.aux_stats$)
No Workload (NW) stats:
CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:
SREADTIM - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)
Statistics collected during different workloads may be stored and used for influencing the optimizer plan as per load times
/* e.g. activate the DAY statistics each day at 7:00 am */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate + 1');
END;
/
/* e.g. activate the NIGHT statistics each day at 9:00 pm */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate + 1');
END;
/
*** ********************************************************
*** Initialize the OLTP System Statistics for the CBO
*** ********************************************************
1. Delete any existing system statistics from dictionary:
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;
PL/SQL procedure successfully completed.
2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');
PL/SQL procedure successfully completed.
3. All system statistics are now visible in the data dictionary table:
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- ------------------ ---------- --------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-09-2001 16:40
SYSSTATS_INFO DSTOP 08-09-2001 16:42
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM 7.581
SYSSTATS_MAIN MREADTIM 56.842
SYSSTATS_MAIN CPUSPEED 117
SYSSTATS_MAIN MBRC 9
where
=> sreadtim : wait time to read single block, in milliseconds
=> mreadtim : wait time to read a multiblock, in milliseconds
=> cpuspeed : cycles per second, in millions
-------------------------------------------------------------------
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> select * from t1 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> select /*+index(t2 inx_t2) */ * from t2 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 304 hsecs
Run2 ran in 331 hsecs
run 1 ran in 91,84% of the time
Name Run1 Run2 Diff
STAT...table scans (short tabl 1 0 -1
LATCH.resmgr:schema config 0 1 1
LATCH.job_queue_processes para 0 1 1
LATCH.undo global data 7 6 -1
LATCH.resmgr:actses active lis 0 1 1
STAT...shared hash latch upgra 0 1 1
STAT...session cursor cache co 1 0 -1
STAT...heap block compress 6 7 1
STAT...index scans kdiixs1 0 1 1
LATCH.In memory undo latch 0 2 2
LATCH.library cache lock 4 6 2
LATCH.redo allocation 13 16 3
STAT...CPU used by this sessio 15 12 -3
STAT...calls to get snapshot s 5 2 -3
STAT...active txn count during 4 8 4
STAT...cleanout - number of kt 4 8 4
STAT...redo entries 9 13 4
STAT...calls to kcmgcs 4 8 4
LATCH.messages 18 22 4
STAT...consistent gets - exami 4 9 5
LATCH.library cache pin 38 44 6
STAT...recursive cpu usage 13 7 -6
LATCH.library cache 48 55 7
LATCH.active service list 2 10 8
STAT...db block gets 17 29 12
STAT...db block gets from cach 17 29 12
STAT...consistent changes 17 29 12
STAT...db block changes 26 42 16
STAT...bytes received via SQL* 3,188 3,209 21
STAT...DB time 36 15 -21
STAT...CPU used when call star 34 12 -22
STAT...Elapsed Time 311 335 24
LATCH.simulator hash latch 11 44 33
LATCH.simulator lru latch 11 44 33
LATCH.JS queue state obj latch 0 36 36
LATCH.enqueues 2 78 76
LATCH.enqueue hash chains 2 79 77
STAT...no work - consistent re 206 397 191
STAT...consistent gets 218 415 197
STAT...consistent gets from ca 218 415 197
STAT...table scan blocks gotte 206 0 -206
STAT...session logical reads 235 444 209
STAT...undo change vector size 2,132 2,420 288
LATCH.cache buffers chains 550 933 383
STAT...buffer is not pinned co 0 576 576
STAT...redo size 2,780 3,400 620
STAT...table fetch by rowid 0 2,765 2,765
STAT...buffer is pinned count 0 5,139 5,139
STAT...table scan rows gotten 51,831 0 -51,831
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,290 1,962 672 65.75%
PL/SQL procedure successfully completed.
No comments:
Post a Comment