Performance issue when moving from 8i to 9i

[Vinodpillai]

I have a vb-oracle app. I moved the backend from 8i to 9i. No change in logic. Kept the oracle parameters the same.
The number of apps that hit above the 20 sec resonse time has increased from 3% to 6%.
I have checked the hit ratio, disk I/O rates, no queries executing for long, in short the stats pack report also is good.

Any idea/tips where i need to look into.

Below is th stats pack report

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA8I 4239284712 ora8i 1 9.2.0.4.0 NO GESBINAPSBKP

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1377 26-May-04 11:00:03 239 31.7
End Snap: 1388 26-May-04 13:00:06 316 32.1
Elapsed: 120.05 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 703M Std Block Size: 8K
Shared Pool Size: 480M Log Buffer: 32K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 17,889.33 6,512.20
Logical reads: 2,290.68 833.87
Block changes: 136.92 49.84
Physical reads: 1,466.03 533.67
Physical writes: 77.92 28.36
User calls: 354.35 128.99
Parses: 92.15 33.54
Hard parses: 18.21 6.63
Sorts: 11.94 4.35
Logons: 0.08 0.03
Executes: 927.69 337.70
Transactions: 2.75

% Blocks changed per Read: 5.98 Recursive Call %: 78.75
Rollback per transaction %: 0.41 Rows per Sort: 1789.84

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.70 Redo NoWait %: 99.99
Buffer Hit %: 76.07 In-memory Sort %: 99.74
Library Hit %: 96.92 Soft Parse %: 80.24
Execute to Parse %: 90.07 Latch Hit %: 99.60
Parse CPU to Parse Elapsd %: 85.19 % Non-Parse CPU: 90.59

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 98.25 86.82
% SQL with executions>1: 35.68 34.70
% Memory for SQL w/exec>1: 28.11 26.06

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 2,763,779 5,525 40.36
direct path read 767,003 3,543 25.88
CPU time 3,033 22.16
PX Deq: Execute Reply 2,061 818 5.98
db file scattered read 137,043 312 2.28
-------------------------------------------------------------
Wait Events for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 2,763,779 0 5,525 2 139.7
direct path read 767,003 0 3,543 5 38.8
PX Deq: Execute Reply 2,061 156 818 397 0.1
db file scattered read 137,043 0 312 2 6.9
buffer busy waits 214,585 0 126 1 10.8
library cache pin 119 33 101 852 0.0
db file parallel read 6,375 0 90 14 0.3
log file sync 18,029 13 38 2 0.9
SQL*Net more data to client 36,126 0 37 1 1.8
latch free 20,196 2,407 19 1 1.0
PX Deq: Parse Reply 89 4 13 150 0.0
log file switch completion 37 0 7 198 0.0
control file sequential read 2,351 0 7 3 0.1
PX Deq Credit: send blkd 25 1 3 123 0.0
PX Deq: Signal ACK 122 54 3 24 0.0
PX Deq: Join ACK 108 0 2 21 0.0
log file parallel write 22,146 21,820 2 0 1.1
db file parallel write 2,959 0 2 1 0.1
log file sequential read 208 0 2 9 0.0
control file parallel write 2,549 0 2 1 0.1
PX qref latch 1 1 1 1018 0.0
SQL*Net message from dblink 154 0 0 2 0.0
SQL*Net break/reset to clien 938 0 0 0 0.0
direct path write 689 0 0 1 0.0
enqueue 98 0 0 4 0.0
log buffer space 45 0 0 4 0.0
LGWR wait for redo copy 103 0 0 0 0.0
log file single write 52 0 0 0 0.0
PX Deq: Table Q Sample 4 0 0 1 0.0
async disk IO 65 0 0 0 0.0
PX Deq: Msg Fragment 7 0 0 0 0.0
SQL*Net message to dblink 154 0 0 0 0.0
SQL*Net more data from dblin 14 0 0 0 0.0
PX Deq: Table Q qref 6 0 0 0 0.0
PX Deq Credit: need buffer 2 0 0 0 0.0
SQL*Net message from client 2,350,390 0 904,288 385 118.8
PX Idle Wait 3,822 3,811 12,798 3349 0.2
jobq slave wait 126 123 387 3068 0.0
PX Deq: Execution Msg 2,172 40 185 85 0.1
SQL*Net message to client 2,350,470 0 6 0 118.8
PX Deq: Table Q Normal 148 2 5 37 0.0
SQL*Net more data from clien 1,374 0 2 1 0.1
-------------------------------------------------------------
Background Wait Events for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file sequential read 2,243 0 7 3 0.1
db file sequential read 983 0 6 6 0.0
db file scattered read 253 0 3 10 0.0
log file parallel write 22,144 21,818 2 0 1.1
db file parallel write 2,959 0 2 1 0.1
log file sequential read 208 0 2 9 0.0
control file parallel write 2,549 0 2 1 0.1
direct path read 221 0 0 2 0.0
LGWR wait for redo copy 103 0 0 0 0.0
log file single write 52 0 0 0 0.0
direct path write 221 0 0 0 0.0
log buffer space 3 0 0 1 0.0
async disk IO 65 0 0 0 0.0
latch free 2 0 0 0 0.0
rdbms ipc message 74,645 47,706 46,175 619 3.8
pmon timer 2,465 2,465 8,198 3326 0.1
smon timer 24 24 8,101 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> 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

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
4,612,515 226 20,409.4 28.0 292.69 3246.85 2308742209
SELECT A.* FROM NAPS_DDUP_PER_INDX_NEW A WHERE A.C_REF_NO
<> :b3 AND A.C_STDCODE_RES = :b2 AND
A.C_INDEX_ADD = :b1

1,939,282 14 138,520.1 11.8 365.22 930.34 3116886065
Module: C:\Documents and Settings\All Users\Desktop\Shor
begin sp_scoring_main_olV0001); end;

1,508,353 260 5,801.4 9.1 18.22 238.10 2790059889
SELECT Count(*) From naps_ddup_negative_indx_NEW Where
C_REF_NO <> :b4 AND D_DOB = :b3 AND
C_STDCODE_OFF = :b2 AND C_INDEX_ADD_OFF
I = :b1

1,283,154 165 7,776.7 7.8 12.63 113.26 1830281883
SELECT A.* FROM naps_ddup_negative_indx_NEW A WHERE A.C_RE
F_NO <> :b3 AND A.C_STDCODE_RES = :b2 AND
A.C_INDEX_ADD = :b1

878,710 67 13,115.1 5.3 232.44 1512.32 1492940356
BEGIN sp_scoring_main_ol1) ; END;

567,808 670 847.5 3.4 64.80 72.03 607027098
BEGIN sp_verification_mismatch1) ; END;

431,016 71,837 6.0 2.6 28.94 881.69 4001083016
SELECT C_STATUS_CD,C_DESCRIPTION FROM NAPS_APP_M, NAPS
_STATUS_M WHERE C_REF_NO = :b1 AND NAPS_AP
P_M.C_STATUS_CD = NAPS_STATUS_M.C_INTERNAL_CODE

258,792 2,081,892 0.1 1.6 144.02 143.87 3920974219
SELECT COUNT(*) FROM DUAL WHERE :b2 LIKE :b1

235,139 261 900.9 1.4 4.03 101.31 3986791793
SELECT Count(*) From naps_ddup_per_indx_NEW Where C_REF
_NO <> :b4 AND D_DOB = :b3 AND
C_STDCODE_OFF = :b2 AND C_INDEX_ADD_OFFI = :
b1

200,554 1 200,554.0 1.2 5.92 33.74 443342356
select C_INDEX_ADD,C_INDEX_ADD_WORD,count(*) count,min(a.c_ref_
no) min from naps_app_personal a,naps_app_m b where a.c_ref_no =
b.c_ref_no and C_INDEX_ADD is not null and C_INDEX_ADD_WORD is
null and C_INDEX_ADD_WORD_OFFI is null and c_status_cd = '30' gr
oup by C_INDEX_ADD,C_INDEX_ADD_WORD order by substr(C_INDEX_ADD,

196,849 1,451,548 0.1 1.2 79.13 75.57 4093301498
SELECT COUNT(*) FROM DUAL WHERE :b7 IN b6,:b5,:b4,:b3,:b2,:b1)

SQL ordered by Gets for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> 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

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

156,511 6,787 23.1 0.9 2.11 3.00 3748638263
DELETE from naps_temp_token

145,166 111 1,307.8 0.9 0.59 0.66 3157916517
SELECT A.* From naps_ddup_per_indx_NEW A Where A.C_REF_NO
<> :b4 AND A.C_STDCODE_OFF = :b3 AND
A.D_DOB = To_dateb2,'dd/mm/yyyy') AND A.C_INDE
X_ADD_OFFI = :b1

144,765 261 554.7 0.9 4.97 7.81 2065655552
SELECT A.* FROM NAPS_DDUP_PER_INDX_NEW A WHERE A.C_REF_NO
<> :b2 AND A.D_DOB = TO_DATEb1,'DD/MM/YYYY
')

138,958 1,172 118.6 0.8 2.42 2.44 3911990899
select C_REJECT_CD,C_DESCRIPTION from NAPS_REJ_M order by C_REJE
CT_CD

127,390 4 31,847.5 0.8 12.58 87.16 1636096563
BEGIN sp_MAIN_ol1) ; END;

114,091 1 114,091.0 0.7 5.70 36.46 2208529929
Module: SQL*Plus
select c_ref_no from naps_ddup_per_indx_new where c_index_add =
'%' and c_ref_no in( select c_ref_no from tmp_madan where ((c_la
stmodifieddate - to_date(c_first_name,'dd/mm/yyyy hh24:mi:ss'))
* 100000) > 50 and c_lastmodifieddate >= to_date('26/05/2004','
dd/mm/yyyy'))

102,426 429,750 0.2 0.6 26.78 25.55 3768677899
SELECT COUNT(*) FROM DUAL WHERE :b2 LIKE :b1

99,428 1 99,428.0 0.6 0.58 0.66 1001459515
Select a.C_REF_NO,b.C_DESCRIPTION,c.C_FIRST_NAME,c.C_LAST_NAME,
c.C_MOTH_MAID_NAME ,a.C_BATCH_NO,a.C_COMP_NAME,c.D_DOB,a.C_USERI
D,a.C_RESULT_STREAM,a.F_CREDITLIMIT,a.c_account_no,a.d_billing_c
ycle,a.C_PRIM_CARD_NO,a.C_ADDON_CARD_NO,a.c_source_cd ,a.c_promo
_cd, a.c_lastmodifieddate,c_de_userid, Case when substr(NVL(a.C_

95,724 977,940 0.1 0.6 54.45 54.40 184501379
SELECT COUNT(*) FROM DUAL WHERE :b7 IN b6,:b5,:b4,:b3,:b2,:b1)


91,708 939 97.7 0.6 1.86 1.74 1335861604
SELECT C_CARD_TYPE,C_CARD_DESCRIPTION FROM NAPS_CARD_TYPE_M orde
r by C_CARD_TYPE

83,086 1 83,086.0 0.5 3.17 110.70 2220301797
SELECT count(DISTINCT A.C_REF_NO) FROM NAPS_APP_TRACKING B ,N
SQL ordered by Gets for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> 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

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
APS_APP_M A WHERE A.C_REF_NO=B.C_REF_NO AND D_END_TIME >=
TO_DATEb3, 'DD/MM/YYYY HH24:MI:SS') AND D_END_TIME < TO
_DATEb2, 'DD/MM/YYYY HH24:MI:SS') AND C_INPUT_STATUS_CD
IN ('04' ,'08') AND C_OUTPUT_STATUS_CD = '11' AND A.C_SOUR

71,775 261 275.0 0.4 5.00 205.66 3520295509
SELECT COUNT(*) FROM NAPS_DDUP_PER_INDX_NEW WHERE C_REF
_NO <> :b2 AND D_DOB = TO_DATEb1,'D
D/MM/YYYY')

68,388 34,194 2.0 0.4 2.20 2.23 417894355
SELECT C_COLUMN_NAME, C_DATATYPE, C_TABLE_NAME from naps_
field_m where I_FIELD_NO = :b1

-------------------------------------------------------------
SQL ordered by Reads for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
2,135,675 226 9,449.9 20.2 292.69 3246.85 2308742209
SELECT A.* FROM NAPS_DDUP_PER_INDX_NEW A WHERE A.C_REF_NO
<> :b3 AND A.C_STDCODE_RES = :b2 AND
A.C_INDEX_ADD = :b1

553,272 14 39,519.4 5.2 365.22 930.34 3116886065
Module: C:\Documents and Settings\All Users\Desktop\Shor
begin sp_scoring_main_olV0001); end;

245,246 67 3,660.4 2.3 232.44 1512.32 1492940356
BEGIN sp_scoring_main_ol1) ; END;

198,656 1 198,656.0 1.9 5.92 33.74 443342356
select C_INDEX_ADD,C_INDEX_ADD_WORD,count(*) count,min(a.c_ref_
no) min from naps_app_personal a,naps_app_m b where a.c_ref_no =
b.c_ref_no and C_INDEX_ADD is not null and C_INDEX_ADD_WORD is
null and C_INDEX_ADD_WORD_OFFI is null and c_status_cd = '30' gr
oup by C_INDEX_ADD,C_INDEX_ADD_WORD order by substr(C_INDEX_ADD,

124,026 71,837 1.7 1.2 28.94 881.69 4001083016
SELECT C_STATUS_CD,C_DESCRIPTION FROM NAPS_APP_M, NAPS
_STATUS_M WHERE C_REF_NO = :b1 AND NAPS_AP
P_M.C_STATUS_CD = NAPS_STATUS_M.C_INTERNAL_CODE

90,235 260 347.1 0.9 18.22 238.10 2790059889
SELECT Count(*) From naps_ddup_negative_indx_NEW Where
C_REF_NO <> :b4 AND D_DOB = :b3 AND
C_STDCODE_OFF = :b2 AND C_INDEX_ADD_OFF
I = :b1

70,604 1 70,604.0 0.7 5.70 36.46 2208529929
Module: SQL*Plus
select c_ref_no from naps_ddup_per_indx_new where c_index_add =
'%' and c_ref_no in( select c_ref_no from tmp_madan where ((c_la
stmodifieddate - to_date(c_first_name,'dd/mm/yyyy hh24:mi:ss'))
* 100000) > 50 and c_lastmodifieddate >= to_date('26/05/2004','
dd/mm/yyyy'))

36,943 261 141.5 0.3 5.00 205.66 3520295509
SELECT COUNT(*) FROM NAPS_DDUP_PER_INDX_NEW WHERE C_REF
_NO <> :b2 AND D_DOB = TO_DATEb1,'D
D/MM/YYYY')

33,949 2 16,974.5 0.3 0.02 12.41 1994146188
SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "GDSD") */ A1.C
0 C0,A2."C_EMPLOYER_CITY" C1,A2."C_CITY" C2,A1.C12 C3,A1.C9 C4,A
1.C2 C5,A1.C3 C6,A1.C4 C7,A1.C5 C8,A1.C6 C9,A1.C1 C10 FROM (SELE
CT /*+ ORDERED NO_EXPAND USE_NL(A4) INDEX(A4 "PK_NAPS_AGENCY_M")
*/ A3.C0 C0,A3.C1 C1,A3.C2 C2,A3.C3 C3,A3.C4 C4,A3.C5 C5,A3.C6

28,572 261 109.5 0.3 4.03 101.31 3986791793
SELECT Count(*) From naps_ddup_per_indx_NEW Where C_REF
_NO <> :b4 AND D_DOB = :b3 AND
C_STDCODE_OFF = :b2 AND C_INDEX_ADD_OFFI = :
SQL ordered by Reads for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
b1

25,116 165 152.2 0.2 12.63 113.26 1830281883
SELECT A.* FROM naps_ddup_negative_indx_NEW A WHERE A.C_RE
F_NO <> :b3 AND A.C_STDCODE_RES = :b2 AND
A.C_INDEX_ADD = :b1

24,571 13 1,890.1 0.2 1.52 9.98 2256114980
select * from NAPS_batch_m where C_BATCH_DEO_NAME='RINKU'and C_B
ATCH_STATUS ='01'

19,794 1 19,794.0 0.2 3.17 110.70 2220301797
SELECT count(DISTINCT A.C_REF_NO) FROM NAPS_APP_TRACKING B ,N
APS_APP_M A WHERE A.C_REF_NO=B.C_REF_NO AND D_END_TIME >=
TO_DATEb3, 'DD/MM/YYYY HH24:MI:SS') AND D_END_TIME < TO
_DATEb2, 'DD/MM/YYYY HH24:MI:SS') AND C_INPUT_STATUS_CD
IN ('04' ,'08') AND C_OUTPUT_STATUS_CD = '11' AND A.C_SOUR

19,004 4 4,751.0 0.2 12.58 87.16 1636096563
BEGIN sp_MAIN_ol1) ; END;

9,831 260 37.8 0.1 1.09 58.64 3442001445
SELECT COUNT(*) FROM NAPS_DDUP_NEGATIVE_INDX_NEW WHERE
C_REF_NO <> :b2 AND D_DOB = TO_DATE
b1,'DD/MM/YYYY')

4,695 7 670.7 0.0 0.58 1.71 1958761231
select * from naps_batch_m where (C_BATCH_STATUS = '00' or C_BA
TCH_STATUS = 'P') Order By C_BATCH_NO

3,607 264 13.7 0.0 0.69 20.02 916995066
SELECT c_ref_no FROM NAPS_DDUP_PER_INDX_NEW WHERE C_STDCODE
_RES = :b3 AND C_INDEX_ADD = :b2
AND C_REF_NO <> :b1 AND ROWNUM=1

3,163 1 3,163.0 0.0 1.00 7.66 2581447232
SELECT COUNT(*) FROM NAPS_APP_M WHERE D_MODIFIED_STA
TUS >= TO_DATEb3, 'DD/MM/YYYY HH24:MI:SS') AND D_MODIFIED_
STATUS < TO_DATEb2, 'DD/MM/YYYY HH24:MI:SS') AND C_STATUS_
CD='10' AND C_PROMO_CD LIKE 'SU%' AND ( ( :b1 = 'Y'
AND SUBSTR(C_UNIQUE_CD,1,1) IN (SELECT C_VALUE1 FROM N

3,027 1 3,027.0 0.0 0.64 1.34 2873876478
SELECT COUNT(*) FROM NAPS_APP_M WHERE C_LASTMODIFIEDDATE >
= TO_DATEb3, 'DD/MM/YYYY HH24:MI:SS') AND C_LASTMODIFIEDDAT
E < TO_DATEb2, 'DD/MM/YYYY HH24:MI:SS') AND C_STATUS_CD='31
' AND ( ( :b1 = 'Y' AND SUBSTR(C_UNIQUE_CD,1,1) IN
(SELECT C_VALUE1 FROM NAPS_PARAM_M WHERE C_PAR_CD = 'INST') )

3,009 1 3,009.0 0.0 0.39 11.30 4284467539
SELECT COUNT(*) FROM NAPS_APP_M WHERE D_MODIFIED_STATUS >=

-------------------------------------------------------------
SQL ordered by Executions for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
2,081,892 2,081,346 1.0 0.00 0.00 3920974219
SELECT COUNT(*) FROM DUAL WHERE :b2 LIKE :b1

1,451,548 1,451,346 1.0 0.00 0.00 4093301498
SELECT COUNT(*) FROM DUAL WHERE :b7 IN b6,:b5,:b4,:b3,:b2,:b1)


977,940 977,881 1.0 0.00 0.00 184501379
SELECT COUNT(*) FROM DUAL WHERE :b7 IN b6,:b5,:b4,:b3,:b2,:b1)


429,750 429,740 1.0 0.00 0.00 3768677899
SELECT COUNT(*) FROM DUAL WHERE :b2 LIKE :b1

218,141 218,140 1.0 0.00 0.00 2813514905
SELECT COUNT(*) FROM DUAL WHERE b4 IN b3,:b2,:b1))

218,114 218,114 1.0 0.00 0.00 2668960897
SELECT COUNT(*) FROM DUAL WHERE b2 LIKE :b1)

82,754 82,753 1.0 0.00 0.00 1260263532
SELECT COUNT(*) FROM DUAL WHERE b2 IN b1))

82,746 82,746 1.0 0.00 0.00 3110758603
SELECT COUNT(*) FROM DUAL WHERE b1 LIKE :b2) OR b2 LIKE :b1)


71,837 71,837 1.0 0.00 0.01 4001083016
SELECT C_STATUS_CD,C_DESCRIPTION FROM NAPS_APP_M, NAPS
_STATUS_M WHERE C_REF_NO = :b1 AND NAPS_AP
P_M.C_STATUS_CD = NAPS_STATUS_M.C_INTERNAL_CODE

61,417 61,417 1.0 0.00 0.00 2187219933
SELECT Count(*) From Dual Where :b6 Like :b5 OR :b4 IN b3,:b2,
:b1)

34,194 34,194 1.0 0.00 0.00 417894355
SELECT C_COLUMN_NAME, C_DATATYPE, C_TABLE_NAME from naps_
field_m where I_FIELD_NO = :b1

31,678 31,678 1.0 0.00 0.00 2174885995
SELECT Count(*) From Dual Where :b6 Like :b5 OR :b4 IN b3,:b2,
:b1)

19,012 19,012 1.0 0.00 0.00 846669459
SELECT Count(*) From Dual Where :b2 Like :b1 AND :b1 <> '%%%'

18,832 18,832 1.0 0.00 0.00 3590349883
INSERT into naps_temp_token(name_field) values(ltrim(rtr
imb1)))

16,721 16,721 1.0 0.00 0.00 2757748061
SELECT Replaceb3,:b2,:b1) FROM Dual

SQL ordered by Executions for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
16,563 16,563 1.0 0.00 0.00 1266946682
SELECT soundexb1) from sys.dual

15,043 28 0.0 0.00 0.00 886202170
SELECT subcode_param FROM NAPS_DICTIONARY WHERE
LTRIM(RTRIM(code_param)) = LTRIM(RTRIMb1))

13,207 13,207 1.0 0.00 0.00 2855084871
INSERT Into naps_temp_token(c_alpha) Values(Substrb1,1,1))

12,353 12,353 1.0 0.00 0.00 2801937624
SELECT COUNT(*) FROM DUAL WHERE ('%PO%' LIKE '%PO%') OR('%PO%'
LIKE '%PS%')

11,134 11,134 1.0 0.00 0.00 1311570364
INSERT /*+ IDX(0) */ INTO "NUCLEUS"."MLOG$_NAPS_APP_M2" (dmltype
$$,old_new$$,snaptime$$,change_vector$$,m_row$$) VALUES d,,t
o_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:m)

9,438 9,438 1.0 0.00 0.00 3853296964
INSERT /*+ IDX(0) */ INTO "NUCLEUS"."MLOG$_NAPS_APP_PERSONAL3" (
dmltype$$,old_new$$,snaptime$$,change_vector$$,m_row$$) VALUES (
:d,,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,
:m)

9,002 0 0.0 0.00 0.00 1053795750
COMMIT

8,431 8,431 1.0 0.00 0.00 3513685934
select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual

7,109 110 0.0 0.00 0.00 956236531
SELECT ENABLE from naps_occupation_type where I_FIEL
D_NO = :b2 AND C_OCCUPATION_TYPE = :b1 AND
ENABLE = 'F' Group By Enable

7,109 7,109 1.0 0.00 0.00 1909787111
SELECT C_COLUMN_NAME, C_DATATYPE , C_TABLE_NAME, C_NULL_FLAG
from naps_field_m where I_FIELD_NO = :b1

6,787 42,622 6.3 0.00 0.00 3748638263
DELETE from naps_temp_token

6,120 6,120 1.0 0.00 0.00 2662418659
SELECT COUNT(*) FROM DUAL WHERE ('%PO%' LIKE '%RK%')

5,770 3,362 0.6 0.00 0.00 1093956359
SELECT NO_FIELD FROM naps_temp_token WHERE NO_FIELD IS Not
NULL Order By NO_FIELD DESC

5,770 18,586 3.2 0.00 0.00 2260829244
SELECT NAME_FIELD FROM naps_temp_token WHERE NAME_FIELD IS
Not NULL Group By NAME_FIELD Order By Length(NAME_FIELD) D
esc, NAME_FIELD
SQL ordered by Executions for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------

5,770 13,845 2.4 0.00 0.00 2637133116
SELECT C_ALPHA FROM naps_temp_token WHERE C_ALPHA IS Not N
ULL Group By C_ALPHA Order By C_ALPHA DESC

5,588 3,406 0.6 0.00 0.00 2659013689
SELECT NVL(C_BPHONE_VERI,'N'), NVL(C_RPHONE_VERI,'N') FROM
NAPS_VERIFICATION_TELEPH WHERE C_REF_NO = :b1

5,587 5,587 1.0 0.00 0.00 446315153
SELECT C_COLUMN_NAME,C_DATATYPE,C_TABLE_NAME,I_FIELD_NO FROM
NAPS_VERI_FIELD_MAPPING a, NAPS_FIELD_M b WHERE a.I_APP_FIELD
_NO = b.I_FIELD_NO AND a.I_VERI_FIELD_NO = :b1

5,587 5,587 1.0 0.00 0.00 3878320006
SELECT UPPER(C_PARAM_CODE) FROM NAPS_FIELD_M WHERE I_F
IELD_NO = :b1

5,586 5,586 1.0 0.00 0.00 2623941321
SELECT C_COLUMN_NAME, C_DATATYPE, C_TABLE_NAME, C_CONTROL_NAME,
C_NULL_FLAG from naps_field_m where I_FIELD_NO = :
b1

4,863 3 0.0 0.00 0.00 220575450
SELECT subcode_param FROM NAPS_DICTIONARY WHER

-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
12,352 12,353 1.86 2801937624
SELECT COUNT(*) FROM DUAL WHERE ('%PO%' LIKE '%PO%') OR('%PO%'
LIKE '%PS%')

8,417 8,431 1.27 3513685934
select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') from dual

6,120 6,120 0.92 2662418659
SELECT COUNT(*) FROM DUAL WHERE ('%PO%' LIKE '%RK%')

4,700 9,438 0.71 3853296964
INSERT /*+ IDX(0) */ INTO "NUCLEUS"."MLOG$_NAPS_APP_PERSONAL3" (
dmltype$$,old_new$$,snaptime$$,change_vector$$,m_row$$) VALUES (
:d,,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,
:m)

3,710 11,134 0.56 1311570364
INSERT /*+ IDX(0) */ INTO "NUCLEUS"."MLOG$_NAPS_APP_M2" (dmltype
$$,old_new$$,snaptime$$,change_vector$$,m_row$$) VALUES d,,t
o_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:m)

2,610 2,610 0.39 691436667
Module: C:\Documents and Settings\All Users\Desktop\Shor
SELECT COUNT(*) FROM DUAL WHERE ('%O%' LIKE '%R%') OR('%O%' LIK
E '%S%')

2,536 2,536 0.38 284054344
SELECT COUNT(*) FROM DUAL WHERE ('%O%' LIKE '%C%') OR('%O%' LIK
E '%O%')

2,491 2,491 0.38 4271482514
SELECT COUNT(*) FROM DUAL WHERE ('%NO%' LIKE '%NO%')

2,441 2,441 0.37 124846797
Module: C:\Documents and Settings\324004063\Desktop\Shor
SELECT COUNT(*) FROM DUAL WHERE ('%A%' LIKE '%A%')

2,171 2,171 0.33 2547138897
select naps_form_d.i_field_no,naps_form_d.C_enable,naps_field_m.
c_control_name from naps_form_d,naps_field_m where naps_form_d.c
_form_type='CRP' AND naps_form_d.i_field_no=naps_field_m.i_field
_no AND naps_form_d.C_MANADATORY_DE= 'T'

1,897 1,897 0.29 2136647675
Module: C:\Documents and Settings\All Users\Desktop\Shor
SELECT COUNT(*) FROM DUAL WHERE ('%C%' LIKE '%R%') OR('%C%' LIK
E '%S%')

1,857 1,857 0.28 1228088873
SELECT COUNT(*) FROM DUAL WHERE ('%C%' LIKE '%C%') OR('%C%' LIK
E '%O%')

1,747 1,747 0.26 1017286399
SELECT COUNT(*) FROM DUAL WHERE ('%PS%' LIKE '%PO%') OR('%PS%'
SQL ordered by Parse Calls for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
LIKE '%PS%')

1,735 1,735 0.26 2992806086
DELETE NAPS_DEDUPE_WEIGHTS

1,560 1,560 0.24 2707378256
Alter Session SET NLS_DATE_FORMAT = 'dd/mm/yyyy HH24:MI:SS'

1,540 1,540 0.23 953851328
select c_sub_cd ,C_DESC from naps_param_m where c_par_cd= 'SEX'
order by c_sub_cd

1,540 1,540 0.23 3847792913
select c_sub_cd,C_DESC from naps_param_m where c_par_cd= 'RELT'
order by c_sub_cd

1,474 1,474 0.22 3091636854
Module: C:\Documents and Settings\All Users\Desktop\Shor
SELECT COUNT(*) FROM DUAL WHERE ('%P%' LIKE '%R%') OR('%P%' LIK
E '%S%')

1,430 1,430 0.22 3078138008
SELECT COUNT(*) FROM DUAL WHERE ('%P%' LIKE '%C%') OR('%P%' LIK
E '%O%')

1,417 1,417 0.21 185062511
BEGIN sp_lock1, :2) ; END;

1,406 1,406 0.21 1599679777
SELECT COUNT(*) FROM DUAL WHERE ('%B%' LIKE '%A%')

1,392 1,392 0.21 2418147073
select C_control_name,ENABLE from naps_occupation_type a,naps_f
ield_m b where a.I_FIELD_NO= b.I_FIELD_NO and a.C_OCCUPATION_TY
PE='S'

1,389 1,389 0.21 1279062932
Select c_reject_cd,c_description from naps_rej_m where c_code_ty
pe='V'

1,387 1,387 0.21 3768325017
SELECT TRIM(C_VALUE1) FROM NAPS_PARAM_M WHERE C_PAR_CD='FLPA'

1,374 1,374 0.21 3748252145
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

1,172 1,172 0.18 3911990899
select C_REJECT_CD,C_DESCRIPTION from NAPS_REJ_M order by C_REJE
CT_CD

1,111 1,111 0.17 686971894
Select C_DESC from NAPS_PARAM_M where C_PAR_CD='IMGP' And C_SUB_
CD='A'

SQL ordered by Parse Calls for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,096 1,096 0.17 4123458742
alter session set nls_date_format='dd/mm/yyyy :hh24:mi:ss'

948 948 0.14 883975327
SELECT COUNT(*) FROM DUAL WHERE ('%NR%' LIKE '%NR%')

939 939 0.14 1335861604
SELECT C_CARD_TYPE,C_CARD_DESCRIPTION FROM NAPS_CARD_TYPE_M orde
r by C_CARD_TYPE

930 930 0.14 647787365
SELECT COUNT(*) FROM DUAL WHERE ('%A%' LIKE '%E%')

920 920 0.14 148934870
SELECT C_SUB_CD,C_DESC FROM NAPS_PARAM_M WHERE C_PAR_CD='RELT' o
rder by C_SUB_CD

920 920 0.14 4217296197
SELECT C_SUB_CD,C_DESC FROM NAPS_PARAM_M WHERE C_PAR_CD='ACCT' o
rder by C_SUB_CD

864 864 0.13 1364176075
SELECT COUNT(*) FROM DUAL WHERE ('%PS%' LIKE '%RK%')

838 9,002 0.13 1053795750
COMMIT

831 831 0.13 736851258
Select C_Column_Name,C_DataType,C_Control_Type,C_Control_name,I_
Field_No,C_Table_Name from naps_Field_M where C_Column_Name IS N
OT NULL order by i_field_no

-------------------------------------------------------------
Instance Activity Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 303,324 42.1 15.3
CPU used when call started 315,778 43.8 16.0
CR blocks created 2,031 0.3 0.1
DBWR buffers scanned 1,645,431 228.4 83.2
DBWR checkpoint buffers written 17,815 2.5 0.9
DBWR checkpoints 40 0.0 0.0
DBWR free buffers found 1,632,025 226.6 82.5
DBWR lru scans 2,622 0.4 0.1
DBWR make free requests 2,763 0.4 0.1
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 1,645,431 228.4 83.2
DBWR transaction table writes 288 0.0 0.0
DBWR undo block writes 6,277 0.9 0.3
DFO trees parallelized 29 0.0 0.0
PX local messages recv'd 4,106 0.6 0.2
PX local messages sent 4,106 0.6 0.2
Parallel operations downgraded 25 21 0.0 0.0
Parallel operations downgraded 50 2 0.0 0.0
Parallel operations downgraded to 0 0.0 0.0
Parallel operations not downgrade 6 0.0 0.0
SQL*Net roundtrips to/from client 2,349,009 326.1 118.7
SQL*Net roundtrips to/from dblink 154 0.0 0.0
active txn count during cleanout 3,398 0.5 0.2
background checkpoints completed 13 0.0 0.0
background checkpoints started 13 0.0 0.0
background timeouts 8,626 1.2 0.4
branch node splits 7 0.0 0.0
buffer is not pinned count 16,829,608 2,336.5 850.5
buffer is pinned count 16,052,798 2,228.6 811.3
bytes received via SQL*Net from c 297,980,611 41,369.0 15,059.4
bytes received via SQL*Net from d 68,924 9.6 3.5
bytes sent via SQL*Net to client 456,793,604 63,417.1 23,085.5
bytes sent via SQL*Net to dblink 25,848 3.6 1.3
calls to get snapshot scn: kcmgss 7,761,464 1,077.5 392.3
calls to kcmgas 34,145 4.7 1.7
calls to kcmgcs 4,089 0.6 0.2
change write time 517 0.1 0.0
cleanout - number of ktugct calls 3,743 0.5 0.2
cleanouts and rollbacks - consist 1,732 0.2 0.1
cleanouts only - consistent read 127 0.0 0.0
cluster key scan block gets 5,758 0.8 0.3
cluster key scans 2,976 0.4 0.2
commit cleanout failures: block l 56 0.0 0.0
commit cleanout failures: buffer 1 0.0 0.0
commit cleanout failures: callbac 0 0.0 0.0
commit cleanout failures: cannot 6 0.0 0.0
commit cleanouts 138,362 19.2 7.0
commit cleanouts successfully com 138,299 19.2 7.0
commit txn count during cleanout 3,930 0.6 0.2
consistent changes 13,570 1.9 0.7
consistent gets 21,740,967 3,018.3 1,098.8
consistent gets - examination 3,060,747 424.9 154.7
current blocks converted for CR 0 0.0 0.0
cursor authentications 32,411 4.5 1.6
data blocks consistent reads - un 13,591 1.9 0.7
db block changes 986,255 136.9 49.8
Instance Activity Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
db block gets 870,750 120.9 44.0
deferred (CURRENT) block cleanout 63,793 8.9 3.2
dirty buffers inspected 4,133 0.6 0.2
enqueue conversions 10,478 1.5 0.5
enqueue releases 88,497 12.3 4.5
enqueue requests 88,502 12.3 4.5
enqueue timeouts 10 0.0 0.0
enqueue waits 75 0.0 0.0
exchange deadlocks 0 0.0 0.0
execute count 6,682,131 927.7 337.7
free buffer inspected 4,343 0.6 0.2
free buffer requested 3,969,040 551.0 200.6
hot buffers moved to head of LRU 802,303 111.4 40.6
immediate (CR) block cleanout app 1,859 0.3 0.1
immediate (CURRENT) block cleanou 12,977 1.8 0.7
index fast full scans (direct rea 100 0.0 0.0
index fast full scans (full) 0 0.0 0.0
index fast full scans (rowid rang 113 0.0 0.0
index fetch by key 551,826 76.6 27.9
index scans kdiixs1 2,563,648 355.9 129.6
leaf node 90-10 splits 84 0.0 0.0
leaf node splits 433 0.1 0.0
logons cumulative 580 0.1 0.0
messages received 27,962 3.9 1.4
messages sent 27,962 3.9 1.4
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 15,664,159 2,174.7 791.6
opened cursors cumulative 664,523 92.3 33.6
parse count (failures) 16 0.0 0.0
parse count (hard) 131,137 18.2 6.6
parse count (total) 663,731 92.2 33.5
parse time cpu 28,553 4.0 1.4
parse time elapsed 33,517 4.7 1.7
physical reads 10,559,815 1,466.0 533.7
physical reads direct 6,610,903 917.8 334.1
physical writes 561,224 77.9 28.4
physical writes direct 525,873 73.0 26.6
physical writes non checkpoint 554,305 77.0 28.0
pinned buffers inspected 119 0.0 0.0
prefetched blocks 1,048,369 145.6 53.0
prefetched blocks aged out before 174 0.0 0.0
process last non-idle time 518,139,960,694 71,933,911.0 ############
queries parallelized 29 0.0 0.0
recursive calls 9,458,111 1,313.1 478.0
recursive cpu usage 122,683 17.0 6.2
redo blocks written 269,972 37.5 13.6
redo buffer allocation retries 81 0.0 0.0
redo entries 502,366 69.7 25.4
redo log space requests 38 0.0 0.0
redo log space wait time 738 0.1 0.0
redo ordering marks 0 0.0 0.0
redo size 128,856,868 17,889.3 6,512.2
redo synch time 3,772 0.5 0.2
redo synch writes 18,071 2.5 0.9
redo wastage 5,187,392 720.2 262.2
redo write time 729 0.1 0.0
Instance Activity Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo writer latching time 1 0.0 0.0
redo writes 22,144 3.1 1.1
rollback changes - undo records a 55,673 7.7 2.8
rollbacks only - consistent read 327 0.1 0.0
rows fetched via callback 480,696 66.7 24.3
session connect time 646,988,979,574 89,822,154.6 ############
session cursor cache count 2,228 0.3 0.1
session cursor cache hits 403,560 56.0 20.4
session logical reads 16,499,765 2,290.7 833.9
session pga memory 675,233,592 93,743.4 34,125.1
session pga memory max 680,894,408 94,529.3 34,411.2
session uga memory 8,609,648,616 1,195,286.5 435,116.4
session uga memory max 677,448,008 94,050.8 34,237.0
shared hash latch upgrades - no w 2,187,236 303.7 110.5
shared hash latch upgrades - wait 1,125 0.2 0.1
sorts (disk) 227 0.0 0.0
sorts (memory) 85,762 11.9 4.3
sorts (rows) 153,906,339 21,367.0 7,778.2
summed dirty queue length 17,585 2.4 0.9
switch current to new buffer 10,956 1.5 0.6
table fetch by rowid 11,022,797 1,530.3 557.1
table fetch continued row 125 0.0 0.0
table scan blocks gotten 8,047,096 1,117.2 406.7
table scan rows gotten 144,167,333 20,014.9 7,286.0
table scans (cache partitions) 0 0.0 0.0
table scans (direct read) 1,328 0.2 0.1
table scans (long tables) 1,460 0.2 0.1
table scans (rowid ranges) 1,328 0.2 0.1
table scans (short tables) 516,662 71.7 26.1
transaction rollbacks 383 0.1 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
user calls 2,552,356 354.4 129.0
user commits 19,706 2.7 1.0
user rollbacks 81 0.0 0.0
write clones created in backgroun 1 0.0 0.0
write clones created in foregroun 3 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
NAPS
3,639,285 505 3.2 2.7 9,906 1 213,272 0.6
INDX
163,711 23 5.1 1.0 17,660 2 960 4.1
TEMP
5,827 1 6.4 85.6 9,107 1 0 0.0
RBS
83 0 5.1 1.0 6,578 1 54 0.0
SYSTEM
3,716 1 7.0 1.3 111 0 19 5.8
TOOL1
2,199 0 7.2 1.0 1,220 0 0 0.0
DATA
63 0 12.5 1.0 13 0 0 0.0
CWSNIFFERDATA
44 0 14.5 1.8 29 0 0 0.0
CWDATAMARTDATA
13 0 24.6 1.0 13 0 0 0.0
DIGITAL
13 0 24.6 1.0 13 0 0 0.0
DRSYS
13 0 24.6 1.0 13 0 0 0.0
OEM_REPOSITORY
13 0 43.8 1.0 13 0 0 0.0
TOOLS
13 0 28.5 1.0 13 0 0 0.0
USERS
13 0 26.2 1.0 13 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
CWDATAMARTDATA I:\ORACLE\ORADATA\ORA8I\CWDATA01.DBF
13 0 24.6 1.0 13 0 0

CWSNIFFERDATA I:\ORACLE\ORADATA\ORA8I\CWSNIFFER01.DBF
44 0 14.5 1.8 29 0 0

DATA J:\ORACLE\ORADATA\ORA8I\DATATEMP01.DBF
63 0 12.5 1.0 13 0 0

DIGITAL I:\ORACLE\ORADATA\ORA8I\DIGITAL.DBF
13 0 24.6 1.0 13 0 0

DRSYS I:\ORACLE\ORADATA\ORA8I\DRSYS.DBF
13 0 24.6 1.0 13 0 0

INDX J:\ORACLE\ORADATA\ORA8I\INDX01.DBF
71,255 10 5.2 1.0 9,574 1 168 2.1
J:\ORACLE\ORADATA\ORA8I\INDX02.DBF
92,456 13 4.9 1.0 8,086 1 792 4.5

NAPS I:\ORACLE\ORADATA\ORA8I\NAPS01.DBF
1,159,906 161 3.4 2.9 4,227 1 32,494 0.6
I:\ORACLE\ORADATA\ORA8I\NAPS02.DBF
1,593,761 221 2.7 2.2 1,894 0 126,649 0.6
I:\ORACLE\ORADATA\ORA8I\NAPS03.DBF
882,432 123 3.9 3.5 3,050 0 54,129 0.6
K:\ORACLE\ORADATA\ORA8I\NAPS04.DBF
3,186 0 3.0 7.0 735 0 0

OEM_REPOSITORY H:\ORACLE\ORADATA\ORA8I\OEM_REPOSITORY.ORA
13 0 43.8 1.0 13 0 0

RBS K:\ORACLE\ORADATA\ORA8I\RBS01.DBF
83 0 5.1 1.0 6,578 1 54 0.0

SYSTEM H:\ORACLE\ORADATA\ORA8I\SYSTEM01.DBF
3,716 1 7.0 1.3 111 0 19 5.8

TEMP K:\ORACLE\ORADATA\ORA8I\TEMP01.DBF
330 0 8.6 81.0 530 0 0
K:\ORACLE\ORADATA\ORA8I\TEMP02.DBF
5,497 1 6.3 85.9 8,577 1 0

TOOL1 J:\ORACLE\ORADATA\ORA8I\TOOL1.DBF
2,199 0 7.2 1.0 1,220 0 0

TOOLS H:\ORACLE\ORADATA\ORA8I\TOOLS01.DBF
13 0 28.5 1.0 13 0 0

USERS H:\ORACLE\ORADATA\ORA8I\USERS01.DBF
13 0 26.2 1.0 13 0 0

File IO Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
-------------------------------------------------------------
Buffer Pool Statistics for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 90,000 76.1 16,499,891 3,949,167 35,359 0 0 214,486
-------------------------------------------------------------

Instance Recovery Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B 0 14 728 12711 12000 18432 12000
E 0 14 832 12264 12000 18432 12000
-------------------------------------------------------------



Buffer wait Statistics for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> ordered by wait time desc, waits desc

Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 214,307 127 1
undo block 54 0 0
-------------------------------------------------------------
Enqueue activity for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc

Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TC 135 135 0 27 12.63 0
CU 490 490 0 14 3.43 0
PS 529 523 10 34 .00 0
-------------------------------------------------------------
Rollback Segment Stats for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 26.0 0.00 0 0 0 0
1 2,354.0 0.00 1,689,158 0 0 0
2 2,813.0 0.00 2,100,412 61 0 0
3 4,907.0 0.00 3,757,858 108 0 0
4 4,839.0 0.00 4,132,166 121 0 0
5 3,245.0 0.00 2,550,520 73 0 0
6 2,410.0 0.00 1,779,050 0 0 0
7 3,180.0 0.00 2,613,296 37 0 0
8 26.0 0.00 0 0 0 0
9 41.0 0.00 2,412 0 0 0
10 3,760.0 0.00 8,256,344 0 0 0
11 2,421.0 0.00 1,863,952 53 0 0
12 3,094.0 0.00 2,603,156 0 0 0
13 4,474.0 0.00 4,084,440 0 0 0
14 5,104.0 0.00 4,498,686 126 0 0
15 3,261.0 0.00 2,805,584 0 0 0
16 2,417.0 0.00 2,041,260 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 1,302,528 0 1,302,528
1 209,707,008 0 209,715,200 209,707,008
2 20,963,328 40,960 20,971,520 20,963,328
3 20,963,328 40,960 20,971,520 20,963,328
4 20,963,328 41,298 20,971,520 20,963,328
5 16,703,488 41,254 20,971,520 16,703,488
6 19,324,928 40,960 20,971,520 19,324,928
7 20,062,208 215,616 20,971,520 20,062,208
8 20,185,088 401,166 20,971,520 20,185,088
9 20,963,328 42,957 20,971,520 20,963,328
10 18,669,568 47,224 20,971,520 18,669,568
11 18,382,848 41,711 20,971,520 18,382,848
12 20,963,328 775,241 20,971,520 20,963,328
13 20,963,328 48,408 20,971,520 20,963,328
14 20,955,136 40,964 20,971,520 20,955,136
15 20,963,328 61,194 20,971,520 20,963,328
16 20,963,328 154,611 20,971,520 20,963,328
-------------------------------------------------------------
Latch Activity for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->"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 Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 22,156 0.0 0 0
FAL request queue 26 0.0 0 0
FIB s.o chain latch 182 0.0 0 0
FOB s.o list latch 2,284 0.0 0 0
SQL memory manager worka 134 0.0 0 0
active checkpoint queue 8,315 0.0 0 0
alert log latch 52 0.0 0 0
archive control 277 0.0 0 0
archive process latch 219 0.5 0.0 0 0
begin backup scn array 15 0.0 0 0
cache buffer handles 837 0.0 0 0
cache buffers chains 36,792,340 0.3 0.0 0 5,122,206 0.0
cache buffers lru chain 68,522 0.2 0.0 0 5,138,281 0.1
channel handle pool latc 1,059 0.0 0 0
channel operations paren 6,516 0.0 0 0
checkpoint queue latch 721,732 0.0 0.0 0 35,266 0.0
child cursor hash table 924,194 0.1 0.0 0 0
commit callback allocati 2 0.0 0 0
dictionary lookup 1 0.0 0 0
dml lock allocation 97,282 0.0 0.0 0 0
dummy allocation 1,333 3.0 0.0 0 0
enqueue hash chains 187,586 0.0 0.0 0 0
enqueues 67,684 0.2 0.0 0 0
error message lists 279 11.5 0.0 0 0
event group latch 567 0.0 0 0
global tx hash mapping 10,278 0.0 0 0
hash table column usage 635 0.0 0 959,230 0.0
job workq parent latch 0 0 4 0.0
job_queue_processes para 122 0.0 0 0
ktm global data 24 0.0 0 0
lgwr LWN SCN 22,314 0.3 0.0 0 0
library cache 22,146,603 1.1 0.0 6 440,737 4.4
library cache load lock 4,344 0.0 0 0
library cache pin 16,176,283 0.6 0.0 0 0
library cache pin alloca 1,754,037 0.0 0.0 0 0
list of block allocation 1,660 0.0 0 0
loader state object free 4,044 0.5 0.0 0 0
longop free list parent 4 0.0 0 4 0.0
messages 184,617 0.0 0.0 0 0
mostly latch-free SCN 22,594 0.8 0.0 0 0
multiblock read objects 320,148 0.0 0.0 0 0
ncodef allocation latch 118 0.0 0 0
object stats modificatio 1,592 0.0 0 0
parallel query alloc buf 2,418 1.4 0.0 0 0
parallel query stats 214 21.5 0.0 0 0
post/wait queue 27,470 0.0 0.0 0 18,114 0.0
process allocation 567 0.0 0 567 0.0
process group creation 1,059 0.0 0 0
process queue 620 4.0 0.0 0 0
process queue reference 81,953 0.0 0.0 0 4,787 0.5
Latch Activity for DB: ORA8I Instance: ora8i Snaps: 1377 -1388
->"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 Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
query server freelists 669 2.2 0.0 0 0
query server process 20 0.0 0

 

[alexhu]

changes in execution plan for your 'slow' queries ?

Alex

 

[sem]

I think that Buffer Hit %: 76.07 is not sufficient. This may be caused by inefficient execution plans. Have you gathered statistics after migration?

Regards, Dima

http://seminihin.narod.ru

 

[stefanhei]

Is 9i "warmed up" (all buffers,... filled)? Are statspack-reports from 8i available to compare? Does your 9i have the same physical layout as 8i (files on different disks,...)?

Stefan

 

[Vinodpillai]

unfortunately the statspack report from 8i is not avaialbale. I am trying to setup an 8i env to do some additional tracing.

any inputs from the 9i statspack report that i could use to get a qucik solution