untitled

Transcription

1 Statspack Eygle Oracle Statspack Oracle8.1.6 Oracle, DBA Oracle Statspack Oracle Statspack DBA Statspack $ORACLE_HOME/RDBMS/ADMIN ORACLE8.1.6, stat ORACLE8.1.7, sp Oracle8.1.6 Statspack Oracle80~Oracle Statspack statscbps.sql v$buffer_pool_statistics tar OTN Statspack UTLBSTAT.SQL UTLESTAT.SQLBSTAT/ESTAT UTLBSTAT V$ UTLESTAT statspack Statspack Statspack BSTAT/ESTAT Statspack Oracle Statspack Report.

2 . Statspack 1. job_queue_processes 0 () () SQL> alter system set job_queue_processes = 6; System altered Oracle9i both,( spfile 9i pfile 8i ): SQL> alter system set job_queue_processes = 6 scope=both; 2. timed_statistics Timed_statistics SQL> alter system set timed_statistics = true; System altered SQL> timed_statistics statspack system false

3 . Statspack Statspack internal SYSDBA(connect / as sysdba) telnet Oracle8.1.6 statscre.sql; Oracle8.1.7 spcreate.sql $ORACLE_HOME/RDBMS/ADMIN D:\oracle\ora81\RDBMS\ADMIN>sqlplus internal SQL*Plus: Release Production on : Oracle9i internal sys sysdba : D:\oracle\ora92\rdbms\admin>sqlplus "/ as sysdba" SQL*Plus: Release Production on :18: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. : Oracle9i Enterprise Edition Release Production With the Partitioning, Oracle Label Security, OLAP Oracle Data Mining options JServer Release Production SQL>

4 500M : 100M,, 500M LMT SQL> create tablespace perfstat 2 datafile 'd:\oracle\oradata\eygle\perfstat.dbf' 3 size 500M 4 extent management local; SQL> () E:\Oracle\ora92\rdbms\admin>dir /w sp* E ACC E:\Oracle\ora92\rdbms\admin spauto.sql spcpkg.sql spcreate.sql spctab.sql spcusr.sql spdoc.txt spdrop.sql spdtab.sql spdusr.sql sppurge.sql sprepins.sql spreport.sql sprepsql.sql sptrunc.sql spuexp.par spup816.sql spup817.sql spup90.sql , ,146,565,120 Statspack, perfstat Specify PERFSTAT user's default tablespace : statspack, ( 123 ) :perfstat,... Creating PERFSTAT user... Choose the PERFSTAT user's password. Not specifying a password will result in the installation FAILING Specify PERFSTAT password perfstat_password : 123

5 123 PL/SQL create user perfstat identified by 123 * ERROR 1 : ORA-00988:. Creating Package STATSPACK... Creating Package Body STATSPACK... NOTE: SPCPKG complete. Please check spcpkg.lis for any errors..lis SQL> host dir *.lis D D:\oracle\ora81\RDBMS\ADMIN : spcpkg.lis :25 2,276 spctab.lis :25 3,965 spcusr.lis :23 1,187 spdtab.lis : spdusr.lis 5 7, ,965,304,832 SQL> host find ORA- *.lis SQL> host find "err" *.lis SPAUTO.LIS SPCPKG.LIS SPCPKG complete. Please check spcpkg.lis for any errors SPCTAB.LIS SPCTAB complete. Please check spctab.lis for any errors SPCUSR.LIS SPCUSR complete. Please check spcusr.lis for any errors SPDTAB.LIS UNIX

6 $ ls *.lis spauto.lis spcpkg.lis spctab.lis spcusr.lis spdtab.lis spdusr.lis $ grep ORA- *.lis $ grep err *.lis spcpkg.lis:spcpkg complete. Please check spcpkg.lis for any errors. spctab.lis:spctab complete. Please check spctab.lis for any errors. spcusr.lis:spcusr complete. Please check spcusr.lis for any errors. spdtab.lis:spdtab complete. Please check spdtab.lis for any errors. spdusr.lis:spdusr complete. Please check spdusr.lis for any errors. spdrop.sql spcreate.sql SQL*Plus, SYSDBA off; NOTE: SPDUSR complete. Please check spdusr.lis for any errors. SQL>

7 . Statspack statspack.snap spreport.sql SQL>execute statspack.snap PL/SQL procedure successfully completed. SQL>execute statspack.snap PL/SQL procedure successfully completed. SQL> exec statspack.snap; BEGIN statspack.snap; END; * ERROR at line 1: ORA-01401: inserted value too large for column ORA-06512: at "PERFSTAT.STATSPACK", line 978 ORA-06512: at "PERFSTAT.STATSPACK", line 1612 ORA-06512: at "PERFSTAT.STATSPACK", line 71 ORA-06512: at line 1 Oracle BugBug Bug , spcpkg.sql $ORACLE_HOME/rdbms/admin/spcpkg.sql "substr" "substrb" select l_snap_id, p_dbid, p_instance_number, substr(sql_text,1,31) substr, byte.substrb byte statpack top 10 sql 31, SQL 31

8 . Statspack spatuo.sql spauto.sql dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'hh'), 'trunc(sysdate+1/24,''hh'')', TRUE, :instno); job /24 HH 1/48 MI 1/144 MI 1/288 MI spauto.sql dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'mi'), 'trunc(sysdate+1/48,''mi'')', TRUE, :instno); spauto 30 spauto.lis PL/SQL procedure successfully completed. Job number for automated statistics collection for this instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Note that this job number is needed when modifying or removing the job: JOBNO Job queue process ~~~~~~~~~~~~~~~~~ Below is the current setting of the job_queue_processes init.ora parameter - the value for this parameter must be greater than 0 to use automatic statistics gathering: NAME TYPE VALUE job_queue_processes integer 5 Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is:

9 JOB NEXT_DATE NEXT_SEC AUG-03 16:00:00 1 statspack statspack

10 . spreport.sql DB Id DB Name Inst Num Instance EYGLE 1 eygle Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level Comment eygle EYGLE : : :1 5 3 eygle EYGLE : :3 5 3 Specify the Begin End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ begin_snap : 1 Begin Snapshot Id specified: 1 end_snap : 100 End Snapshot Id specified: 100 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_1_100. To use this name, press <return> to continue, otherwise enter an alternative. report_name : rep1205.txt Using the report name rep1205.txt ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID STATSPACK report for DB Name DB Id Instance Inst Num Release OPS Host EYGLE eygle NO AM-SERVER

11 :ela := ; * ERROR 4 : ORA-06550: 4, 17 : PLS-00103: ";" (-+modnotnull<an identifier> <a double-quoted delimited-identifier><a bind variable>avg countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute foralltimetimestampintervaldate <a string literal with character set specification> <a number><a single-quoted SQL string> "null" ";" ORA-06550: 6, 16 : PLS-00103: ";" (-+modnotnull<an identifier> <a double-quoted delimited-identifier><a bind variable>avg countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute foralltimetimestampintervaldate <a stri statspack report

12 . : SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs; JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL PERFSTAT :14:33: :43:00 trunc(sysdate+1/144,'mi') SQL> execute dbms_job.remove('22') PL/SQL procedure successfully completed job job statspack

13 . stats$snapshot SQL> select max(snap_id) from stats$snapshot; MAX(SNAP_ID) SQL> delete from stats$snapshot where snap_id < = 166; 143 rows deleted snap_id SQL> select a.object_id,a.oracle_username,b.object_name from v$locked_object a,dba_objects b where a.object_id = b.object_id / OBJECT_ID ORACLE_USERNAME OBJECT_NAME PERFSTAT SNAP$ PERFSTAT STATS$LIBRARYCACHE PERFSTAT STATS$ROLLSTAT PERFSTAT STATS$SGA PERFSTAT STATS$PARAMETER PERFSTAT STATS$SQL_STATISTICS PERFSTAT STATS$SQL_SUMMARY PERFSTAT STATS$ENQUEUESTAT PERFSTAT STATS$WAITSTAT PERFSTAT STATS$BG_EVENT_SUMMARY PERFSTAT STATS$SYSTEM_EVENT PERFSTAT STATS$SYSSTAT PERFSTAT STATS$SGASTAT PERFSTAT STATS$ROWCACHE_SUMMARY PERFSTAT STATS$BUFFER_POOL_STATISTICS PERFSTAT STATS$LATCH_MISSES_SUMMARY PERFSTAT STATS$SNAPSHOT PERFSTAT STATS$FILESTATXS PERFSTAT STATS$LATCH 174 PERFSTAT JOB$ 20 rows selected Oracle Truncate : sptrunc.sql (8i9i ) statspack truncate table STATS$FILESTATXS; truncate table STATS$LATCH; truncate table STATS$LATCH_CHILDREN; truncate table STATS$LATCH_MISSES_SUMMARY; truncate table STATS$LATCH_PARENT;

14 truncate table STATS$LIBRARYCACHE; truncate table STATS$BUFFER_POOL_STATISTICS; truncate table STATS$ROLLSTAT; truncate table STATS$ROWCACHE_SUMMARY; truncate table STATS$SGA; truncate table STATS$SGASTAT; truncate table STATS$SYSSTAT; truncate table STATS$SESSTAT; truncate table STATS$SYSTEM_EVENT; truncate table STATS$SESSION_EVENT; truncate table STATS$BG_EVENT_SUMMARY; truncate table STATS$WAITSTAT; truncate table STATS$ENQUEUESTAT; truncate table STATS$SQL_SUMMARY; truncate table STATS$SQL_STATISTICS; truncate table STATS$SQLTEXT; truncate table STATS$PARAMETER; delete from STATS$SNAPSHOT; delete from STATS$DATABASE_INSTANCE; commit; Delete SQL

15 . 1 Statspack spuexp.par file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=perfstat consistent=y exp userid=perfstat/my_perfstat_password parfile=spuexp.par 2 spdrop.sql : spdtab.sql spdusr.sql 3Oracle92 1 statspack, SYSDBA, Schema : SPUP90.SQL: SPUP817.SQL: Statspack 8.1.7, SPUP816.SQL: Statspack 8.1.6, SPUP817.SQL. 2sprepsql.sql SQL Hash SQL

16 . STATSPACK Statspack level threshold 1level Statspack 5 a.level 0: SGA b.level 5: SQL level0 SQL stats$sql_summary c.level 10: level5 stats$lathc_children Oracle support statspack SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=> true ); 0 i_modify_parameter SQL>execute statspack.snap(i_snap_level=>10); 2 stats$sql_summary SQL SQL stats$sql_summary Statspack stats$statspack_parameter a. executions_th SQL ( 100) b. disk_reads_tn SQL 1000 c. parse_calls_th SQL 1000 d. buffer_gets_th SQL statspack.modify_statspack_parameter SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000;

17 . statistic#, 9i, select substr(to_char(snap_time,'yyyy-mm-dd HH24:MI:SS'),12), (newreads.value-oldreads.value) reads, (newwrites.value-oldwrites.value) writes from perfstat.stats$sysstat oldreads, perfstat.stats$sysstat newreads, perfstat.stats$sysstat oldwrites, perfstat.stats$sysstat newwrites, perfstat.stats$snapshot sn where newreads.snap_id = sn.snap_id newwrites.snap_id = sn.snap_id oldreads.snap_id = sn.snap_id-1 oldwrites.snap_id = sn.snap_id-1 oldreads.statistic# = 40 newreads.statistic# = 40 oldwrites.statistic# = 41 newwrites.statistic# = 41 (newreads.value-oldreads.value) > 0 (newwrites.value-oldwrites.value) > 0 /

18 select substr(to_char(snap_time,'yyyy-mm-dd HH24:MI'),12), round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) / ((a.value-e.value)+(b.value-f.value))) "BUFFER HIT RATIO" from perfstat.stats$sysstat a, perfstat.stats$sysstat b, perfstat.stats$sysstat c, perfstat.stats$sysstat d, perfstat.stats$sysstat e, perfstat.stats$sysstat f, perfstat.stats$sysstat g, perfstat.stats$snapshot sn where a.snap_id = sn.snap_id

19 b.snap_id = sn.snap_id c.snap_id = sn.snap_id d.snap_id = sn.snap_id e.snap_id = sn.snap_id-1 f.snap_id = sn.snap_id-1 g.snap_id = sn.snap_id-1 a.statistic# = 39 e.statistic# = 39 b.statistic# = 38 f.statistic# = 38 c.statistic# = 40 g.statistic# = 40 d.statistic# = 41 Bufferbuffer hit ratio Oracle, 90% IO buffer 100%, 95%

20 . OracleOracle Oracle Oracle Oracle8i200,Oracle9i360 idlenon-idle Oracle,, : dispatcher timer lock element cleanup Null event parallel query dequeue wait parallel query idle wait - Slaves pipe get PL/SQL lock timer pmon timer- pmon rdbms ipc message slave wait smon timer SQL*Net break/reset to client SQL*Net message from client SQL*Net message to client SQL*Net more data to client virtual circuit status client message Oracle, : db file scattered read db file sequential read buffer busy waits free buffer waits enqueue latch free log file parallel write log file sync

21 1. db file scattered read-db (scattered) Buffer Cache Oracle CBO Oracle RBO Oracle LRULeast Recently Usedcold end Cache v$session_longops 6 : DB Name DB Id Instance Inst Num Release OPS Host K k NO k statspack Snap Length Start Id End Id Start Time End Time (Minutes) Feb-03 10:00:11 25-Feb-03 15:00: Cache Sizes ~~~~~~~~~~~ db_block_buffers: db_block_size: 8192 log_buffer: shared_pool_size: Top 5 Wait Events

22 ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time db file scattered read 16,842,920 3,490, latch free 844,272 3,270, buffer busy waits 114, , db file sequential read 2,067, , enqueue , Top db file scattered read 10 SQL SQL SQL ordered by Gets for DB: K2 Instance: k2 Snaps: Gets % of Buffer Gets Executes per Exec Total Hash Value SQL statement ,480, , SELECT "PROCESS_REQ"."WORK_ID", "PROCESS_REQ"."STOCK_NO", "PROCESS_R 3,784, , SELECT * FROM FIND_LATER_WO ORDER BY NOTE,ORDER_NO 1,200, , SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM" 923, , SELECT "ITEM_STOCK"."ITEM_NO", "ITEM_STOCK"."STOCK_NO", 921, , SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM" 911, , SELECT "LISTS"."ITEM_NO", "LISTS"."SUB_ITEM", "LISTS" 831, , SELECT "GROUP_OPER"."ITEM_NO", "GROUP_OPER"."PROCESS_ID", 802, , SELECT "LISTS"."ITEM_NO", "LISTS"."SUB_ITEM", "ITEM".

23 800, , SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM" 666, , SELECT "ITEM_STOCK"."ITEM_NO", "ITEM_STOCK"."STOCK_NO",.. Buffer Gets SQL Hash Value v$sql_text SQL v$session_longops : TARGET COUNT(*) SA.PPBT_GRAPHOBJTABLE 418 SA.PPBT_PPBTOBJRELATTABLE 53 SQL ( v$session_longops OPNAME) PPBT_GRAPHOBJTABLE PPBT_PPBTOBJRELATTABLE : SQL> select count(*) from SA.PPBT_PPBTOBJRELATTABLE; COUNT(*) SQL> select count(*) from SA.PPBT_GRAPHOBJTABLE; COUNT(*) ! 2. db file sequential read-db ()

24 3. Free Buffer- Buffer Free SQL Buffer Cache DB_BUFFER_CACHE Free Buffer DBWR DBWR, IO 4. Buffer Busy- unshareablebuffer cachebuffer Busy Wait1% V$WAITSTAT freelistoracle8i DMTfreelist groups 8.1.6freelists8.1.6 feelistscompatible SQL> alter table sp_item storage (freelists 2); undo headerrollback segment undo block (consistent read)db_cache_size data block pctfree"" Locally Managed Tablespaces ""pctfree,

25 DML (insert/update/ delete)oracle ITLinitransITL v$waitstat: Oracle9i :ASSMSegment Space Management Auto Oracle ASSM LMT Oracle freelist buffer busy wait Oracle9i Oracle ASSM DML Oracle freelist Oracle9i Buffer Busy wait 5. latch free-latch latchsga latch latchlatch(latch free miss ) latch library cache latchredo allocation latch cache buffers LRU chainbuffer cache""cache buffers chain

26 latchbugmetalinkbug latch miss ratios0.5% OraclelatchCSMA/CDlatch (willing-to-wait)latch,latch,,_spin_countlatch,.8i/9i_spin_count=2000 : SQL> select addr,name 2 from v$latch_children 3 where name like '%chain%' 4 / ADDR NAME C0558AE8 enqueue hash chains C0558A58 enqueue hash chains C05589C8 enqueue hash chains C enqueue hash chains C05588A8 enqueue hash chains C enqueue hash chains C enqueue hash chains C05586F8 enqueue hash chains C1FC7830 cache buffers lru chain C1FC7448 cache buffers lru chain C1FC7060 cache buffers lru chain ADDR NAME C1FC6C78 cache buffers lru chain C247D970 cache buffers chains C247C8C0 cache buffers chains C247B810 cache buffers chains C247A760 cache buffers chains C24796B0 cache buffers chains C cache buffers chains C cache buffers chains C24764A0 cache buffers chains C24753F0 cache buffers chains

27 C cache buffers chains. SQL> col segment_name for a40 SQL> set linesize 120 SQL> l 1 select /*+ ordered */ 2 e.owner '.' e.segment_name segment_name, 3 e.extent_id extent#, 4 x.dbablk - e.block_id + 1 block#, 5 x.tch, 6 l.child# 7 from 8 sys.v$latch_children l, 9 sys.x$bh x, 10 sys.dba_extents e 11 where 12 l.name = 'cache buffers chains' 13 l.sleeps > &sleep_count 14 x.hladdr = l.addr 15 e.file_id = x.file# 16* x.dbablk between e.block_id e.block_id + e.blocks - 1 SQL> / Enter value for sleep_count: old 13: l.sleeps > &sleep_count new 13: l.sleeps > SEGMENT_NAME EXTENT# BLOCK# TCH CHILD# SYS.I_ACCESS SYS.I_DEPENDENCY SYS.I_DEPENDENCY SYS.I_DEPENDENCY SYS.I_DEPENDENCY HSCONTENT.HSIDX_INFO_PARAM HSCONTENT.HSIDX_INFO_PARAM HSCONTENT.HSIDX_INFO_PARAM HSCONTENT.HSIDX_INFO_PARAM HSCONTENT.HSIDX_INFO_PARAM HSCONTENT.PK_HS_DLF_OBJECT HSCONTENT.PK_HS_DLF_OBJECT HSCONTENT.PK_HS_DLF_OBJECT HSCONTENT.PK_HS_DLF_OBJECT HSCONTENT.PK_HS_DLF_OBJECT HSCONTENT.PK_HS_DLF_OBJECT

28 . 6. Enqueue enqueue enqueuefifo EnqueueSTHW TX TM ST enqueue(dmt) LMT. DB Name DB Id Instance Inst Num Release OPS Host DB aaa NO server Snap Id Snap Time Sessions Begin Snap: :10:16 46 End Snap: :40:05 46 Elapsed: (mins) Statspackreport, Cache Sizes ~~~~~~~~~~~ db_block_buffers: log_buffer: db_block_size: shared_pool_size: Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time enqueue 53,793 16,192, rdbms ipc message 19,999 5,927, pmon timer 1, , smon timer , SQL*Net message from client 94, , StatspackTop 5 enqueue 4enqueue (mins)enqueue16,192,686cs,45

29 Statspack:

30 HW enqueue TXenqueueTX enqueue commit/rollback enqueue rowid enqueue ITL initrans/maxtransitl pctfree TM enqueuedml 7. Log Buffer Space- log buffer LGWR log switch redo log buffer I/O log buffer statspack Top5 : Top 5 Wait Events ~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time log file parallel write 1,436,993 1,102,

31 log buffer space 16, , log file sync 1,413, , control file parallel write 329, , db file scattered read 425, , Log File Switch- (commit)"" Log file Switch : 9. log file sync- LGWR LGWR RAID RAID 5RAID5 /raw device 10. log file single write

32 11. log file parallel write log buffer redo redo log ( log file sync) Log group flush log buffer I/O ( IO IO SLAVE redo log file member,) log file sync log file 12. control file parallel write- server I/O () IO IO 13. / I/O I/O

33 14. direct path write- I/O I/O ( ) Local report : DB Name DB Id Instance Inst Num Release OPS Host DB db NO IBM Snap Length Start Id End Id Start Time End Time (Minutes) :32: :54: Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time direct path write 98,631 3, log file switch completion 62 2, direct path read 37,434 1, db file sequential read control file sequential read 3, Top 5 direct path write : Instance Activity Stats for DB: DB Instance: db Snaps: 65 - Statistic Total per Second per Trans

34 sorts (disk) sorts (memory) sorts (rows) 2,804,580 2, , sort disk, : File IO Statistics for DB: GHCXSDB Instance: ghcxsdb Snaps: Tablespace Filename Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg Wait (ms) PERFSTAT D:\ORACLE\ORADATA\PERFSTAT.DBF RBS D:\ORACLE\ORADATA\GHCXSDB\RBS01.DBF ,399 0 SYSTEM D:\ORACLE\ORADATA\GHCXSDB\SYSTEM01.DBF TEMP D:\ORACLE\ORADATA\GHCXSDB\TEMP01.DBF 223, ,303 0 sort_area_size : slave wait- Slave Wait Slave I/O 16. Idle Event- stats$idle_event Top 5 :

35 SQL> select * from stats$idle_event; EVENT smon timer pmon timer rdbms ipc message Null event parallel query dequeue pipe get client message SQL*Net message to client SQL*Net message from client SQL*Net more data from client dispatcher timer virtual circuit status lock manager wait for remote message PX Idle Wait wakeup time manager 15 rows selected.

36

37

38