Report for SnapID 905 - 1118 StatsPack Report for oradb

StatsPack Report for oradb


Common Instance Information
Hostname:buzz
Instance:oradb
Version:9.2.0.5.0
Cluster:NO
FileSize (Data+Log) 2,120.00 MB
Startup / Uptime16.07.2005 11:19 / 27.15 d
Report generated:12.08.2005 14:50

SnapShot Info Help
 Snap ID Snap TimeSessionsCurs/SessComment
Start90516.07.2005 11:199 4.22 
End111825.07.2005 08:0013 746.00 
Elapsed: 12,760.25 min

Load Profile
 Per SecondPer Transaction
Redo Size 587.33 1,939.65
Logical Reads 86.40 285.34
Block Changes 3.56 11.76
Physical Reads 16.87 55.70
Physical Writes 0.09 0.31
User Calls 5.72 18.89
Parses 0.52 1.73
Hard Parses 0.01 0.04
Sorts 0.76 2.51
Logons 0.01 0.03
Executes 6.79 22.42
Transactions 0.30 


Instance Efficiency Percentages (Target: 100%)
EventEfficiency (%)Comment
Buffer Nowait
100.00 If this ratio is low, check the Buffer Wait Stats section for more detail on which type of block is being contended for.
Redo Nowait 100.00A value close to 100% indicates minimal time spent waiting for redo logs to become available, either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up.
Buffer Hit Help 80.66 A low buffer hit ratio does not necessarily mean the cache is too small: it may very well be that potentially valid full-table-scans are artificially reducing what is otherwise a good ratio. A too-small buffer cache can sometimes be identified by the appearance of write complete waits event indicating hot blocks (i.e. blocks still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event.
In-Memory Sort 99.99A too low ratio indicates too many disk sorts appearing. One possible solution could be increasing the sort area/SGA size.
Library Hit 99.70 A low library hit ratio could imply that SQL is prematurely aging out of a too-small shared pool, or that non-shareable SQL is being used. If the soft parse ratio is also low, check whether there is a parsing issue.
Soft Parse Help 97.97When the soft parse ratio falls much below 80%, investigate whether you can share SQL by using bind variables or force cursor sharing. But before drawing any conclusions, compare the soft parse ratio against the actual hard and soft parse rates shown in the Loads Profile. Furthermore, investigate the number of Parse CPU to Parse Elapsed below.
Execute to Parse Help 92.28A low value here indicates that there is no much re-usable SQL (see Soft Parse for possible actions). Negative values connote that there are more Parses than Executes, which could point to syntactically incorrect SQL statements (or missing privileges).
Latch Hit 100.00 A low value for this ratio indicates a latching problem, whereas a high value is generally good. However, a high latch hit ratio can artificially mask a low get rate on a specific latch. Cross-check this value with the Top 5 Wait Events to see if latch free is in the list, and refer to the Latch sections of this report.
Parse CPU to Parse Elapsed 91.92See Soft Parse above.
Non-Parse CPU 95.84 


Top 10 Wait Events Help
Ordered by Wait Time (desc), Waits (desc)
Start with these topmost events and find out about details in the corresponding block, e.g. for "db file * read" check the TableSpace IO (and File IO) blocks to identify the possibly affected schemas, and then the SQL Statements by Reads (and Wait Objects) to find out what statements (and/or objects) may need some tuning , for "enqueue" waits look up the Enqueue Activity section of this document. If the CPU is indicated as a bottleneck, check the SQL Statements by Gets. Then continue the same with the next block, All Wait Events
EventWaitsWait TimeTotal Wt Time
log file sync 231,74412:10.758 31.06%
db file scattered read 1,998,0329:45.361 22.36%
control file parallel write 249,2068:25.192 21.50%
log file parallel write 248,1705:34.339 11.68%
db file parallel write 8,4352:32.043 3.92%
db file sequential read 173,7221:26.983 3.66%
direct path read (lob) 27,0361:43.166 1.84%
SQL*Net more data to client 970,90921.073 0.90%
SQL*Net message from dblink 34319.864 0.80%
process startup 21112.237 0.52%


Top 5 SQL ordered by Gets
End Buffer Gets Treshold: 10000

Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100.
If your primary tuning goal is reducing resource usage, start tuning these statements/objects (CPU) plus SQL by Reads (File IO).

Buffer GetsExecutions Gets per ExecTotalCPU TimeElapsed TimeHash Value
2,925,319 29,846 98.0 4.4% 1:53.2201:45.1741686291633
  begin delete ant where rowid in ( select ANT . rowid from ANT , AUF where AUF . STSID not in ( 'EIN' , 'FGB' , 'IAB' ) and AUF . AUTID = 1 and AUF . AUFID = ANT . AUFID and ANT . STSID = 'EIN' ) ; :COUNT := sql % rowcount ; end ;
2,924,320 29,840 98.0 4.4% 1:48.8401:41.8304177894216
  DELETE ANT WHERE ROWID IN ( SELECT ANT . ROWID FROM ANT , AUF WH ERE AUF . STSID NOT IN ( 'EIN' , 'FGB' , 'IAB' ) AND AUF . AUTID = 1 AND AUF . AUFID = ANT . AUFID AND ANT . STSID = 'EIN' )
 
OperationObject OptimizerCostCPUCostIOCostSize
DELETE STATEMENT
.RULE    
 
. DELETE
.     
 
. . NESTED LOOPS
.     
 
. . . VIEW
.     
 
. . . . SORT UNIQUE
.     
 
. . . . . NESTED LOOPS
.     
 
. . . . . . TABLE ACCESS FULL
FRODO.AUFANALYZED    
 
. . . . . . INDEX RANGE SCAN
FRODO.XIE4ANTANALYZED    
 
. . . TABLE ACCESS BY USER ROWID
FRODO.ANTANALYZED    
 
1,469,542 2,002 734.0 2.2% 20.19020.6543324404337
  select 'datapoint: '||count(*) from all_tab_privs where grant ee = 'PATRON'
992,635 76,355 13.0 1.5% 18.1502:57.0122276398955
  begin :DONE := pw_mgr . watch_processes ; end ;
921,044 229,056 4.0 1.4% 0.3705:56.7721712657913
  begin pw_common . update_timestamp ( :PROCESS_ID ) ; end ;


Top 5 SQL ordered by Reads
End Disk Reads Treshold: 1000
If your primary tuning goal is to reduce resource usage, start by tuning these statements/objects (File IO) plus SQL by Gets (CPU).
Pysical ReadsExecutionsReads per ExecTotal CPU TimeElapsed TimeHash Value
3,169 1 3,169.0 0.2% 2.9103.8271971852235
  SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$SQL_SUMMARY ".* FROM "PERFSTAT"."STATS$SQL_SUMMARY"
 
OperationObject OptimizerCostCPUCostIOCostSize
SELECT STATEMENT
.CHOOSE    
 
. TABLE ACCESS FULL
PERFSTAT.STATS$SQL_SUMMARY     
 
3,141 1 3,141.0 0.1% 1.2504.739973039809
  SELECT /*+NESTED_TABLE_GET_REFS+*/ "FRODO"."IMP".* FROM "FRODO "."IMP"
 
OperationObject OptimizerCostCPUCostIOCostSize
SELECT STATEMENT
.CHOOSE 852  
 
. TABLE ACCESS FULL
FRODO.IMPANALYZED 852 110,524,207 834
14,875 k
2,372 1 2,372.0 0.0% 1.8701.9423835363457
  SELECT /*+NESTED_TABLE_GET_REFS+*/ "FRODO"."INF_BACKUP".* FROM "FRODO"."INF_BACKUP"
 
OperationObject OptimizerCostCPUCostIOCostSize
SELECT STATEMENT
.CHOOSE 655  
 
. TABLE ACCESS FULL
FRODO.INF_BACKUPANALYZED 655 68,028,356 644
15,970 k
2,279 1 2,279.0 0.1% 2.5003.7781898264937
  SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$LATCH".* FR OM "PERFSTAT"."STATS$LATCH"
 
OperationObject OptimizerCostCPUCostIOCostSize
SELECT STATEMENT
.CHOOSE    
 
. TABLE ACCESS FULL
PERFSTAT.STATS$LATCH     
 
1,928 1 1,928.0 0.0% 1.0502.5683658914384
  SELECT /*+NESTED_TABLE_GET_REFS+*/ "PERFSTAT"."STATS$SYSSTAT".* FROM "PERFSTAT"."STATS$SYSSTAT"
 
OperationObject OptimizerCostCPUCostIOCostSize
SELECT STATEMENT
.CHOOSE    
 
. TABLE ACCESS FULL
PERFSTAT.STATS$SYSSTAT     
 


Top 5 SQL ordered by Executions
End Executions Treshold: 100
Start with tuning these statements if your primary goal is to increase the response time.
ExecutionsRows ProcessedRows per ExecCPU per Exec Elap per ExecHash Value
459,461 0 0.00.000 0.0001053795750
  COMMIT
229,058 229,058 1.00.000 0.000876411139
  begin pw_common . set_process_state ( :PROCESS_ID , :STATUS ) ; end ;
229,056 4 0.00.000 0.000694256415
  UPDATE PRO SET PRO.STSID = :B2, PROAENAM=SYSDATE, PROAENDURCH=US ER, PROTSN=DECODE(PROTSN,999999999,0,PROTSN+1) WHERE PRO.STSID ! = :B2 AND PRO.PROID = :B1
 
OperationObject OptimizerCostCPUCostIOCostSize
UPDATE STATEMENT
.RULE    
 
. UPDATE
.     
 
. . TABLE ACCESS BY INDEX ROWID
FRODO.PROANALYZED    
 
. . . INDEX UNIQUE SCAN
FRODO.XPKPROANALYZED    
 
229,056 229,056 1.00.000 0.0011712657913
  begin pw_common . update_timestamp ( :PROCESS_ID ) ; end ;
229,054 229,053 1.00.000 0.000853794241
  select to_char(sysdate,'DD.MM.YYYY') ,to_char(sysdate,'HH24:MI:S S') into :b0,:b1 from dual


Top 5 SQL ordered by Parse Calls
End Parse Calls Treshold: 1000
Consider tuning these statements/objects, if the percentage of CPU used for parsing is high. Currently, parsing takes avg. 4.16%% of all CPU usage by all sessions.
Parse CallsExecutionsTotal ParsesHash Value
29,846 29,846 0.07%2609822319
  lock table AUF in exclusive mode
 
OperationObject OptimizerCostCPUCostIOCostSize
DDL STATEMENT
.RULE    
 
. TABLE ACCESS FULL
FRODO.AUFANALYZED    
 
27,349 27,349 0.07%3867936055
  commit
6,626 6,626 0.02%3997906522
  select user# from sys.user$ where name = 'OUTLN'
6,612 6,612 0.02%1356713530
  select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
6,373 6,373 0.02%2176627595
  ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'GERMA NY' NLS_CURRENCY= '¤' NLS_ISO_CURRENCY= 'GERMANY' NLS_NUMERIC_CH ARACTERS= ',.' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD.MM .YYYY' NLS_DATE_LANGUAGE= 'GERMAN' NLS_SORT= 'BINARY' TIME_ZONE = '+02:00' NLS_DUAL_CURRENCY = '¤' NLS_TIME_FORMAT = 'HH24:MI:SS XFF' NLS_TIMESTAMP_FORMAT = 'DD.MM.RR HH24:MI:SSXFF' NLS_TIME_TZ _FORMAT = 'HH24:MI:SSXFF TZR' NLS_TIMESTAMP_TZ_FORMAT = 'DD.MM.R R HH24:MI:SSXFF TZR'


TableSpace IO Summary Statistics Help
Ordered by IOs (Reads + Writes) desc
ReadSize/WriteSize report the physical IO Size
TableSpaceReadsAvgReads/sAvgRd (ms)Avg Blks/Rd WritesAvg Wrt/sReadSizeWriteSizeBuffer WaitsAvg Buf Wt (ms)
DATA 2,161,985 2.82 0.3 5.9 7,378 0.01 97.3 G 57.6 M 521 0.2
TOOLS 18,252 0.02 3.8 6.3 14,660 0.02 900.0 M 114.5 M 0 0.0
ROLLBACK 60 0.00 11.0 1.0 24,467 0.03 480.0 K 191.1 M 26 0.0
SYSTEM 9,498 0.01 5.6 1.9 631 0.00 137.4 M 4.9 M 1 10.0
INDEX1 6,470 0.01 1.6 1.3 1,306 0.00 65.6 M 10.2 M 13 6.2
TEMP 3,610 0.00 346.7 5.1 2,665 0.00 144.0 M 158.3 M 0 0.0
MONITORING 2,852 0.00 0.5 1.0 2,089 0.00 22.4 M 16.3 M 0 0.0

File IO Summary Statistics Help
Ordered by TableSpace, File
ReadSize/WriteSize report the physical IO Size
TableSpaceFilenameReadsAvg Reads/sAvgRd (ms) Avg Blks/RdWritesAvg Wrt/sRead SizeWrite Size Buffer WaitsAvg Buf Wt (ms)
DATA/database/oradb/data_1.dbf 2,161,985 2.82 0.3 5.9 7,378 0.01 97.3 G 57.6 M 521 0.2
INDEX1/database/oradb_index/index_1.dbf 6,470 0.01 1.6 1.3 1,306 0.00 65.6 M 10.2 M 13 6.2
MONITORING/database/oradb/monitoring_1.dbf 2,852 0.00 0.5 1.0 2,089 0.00 22.4 M 16.3 M 0 0.0
ROLLBACK/database/oradb/rollback_1.dbf 60 0.00 11.0 1.0 24,467 0.03 480.0 K 191.1 M 26 0.0
SYSTEM/database/oradb/system_1.dbf 9,498 0.01 5.6 1.9 631 0.00 137.4 M 4.9 M 1 10.0
TEMP/database/oradb/temp_1.dbf 3,610 0.00 346.7 5.1 2,665 0.00 144.0 M 158.3 M 0 0.0
TOOLS/database/oradb/tools_1.dbf 18,252 0.02 3.8 6.3 14,660 0.02 900.0 M 114.5 M 0 0.0

Redo Log Sizes
at the time the report was generated
Group#MembersFileSize
12 10.0 M
22 10.0 M
32 10.0 M


Top 10 Logical Reads per Segment
TableSpaceObjectTypeLogReadsRatio
DATAFRODO.BEDTABLE 11,347,264 17.35%
INDEX1FRODO.XIE4ANTINDEX 7,179,920 10.98%
DATAFRODO.INFTABLE 6,655,600 10.18%
INDEX1FRODO.XPKZTLINDEX 6,551,216 10.02%
INDEX1FRODO.XPKINFINDEX 6,479,648 9.91%
DATAFRODO.ZTLTABLE 3,366,976 5.15%
DATAFRODO.PROTABLE 1,919,024 2.93%
DATAFRODO.BEATABLE 1,758,032 2.69%
DATAFRODO.AUFTABLE 1,680,512 2.57%
SYSTEMSYS.USER$TABLE 1,461,904 2.24%

Top 10 Physical Reads per Segment
TableSpaceObjectType PhyReadsRatio
DATAFRODO.BED TABLE 10,936,280 84.93%
DATAFRODO.BEA TABLE 1,188,903 9.23%
DATAFRODO.IMP TABLE 229,648 1.78%
DATAFRODO.INF TABLE 133,458 1.04%
DATAFRODO.SYS_LOB0000012058C00002$$ LOB 99,747 0.77%
DATAFRODO.IST TABLE 32,590 0.25%
TOOLSPERFSTAT.STATS$SQL_SUMMARY TABLE 25,920 0.20%
DATAFRODO.INF_BACKUP TABLE 23,379 0.18%
TOOLSPERFSTAT.STATS$LATCH TABLE 18,943 0.15%
DATAFRODO.HGI TABLE 17,782 0.14%

Top 10 Buffer Busy Waits per Segment
TableSpaceObjectType WaitsRatio
DATAFRODO.PRO TABLE 506 92.50%
SYSTEMSYS.TAB$ TABLE 27 4.94%
DATAFRODO.SLG TABLE 6 1.10%
DATAFRODO.PWA TABLE 3 0.55%
DATAFRODO.AUF TABLE 2 0.37%
INDEX1FRODO.XIE3ANT INDEX 2 0.37%
INDEX1FRODO.XPKPRO INDEX 1 0.18%

Top 10 Row Lock Waits per Segment
TableSpaceObjectType WaitsRatio
INDEX1FRODO.XPKZTL INDEX 11 91.67%
INDEX1FRODO.XIE3ANT INDEX 1 8.33%

Top 10 ITL Waits per Segment
TableSpaceObjectType WaitsRatio


Instance Recovery Statistics
B: Begin SnapShot, E: End SnapShot
 Target MTTREstd MTTRRecovery Estd IOsActual Redo Blks Target Redo BlksLogFile Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
B07 139 684 18,432 18,432  10,000,000
E09 801 18,957 18,432 18,432  10,000,000


PGA Aggreg Target Memory Statistics
B: Begin SnapShot, E: End SnapShot
 PGA Aggreg TargetPGA in UseW/A PGA in Use1-Pass Mem Req Optim W/A ExecsNon-W/A PGA MemoryAuto W/A PGA MemManual W/A PGA Mem

PGA Memory Statistics
WorkArea (W/A) memory is used for: sort, bitmap merge, and hash join ops
StatisticBeginEndDiff
maximum PGA allocated 12.3 M 28.8 M 133.75%
total PGA allocated 12.3 M 17.3 M 40.26%
total PGA inuse 10.6 M 11.5 M 9.08%


Enqueue Activity Help
Enqueue Stats gathered prior to 9i should not be compared with 9i data
Ordered by Waits desc, Requests desc
EqRequestsSucc GetsFailed GetsPctFail WaitsAvg Wt TimeWait Time
TM 2,179,005 2,179,004 0 0.00 510.0040.217
SQ 379 379 0 0.00 30.0020.005
TX 240,978 240,978 0 0.00 10.0980.098


Undo Segment Summary Help
Undo Segment block stats
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS#Undo Blocks# TXNMax Qry Len (s)Max Tx Concurcy Snapshot Too OldOut of SpaceuS/ur/uU / eS/eR/eU


Pool Sizes Help
PoolBeginEnd
Shared_Pool_Size 48.00 M 48.00 M
Shared_Pool_Reserved_Size 2.38 M 2.38 M
Large_Pool_Size 0.00 K 0.00 K
Java_Pool_Size 32.00 M 32.00 M
Sort_Area_Size 1.00 M 1.00 M
Sort_Area_Retained_Size 0.00 K 0.00 K

Shared Pool Statistics
NameBeginEnd
Memory Usage % 60.26 95.26
% SQL with executions > 1 46.97 61.88
% Memory for SQL with executions > 1 30.20 72.25

Buffer Wait Statistics Help
Ordered by Wait Time desc, Waits desc
ClassWaitsTot Wait TimeAvg Wait TimeWaits/s
data block 53000 2,789.47
segment header 504 250.00
undo header 2600  


Cache Sizes (End)
CacheSize
Buffer Cache 100.0 M
Std Block Size 8.0 K
Shared Pool Size 48.0 M
Log Buffer 32.0 K

Dictionary Cache
"Pct Misses" should be very low (< 2% in most cases)
"Cache Usage" is the number of cache entries being used
"Pct SGA" is the ratio of usage to allocated size for that cache
CacheGet RequestsPct MissScan ReqsPct Miss Mod ReqsFinal UsagePct SGA
dc_database_links 170 1.76 0   0 3 100.00
dc_files 6,759 0.01 0   0 1 100.00
dc_free_extents 4,505 1.15 51 0.00 51 2 100.00
dc_global_oids 145 6.21 0   0 10 100.00
dc_histogram_defs 61,438 18.20 0   1,002 1,590 100.00
dc_object_ids 145,687 1.22 0   1 505 100.00
dc_objects 46,892 3.38 0   737 756 100.00
dc_profiles 6,681 0.01 0   0 2 100.00
dc_rollback_segments 27,917 0.00 0   0 10 100.00
dc_segments 21,870 10.90 0   763 431 100.00
dc_sequences 376 3.19 0   376 4 100.00
dc_tablespace_quotas 176 1.14 0   44 2 100.00
dc_tablespaces 960,658 0.00 0   0 5 100.00
dc_used_extents 51 100.00 0   51 0 0.00
dc_user_grants 52,904 0.05 0   0 22 100.00
dc_usernames 38,024 0.03 0   0 10 100.00
dc_users 1,117,693 0.00 0   0 23 100.00

Library Cache Help
"Pct Misses" should be very low (< 10%), "Reloads" should not be significantly high.
NameSpaceGet RequestsPct MissPin ReqsPct Miss ReloadsInvalidations
BODY 15,561 0.71 15,561 0.77 7 0
CLUSTER 8,049 0.30 4,403 1.09 0 0
INDEX 504 21.03 593 34.23 0 0
SQL AREA 353,849 1.25 6,140,671 0.21 4,429 3,427
TABLE/PROCEDURE 180,314 0.82 2,747,068 0.48 5,949 0
TRIGGER 2 50.00 2 50.00 0 0


SGA Memory Summary
Values at the time of the End SnapShot
SGA RegionSize
Database Buffers 100.0 M
Fixed Size 714.2 K
Redo Buffers 280.0 K
Variable Size 112.0 M
Sum 213.0 M


Initialization Parameters (init.ora)
Parameter NameBegin ValueEnd Value (if different)
O7_DICTIONARY_ACCESSIBILITYTRUE 
audit_file_dest/database/oradb_dump/audit 
audit_trailNONE 
background_dump_dest/database/oradb_dump/bdump 
compatible9.2.0.0.0 
control_files/database/config1/oradb/control/oradb_1.ctl, /database/config2/oradb/control/oradb_2.ctl, /database/config3/oradb/control/oradb_3.ctl 
core_dump_dest/database/oradb_dump/cdump 
db_block_buffers12800 
db_block_size8192 
db_domaindomain.com 
db_file_multiblock_read_count8 
db_files1022 
db_nameoradb 
dml_locks100 
global_namesFALSE 
job_queue_processes1 
log_buffer32768 
log_checkpoint_interval10000000 
log_checkpoint_timeout0 
max_dump_file_size1000M 
nls_currencyDM 
nls_date_formatDD.MM.YYYY 
nls_date_languageGERMAN 
nls_iso_currencyGERMANY 
nls_languageENGLISH 
nls_sortGERMAN 
nls_territoryGERMANY 
open_cursors500 
optimizer_modeCHOOSE 
oracle_trace_collection_path/database/oradb_dump/trace 
processes50 
resource_limitTRUE 
shared_pool_reserved_size2500000 
shared_pool_size50331648 
sort_area_size1048576 
timed_statisticsTRUE 
user_dump_dest/database/oradb_dump/udump 
utl_file_dir/database/oradb_dump/utl 


w3c Created by OSPRep v0.3.4 © 2003-2005 by Itzchak Rehberg & IzzySoft IzzySoft