perf_tool.PDF

Transcription

1 Oracle RDBMS Creation Date: Jan. 16, 2000 Last Update: Oct. 18, 2001 Version: 1.0 1

2 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

3 IEXPLAIN PLAN II. SQL*PlusAUTOTRACE III. SQL IV. UTLBSTAT/UTLESTAT V. STATSPACK VI. Oracle Enterprise Manager Oracle8.1.6EE for Windows NT 3

4 I. EXPLAIN PLAN 4

5 I. EXPLAIN PLAN EXPLAIN PLAN EXPLAIN PLAN EXPLAIN PLAN 5

6 EXPLAIN PLAN SQL EXPLAIN PLAN EXPLAIN PLAN SQL 6

7 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

8 EXPLAIN PLAN %ORACLE_HOME% rdbms admin utlxpls Plan Table Operation Name Rows Bytes Cost SELECT STATEMENT TABLE ACCESS BY INDEX ROW EMP INDEX RANGE SCAN EMP_INDX 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

9 II. SQL*PlusAUTOTRACE 9

10 II. SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE AUTOTRACE 10

11 SQL*PlusAUTOTRACE SQL SQL*PlusAUTOTRACE SQL*PlusAUTOTRACE SQL 11

12 SQL*PlusAUTOTRACE 1. SYS utlxplan.sql PLAN_TABLE SQL> %ORACLE_HOME% rdbms admin utlxplan.sql PLAN_TABLE 2. plustrce.sql plustrace 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

13 SQL*Plus AUTOTRACE SQL SQL*Plus AUTOTRACE SQL set autotrace on SQL> select * from dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NEWCREW TOKYO Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DEPT' Statistics

14 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

15 SQL*Plus AUTOTRACE I/O Statistics db block gets consistent gets 3366 physical reads physical gets/(db block gets+consistent gets) SQL*Plus AUTOTRACE I/O 3366/ ( )= % LRU 6090% db_block_buffers db_block_buffers OS SQL 15

16 SQL*Plus AUTOTRACE PGA I/O Statistics 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

17 SQL*Plus AUTOTRACE SQL> select * fromemp; PL/SQL Statistics 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

18 SQL*Plus AUTOTRACE PL/SQL Statistics 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

19 III. SQL tkprof 19

20 III. SQL tkprof SQL tkprof SQL SQL 20

21 SQL tkprof SQL USER_DUMP_DEST tkprof SQL SQL SQL SQL PARSEEXECUTE FETCH CPU SQL SQL TKPROF 21

22 SQL tkprof SQL ON MAX_DUMP_FILE_SIZE USER_DUMP_DEST TIMED_STATISTICS < > SQL_TRACETRUE < > ALTER SESSION SET SQL_TRACE=TRUE; SQL OFF 5. tkprof $ tkprof explain=usr/passwd ; SQL 1. MAX_DUMP_FILE_SIZE USER_DUMP_DEST TIMED_STATISTICS Oracle Enterprise Edition for Windows NT timed_statistics = true user_dump_dest = E: Oracle admin DB UDUMP max_dump_file_size = 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

23 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 Execute Fetch total Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 38 (TEST) Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 4 SORT (GROUP BY) 16 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP' EXPLAIN PLAN 23

24 SQL tkprof EXPLAIN PLAN Rows Execution Plan 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

25 SQL tkprof SQL disk query + current call count cpu elapsed disk query current rows Parse Execute Fetch total 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

26 SQL tkprof I/O call count cpu elapsed disk query current rows Parse Execute Fetch total cpu elapsed I/O disk/ (query + current) tkprof I/O 1(disk / (query + current)) /( )) = % DB_BLOCK_BUFFERS p16 cpu elapsed I/O 26

27 SQL tkprof DB ACCOUNTS SELECT balance FROM accounts WHERE acc_num = call count cpu elapsed disk query current rows Parse Execute Fetch total query + current / rows 20 tkprof SQL rows = querycurrent / rows 20 acc_num SQL rows = SELECT balance FROM accounts WHERE acc_num= call count cpu elapsed disk query current rows Parse Execute Fetch total

28 IV. UTLBSTAT/UTLESTAT 28

29 IV. UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT UTLBSTAT/UTLESTAT 29

30 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

31 UTLBSTAT/UTLESTAT report.txt report.txt I/O report.txt report.txt UTLBSTAT/UTLESTAT 31

32 UTLBSTAT/UTLESTAT TIMED_STATISTICSTRUE utlestat.sql utlbstat.sql report.txt SQL*Plus Ver.8.1 sysdba rdbms admin utlbstat 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

33 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 CLUSTER INDEX OBJECT PIPE SQL AREA TABLE/PROCED TRIGGER SQL SQL EMP emp SQL SHARED_POOL_SIZE 33

34 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 CPU used when call started DBWR checkpoint buffers wri DBWR undo block writes SQL*Net roundtrips to/from background timeouts buffer is not pinned count bytes received via SQL*Net bytes sent via SQL*Net to c calls to get snapshot scn: calls to kcmgas calls to kcmgcs commit cleanouts commit cleanouts successful consistent gets cursor authentications db block changes db block gets enqueue releases enqueue requests execute count free buffer requested

35 logons cumulative messages received messages sent no work - consistent read g opened cursors cumulative parse count (hard) parse count (total) parse time cpu parse time elapsed physical reads physical reads direct physical writes physical writes direct physical writes non checkpo process last non-idle time recursive calls recursive cpu usage redo blocks written redo entries redo size redo synch writes redo wastage redo write time redo writes session connect time session logical reads session pga memory session pga memory max session uga memory session uga memory max sorts (disk) sorts (memory) sorts (rows) table fetch by rowid table scan blocks gotten table scan rows gotten table scans (short tables) total file opens user calls user commits

36 UTLBSTAT/UTLESTAT 1. CPU used by this session Statistic Total Per Transact Per Logon Per Second CPU used by this session parse time cpu consistent gets db block gets physical reads CPU used by this session CPU used by this sessionparse time cpu parse time cpu SQL SQL P16 90% DB_BLOCK_BUFFERS 1 physical reads/ (consistent gets + db block gets) = 1 - (18/ ( ) Statistic P16Total Per Transact Per Logon Per Second sorts (disk) sorts (memory) sorts (rows)

37 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 UNDO_SEGMENT0 RBS SVRMGR> select * fromstats$roll; UNDO_ TRANS_ TRANS_ UNDO_ SEGMENT_SIZE XACTS SHRINKS WRAPS SEGMENT TBL_GETS TBL_WAITS BYTES_WRITTEN BYTES

38 UTLBSTAT/UTLESTAT / 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 dc_segments dc_users dc_user_grants dc_objects dc_usernames dc_object_ids dc_profiles dc_histogram_de

39 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 RBS /disk2/rbs01.dbf SYSTEM /disk1/system01.dbf USERS /disk3/user01.dbf 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 nov-00 10:15:40 20-nov-00 10:18:30 39

40 V. STATSPACK 40

41 V. STATSPACK STATSPACK STATPACK(statistics package) BSTAT/ESTAT STATSPACK 41

42 42 STATSPACK Ver UTLBSTAT/UTLESTAT snap1 snap2 snap3 snap4 snap5 STATSPACK UTLBSTAT/UTLESTAT Ver SNAP SNAP2 SNAP2 SNAP4 SNAP1 SNAP5 3 STATSPACK (Ver Ver8.1.7 ) statspack.doc spdoc.txt STATSPACK

43 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

44 STATSPACK Version sysdba STATSPACK %ORACLE_HOME% rdbms admin statscre.sql; perfstat 2. perfstat SQL> connect perfstat/perfstat; SQL> execute statspack.snap; 3. rdbms admin statsrep.sql; STATSPACK 1. STATSPACK SQL*Plussysdba Ver statscre.sql Ver 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 statsrep.sql Enter ending Snap Id : 4; Ver spreport.sql Sp_id Sp_id Enter name of output file[st_1_2]: st_1_4; 44

45 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

46 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

47 STATSPACK Summary CPU/ Summary Summary BSTAT/ ESTAT BSTAT/ESTAT 1. DB Name DB Id Instance Inst Num Release OPS Host db db NO HSAITO01 2. Snap Length Start Id End Id Start Time End Time (Minutes) Dec-00 17:12:07 25-Dec-00 17:19: init.ora Cache Sizes ~~~~~~~~~~~ db_block_buffers: db_block_size : 8192 log_buffer: shared_pool_size:

48 STATSPACK Summary 4. Per Second Per Transaction Load Profile ~~~~~~~~~~~~ Per Second Per Transaction Redo size: , REDO Logical reads: , Block changes: Physical reads: Physical writes: User calls: Parses: , Hard parses: Sorts: Transactions: 0.00 Rows per Sort: % Pct Blocks changed / Read: 9.56 % Recursive Call Pct: % Rollback / transaction Pct:

49 STATSPACK Summary 100 SQL Redo db_block_buffers shared_pool_size log_buffer Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait Ratio: Buffer Hit Ratio: Library Hit Ratio: SQL Redo NoWait Ratio: Redo In-memory Sort Ratio: Soft Parse Ratio: Latch Hit Ratio: Shared Pool Statistics Begin End ~~~~~~ ~~~~~ Memory Usage Pct: Pct SQL with executions > 1: SQL Pct Memory for SQL w/exec > 1: SQL 49

50 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 db file sequential read control file parallel write READ free buffer waits, buffer busy wait db_block_buffers I/O write complete waits DBWR I/O SQL 50

51 STATSPACK 51

52 VI. Oracle Enterprise Manager 52

53 V. Oracle Enterprise Manager Oracle Enterprise Manager Oracle Diagnostics Pack Oracle Tuning Pack 53

54 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 Oracle Diagnostics PackOracle Tuning Pack 54

55 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

56 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

57 Oracle Oracle8i days SQL days 1 day day INDEX day INDEX day TEL FAX 57

58 Oracle Oracle8Oracle8iNet8 58