Oracle RDBMS Creation Date: Jan. 16, 2000 Last Update: Oct. 18, 2001 Version: 1.0 1
SQL EXPLAIN PLAN SQL*Plus AUTOTRACE SQL Oracle Enterprise Manager Tuning Pack SQL SQL*Plus AUTOTRACE SQL STATSPACK Oracle Enterprise Manager Tuning Pack UTLBSTAT/UTLESTAT etc.statspack Oracle Enterprise Manager Diagnostics Pack SQL EXPALIN PLANSQL*Plus AUTOTRACE SQL Oracle Enterprise Manager Tuning Pack SQL SQL*Plus AUTOTRACE SQL STATSPACKOracle Enterprise Manager Tuning Pack ( / I/O ) UTLBSTAT/UTLESTAT STATSPACKOracle Enterprise Manager Diagnostics Pack 2
IEXPLAIN PLAN II. SQL*PlusAUTOTRACE III. SQL IV. UTLBSTAT/UTLESTAT V. STATSPACK VI. Oracle Enterprise Manager Oracle8.1.6EE for Windows NT 3
I. EXPLAIN PLAN 4
I. EXPLAIN PLAN EXPLAIN PLAN EXPLAIN PLAN EXPLAIN PLAN 5
EXPLAIN PLAN SQL EXPLAIN PLAN EXPLAIN PLAN SQL 6
EXPLAIN PLAN 1. utlxplan.sqlplan_table SQL> %ORACLE_HOME% rdbms admin utlxplan.sql PLAN_TABLE 2. EXPLAIN PLAN EXPLAIN PLAN FOR SQL 3. PLAN_TABLE utlxpls SQL> %ORACLE_HOME% rdbms admin utlxpls.sql EXPLAIN PLAN. utlxplan.sql SQL PLAN_TABLE. EXPLAIN PLAN SQL. utlxpls.sql EXPLAIN PLAN SQL OPERATION PLAN_TABLE OPERATIONOPTIONS NAME PLAN_TABLE OBJECT_NAME ROWS BYTES COST CBT RBT NULL utlxpls.sql Select id, operation, options, object_name, position FROM plan_table WHERE object_name like EMP% order by id, position and timestamp = (select max(timestamp) fromplan_table; PLAN_TABLE OPERATION OPTIONS OBJECT_NAME OPTIMIZER ID POSITION 7
EXPLAIN PLAN SQL> @ %ORACLE_HOME% rdbms admin utlxpls Plan Table ------------------------------------------------------------------------------------------ Operation Name Rows Bytes Cost ------------------------------------------------------------------------------------------ SELECT STATEMENT 10 80 2 TABLE ACCESS BY INDEX ROW EMP 10 80 2 INDEX RANGE SCAN EMP_INDX 10 1 -------------------------------------------------------------------------------- ROWID EMP EXPLAIN PLAN EXPLAIN PLAN PLAN_TABLE SQL utlxpls.sql SELECT ename, sal FROM emp WHERE sal2000 order by sal SQL PLAN TABLE Position 1. EMP_INDX ROWID 2. ROWID EMP 8
II. SQL*PlusAUTOTRACE 9
II. SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE AUTOTRACE 10
SQL*PlusAUTOTRACE SQL SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE SQL 11
SQL*PlusAUTOTRACE 1. SYS utlxplan.sql PLAN_TABLE SQL> %ORACLE_HOME% rdbms admin utlxplan.sql PLAN_TABLE 2. plustrce.sql plustrace SQL> @%ORACLE_HOME% sqlplus admin plustrce.sql 3. plustrace 4. SQL> SET AUTOTRACE ON; SQL*PlusAUTOTRACE 1. SYS ultxplan.sql PLAN_TABLE 2. plustrce.sql plustrace V$ plustrce.sql 3. plustrace 4. traceonly SET AUTOTRACE SET AUTOTRACE OFFONTRACEONLY EXPLAIN STATISTICS OFF ON TRACEONLY EXPLAIN STATISTICS 12
SQL*Plus AUTOTRACE SQL SQL*Plus AUTOTRACE SQL set autotrace on SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NEWCREW TOKYO Execution Plan ---------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DEPT' Statistics ------------------------------------------ 13
SQL*Plus AUTOTRACE V$SESSTAT V$SYSSTAT I/O PGA PL/SQL db block getsconsistent gets physical reads sorts (memory) sorts (disk) recursive calls SQL rows processed 1. I/O PGA 2. PL/SQL 14
SQL*Plus AUTOTRACE I/O Statistics ---------------------------------------------------------- 42280 db block gets 360443 consistent gets 3366 physical reads physical gets/(db block gets+consistent gets) SQL*Plus AUTOTRACE I/O 3366/ (42280+360443)= 0.991 99% LRU 6090% db_block_buffers db_block_buffers OS SQL 15
SQL*Plus AUTOTRACE PGA I/O Statistics ---------------------------------------------------------- 396 sort (memory) 18 sort (disk) sort (disk) SORT_AREA_SIZE SQL*Plus AUTOTRACE PGA PGA sort (disk) I/O sorts (disk) sort (disk) SORT_AREA_SIZE SORT_AREA_SIZE 16
SQL*Plus AUTOTRACE SQL> select * fromemp; PL/SQL Statistics ------------------------------------------- 597 recursive calls 13 db block gets 91 consistent gets 8 physical reads SQL SQL> select * fromemp; Statistics -------------------------------------------- recursive calls 0 recursive calls ( SQL) 12 db block gets 6 consistent gets 0 physical reads SQL*Plus AUTOTRACE SQL PL/SQL SQL SQL SQL PL/SQL PL/SQL Alter system flush shared_pool; PL/SQL SQL recursive calls recursive calls SELECT * FROM emp PL/SQL SELECT * FROM emp ecursive calls PL/SQL SQL recrusive calls 17
SQL*Plus AUTOTRACE PL/SQL Statistics ---------------------------------------------------------- 0 recursive calls SQL recrusive calls SQL*Plus AUTOTRACE SQL recrusive calls SQL SQL EMP emp SQL SQL SQL SQL SQL SQL Select * fromemp ; Select * from EMP; Select * fromscott.emp; Select * from test.emp; 18
III. SQL tkprof 19
III. SQL tkprof SQL tkprof SQL SQL 20
SQL tkprof SQL USER_DUMP_DEST tkprof SQL SQL SQL SQL PARSEEXECUTE FETCH CPU SQL SQL TKPROF 21
SQL tkprof 1. 2. SQL ON MAX_DUMP_FILE_SIZE USER_DUMP_DEST TIMED_STATISTICS < > SQL_TRACETRUE < > ALTER SESSION SET SQL_TRACE=TRUE; 3. 4. SQL OFF 5. tkprof $ tkprof explain=usr/passwd ; SQL 1. MAX_DUMP_FILE_SIZE USER_DUMP_DEST TIMED_STATISTICS Oracle Enterprise Edition 8.1.6 for Windows NT timed_statistics = true user_dump_dest = E: Oracle admin DB UDUMP max_dump_file_size = 10240 SQL CPU INIT.ORA TIMED_STATISTICS Alter session set timed_statistics = ture; 2. SQL SQL 3. SQL 4. SQL 5. TKPROF USER_DUMP_DEST 22
SQL tkprof TKPROF SQL SQL EXPLAIN PLAN SQL SQL select avg(sal) fromemp group by deptno call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.06 0 77 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.01 2 5 12 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.06 0.07 2 82 12 4 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 38 (TEST) Rows Execution Plan ------- --------------------------------------------------- 4 SELECT STATEMENT GOAL: CHOOSE 4 SORT (GROUP BY) 16 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP' EXPLAIN PLAN 23
SQL tkprof EXPLAIN PLAN Rows Execution Plan ------ --------------------------------------------------- 4 SELECT STATEMENT GOAL: CHOOSE 4 SORT GROUP BY) 16 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP' tkprof EXPLAIN PLAN P9 ROWS tkprof SELECT avg (sal) FROM emp group by deptnosql 16 TABLE ACCESS GOAL: ANALYZED FULLOF EMP 16 EMP GROUP BY 4 SELECT STATEMENT GOAL:CHOOSE SQL SELECT 4 24
SQL tkprof SQL disk query + current call count cpu elapsed disk query current rows ------- ------ -------- ---------- ------- -------- --------- ---------- Parse 1 0.03 0.03 0 68 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 13576 2.52 7.29 1196 14770 5 203616 ------- ------ -------- --------- --------- ---------- ------- ---------- total 13578 2.55 7.32 1196 14838 5 203616 rows cpu CPU elapsed %ORACLE_HOME% tkprof TKPROF SQL PARSE SQL EXECUTE SQL DML SELECT FETCH SELECT DISK QUERYCURRENT CPU ELAPSED ROWS CPU SQL 25
SQL tkprof I/O call count cpu elapsed disk query current rows ------- ------ -------- ---------- ------- -------- --------- ---------- Parse 1 0.03 0.03 0 68 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 13576 2.52 7.29 1196 14770 5 203616 ------- ------ -------- --------- --------- ---------- ------- ---------- total 13578 2.55 7.32 1196 14838 5 203616 cpu elapsed I/O disk/ (query + current) tkprof I/O 1(disk / (query + current)) 1 1196/(14338 + 5)) =0.92 60 90% DB_BLOCK_BUFFERS p16 cpu elapsed I/O 26
SQL tkprof DB ACCOUNTS SELECT balance FROM accounts WHERE acc_num = 49999 call count cpu elapsed disk query current rows -------- ----- ---- ------- ----- ------ ------- ---- Parse 1 0.43 0.54 3 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 61.76 79.36 5883 5883 0 1 -------- ----- ---- ------- ----- ------ ------- ---- total 3 62.19 79.90 5886 5886 0 1 query + current / rows 20 tkprof SQL 79.90 rows =1 5886 querycurrent / rows 20 acc_num SQL 79.90 0.88 rows =1 5886 14 SELECT balance FROM accounts WHERE acc_num= 49999 call count cpu elapsed disk query current rows -------- ----- ---- ------- ----- ------ ------- ---- Parse 1 0.44 0.66 3 10 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.02 0.22 4 4 0 1 -------- ----- ---- ------- ----- ------ ------- ---- total 3 0.46 0.88 7 14 0 1 27
IV. UTLBSTAT/UTLESTAT 28
IV. UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT 29
UTLBSTAT/UTLESTAT BSTATESTAT report.txt BSTAT ESTAT UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT utlbstat.sql utlestat.sql report.txt utlbstat.sql BEGIN utlestat.sql END V$ STATS$ UTLBSTAT/UTLESTAT 30
UTLBSTAT/UTLESTAT report.txt report.txt I/O report.txt report.txt UTLBSTAT/UTLESTAT 31
UTLBSTAT/UTLESTAT TIMED_STATISTICSTRUE utlestat.sql utlbstat.sql report.txt SQL*Plus Ver.8.1 sysdba SQL> @%ORACLE_HOME% rdbms admin utlbstat SQL> @%ORACLE_HOME% rdbms admin utlestat utlestat.sql report.txt report.txt UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT utlbstat.sql utlestat.sql report.txt 1. TIMED_STATISTICSTRUE TIMED_STATISTICS Alter session set timed_statistics = ture; 2. utlbstat.sql utlestat.sql 3. Server Manager sysdba SQL SQL@%ORACLE_HOME% rdbms admin utlbstat.sql; SQL@%ORACLE_HOME% rdbms admin utlestat.sql; Ver.8 Server Manager Ver.8.1 SQL*Plus 4. report.txt BSTAT/ESTAT report.txt utlestat.sql report.txt 32
UTLBSTAT/UTLESTAT PINHITRATIO 90% PINSRELOADS RELOADS/PINS100 SQL SHARED_POOL_SIZE report.txt SVRMGR> Rem Select Library cache statistics. The pin hit rate should be high. SVRMGR> select namespace library, gets, 2> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3) gethitratio, 3> pins, round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) pinhitratio, 4> reloads, invalidations 5> fromstats$lib; LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI --------------------- ---------- ------------------ ---------- -------------------- -------------- ---------- BODY 0 1 0 1 0 0 CLUSTER 0 1 0 1 0 0 INDEX 0 1 0 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 358.978 798.977 1 0 TABLE/PROCED 33 1 45 1 0 0 TRIGGER 0 1 0 1 0 0 SQL SQL EMP emp SQL SHARED_POOL_SIZE 33
SVRMGR> select n1.name "Statistic", 2> n1.change "Total", 3> round(n1.change/trans.change,2) "Per Transaction", 4> round(n1.change/((start_users + end_users )/2),2) "Per Logon", 5> round(n1.change/(to_number(to_char(end_time, 'J'))*60*60*24-6> to_number(to_char(start_time, 'J'))*60*60*24 + 7> to_number(to_char(end_time, 'SSSSS')) - 8> to_number(to_char(start_time, 'SSSSS'))) 9>, 2) "Per Second" 10> from 11> stats$stats n1, 12> stats$stats trans, 13> stats$dates 14> where 15> trans.name='user commits' 16> and n1.change!= 0 17> order by n1.name; Statistic Total Per Transact Per Logon Per Second --------------------------- ------------ ------------ ------------ ------------ CPU used by this session 1647 1647 149.73 9.69 CPU used when call started 15 15 1.36.09 DBWR checkpoint buffers wri 4 4.36.02 DBWR undo block writes 1 1.09.01 SQL*Net roundtrips to/from 47 47 4.27.28 background timeouts 233 233 21.18 1.37 buffer is not pinned count 56 56 5.09.33 bytes received via SQL*Net 2965 2965 269.55 17.44 bytes sent via SQL*Net to c 3896 3896 354.18 22.92 calls to get snapshot scn: 104 104 9.45.61 calls to kcmgas 1 1.09.01 calls to kcmgcs 3 3.27.02 commit cleanouts 12 12 1.09.07 commit cleanouts successful 12 12 1.09.07 consistent gets 177 177 16.09 1.04 cursor authentications 1 1.09.01 db block changes 54 54 4.91.32 db block gets 118 118 10.73.69 enqueue releases 160 160 14.55.94 enqueue requests 154 154 14.91 execute count 85 85 7.73.5 free buffer requested 9 9.82.05 34
logons cumulative 138 138 12.55.81 messages received 4 4.36.02 messages sent 4 4.36.02 no work - consistent read g 51 51 4.64.3 opened cursors cumulative 417 417 37.91 2.45 parse count (hard) 10 10.91.06 parse count (total) 357 357 32.45 2.1 parse time cpu 6 6.55.04 parse time elapsed 6 6.55.04 physical reads 18 18 1.64.11 physical reads direct 18 18 1.64.11 physical writes 22 22 2.13 physical writes direct 18 18 1.64.11 physical writes non checkpo 21 21 1.91.12 process last non-idle time 19831662 19831662 1802878.36 116656.84 recursive calls 2932 2932 266.55 17.25 recursive cpu usage 1 1.09.01 redo blocks written 69 69 6.27.41 redo entries 34 34 3.09.2 redo size 38132 38132 3466.55 224.31 redo synch writes 1 1.09.01 redo wastage 892 892 81.09 5.25 redo write time 3 3.27.02 redo writes 3 3.27.02 session connect time 19831662 19831662 1802878.36 116656.84 session logical reads 295 295 26.82 1.74 session pga memory 18472784 18472784 1679344 108663.44 session pga memory max 18525288 18525288 1684117.09 108972.28 session uga memory 730920 730920 66447.27 4299.53 session uga memory max 3096688 3096688 281517.09 18215.81 sorts (disk) 2 2.18.01 sorts (memory) 77 77 7.45 sorts (rows) 4916 4916 446.91 28.92 table fetch by rowid 5 5.45.03 table scan blocks gotten 7 7.64.04 table scan rows gotten 92 92 8.36.54 table scans (short tables) 6 6.55.04 total file opens 1 1.09.01 user calls 325 325 29.55 1.91 user commits 1 1.09.01 35
UTLBSTAT/UTLESTAT 1. CPU used by this session Statistic Total Per Transact Per Logon Per Second --------------------------- ------------ ------------ ------------ ------------ CPU used by this session 1647 1647 149.73 9.69 parse time cpu 6 6.55.04 2. consistent gets 177 177 16.09 1.04 db block gets 118 118 10.73.69 physical reads 18 18 1.64.11 1. CPU used by this session CPU used by this sessionparse time cpu parse time cpu SQL SQL 18 2. P16 90% DB_BLOCK_BUFFERS 1 physical reads/ (consistent gets + db block gets) = 1 - (18/ (177+118)0.94 3. Statistic P16Total Per Transact Per Logon Per Second --------------------------- ------------ ------------ ------------ ------------ sorts (disk) 2 2.18.01 sorts (memory) 77 77 7.45 sorts (rows) 4916 4916 446.91 28.92 36
UTLBSTAT/UTLESTAT buffer busy wait undo header segment header (* 1) V$SESSION_WAIT *1. FIFO first in first out SVRMGR> Rem Buffer busy wait statistics. SVRMGR> select * fromstats$waitstat 2> where count!= 0 order by count desc; CLASS COUNT TIME ------------------ ---------------- ---------------- 0 UNDO_SEGMENT0 RBS SVRMGR> select * fromstats$roll; UNDO_ TRANS_ TRANS_ UNDO_ SEGMENT_SIZE XACTS SHRINKS WRAPS SEGMENT TBL_GETS TBL_WAITS BYTES_WRITTEN BYTES ------------- --------------- ---------------- ------------------- ------------------- ------------- ------------ -------- 0 1 0 0 401408 0 0 0 1 2 0 1532 4186112 1 0 0 2 1 0 0 4186112 0 0 0 3 1 0 0 4186112 0 0 0 4 21 0 0 4186112 0 0 0 5 1 0 0 4186112 0 0 0 6 1 0 0 4186112 0 0 0 7 2 0 1362 4186112-1 0 0 37
UTLBSTAT/UTLESTAT / 100 15 SHARED_POOL_SIZE stats$dc GET_MISS/GET_REQS100>=15 SHARED_POOL_SIZE CUR_USAGCOUNT SHARE_POOL_SIZE SVRMGR> Rem get_miss and scan_miss should be very low compared to the requests. SVRMGR> Rem cur_usage is the number of entries in the cache that are being used. SVRMGR> select * fromstats$dc 2> where get_reqs!= 0 or scan_reqs!= 0 or mod_reqs!= 0; NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG --------------- -------------- ------------- -------------- ------------- --------------- -------- --------------- dc_tablespaces 4 0 0 0 0 5 3 dc_segments 12 0 0 0 0 78 66 dc_users 15 0 0 0 0 16 15 dc_user_grants 9 0 0 0 0 20 10 dc_objects 23 0 0 0 0 381 373 dc_usernames 2 0 0 0 0 21 dc_object_ids 18 0 0 0 0 143 130 dc_profiles 1 0 0 0 0 6 1 dc_histogram_de 8 1 0 0 0 15 5 38
UTLBSTAT/UTLESTAT I/O I/O TABLE_ BLKS_ READ_ BLKS_ WRITE_ AVG_RT SPACE FILE_NAME READS READ TIME WRITES WRT TIME MEGABYTES blocks/rd ------------ ----------------------- ------ ------ ---------- ---------- ---------- ---------- ---------- ---------- ------- INDX /disk4/index.dbf 8 8 0 8 8 0 21 0 0 RBS /disk2/rbs01.dbf 26 26 50 257 257 441 73 0 0 SYSTEM /disk1/system01.dbf 806 1538 1985 116 116 1721 272 0 0 USERS /disk3/user01.dbf 168 672 483 675 675 0 21 0 3 read I/O I/O multiblock read INDX READS/BLKS_READ 1 READ USERS READS168 BLKS_READ672 I/O4 READ TABLE_SPACE utlbstat.sql utlestat.sql START_TIME END_TIME ------------------ ------------------ 20-nov-00 10:15:40 20-nov-00 10:18:30 39
V. STATSPACK 40
V. STATSPACK STATSPACK STATPACK(statistics package) BSTAT/ESTAT STATSPACK 41
42 STATSPACK Ver.8.1.6 UTLBSTAT/UTLESTAT snap1 snap2 snap3 snap4 snap5 STATSPACK UTLBSTAT/UTLESTAT Ver.8.1.6 SNAP SNAP2 SNAP2 SNAP4 SNAP1 SNAP5 3 STATSPACK (Ver.8.1.6 Ver8.1.7 ) statspack.doc spdoc.txt STATSPACK
STATSPACK UTLBSTAT/UTLESTAT SQL etc. UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT SQL STATSPACK Feature Statspack BSTAT/ESTAT Instance summary page Y N Normalization of instance statistics by time and number of transactions Y N Wait events Y Y High-resource SQL Y* N Instance-activity statistics Y Y Tablespace and file I/O statistics Y Y Buffer-wait breakdown by type Y Y Enqueue statistics Y N Rollback-segment activity and storage data Y Y Latch activity Y Y Latch-sleep breakdown Y N Latch children Y** N Buffer-pool statistics Y N Dictionary-cache activity Y Y Library-cache activity Y Y SGA memory summary Y N SGA memory breakdown Y N Nondefault init.ora parameters Y Y Configurable output name Y N Ability to move performance data Y N Configurable amount of data collected Y N Ability to run in multiple instances for Oracle Parallel Server Y N *Level 5 (default) data collection **Level 10 data collection level 5,10 43
STATSPACK Version 8.1.6 1. sysdba STATSPACK SQL> @ %ORACLE_HOME% rdbms admin statscre.sql; perfstat 2. perfstat SQL> connect perfstat/perfstat; SQL> execute statspack.snap; 3. SQL> @%ORACLE_HOME% rdbms admin statsrep.sql; STATSPACK 1. STATSPACK SQL*Plussysdba Ver.8.1.6 statscre.sql Ver.8.1.7 spcreate.sql Server Manager SQL*Plus statscre.sql statscur.sql statsctab.sql statspack.sql statscur.sql STATSPACK perfstat V$ SELECT Specify perfstat perfstat user s default tablespace: tools; Specify perfstat user s temporary tablespace: temp ; Enter tablespace where STATSPACK objects will be created: tools ; timed_statistics true 2. perfstat statspack.snap snap_id 3. Enter begining Snap Id : 1; perfstat Ver. 8.1.6 statsrep.sql Enter ending Snap Id : 4; Ver.8.1.7 spreport.sql Sp_id Sp_id Enter name of output file[st_1_2]: st_1_4; 44
STATSPACK dbms_job job_queue_processes 1 dbms_job.interval execute dbms_job.interval(<job number>, sysdate+(1/48) ); execute dbms_job.remove(<job number>); statsauto.sql STATSPACK dbms_job job_queue_processes dbms_job.interval dbms_job.interval (<job number>, sysdate+(1/48) ) 1/48 30 dbms_job.remove(<job number>) statsauto.sql 45
STATSPACK i_snap_level level 0: level 5: 0SQL level 10: 5 DB (R8.1.7 ) SQL> %ORACLE_HOME% rdbms admin sppurge.sql i_snap_level snapshot1 level5 snapshot2 level10 level5 sppurge.sql sptrunc.sql 46
STATSPACK Summary CPU/ Summary Summary BSTAT/ ESTAT BSTAT/ESTAT 1. DB Name DB Id Instance Inst Num Release OPS Host ------------ -------------- ---------- ------------ ------------- ------ ---------- db816 4120106846 db816 1 8.1.6.0.0 NO HSAITO01 2. Snap Length Start Id End Id Start Time End Time (Minutes) -------- ----------- ------------------------- -------------------------- ----------- 11 12 25-Dec-00 17:12:07 25-Dec-00 17:19:13 7.10 3. init.ora Cache Sizes ~~~~~~~~~~~ db_block_buffers: 13905 db_block_size : 8192 log_buffer: 32768 shared_pool_size: 37970944 47
STATSPACK Summary 4. Per Second Per Transaction Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 801.21 341,316.00 REDO Logical reads: 16.63 7,085.00 Block changes: 1.59 677.00 Physical reads: 0.14 61.00 Physical writes: 0.06 25.00 User calls: 2.03 863.00 Parses: 2.53 1,077.00 Hard parses: 0.12 53.00 Sorts: 0.64 271.00 Transactions: 0.00 Rows per Sort: 30.92 % Pct Blocks changed / Read: 9.56 % Recursive Call Pct: 92.04 % Rollback / transaction Pct: 0.00 48
STATSPACK Summary 100 SQL 100 5. Redo db_block_buffers shared_pool_size log_buffer Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait Ratio: 100.00 Buffer Hit Ratio: 99.14 Library Hit Ratio: 91.33 SQL Redo NoWait Ratio: 100.00 Redo In-memory Sort Ratio: 98.89 Soft Parse Ratio: 95.08 Latch Hit Ratio: 100.00 Shared Pool Statistics Begin End ~~~~~~ ~~~~~ Memory Usage Pct: 24.04 26.41 Pct SQL with executions > 1: 52.32 76.11 SQL Pct Memory for SQL w/exec > 1: 35.21 63.98 SQL 49
STATSPACK Summary 5 6. control file sequential write 1 db file sequential read DB 1 SQL / control file parallel write Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- control file sequential read 30 70 38.67 db file sequential read 40 51 28.18 control file parallel write 137 33 18.23 READ free buffer waits, buffer busy wait db_block_buffers I/O write complete waits DBWR I/O SQL 50
STATSPACK 51
VI. Oracle Enterprise Manager 52
V. Oracle Enterprise Manager Oracle Enterprise Manager Oracle Diagnostics Pack Oracle Tuning Pack 53
Oracle Enterprise Manager Oracle GUI Oracle Diagnostics Pack Oracle Tuning Pack Oracle Change Management Pack Oracle Enterprise Manager Oracle Enterprise Manager Oracle GUI Oracle Diagnostics Pack Oracle Tuning Pack Oracle Change Management Pack Oracle Enterprise Manager http://www.oracle.co.jp Oracle Diagnostics PackOracle Tuning Pack 54
Oracle Enterprise Manager Oracle Diagnostics Pack Capacity Planner Performance Manager Advanced Events TopSessions Oracle Trace Performance Manager I/O Oracle Enterprise Manager Oracle Diagnostics Pack Oracle Diagnostics Pack Oracle Enterprise Manager Oracle Capacity Planner Oracle Performance Manager Oracle TopSessions Oracle Lock Manager Oracle Advanced Events Oracle TRACE 55
Oracle Enterprise Manager Oracle Tuning Pack Oracle Expert Oracle SQL Analyze Index Tuning Wizard Oracle Tablespace Map Reorg Wizard Oracle Enterprise Manager Oracle Tuning Pack Oracle Tuning Pack Oracle Enterprise Manager SQL Oracle Tuning Pack Oracle Enterprise Manager Oracle Expert Oracle SQL Analyze SQL Index Tuning Wizard Oracle Tablespace MapReorg Wizard 56
Oracle Oracle8i days SQL days 1 day day INDEX day INDEX day TEL FAX 57
Oracle Oracle8Oracle8iNet8 58