Oracle Database Discussions

Hi

Please help me to findout what is wrong with our database with help of statspack report.


STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
MKTPRD 1405695857 mktprd 1 8.1.7.4.1 NO SPSSPRDCLU1

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 8734 15-Dec-06 01:06:37 12
End Snap: 8745 15-Dec-06 12:00:38 12
Elapsed: 654.02 (mins)

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 100000 log_buffer: 1048576
db_block_size: 8192 shared_pool_size: 644349952

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,526.48 66,188.34
Logical reads: 652.45 28,290.37
Block changes: 7.58 328.70
Physical reads: 422.73 18,329.69
Physical writes: 195.33 8,469.45
User calls: 5.07 219.74
Parses: 0.76 32.78
Hard parses: 0.03 1.19
Sorts: 0.21 9.02
Logons: 0.16 7.14
Executes: 0.88 38.04
Transactions: 0.02

% Blocks changed per Read: 1.16 Recursive Call %: 62.92
Rollback per transaction %: 3.31 Rows per Sort: #######

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 35.21 In-memory Sort %: 99.49
Library Hit %: 97.39 Soft Parse %: 96.38
Execute to Parse %: 13.83 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 75.32 % Non-Parse CPU: 99.93

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 4.38 7.93
% SQL with executions>1: 46.67 45.15
% Memory for SQL w/exec>1: 33.05 52.95

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 436,855 846,299 38.64
SQL*Net more data from dblink 11,358,709 697,812 31.86
db file sequential read 884,818 437,456 19.98
direct path read 96,460 90,170 4.12
control file parallel write 12,630 60,427 2.76
-------------------------------------------------------------
Wait Events for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file scattered read 436,855 0 846,299 19 482.7
SQL*Net more data from dblin 11,358,709 0 697,812 1 ######
db file sequential read 884,818 0 437,456 5 977.7
direct path read 96,460 0 90,170 9 106.6
control file parallel write 12,630 0 60,427 48 14.0
SQL*Net more data to client 1,086,978 0 34,999 0 ######
log file parallel write 7,594 0 9,767 13 8.4
direct path write 9,080 0 3,120 3 10.0
SQL*Net message from dblink 10,125 0 2,730 3 11.2
control file sequential read 3,654 0 1,665 5 4.0
file open 363 0 1,048 29 0.4
buffer busy waits 990 0 745 8 1.1
refresh controlfile command 412 0 730 18 0.5
log file sync 470 0 590 13 0.5
db file parallel write 528 0 350 7 0.6
latch free 213 195 193 9 0.2
SQL*Net break/reset to clien 226 0 126 6 0.2
sort segment request 1 1 104 1040 0.0
local write wait 58 0 82 14 0.1
file identify 6 0 40 67 0.0
enqueue 287 0 38 1 0.3
single-task message 4 0 3 8 0.0
SQL*Net message to dblink 10,125 0 2 0 11.2
LGWR wait for redo copy 4 0 0 0 0.0
db file single write 3 0 0 0 0.0
SQL*Net more data to dblink 1 0 0 0 0.0
SQL*Net message from client 181,047 0 11,988,521 662 200.1
SQL*Net message to client 181,052 0 43 0 200.1
SQL*Net more data from clien 2 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
control file parallel write 12,535 0 60,409 48 13.9
log file parallel write 7,594 0 9,767 13 8.4
control file sequential read 1,239 0 711 6 1.4
db file parallel write 528 0 350 7 0.6
db file scattered read 148 0 149 10 0.2
db file sequential read 68 0 95 14 0.1
latch free 82 82 44 5 0.1
LGWR wait for redo copy 4 0 0 0 0.0
file open 1 0 0 0 0.0
rdbms ipc message 49,273 37,988 15,578,454 3162 54.4
pmon timer 12,749 12,732 3,923,887 3078 14.1
smon timer 127 126 3,896,181 ###### 0.1
-------------------------------------------------------------
SQL ordered by Gets for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Buffer Gets Threshold: 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

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
792,971 1 792,971.0 3.1 2416128217
SELECT T0."STYLE_NUMBER" AS C0 FROM MKT."CRM_TRANSACTION" T0 WHE
RE (((((T0."TRANSACTION_DATE" >= '2006-10-22 00:00:00') AND (T0.
"TRANSACTION_DATE" <= '2006-11-18 00:00:00')) AND (T0."SALE_OR_C
REDIT_CODE" = 1)) AND (T0."CHANNEL_CODE" = 3)) AND (T0."PRODUCT_
GROUP" = 51))

275,393 1 275,393.0 1.1 3711680687
SELECT T0."ACCOUNT_NUMBER" AS C0,T0."CUSTOMER_ID" AS C1,T0."HOUS
EHOLD_ID" AS C2,T0."PRIMARY_INDICATOR" AS C3,T0."CHARGE_CARD_TYP
E_CODE" AS C4,T0."FP_STORE_OF_RES" AS C5,T0."FACTORY_OUTLET_OF_R
ES" AS C6,T0."COUNTRY_CODE" AS C7,T0.STATE AS C8,T0."FOREIGN_PRO
VINCE" AS C9,T0.CITY AS C10,T0.ZIP AS C11,T0."ZIP_PLUS" AS C12,T

212,210 1 212,210.0 0.8 976170919
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis purge */ que
st_ix_scheduler.run_task(123,JOB,NEXT_DATE); :mydate := next_dat
e; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

211,780 135 1,568.7 0.8 3005787264
DELETE FROM QUEST_IX_INSTANCE_SNAPSHOTS WHERE INSTANCE_ID = :b1
AND SNAPSHOT_ID = :b2

127,660 34 3,754.7 0.5 1338209959
SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","SEGMENT_NAME","SEGMENT_T
YPE","TABLESPACE_NAME","BYTES","EXTENTS" FROM "DBA_SEGMENTS" "S"
WHERE "TABLESPACE_NAME"=:1 AND :2=:3 AND "EXTENTS">:4 OR "TABLE
SPACE_NAME"=:5 AND :6=:7 AND :8=:9 AND "EXTENTS">:10

126,845 1 126,845.0 0.5 1842131022
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */ qu
est_ix_scheduler.run_task(122,JOB,NEXT_DATE); :mydate := next_da
te; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

103,226 67 1,540.7 0.4 3215134157
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis snapshots */
quest_ix_scheduler.run_task(121,JOB,NEXT_DATE); :mydate := next
_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

65,745 11 5,976.8 0.3 2301846564
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

51,843 1 51,843.0 0.2 2692547174
BEGIN :result := quest_ix_summary_reports.activity_summary(:run_
id); END;

SQL ordered by Gets for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Buffer Gets Threshold: 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

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
45,538 135 337.3 0.2 1060636944
delete from "QUEST"."QUEST_IX_SYSSTAT_SNAP" where "INSTANCE_ID"
= :1 and "SNAPSHOT_ID" = :2

38,194 11 3,472.2 0.1 625421128
INSERT INTO STATS$SQLTEXT ( HASH_VALUE,TEXT_SUBSET,PIECE,SQL_TEX
T,ADDRESS,COMMAND_TYPE,LAST_SNAP_ID ) SELECT ST1.HASH_VALUE,SS.
TEXT_SUBSET,ST1.PIECE,ST1.SQL_TEXT,ST1.ADDRESS,ST1.COMMAND_TYPE,
SS.SNAP_ID FROM V$SQLTEXT ST1,STATS$SQL_SUMMARY SS WHERE SS.S
NAP_ID = :b1 AND SS.DBID = :b2 AND SS.INSTANCE_NUMBER = :b3 A

30,648 66 464.4 0.1 2820061326
INSERT INTO QUEST_IX_SYSTEM_EVENT_SNAP ( INSTANCE_ID,SNAPSHOT_TI
ME,EVENT_ID,CATEGORY_ID,TOTAL_WAITS,TIME_WAITED,SNAPSHOT_ID ) S
ELECT :b1,SYSDATE,EVENT_ID,CATEGORY_ID,NVL(TOTAL_WAITS,0),NVL(TI
ME_WAITED,0),:b2 FROM (SELECT RTRIM(EVENT) EVENT,TOTAL_WAITS,T
IME_WAITED FROM V$SYSTEM_EVENT ) SE,QUEST_IX_EVENT_CATEGORY_MA

28,733 24 1,197.2 0.1 2446064102
SELECT /*+ ORDERED USE_HASH(cg) USE_HASH(db) USE_HASH(dr) */PR
.SNAPSHOT_START_TIME TIMESTAMP,PR.VALUE PHYSICAL_READS,CG.VALUE
CONSISTENT_GETS,DB.VALUE DB_BLOCK_GETS,NVL(DR.VALUE,0) DIRECT_RE
ADS FROM (SELECT SS.SNAPSHOT_ID,S.SNAPSHOT_START_TIME,NAME,VAL
UE FROM QUEST_IX_SYSSTAT_SNAP SS,QUEST_IX_STAT_CATEGORY_MAP SN

26,909 135 199.3 0.1 3443699134
delete from "QUEST"."QUEST_IX_PARAMETER_SNAP" where "INSTANCE_I
D" = :1 and "SNAPSHOT_ID" = :2

26,479 135 196.1 0.1 4188755535
delete from "QUEST"."QUEST_IX_LATCH_SNAP" where "INSTANCE_ID" =
:1 and "SNAPSHOT_ID" = :2

25,409 1 25,409.0 0.1 2416379858
BEGIN :result := quest_ix_latch_report.latch_summary(:run_id); E
ND;

25,382 2 12,691.0 0.1 651128462
select S.segment_type, S.OWNER||'.'||S.SEGMENT_NAME OBJECT, S.NE
XT_EXTENT/1024 NEXT_KB, F.FREE/1024 FREE_KB, S.TABLESPACE_NAME
from DBA_SEGMENTS S, DBA_TABLESPACES T , (select TABLESPACE_NA
ME,max(BYTES) FREE from DBA_FREE_SPACE group by TABLESPACE_NAME)
F , (select TABLESPACE_NAME,max(greatest(decode(AUTOEXTENSIBLE

25,272 2 12,636.0 0.1 1252505680
select T.TABLESPACE_NAME,S.NEED/1048576 SUM_NEXT_MB, (nvl(F.FREE
,0)+D.GROWABLE)/1048576 SUM_FREE_MB from DBA_TABLESPACES T ,
(select TABLESPACE_NAME,sum(NEXT_EXTENT) NEED from DBA_SEGMENTS

-------------------------------------------------------------
SQL ordered by Reads for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Disk Reads Threshold: 1000

Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
757,189 1 757,189.0 4.6 2416128217
SELECT T0."STYLE_NUMBER" AS C0 FROM MKT."CRM_TRANSACTION" T0 WHE
RE (((((T0."TRANSACTION_DATE" >= '2006-10-22 00:00:00') AND (T0.
"TRANSACTION_DATE" <= '2006-11-18 00:00:00')) AND (T0."SALE_OR_C
REDIT_CODE" = 1)) AND (T0."CHANNEL_CODE" = 3)) AND (T0."PRODUCT_
GROUP" = 51))

275,375 1 275,375.0 1.7 3711680687
SELECT T0."ACCOUNT_NUMBER" AS C0,T0."CUSTOMER_ID" AS C1,T0."HOUS
EHOLD_ID" AS C2,T0."PRIMARY_INDICATOR" AS C3,T0."CHARGE_CARD_TYP
E_CODE" AS C4,T0."FP_STORE_OF_RES" AS C5,T0."FACTORY_OUTLET_OF_R
ES" AS C6,T0."COUNTRY_CODE" AS C7,T0.STATE AS C8,T0."FOREIGN_PRO
VINCE" AS C9,T0.CITY AS C10,T0.ZIP AS C11,T0."ZIP_PLUS" AS C12,T

2,176 67 32.5 0.0 3215134157
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis snapshots */
quest_ix_scheduler.run_task(121,JOB,NEXT_DATE); :mydate := next
_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

1,174 1 1,174.0 0.0 1842131022
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis report */ qu
est_ix_scheduler.run_task(122,JOB,NEXT_DATE); :mydate := next_da
te; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

744 126 5.9 0.0 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

675 1 675.0 0.0 976170919
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN /* Database Analysis purge */ que
st_ix_scheduler.run_task(123,JOB,NEXT_DATE); :mydate := next_dat
e; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

675 135 5.0 0.0 3005787264
DELETE FROM QUEST_IX_INSTANCE_SNAPSHOTS WHERE INSTANCE_ID = :b1
AND SNAPSHOT_ID = :b2

591 1 591.0 0.0 2692547174
BEGIN :result := quest_ix_summary_reports.activity_summary(:run_
id); END;

549 24 22.9 0.0 2446064102
SELECT /*+ ORDERED USE_HASH(cg) USE_HASH(db) USE_HASH(dr) */PR
.SNAPSHOT_START_TIME TIMESTAMP,PR.VALUE PHYSICAL_READS,CG.VALUE
CONSISTENT_GETS,DB.VALUE DB_BLOCK_GETS,NVL(DR.VALUE,0) DIRECT_RE
ADS FROM (SELECT SS.SNAPSHOT_ID,S.SNAPSHOT_START_TIME,NAME,VAL
UE FROM QUEST_IX_SYSSTAT_SNAP SS,QUEST_IX_STAT_CATEGORY_MAP SN

525 11 47.7 0.0 2301846564
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
SQL ordered by Reads for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Disk Reads Threshold: 1000

Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------

245 66 3.7 0.0 543854121
INSERT INTO QUEST_IX_SYSSTAT_SNAP ( INSTANCE_ID,SNAPSHOT_TIME,CA
TEGORY_ID,STATISTIC_ID,VALUE,SNAPSHOT_ID ) SELECT :b1,SYSDATE,C
ATEGORY_ID,SCM.STATISTIC_ID,VALUE,:b2 FROM V$SYSSTAT VSS,QUEST
IXSTAT_CATEGORY_MAP SCM WHERE SCM.NAME = RTRIM(VSS.NAME)

228 66 3.5 0.0 2820061326
INSERT INTO QUEST_IX_SYSTEM_EVENT_SNAP ( INSTANCE_ID,SNAPSHOT_TI
ME,EVENT_ID,CATEGORY_ID,TOTAL_WAITS,TIME_WAITED,SNAPSHOT_ID ) S
ELECT :b1,SYSDATE,EVENT_ID,CATEGORY_ID,NVL(TOTAL_WAITS,0),NVL(TI
ME_WAITED,0),:b2 FROM (SELECT RTRIM(EVENT) EVENT,TOTAL_WAITS,T
IME_WAITED FROM V$SYSTEM_EVENT ) SE,QUEST_IX_EVENT_CATEGORY_MA

225 4 56.3 0.0 3798150885
select owner,table_name,table_type,NULL from sys.dba_catalog whe
re table_type in('~','TABLE','VIEW')and owner not in('SYS','SYST
EM')

182 11 16.5 0.0 625421128
INSERT INTO STATS$SQLTEXT ( HASH_VALUE,TEXT_SUBSET,PIECE,SQL_TEX
T,ADDRESS,COMMAND_TYPE,LAST_SNAP_ID ) SELECT ST1.HASH_VALUE,SS.
TEXT_SUBSET,ST1.PIECE,ST1.SQL_TEXT,ST1.ADDRESS,ST1.COMMAND_TYPE,
SS.SNAP_ID FROM V$SQLTEXT ST1,STATS$SQL_SUMMARY SS WHERE SS.S
NAP_ID = :b1 AND SS.DBID = :b2 AND SS.INSTANCE_NUMBER = :b3 A

142 116 1.2 0.0 3111103299
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#

74 116 0.6 0.0 1428100621
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#

67 214 0.3 0.0 4049165760
select order#,columns,types from access$ where d_obj#=:1

61 135 0.5 0.0 1849561270
delete from "QUEST"."QUEST_IX_SYSTEM_EVENT_SNAP" where "INSTANC
E_ID" = :1 and "SNAPSHOT_ID" = :2

53 198 0.3 0.0 3804240596
INSERT INTO QUEST_IX_MISC_SNAP ( INSTANCE_ID,SNAPSHOT_TIME,NAME,
VALUE,SNAPSHOT_ID ) VALUES ( :b1,SYSDATE,:b2,:b3,:b4 )

-------------------------------------------------------------
SQL ordered by Executions for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Executions Threshold: 100

Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
6,600 125 0.0 259448441
SELECT * FROM QUEST_IX_SESSION_WAIT_STATS

3,206 265 0.1 313510536
select job from sys.job$ where next_date < sysdate and (field1
= :1 or (field1 = 0 and 'Y' = :2)) order by next_date, job

2,271 2,271 1.0 2837994794
INSERT INTO QUEST_IX_RUN_OBS_TABLE_ITEM ( RUN_ID,OBSERVATION_ID,
ROW_NO,COLUMN_NO,SEQUENCE_NO,VALUE ) VALUES ( :b1,:b2,:b3,:b4,:
b5,:b6 )

815 1,586 1.9 1705880752
select file# from file$ where ts#=:1

725 725 1.0 2185960232
INSERT INTO QUEST_IX_SERIES_WORKAREA ( SERIES_ID,TIMESTAMP,PLOT_
DATA,LABEL ) VALUES ( :b1,:b2,:b3,:b4 )

654 654 1.0 2144042005
select NAME from TS$ where TS$.NAME='SYSTEM'

585 295 0.5 787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2

573 658 1.1 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

569 524 0.9 4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3

563 427 0.8 955191413
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 an
d(remoteowner=:4 or remoteowner is null and :4 is null)and(linkn
ame=:5 or linkname is null and :5 is null)and(subname=:6 or subn
ame is null and :6 is null)

422 422 1.0 2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3

370 370 1.0 1966425544
select text from view$ where rowid=:1

346 2 0.0 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
SQL ordered by Executions for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Executions Threshold: 100

Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------

340 340 1.0 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3

340 340 1.0 3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)

335 301 0.9 189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1

314 3,729 11.9 395844583
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, charsetid,ch
arsetform,spare1,spare2 from col$ where obj#=:1 order by intcol#

312 312 1.0 2434010489
INSERT INTO QUEST_IX_RUN_METRICS ( RUN_ID,STATISTIC_CLASS,STATIS
TIC_NAME,STATISTIC_KEY,NO_OF_SAMPLES,TOTAL_ELAPSED_TIME,SUM_OF_V
ALUES,SUM_OF_SQUARES,MAX_VALUE,MIN_VALUE,RAW_VALUE_SUM,RAW_VALUE
MIN,RAWVALUE_MAX ) VALUES ( :b1,:b2,UPPER(:b3),:b4,:b5 - 1 ,:
b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13 )

280 20 0.1 1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4

260 260 1.0 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4

260 260 1.0 3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
h)values (:1, :2, :3, :4, :5, :6, :7)

252 235 0.9 1576697787
select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env from sys.job$ j, sys.user$ u1, sys.user$ u
2, sys.user$ u3 where job=:1 and (next_date < sysdate or :2 !=
0) and lowner = u1.name and powner = u2.name and cowner = u3.na
me for update nowait

242 242 1.0 2216582187
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,nvl(spa

238 238 1.0 270179002
update hist_head$ set bucket_cnt=:3, row_cnt=:4, cache_cnt=:5,nu
ll_cnt=:6, timestamp#=:7, sample_size=:8, minimum=:9, maximum=:1
0,distcnt=:11, lowval=:12, hival=:13, density=:14, spare1=:15, s
SQL ordered by Executions for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> End Executions Threshold: 100

Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
pare2=:16, avgcln=:17, col#=:18 where obj#=:1 and intcol#=:2

-------------------------------------------------------------
Instance Activity Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 3,099,879 79.0 3,425.3
CPU used when call started 3,082,473 78.6 3,406.1
CR blocks created 1,382 0.0 1.5
DBWR buffers scanned 92,649 2.4 102.4
DBWR checkpoint buffers written 7,476 0.2 8.3
DBWR free buffers found 91,503 2.3 101.1
DBWR lru scans 165 0.0 0.2
DBWR make free requests 182 0.0 0.2
DBWR summed scan depth 92,649 2.4 102.4
DBWR transaction table writes 308 0.0 0.3
DBWR undo block writes 3,264 0.1 3.6
SQL*Net roundtrips to/from client 180,868 4.6 199.9
SQL*Net roundtrips to/from dblink 10,125 0.3 11.2
background timeouts 41,196 1.1 45.5
branch node splits 1 0.0 0.0
buffer is not pinned count 15,228,560 388.1 16,827.1
buffer is pinned count 23,097,176 588.6 25,521.7
bytes received via SQL*Net from c 20,522,646 523.0 22,677.0
bytes received via SQL*Net from d 18,852,871,237 480,438.1 ############
bytes sent via SQL*Net to client 2,416,059,700 61,569.8 2,669,679.2
bytes sent via SQL*Net to dblink 169,370 4.3 187.2
calls to get snapshot scn: kcmgss 41,568 1.1 45.9
calls to kcmgas 2,516 0.1 2.8
calls to kcmgcs 1,245 0.0 1.4
change write time 346 0.0 0.4
cleanouts and rollbacks - consist 62 0.0 0.1
cleanouts only - consistent read 27 0.0 0.0
cluster key scan block gets 84,345 2.2 93.2
cluster key scans 45,012 1.2 49.7
commit cleanout failures: block l 8 0.0 0.0
commit cleanout failures: cannot 4 0.0 0.0
commit cleanouts 14,362 0.4 15.9
commit cleanouts successfully com 14,350 0.4 15.9
consistent changes 1,802 0.1 2.0
consistent gets 25,233,250 643.0 27,882.0
cursor authentications 122 0.0 0.1
data blocks consistent reads - un 1,802 0.1 2.0
db block changes 297,472 7.6 328.7
db block gets 369,539 9.4 408.3
deferred (CURRENT) block cleanout 9,028 0.2 10.0
dirty buffers inspected 229 0.0 0.3
enqueue conversions 19,871 0.5 22.0
enqueue releases 27,793 0.7 30.7
enqueue requests 27,963 0.7 30.9
enqueue timeouts 169 0.0 0.2
execute count 34,425 0.9 38.0
free buffer inspected 598 0.0 0.7
free buffer requested 14,144,537 360.5 15,629.3
hot buffers moved to head of LRU 458,016 11.7 506.1
immediate (CR) block cleanout app 89 0.0 0.1
immediate (CURRENT) block cleanou 4,817 0.1 5.3
index fast full scans (full) 58 0.0 0.1
leaf node splits 325 0.0 0.4
logons cumulative 6,459 0.2 7.1
logons current
messages received 4,699 0.1 5.2
Instance Activity Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
messages sent 4,699 0.1 5.2
no buffer to keep pinned count 20 0.0 0.0
no work - consistent read gets 19,480,855 496.4 21,525.8
opened cursors cumulative 31,959 0.8 35.3
opened cursors current
parse count (hard) 1,075 0.0 1.2
parse count (total) 29,663 0.8 32.8
parse time cpu 2,148 0.1 2.4
parse time elapsed 2,852 0.1 3.2
physical reads 16,588,373 422.7 18,329.7
physical reads direct 2,451,156 62.5 2,708.5
physical writes 7,664,854 195.3 8,469.5
physical writes direct 7,655,847 195.1 8,459.5
physical writes non checkpoint 7,661,793 195.3 8,466.1
pinned buffers inspected 298 0.0 0.3
prefetched blocks 12,815,530 326.6 14,160.8
prefetched blocks aged out before 1,273 0.0 1.4
process last non-idle time 171,426,616,129 4,368,558.8 ############
recursive calls 337,417 8.6 372.8
recursive cpu usage 424,544 10.8 469.1
redo blocks written 124,803 3.2 137.9
redo entries 529,777 13.5 585.4
redo size 59,900,444 1,526.5 66,188.3
redo synch time 590 0.0 0.7
redo synch writes 514 0.0 0.6
redo wastage 2,035,488 51.9 2,249.2
redo write time 15,637 0.4 17.3
redo writes 7,593 0.2 8.4
rollback changes - undo records a 4 0.0 0.0
rollbacks only - consistent read 1,319 0.0 1.5
rows fetched via callback 74,053 1.9 81.8
session connect time 171,426,616,129 4,368,558.8 ############
session logical reads 25,602,785 652.5 28,290.4
session pga memory 23,029,010,400 586,861.0 ############
session pga memory max 23,073,166,532 587,986.2 ############
session uga memory 128,509,980 3,274.9 142,000.0
session uga memory max 542,774,244 13,831.8 599,750.6
sorts (disk) 42 0.0 0.1
sorts (memory) 8,118 0.2 9.0
sorts (rows) 911,203,307 23,220.7 1,006,854.5
summed dirty queue length 17 0.0 0.0
switch current to new buffer
table fetch by rowid 11,395,286 290.4 12,591.5
table fetch continued row 84 0.0 0.1
table scan blocks gotten 9,785,272 249.4 10,812.5
table scan rows gotten 752,146,501 19,167.4 831,101.1
table scans (long tables) 30 0.0 0.0
table scans (short tables) 2,137 0.1 2.4
total file opens 363 0.0 0.4
transaction rollbacks 2 0.0 0.0
user calls 198,868 5.1 219.7
user commits 875 0.0 1.0
user rollbacks 30 0.0 0.0
write clones created in backgroun 58 0.0 0.1
-------------------------------------------------------------
Tablespace IO Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MKTPRDTRANS2
854,926 22 8.7 8.3 29,667 1 117 4.6
MKTPRDTRANS2IX
165,022 4 5.3 4.2 163,277 4 502 4.6
MKTPRDTEMP
190,821 5 572.5 12.8 26,274 1 28 3.9
MKTPRDEMAIL2
47,035 1 19.1 32.0 23,514 1 0 0.0
MKTPRDHHOLD2
54,461 1 20.7 23.5 12,085 0 343 13.1
MKTPRDPROMO2IX
25,453 1 2.9 1.0 35,748 1 0 0.0
MKTPRDNAMEADD2
43,042 1 18.2 32.0 8,610 0 0 0.0
MKTPRDNAMEADD2IX
22,622 1 4.7 3.4 28,530 1 0 0.0
MKTPRDHHOLD2IX
24,887 1 5.1 1.0 24,675 1 0 0.0
MKTPRDUSER
29,238 1 21.5 31.8 13,804 0 0 0.0
MKTPRDPROMO2
25,467 1 19.2 32.0 12,730 0 0 0.0
MKTPRDACCT2IX
11,505 0 2.7 1.0 15,699 0 0 0.0
MKTPRDACCT2
8,389 0 22.7 32.0 4,192 0 0 0.0
MKTPRDEMAIL2IX
4,322 0 2.9 1.0 5,933 0 0 0.0
MKTPRDMISC
3,043 0 6.9 1.6 3,819 0 0 0.0
MKTPRDRBS
147 0 20.9 1.0 3,431 0 2 0.0
SYSTEM
1,182 0 10.2 2.1 439 0 26 4.2
PERFSTAT
468 0 9.8 1.0 922 0 0 0.0
MKTPRDEMAIL
278 0 19.1 30.6 270 0 0 0.0
MKTPRDRBSBIG
5 0 72.0 1.0 118 0 0 0.0
MKTPRDHHOLDIX
55 0 3.1 1.0 62 0 0 0.0
AUDITDATA
12 0 5.8 1.0 36 0 0 0.0
MKTPRDHISTORY
13 0 28.5 1.0 0 0 0 0.0
MKTPRDTRANS
11 0 9.1 1.0 0 0 0 0.0
MKTPRDTOOLS
2 0 5.0 1.0 4 0 0 0.0
MKTPRDTRANSIX
6 0 11.7 1.0 0 0 0 0.0
Tablespace IO Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MKTPRDHHOLD
5 0 66.0 1.0 0 0 0 0.0
MKTPRDNAMEADD
4 0 7.5 1.0 0 0 0 0.0
MKTPRDACCT
3 0 3.3 1.0 0 0 0 0.0
MKTPRDPROMO
3 0 20.0 1.0 0 0 0 0.0
MKTPRDACCTIX
2 0 10.0 1.0 0 0 0 0.0
MKTPRDEMAILIX
2 0 10.0 1.0 0 0 0 0.0
MKTPRDNAMEADDIX
2 0 15.0 1.0 0 0 0 0.0
MKTPRDPROMOIX
2 0 200.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
AUDITDATA E:\MKTPRD\DATA\MKTPRD_AUD01.DBF
12 0 5.8 1.0 36 0 0

MKTPRDACCT E:\MKTPRD\DATA\MKTPRDACCT01.DBF
3 0 3.3 1.0 0 0 0

MKTPRDACCT2 E:\MKTPRD\DATA\MKTPRDACCT201.DBF
8,389 0 22.7 32.0 4,192 0 0

MKTPRDACCT2IX F:\MKTPRD\INDX\MKTPRDACCT2IX01.DBF
11,505 0 2.7 1.0 15,699 0 0

MKTPRDACCTIX F:\MKTPRD\INDX\MKTPRDACCTIX01.DBF
2 0 10.0 1.0 0 0 0

MKTPRDEMAIL K:\MKTPRD\DATA\MKTPRDEMAIL01.DBF
278 0 19.1 30.6 270 0 0

MKTPRDEMAIL2 K:\MKTPRD\DATA\MKTPRDEMAIL201.DBF
30,897 1 19.0 32.0 15,448 0 0
K:\MKTPRD\DATA\MKTPRDEMAIL202.DBF
16,138 0 19.2 32.0 8,066 0 0

MKTPRDEMAIL2IX F:\MKTPRD\INDX\MKTPRDEMAIL2IX01.DBF
4,322 0 2.9 1.0 5,933 0 0

MKTPRDEMAILIX F:\MKTPRD\INDX\MKTPRDEMAILIX01.DBF
2 0 10.0 1.0 0 0 0

MKTPRDHHOLD E:\MKTPRD\DATA\MKTPRDHHOLD01.DBF
3 0 16.7 1.0 0 0 0
E:\MKTPRD\DATA\MKTPRDHHOLD02.DBF
2 0 140.0 1.0 0 0 0

MKTPRDHHOLD2 E:\MKTPRD\DATA\MKTPRDHHOLD201.DBF
54,461 1 20.7 23.5 12,085 0 343 13.1

MKTPRDHHOLD2IX F:\MKTPRD\INDX\MKTPRDHHOLD2IX01.DBF
24,887 1 5.1 1.0 24,675 1 0

MKTPRDHHOLDIX F:\MKTPRD\INDX\MKTPRDRPTIX01.DBF
55 0 3.1 1.0 62 0 0

MKTPRDHISTORY E:\MKTPRD\DATA\MKTPRDHISTORY01.DBF
13 0 28.5 1.0 0 0 0

MKTPRDMISC K:\MKTPRD\DATA\MKTPRDMISC01.DBF
2,936 0 6.8 1.2 3,676 0 0
K:\MKTPRD\DATA\MKTPRDMISC02.DBF
2 0 10.0 1.0 0 0 0
K:\MKTPRD\DATA\MKTPRDMISC03.DBF
105 0 9.5 12.6 143 0 0
File IO Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------

MKTPRDNAMEADD K:\MKTPRD\DATA\MKTPRDNAMEADD01.DBF
4 0 7.5 1.0 0 0 0

MKTPRDNAMEADD2 K:\MKTPRD\DATA\MKTPRDNAMEADD201.DBF
43,042 1 18.2 32.0 8,610 0 0

MKTPRDNAMEADD2IX F:\MKTPRD\INDX\MKTPRDNAMEADD2IX01.DBF
22,622 1 4.7 3.4 28,530 1 0

MKTPRDNAMEADDIX F:\MKTPRD\INDX\MKTPRDNAMEADDIX01.DBF
2 0 15.0 1.0 0 0 0

MKTPRDPROMO K:\MKTPRD\DATA\MKTPRDPROMO01.DBF
3 0 20.0 1.0 0 0 0

MKTPRDPROMO2 K:\MKTPRD\DATA\MKTPRDPROMO201.DBF
12,651 0 19.2 32.0 6,323 0 0
K:\MKTPRD\DATA\MKTPRDPROMO202.DBF
12,816 0 19.2 32.0 6,407 0 0

MKTPRDPROMO2IX F:\MKTPRD\INDX\MKTPRDPROMO2IX01.DBF
25,453 1 2.9 1.0 35,748 1 0

MKTPRDPROMOIX F:\MKTPRD\INDX\MKTPRDPROMOIX01.DBF
2 0 200.0 1.0 0 0 0

MKTPRDRBS H:\MKTPRD\RBSD\MKTPRDRBS01.DBF
147 0 20.9 1.0 3,431 0 2 0.0

MKTPRDRBSBIG H:\MKTPRD\RBSD\MKTPRDRBSBIG02.DBF
5 0 72.0 1.0 118 0 0

MKTPRDTEMP G:\MKTPRD\TMPD\MKTPRDTEMP01.DBF
190,818 5 572.5 12.8 26,274 1 2 0.0
G:\MKTPRD\TMPD\MKTPRDTEMP02.DBF
1 0 30.0 1.0 0 0 0
G:\MKTPRD\TMPD\MKTPRDTEMP03.DBF
1 0 30.0 1.0 0 0 0
G:\MKTPRD\TMPD\MKTPRDTEMP04.DBF
1 0 10.0 1.0 0 0 26 4.2

MKTPRDTOOLS G:\MKTPRD\DDIC\MKTPRDTOOLS01.DBF
2 0 5.0 1.0 4 0 0

MKTPRDTRANS E:\MKTPRD\DATA\MKTPRDTRANS01.DBF
5 0 12.0 1.0 0 0 0
E:\MKTPRD\DATA\MKTPRDTRANS02.DBF
3 0 6.7 1.0 0 0 0
E:\MKTPRD\DATA\MKTPRDTRANS03.DBF
3 0 6.7 1.0 0 0 0

File IO Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MKTPRDTRANS2 E:\MKTPRD\DATA\MKTPRDTRANS201.DBF
433,376 11 8.6 8.2 14,780 0 47 5.7
E:\MKTPRD\DATA\MKTPRDTRANS202.DBF
421,550 11 8.9 8.4 14,887 0 70 3.9

MKTPRDTRANS2IX F:\MKTPRD\INDX\MKTPRDTRANS2IX01.DBF
73,833 2 5.4 4.5 75,239 2 0
F:\MKTPRD\INDX\MKTPRDTRANS2IX02.DBF
80,566 2 5.2 4.1 74,531 2 502 4.6
F:\MKTPRD\INDX\MKTPRDTRANS2IX03.DBF
10,623 0 4.7 3.3 13,507 0 0

MKTPRDTRANSIX F:\MKTPRD\INDX\MKTPRDTRANSIX01.DBF
2 0 25.0 1.0 0 0 0
F:\MKTPRD\INDX\MKTPRDTRANSIX02.DBF
4 0 5.0 1.0 0 0 0

MKTPRDUSER G:\MKTPRD\DDIC\MKTPRDUSER01.DBF
12,844 0 21.8 31.8 6,382 0 0
G:\MKTPRD\DDIC\MKTPRDUSER02.DBF
12,839 0 21.6 31.9 6,350 0 0
G:\MKTPRD\DDIC\MKTPRDUSER03.DBF
3,555 0 20.3 31.6 1,072 0 0

PERFSTAT G:\MKTPRD\DDIC\PERFSTAT1.DBF
296 0 10.1 1.0 461 0 0
G:\MKTPRD\DDIC\PERFSTAT2.DBF
172 0 9.3 1.0 461 0 0

SYSTEM G:\MKTPRD\DDIC\MKTPRDSYSTEM.DBF
1,182 0 10.2 2.1 439 0 26 4.2

-------------------------------------------------------------
Buffer Pool Statistics for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> Pools D: default pool, K: keep pool, R: recycle pool

Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 14,143,258 19,523,071 14,135,890 9,007 0 0 990
-------------------------------------------------------------





Buffer wait Statistics for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> ordered by wait time desc, waits desc

Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 988 745 1
undo block 2 0 0
-------------------------------------------------------------
Rollback Segment Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->A high value for "Pct Waits" suggests more rollback segments may be required

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 353.0 0.00 20,938 1 0 0
2 1,000.0 0.00 1,809,510 1 0 0
3 1,827.0 0.00 1,825,770 0 0 0
4 1,424.0 0.00 2,659,330 1 0 0
5 1,684.0 0.00 1,940,876 0 0 0
6 1,142.0 0.00 2,429,576 0 0 0
7 1,302.0 0.00 1,869,448 0 0 0
8 1,404.0 0.00 2,088,198 0 0 0
9 1,123.0 0.00 1,742,462 0 0 0
10 1,099.0 0.00 1,985,050 1 0 0
11 956.0 0.00 1,760,302 1 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 401,408 8,192 401,408
2 105,463,808 421,888 104,857,600 105,463,808
3 105,439,232 0 104,857,600 105,439,232
4 105,439,232 421,888 104,857,600 105,439,232
5 105,439,232 0 104,857,600 105,439,232
6 105,439,232 0 104,857,600 105,439,232
7 105,439,232 0 104,857,600 105,439,232
8 105,439,232 0 104,857,600 105,439,232
9 105,439,232 0 104,857,600 105,439,232
10 105,439,232 421,888 104,857,600 105,439,232
11 105,439,232 421,888 104,857,600 105,439,232
-------------------------------------------------------------
Latch Activity for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Pct
Get Get Slps NoWait NoWait
Latch Name Requests Miss /Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
NLS data objects 1 0.0 0
Token Manager 366 0.0 0
active checkpoint queue latch 14,726 0.0 0
begin backup scn array 378,568 0.0 0
cache buffer handles 3,340 0.0 0
cache buffers chains 62,936,565 0.0 0.0 27,010,046 0.0
cache buffers lru chain 9,450,177 0.0 0.0 14,142,161 0.0
channel handle pool latch 130 0.0 0
channel operations parent lat 199 0.0 0
checkpoint queue latch 108,346 0.1 0.6 0
dictionary lookup 81 0.0 0
dml lock allocation 14,058 0.0 0.0 0
enqueue hash chains 75,906 0.2 0.0 0
enqueues 105,184 0.2 0.0 0
event group latch 69 0.0 0
file number translation table 46 0.0 0
global transaction 36,096 0.0 0
global tx free list 66 0.0 0
global tx hash mapping 913 0.0 0
job_queue_processes parameter 617 0.0 0
ktm global data 127 0.0 0
latch wait list 23 0.0 23 0.0
library cache 670,897 0.4 0.0 0
library cache load lock 2,456 0.0 0
list of block allocation 4,996 0.0 0
loader state object freelist 326 0.0 0
longop free list 219 0.0 0
messages 111,881 0.2 0.0 0
mostly latch-free SCN 1 0.0 0
multiblock read objects 920,015 0.0 0.0 0
ncodef allocation latch 617 0.0 0
process allocation 69 0.0 69 0.0
process group creation 130 0.0 0
redo allocation 551,644 0.0 0.0 0
redo writing 64,238 0.2 0.0 0
row cache objects 122,431 0.0 0
sequence cache 938 0.0 0
session allocation 141,953 0.5 0.0 0
session idle bit 405,287 0.0 0.0 0
session switching 617 0.0 0
shared pool 105,757 0.2 0.1 0
sort extent pool 6,198 0.0 0
transaction allocation 140,387 0.0 0.0 0
transaction branch allocation 887 0.0 0
undo global data 32,747 0.0 0.0 0
user lock 256 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> ordered by misses desc

Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 62,936,565 4,495 13 4489/3/1/2/0
cache buffers lru chain 9,450,177 2,947 32 2935/2/3/7/0
library cache 670,897 2,444 34 2432/3/1/8/0
session allocation 141,953 690 14 680/6/4/0/0
shared pool 105,757 237 30 227/1/3/6/0
messages 111,881 186 2 184/2/0/0/0
enqueues 105,184 184 4 183/0/0/1/0
enqueue hash chains 75,906 163 3 162/0/0/1/0
checkpoint queue latch 108,346 132 80 59/66/7/0/0
redo allocation 551,644 24 1 23/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
cache buffers chains kcbgtcr: kslbegin 0 7 11
cache buffers chains kcbzib: finish free bufs 0 4 0
cache buffers chains kcbchg: kslbegin: bufs not 0 1 1
cache buffers chains kcbzgb: scan from tail. no 0 1 0
cache buffers lru chain kcbzgb: multiple sets nowa 0 29 0
cache buffers lru chain kcbbiop: lru scan 0 3 0
checkpoint queue latch kcbk0rrd: update recovery 0 80 0
enqueue hash chains ksqgtl3 0 3 3
enqueues ksqgel: create enqueue 0 4 0
library cache kglrtl 0 25 0
library cache kglpnal: child: before pro 0 7 3
library cache kgldtld: 2child 0 1 0
library cache kgllkdl: child: free pin 0 1 4
messages ksarcv: after wait 0 2 1
redo allocation kcrfwi: before write 0 1 0
session allocation ksuxds: KSUSFCLC not set 0 12 6
session allocation ksucri 0 2 5
shared pool kghalo 0 30 4
-------------------------------------------------------------
Dictionary Cache Stats for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->"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

Get Pct Scan Pct Mod Final Pct
Cache Requests Miss Requests Miss Req Usage SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints 18 72.2 0 18 7 54
dc_database_links 53 3.8 0 0 2 67
dc_files 2,100 2.3 0 0 48 83
dc_free_extents 1,122 30.8 260 0.0 840 6 16
dc_global_oids 0 0 0 0 0
dc_histogram_data 0 0 0 0 0
dc_histogram_data_valu 0 0 0 0 0
dc_histogram_defs 1,877 31.2 0 295 528 100
dc_object_ids 3,839 7.2 0 32 321 99
dc_objects 3,836 15.6 0 170 736 99
dc_outlines 0 0 0 0 0
dc_profiles 147 0.0 0 0 2 40
dc_rollback_segments 5,529 0.0 0 71 17 85
dc_segments 3,067 15.9 0 511 525 97
dc_sequence_grants 0 0 0 0 0
dc_sequences 21 28.6 0 10 8 44
dc_synonyms 392 18.9 0 0 84 91
dc_tablespace_quotas 88 14.8 0 88 13 57
dc_tablespaces 3,444 0.9 0 0 33 97
dc_used_extents 520 53.8 0 520 20 91
dc_user_grants 4,242 0.4 0 0 15 54
dc_usernames 2,154 0.3 0 0 11 52
dc_users 7,222 0.3 0 0 23 79
ifs_acl_cache_entries 0 0 0 0 0
-------------------------------------------------------------


Library Cache Activity for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 813 4.1 798 4.1 0 0
CLUSTER 901 0.2 666 0.6 0 0
INDEX 120 35.0 139 43.9 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 27,486 3.3 96,604 1.9 131 76
TABLE/PROCEDURE 7,283 9.5 27,962 4.9 0 0
TRIGGER 0 0 0 0
-------------------------------------------------------------
SGA Memory Summary for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 819,200,000
Fixed Size 75,804
Redo Buffers 1,056,768
Variable Size 794,083,328
----------------
sum 1,614,415,900
-------------------------------------------------------------


SGA breakdown difference for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745

Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 20,000,768 20,000,768 0
large pool free memory 104,657,600 104,657,600 0
shared pool Checkpoint queue 146,952 146,952 0
shared pool KGFF heap 35,904 35,904 0
shared pool KGK heap 18,052 18,052 0
shared pool KQLS heap 610,640 2,147,512 1,536,872
shared pool PL/SQL DIANA 330,952 681,012 350,060
shared pool PL/SQL MPCODE 127,336 1,217,616 1,090,280
shared pool PLS non-lib hp 2,096 2,096 0
shared pool SEQ S.O. 73,920 73,920 0
shared pool State objects 937,352 937,352 0
shared pool branches 222,000 222,000 0
shared pool db_block_buffers 13,600,000 13,600,000 0
shared pool db_block_hash_buckets 1,731,096 1,731,096 0
shared pool db_files 369,540 369,540 0
shared pool db_handles 350,000 350,000 0
shared pool dictionary cache 353,252 1,080,740 727,488
shared pool distributed_transactions 83,312 83,312 0
shared pool enqueue_resources 196,920 196,920 0
shared pool event statistics per ses 2,889,600 2,889,600 0
shared pool fixed allocation callbac 1,920 1,920 0
shared pool free memory 640,080,156 616,331,660 -23,748,496
shared pool ktlbk state objects 395,472 395,472 0
shared pool library cache 1,086,088 4,850,000 3,763,912
shared pool long op statistics array 110,000 110,000 0
shared pool message pool freequeue 124,552 124,552 0
shared pool messages 123,200 123,200 0
shared pool miscellaneous 769,292 843,024 73,732
shared pool processes 565,600 565,600 0
shared pool sessions 1,811,040 1,811,040 0
shared pool sql area 1,135,628 17,303,972 16,168,344
shared pool state objects 107,996 124,536 16,540
shared pool table columns 18,136 35,456 17,320
shared pool table definiti 880 4,588 3,708
shared pool transaction_branches 170,016 170,016 0
shared pool transactions 824,208 824,208 0
shared pool trigger inform 180 420 240
db_block_buffers 819,200,000 819,200,000 0
fixed_sga 75,804 75,804 0
log_buffer 1,048,576 1,048,576 0
-------------------------------------------------------------
init.ora Parameters for DB: MKTPRD Instance: mktprd Snaps: 8734 -8745

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
always_semi_join standard STANDARD
audit_trail DB
background_dump_dest D:\oracle\ora81\admin\MKTPRD\bdum
compatible 8.1.7.0.0
control_files E:\MKTPRD\CNTR\CTL1MKTPRD.CTL, F:
core_dump_dest D:\oracle\ora81\admin\MKTPRD\cdum
db_block_buffers 100000
db_block_lru_latches 2
db_block_size 8192
db_domain world
db_file_multiblock_read_count 32
db_files 1020
db_name MKTPRD
dml_locks 200
event 10183 trace name context forever,
hash_multiblock_io_count 8
instance_name MKTPRD
job_queue_processes 5
large_pool_size 104657600
local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=172.
log_buffer 1048576
log_checkpoint_interval 1215752191
log_checkpoint_timeout 900
log_checkpoints_to_alert TRUE
max_dump_file_size 10240
nls_date_format MM/DD/YYYY HH24:MI:SS
open_cursors 2000
optimizer_mode CHOOSE
optimizer_percent_parallel 100
parallel_max_servers 96
parallel_min_servers 0
processes 700
remote_login_passwordfile EXCLUSIVE
rollback_segments rbs1, rbs2, rbs3, rbs4, rbs5, rbs
sessions 840
shared_pool_reserved_size 209715200
shared_pool_size 644349952
sort_area_retained_size 1048576
sort_area_size 31457280
text_enable TRUE
timed_statistics TRUE
user_dump_dest D:\oracle\ora81\admin\MKTPRD\udum
-------------------------------------------------------------

End of Report