oraperf logo

YAPP Report Generator

for STATSPACK files

(Version 2.0.11 Beta)

General Information Version Information Reponse Time Information CPU Time Information Wait Time Information Init.ora Parameters Advice Comments C:\sp_15542_15544.txt

General Information

This report is generated by the new Report generator. It is still Beta. Please report problems to OraPerf.com Support

Timing Information

Begin Time End Time Seconds Elapsed
18-Dec-06 13:26:38 18-Dec-06 13:39:02 744

Version Information

Host Name Version Information
pulpdb001.hsa.co.uk 9.2.0.6.0

Breakdown of Response Time

Response Time
  Time Percentage Per Execute Per User Call Per Transaction
Response Time 360931 100.00% 0.75 1.07 443.40
CPU Time 23031 6.38% 0.05 0.07 28.29
Wait Time 337900 93.62% 0.71 1.01 415.11

Breakdown of CPU Time

If the CPU time is the largest contributor to the total response time, it will need to be brokendown into different areas of CPU usage. In Oracle we can currently have three areas of CPU usage:
  • Parse CPU Time
  • Recursive CPU Time
  • Other CPU Time

CPU Time
Time Percentage Per Execute Per User Call Per Transaction
Total 23031 100.00% 0.05 0.07 28.29
Parse CPU Time 1125 4.88% 0.00 0.00 1.38
Recursive CPU Time 16264 70.62% 0.03 0.05 19.98
Other CPU Time 5642 24.50% 0.01 0.02 6.93

Analyzing Parse CPU Time

parse - execute ratio

178703 parses (1221 hard parses), 478456 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.03 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 947 sessions logged on and at the end of the timing interval 0 more sessions where active. Sessions are frequently logon and logoff. This will cause a lot of (soft) parses. The basic idea is to logon once and execute many SQL statements.

The init.ora parameter SESSION_CACHED_CURSORS has NOT been set. Setting this parameter (start with a value of 100), will reduce contention during parsing.

Here is a list that you can check in your application:

  • Ensure that the application is using bind variables.
  • When using PCC programs: make sure that the number of open cursors is high enough.
  • When using PCC programs: use release_cursor = NO and hold_cursor = YES.
  • SQL statements can be parsed once and executed many times with bind variables, so check the design of the application.
  • When using XA programs make sure that the connect string contains a large enough setting for the session cursor cache.

parse contention

During parsing 11250 msec of CPU were used and 2020 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch.

Analyzing Recursive CPU Time

Recursive CPU can be high for different reasons:

Analyzing All Other CPU Time

A number of things can be consuming the remaining CPU:

Inefficient SQL statements

There are 26641288 block gets and 478456 statement executed (avg. 58.14 gets/execute).

Here are the top SQL statements ordered by buffer gets per execute:

CPU usage Per SQL statement executed
CPU sec Executes Hash Value Of Total CPU Statement
26.63 7 2351430522 11.93% Module: JDBC Thin ClientBEGIN Fin_Manual_Alloc_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END;
24.96 6 650378899 11.01% Module: JDBC Thin ClientSELECT * FROM COM_TEMP_RISK WHERE TRANSID = :B1 AND (LPOLDUESEQIN (SELECT LPOLDUESEQ FROM COM_TEMP_DUE WHERE TRANSID = :B1 ANDLPOLDUESEQ IN (SELECT LPOLDUESEQ FROM COM_TEMP_FE_POL_DUE WHERETRANSID =:B1 ) OR LTRANREFNBR IN (SELECT LPOLDUESEQ FROM COM_TEMP_FE_POL_DUE WHERE TRANSID =:B1 ) OR LTRANREFNBR IN ( SELECT CTD
17.81 77 2584253257 7.80% Module: JDBC Thin ClientSELECT 'Refund' type, (select STRCDDESC from com_param_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NRFNDRSNCD ) strnarration, TO_CHAR(fvh.dtvou,'DD/MM/YYYY') dtvou, fvh.dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARAMCD = fvc.NPMTREFTYPE
169.35 1 2484685905 77.52% begin int_load_recons_prc(:1,:2,:3,:4,:5,:6); end;
14.12 5 3246231460 6.42% DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN begin wf_com_diary_management_pkg.WF_TRANSFER_DEFFERED_ITEM_PRC; end ; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
5.34 22 2239433510 2.29% Module: JDBC Thin ClientSELECT 'Refund' type, (select STRCDDESC from com_param_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NRFNDRSNCD ) strnarration , TO_CHAR(cap.dtpay,'DD/MM/YYYY') dtvou, cap.dlocalcurramnt dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARA
4.86 670 1195405947 1.83% SELECT NAME FROM ALL_QUEUES WHERE NAME = :B2 AND OWNER = :B1 AND ((TRIM(ENQUEUE_ENABLED) = 'NO') OR (TRIM(DEQUEUE_ENABLED) = 'NO'))
5.24 621 1120568383 2.29% select parent_owner,parent_name,parent_link_name,parent_type,parent_timestamp,property from ora_kglr7_dependencies where owner=:1 and name=:2 and type=:3 and obj#=:4 order by order_number
4.30 621 2869753936 1.83% Module: ? @pulprds002.hsa.co.uk (TNS V1-V3)SELECT DISTINCT LPAD( TO_NUMBER( cpsm.strparam1),:"SYS_B_00",:"SYS_B_01") "Benefit Code", cpsm.strcddesc"Benefit Description", PAPPD.strprodcd "Product Code", PAPPD.nprodver "Product Version", PAPPD.strclientc
17.31 121 59992051 7.80% DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN sys.dbms_aqadm_sys.register_driver(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
15.75 254 1473085294 6.88% begin OWF_MGR.WF_STAGING_LOAD_PKG.notifyCB(context => :a1,reginfo => sys.aq$_reg_info(:a2, :a3, :a4, :a5),descr => sys.aq$_descriptor(:a6, :a7, :a8, sys.msg_prop_t(:a9, :a10, :a11, :a12, :a13, :a14, :a15, :a16, sys.aq$_agent(:a17, :a18, :a19), :a20)), payload => :a21, payloadl => :a22); end;
2.97 144 3804933640 0.92% Module: JDBC Thin ClientSELECT strWorkQName,strWorkQDesc,OpenNotificationCount , workQFlg,workqType FROM( SELECT strWorkQName,strWorkQDesc, ncount OpenNotificationCount ,nworkQType workQType, nworkQFlg workQFlg FROM wf_work_queue WHERE nWorkQFlg < :"SYS_B_0" UNION ALL SELECT strUserId strWorkQName,(SELECT strFirstName || :"SYS
28.92 26 2496791006 12.84% Module: JDBC Thin ClientSELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FROM DUAL
28.88 26 1284180873 12.84% Module: JDBC Thin ClientSELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NULL UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1
3.20 22 3557186462 1.38% Module: JDBC Thin ClientSELECT NPMNTDTLID, NQRYSTATUS, LPOLDUESEQ, 'Premium' type, strpolnbr, Decode(npaymentterms,2,To_Char(dtduefrom,'dd/mm/yyyy'),1,To_Char(dtdueto,'dd/mm/yyyy'),To_Char(dtdue,'dd/mm/yyyy')) dtdue, To_Char(dtdue,'dd/mm/yyyy') realdtdue, To
27.22 1 2409678645 12.39% SELECT COUNT(TOT_CHQ) , SUM(TOT_CHQ_AMNT) FROM ( SELECT COUNT( STRPMTREFNBR ) TOT_CHQ ,SUM(NVL(FBB.DDRAMNT,0) ) TOT_CHQ_AMNT FROM FIN_BANK_BOOK FBB, FIN_VOU_HDR FVC WHERE FBB.STRVOUTYPE = FVC.STRVOUTYPE AND FBB.STRVOUSERIES = FVC.STRVOUSERIES AND FBB.LVOUNBR = FVC.LVOUNBR AND FBB.NFISCALYEAR = FVC.NFISCALYEAR AND FBB.S
27.89 1 1712146673 12.39% UPDATE FIN_BANK_BOOK FBB SET (NDISCREPANCYTYPE, NRECONCILED, DTR

Read Consistency

During this interval 8698 CR blocks were created (or 11.69 per second).

Consistent Read Operations
Type Total Per Execute
CR Block gets 26641288 55.68
CR - no work 21322305 44.56
CR - cleanouts 763 0.00
CR - cleanouts and rollbacks 7659 0.02
CR - rollbacks 1392 0.00

Block Cleanout

Block Cleanouts on Commit
Type Total Per Commit
Total cleanouts 85374 7.71
Successfull cleanouts 85374 7.71
Cleanout failures 0 0.00

Other Block Cleanout failures
Type Total Per Commit
Write Disabled 0 0.00
Buffer being written or block lost 237 0.02
Hot Backup in Progress 0 0.00
Callback failure or can't pin the buffer 20 0.00

Buffer Scanning

On average 1.00 buffers needed to bescanned to find a free buffer. On average 0.00 dirty buffers were found that were moved to the write queue.

Rollbacks

There were 814 rollbacks and 238 undo records were rollbacked in the timing period.

Breakdown of Wait Time

The wait for the foreground sessions can be broken down in the following wait events (in order of wait time):
Wait Time
Event Time Percentage Avg. Wait Per Execute Per User Call Per Transaction
db file sequential read 177000 52.38% 0.73 0.37 0.53 217.44
db file scattered read 100000 29.59% 1.10 0.21 0.30 122.85
db file parallel read 21600 6.39% 3.32 0.05 0.06 26.54
global cache cr request 11900 3.52% 0.05 0.02 0.04 14.62
enqueue 11400 3.37% 0.31 0.02 0.03 14.00
buffer busy waits 3700 1.09% 0.74 0.01 0.01 4.55
log file sync 2800 0.83% 0.35 0.01 0.01 3.44
global cache null to x 1800 0.53% 0.37 0.00 0.01 2.21
library cache lock 1500 0.44% 0.04 0.00 0.00 1.84
latch free 1300 0.38% 0.41 0.00 0.00 1.60
log switch/archive 1000 0.30% 1000.00 0.00 0.00 1.23
buffer busy global CR 800 0.24% 0.62 0.00 0.00 0.98
global cache open x 600 0.18% 0.07 0.00 0.00 0.74
DFS lock handle 500 0.15% 0.03 0.00 0.00 0.61
log file sequential read 400 0.12% 3.12 0.00 0.00 0.49
control file parallel write 300 0.09% 0.77 0.00 0.00 0.37
control file sequential read 300 0.09% 0.11 0.00 0.00 0.37
SQL*Net more data from clien 300 0.09% 0.03 0.00 0.00 0.37
global cache s to x 200 0.06% 0.06 0.00 0.00 0.25
global cache busy 100 0.03% 4.55 0.00 0.00 0.12
buffer busy global cache 100 0.03% 0.85 0.00 0.00 0.12
global cache open s 100 0.03% 0.04 0.00 0.00 0.12
process startup 100 0.03% 4.55 0.00 0.00 0.12
row cache lock 100 0.03% 0.14 0.00 0.00 0.12
global cache null to s 0 0.00% 0.00 0.00 0.00 0.00
buffer deadlock 0 0.00% 0.00 0.00 0.00 0.00
library cache pin 0 0.00% 0.00 0.00 0.00 0.00
SQL*Net break/reset to clien 0 0.00% 0.00 0.00 0.00 0.00
lock escalate retry 0 0.00% 0.00 0.00 0.00 0.00
log file single write 0 0.00% 0.00 0.00 0.00 0.00
log file switch completion 0 0.00% 0.00 0.00 0.00 0.00
SQL*Net message to client 0 0.00% 0.00 0.00 0.00 0.00
direct path read 0 0.00% 0.00 0.00 0.00 0.00
direct path read (lob) 0 0.00% 0.00 0.00 0.00 0.00
direct path write 0 0.00% 0.00 0.00 0.00 0.00
SQL*Net more data to client 0 0.00% 0.00 0.00 0.00 0.00

Idle Time Wait Events

The following events can be ignored as they indicate idle time in the foreground process. They could be relevant if another process (like an application client) is waiting on the foreground process.

Wait Time
Event Time Per Wait Per Execute Per User Call Per Transaction
SQL*Net message from client 51866 0.15 0.11 0.15 63.72
jobq slave wait 6418 2.83 0.01 0.02 7.88
gcs remote message 1423 0.00 0.00 0.00 1.75
wait for unread message on b 725 0.76 0.00 0.00 0.89
ges remote message 721 0.00 0.00 0.00 0.89
wakeup time manager 706 25.21 0.00 0.00 0.87
log file parallel write 36 0.00 0.00 0.00 0.04
LNS wait on SENDREQ 4 0.06 0.00 0.00 0.00
ARCH wait on SENDREQ 1 0.07 0.00 0.00 0.00
CGS wait for IPC msg 1 0.00 0.00 0.00 0.00
LGWR-LNS wait on channel 0 0.00 0.00 0.00 0.00
async disk IO 0 0.00 0.00 0.00 0.00
ges inquiry response 0 0.00 0.00 0.00 0.00
LNS wait on LGWR 0 0.00 0.00 0.00 0.00
ksxr poll remote instances 0 0.00 0.00 0.00 0.00
KJC: Wait for msg sends to c 0 0.00 0.00 0.00 0.00
db file parallel write 0 0.00 0.00 0.00 0.00
slave TJ process wait 0 0.00 0.00 0.00 0.00
LGWR wait for redo copy 0 0.00 0.00 0.00 0.00
ARCH wait on ATTACH 0 0.00 0.00 0.00 0.00
LGWR wait on LNS 0 0.00 0.00 0.00 0.00

db file sequential read

File IO is probably one of the most important areas where you can tune. There are basically two areas:

The amount of I/O

There are a number of things that you can do to reduce the number of IOs:

  • Increase buffer cache

    • Currently there are 0 database buffers configured.

  • Check checkpoints

    • During this timing interval 1 checkpoints have been started. 224 buffers were written for those checkpoints. That is 0.90% of all buffers written.

  • Check SQL statements

    • Here are the top SQL statements ordered by physical reads per execute:
    Statement Executes Physical Reads Reads/Execute Hash Value Of Total
    Module: JDBC Thin ClientSELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FROM DUAL 26 365255 14048.27 2496791006 25.51 %
    Module: JDBC Thin ClientSELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NULL UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1 26 365244 14047.85 1284180873 51.01 %
    SELECT COUNT(TOT_CHQ) , SUM(TOT_CHQ_AMNT) FROM ( SELECT COUNT( STRPMTREFNBR ) TOT_CHQ ,SUM(NVL(FBB.DDRAMNT,0) ) TOT_CHQ_AMNT FROM FIN_BANK_BOOK FBB, FIN_VOU_HDR FVC WHERE FBB.STRVOUTYPE = FVC.STRVOUTYPE AND FBB.STRVOUSERIES = FVC.STRVOUSERIES AND FBB.LVOUNBR = FVC.LVOUNBR AND FBB.NFISCALYEAR = FVC.NFISCALYEAR AND FBB.S 1 308496 308496.00 2409678645 72.56 %
    UPDATE FIN_BANK_BOOK FBB SET (NDISCREPANCYTYPE, NRECONCILED, DTRECONCILED, DTUPDATED, STRUPDATEDBY) = ( SELECT DISTINCT NDSCRPNCYTYPCD, NVL(NRECOSTATCD,1), CASE WHEN ( NVL(NRECOSTATCD,1) = 4 AND NDSCRPNCYTYPCD IS NULL ) THEN TRUNC(SYSDATE) WHEN ( NVL(NRECOSTATCD,1) = 2 AND ( NDSCRPNCYTYPCD = 3 OR NDSCRPNCYTYPCD = 4 ) ) 1 308319 308319.00 1712146673 94.09 %
    Module: JDBC Thin ClientBEGIN Om_Chk_Prompt_Reqm_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END; 51 231043 4530.25 2099693177 110.22 %
    Module: JDBC Thin ClientBEGIN Com_Queue_Insert_Prc (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13); END; 19 134361 7071.63 1768391363 119.60 %
    Module: ? @pulprds002.hsa.co.uk (TNS V1-V3)SELECT dtduefrom, dtdueto, dtddrun, damnt FROM nb_temp_insert_report_dtl WHERE ltemprepseq =:P_repseqno 4 45149 11287.25 3555506540 122.76 %
    UPDATE FIN_VOU_CSHBNK FVC SET (NPMTREFSTATCD, DTPMTREFSTAT, DTUPDATED, STRUPDATEDBY) = ( SELECT NCHQSTATCD, DECODE(DTTRANS,NULL,FVC.DTPMTREFSTAT,DTTRANS), DECODE( NDSCRPNCYTYPCD , NULL , TRUNC(SYSDATE) ,NULL ), :B1 FROM FIN_BANK_RECO_COMPARE WHERE STRVOUTYPE = FVC.STRVOUTYPE AND STRVOUSERIES = FVC.STRVOUSERIES AND LVOU 1 33847 33847.00 569504725 125.12 %
    Module: JDBC Thin ClientSELECT a.ITEM_KEY LREMINDERID, ( SELECT text_value FROM wf_item_attribute_values b WHERE item_type='DIARY' AND b.ITEM_KEY=a.ITEM_KEY AND b.NAME='SUBJECT' ) STRREMINDER, To_Date((SELECTdate_value FROM wf_item_attribute_values C WHERE item_type='DIARY' AND c.ITEM_KEY=a.ITEM_KEY AND NAME='SCHEDULE_DATE') || ' ' 168 25544 152.05 2016421978 126.91 %
    Module: SQL*PlusSELECT :"SYS_B_00"||CASE WHEN COUNT ( * ) > :"SYS_B_01" THEN :"SYS_B_02" ELSE :"SYS_B_03" END CASE FROM om_run_report_queue WHERE TRUNC ( dtsubmitted ) = TRUNC ( SYSDATE ) AND nrepstatcd IN ( :"SYS_B_04", :"SYS_B_05" ) AND ( lrepseq IN ( SELECT lrepseq 1 24002 24002.00 1157065185 128.58 %
    Module: JDBC Thin ClientBEGIN CLM_PROCESS_CLAIM_PRC(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48); END; 100 11421 114.21 4029400597 129.38 %
    Module: JDBC Thin ClientBEGIN Ps_Alt_Insert_Tmp_Pol_Prod_Prc(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18); END; 24 11173 465.54 2341568263 130.16 %
    Module: JDBC Thin ClientSELECT NVL ( MAX ( NSACCOUNTER ), 0 ) FROM PS_TEMP_ALT_POL_PROD_DTL WHERE STRPOLNBR = :B2 AND NSACCD = :B1 2 11133 5566.50 2818421972 130.94 %
    Module: JDBC Thin ClientSELECT * FROM ( SELECT /*Prospect Query*/ nbpd.strpropnbr strpolnbr, nbpd.strclientcd strpolholder, ccm.strclientcd, cpum.strcddesc strtitlecd, ccm.strfirstname strfirstname, ccm.strmiddlename strmiddlename, ccm.strlastname strlastname, ccm.strprefname strprefname, ccm.dtbirth dtbirth, cca.straddrline1 straddrlin 118 10421 88.31 1288008107 131.66 %
    Module: JDBC Thin ClientSELECT fbs.dttrans FROM fin_bank_stmt fbs, fin_vou_cshbnk fvc, fin_vou_hdr fvh WHERE fvc.strcashbankcd = fbs.strcashbankcd AND fvc.strpmtrefnbr = fbs.strpmtrefnbr AND fvc.nfiscalyear = TO_CHAR(fbs.dttrans,'yyyy') AND fvc.dtpmtrefstat = fbs.DTTRANS AND fbs.nrec 2 10106 5053.00 125357498 132.37 %
    SELECT LFILEUPLDSEQ, STRFILENAME, NSTATCD FROM INT_FILE_UPLOAD_D 1 6640 6640.00 227412811 132.83 %

The cost of I/O

Sometimes by just reducing the amount of IO, each IO can become cheaper because less IO means less contention on IO resources. If for some reason you can't reduce the amount of IO you may look at making each IO cheaper. The way to do this would be to increase the number of spindles and/or controllers. Sometimes a diffent disk layout can also make a big difference. Never split index and data files to different sets of disks. It is generally better to stripe all files over all available disks.

Distribution of I/O Operations
Operation Foreground Perc Background Perc Total Perc Total
Single block data file reads 241634 72.12% 45 1.72% 241679 68.81%
Multi block data file reads 90710 27.07% 0 0.00% 90710 25.83%
Control file reads 2701 0.81% 2553 97.44% 5254 1.50%
Data file writes 1829 0.55% 1830 13.51% 3659 1.04%
Control file writes 389 0.12% 387 2.86% 776 0.22%
Log file writes 11304 3.37% 11304 83.47% 22608 6.44%

The reads are 96% of all the I/O operations that happenend.
File I/O in msec
File Name Reads Avg Read (ms) Avg Blocks/Rd Writes Buffer Waits Avg Buf Waits(ms)
/ora_data1/oradata/pdb/hsatbl09.dbf 49594 7.2 3.8 320 1587 7.8
/ora_data1/oradata/pdb/hsatbl07.dbf 46286 6.6 4.2 329 1807 6.8
/ora_data1/oradata/pdb/hsatbl08.dbf 44365 6.9 3.8 269 1083 8.2
/ora_data1/oradata/pdb/hsatbl10.dbf 32930 8.5 3.6 951 179 9.4
/ora_data1/oradata/pdb/hsatbl01.dbf 27187 8.1 5.0 492 131 7.9
/ora_data1/oradata/pdb/hsatbl03.dbf 26442 8.6 3.7 150 151 7.7
/ora_data1/oradata/pdb/hsatbl02.dbf 25325 9.0 6.1 203 97 7.2
/ora_data1/oradata/pdb/hsatbl04.dbf 22682 9.3 4.1 179 128 7.2
/ora_temp/oradata/pdb/tools01.dbf 22078 4.0 1.1 6866 223 5.2
/ora_data1/oradata/pdb/hsatbl06.dbf 22016 8.5 4.2 149 145 10.0
/ora_data1/oradata/pdb/hsatbl05.dbf 19148 9.0 4.7 73 128 7.3
/ora_temp/oradata/pdb/tools02.dbf 17504 4.1 1.2 1814 227 5.8
/ora_data2/oradata/pdb/hsaindx03.dbf 12657 6.4 1.0 1897 27 6.3
/ora_data2/oradata/pdb/hsaindx01.dbf 10869 6.4 1.0 1949 24 24.2
/ora_data2/oradata/pdb/hsaindx02.dbf 10717 6.2 1.0 1729 68 7.8
/ora_data1/oradata/pdb/hsaindx05.dbf 10431 9.8 1.1 2026 22 9.1
/ora_data1/oradata/pdb/hsaindx04.dbf 9062 9.6 1.0 1692 32 7.5
/ora_undo/oradata/pdb/undotbs01.dbf 680 3.0 1.0 3778 96 1.5
/ora_sys/oradata/pdb/system01.dbf 246 2.6 1.0 48 70 9.4
/ora_undo/oradata/pdb/undotbs2_01.dbf 225 5.0 1.0 1 71 2.0
/ora_sys/oradata/pdb/system02.dbf 57 2.6 1.0 6 113 16.1
/u02/oradata/pdb/temp02.dbf 9 0.0 1.0 31 0
/ora_data1/oradata/pdb/users01.dbf 1 10.0 1.0 1 0

db file scattered read

Please check under db file sequential read for advise.

db file parallel read

No advice available yet.

global cache cr request

No advice available yet.

enqueue

There were 147237 enqueue requests, 21371 requests resulted in a wait. These 21371 waits resulted in 2103 timeouts. The average wait for an enqueue was 0.53 msec. It is not clear what enqueue was responsible for most waits. The following enqueue(s) caused most of the waits:

Enqueue Waits by Type
Type Gets Waits Perc Total Cumulative
US 48158 48158 34.20% 34.20 %
DV 37536 37536 26.65% 60.85 %
TM 37179 37179 26.40% 87.25 %
TX 17580 15491 11.00% 98.25 %
CF 1676 1675 1.19% 99.44 %
TA 199 199 0.14% 99.58 %
TT 160 160 0.11% 99.69 %
HW 146 146 0.10% 99.79 %
JQ 139 127 0.09% 99.88 %
TO 120 120 0.09% 99.97 %
FB 39 39 0.03% 100.00 %
WL 3 2 0.00% 100.00 %
MD 1 1 0.00% 100.00 %

buffer busy waits

This event normally happens because one session is waiting for another session to read a block from disk or one session is waiting for another session to finish changing on the buffer. Depending on the class of the block different tuning options exist.

Buffer Busy Wait Statistics per Block Class
Block Class Count Time
data block 6239 48
1st level bmb 1 0
2nd level bmb 2 0
undo block 91 0
undo header 76 0

log file sync

The average wait time per Transaction is 0.25 msecs. The average number of redo blocks written per redo write is 12.53 block(s). The average number of redo blocks per commit is 12.79 block(s). The average number of redo blocks per transaction is 174.01 block(s). If the number of redo blocks written per write is large enough, consider striping the redo logfile over a number of disks with a small enough stripe width.

Another option is to decrease the init.ora parameter "processes". The LGWR needs to scan all processes to find each process that is waiting for the commit to be written. The current value for processes is 1500. Try setting it close to the number of process that you really need.

The redo buffer is large (5242880 bytes, 5.00 MBytes), this sets the log writer IO size to 1M. Reducing the log IO size to a smaller value will help to remove some latency out of the "log file sync". Set the init.ora parameter _log_io_size to a smaller value, like 3 times the average redo size per commit.

global cache null to x

No advice available yet.

library cache lock

No advice available yet.

latch free

Below follows a table with the overview of which latch is responsible for most waits.

Latch Sleep Statistics Per Latch
Latch Name Gets Sleeps Percentage Cumulative
cache buffers chains 54327730 1333 42.92% 42.92 %
library cache 4277619 1145 36.86% 79.78 %
shared pool 1834774 230 7.41% 87.19 %
ges resource hash list 1299505 197 6.34% 93.53 %
library cache pin allocati 1235849 49 1.58% 95.11 %
KCL gc element parent latc 6158387 45 1.45% 96.56 %
KCL freelist parent latch 4370439 28 0.90% 97.46 %
library cache pin 2594172 26 0.84% 98.29 %
session allocation 505597 18 0.58% 98.87 %
gcs resource freelist 1793956 16 0.52% 99.39 %
gcs resource hash 3644123 10 0.32% 99.71 %
KJCT flow control latch 1496595 5 0.16% 99.87 %
row cache objects 1195580 4 0.13% 100.00 %

The following places in the Oracle kernel caused the latch free events to occur.

Latch Sleep Statistics Per Latch
Function/Where Latch Name Sleeps Percentage Cumulative
kcbchg: kslbegin: bufs not cache buffers chains 410 12.94% 12.94 %
kcbgtcr: kslbegin excl cache buffers chains 309 9.75% 22.70 %
kcbgtcr: fast path cache buffers chains 263 8.30% 31.00 %
kgllkdl: child: cleanup library cache 240 7.58% 38.57 %
kghupr1 shared pool 178 5.62% 44.19 %
kglupc: child library cache 164 5.18% 49.37 %
kglpndl: child: before pro library cache 135 4.26% 53.63 %
kglpnc: child library cache 109 3.44% 57.07 %
kcbget: pin buffer cache buffers chains 104 3.28% 60.35 %
kjlmfnd: search for lockp ges resource hash list 101 3.19% 63.54 %
kjakcai: search for resp b ges resource hash list 90 2.84% 66.38 %
kglpin: child: heap proces library cache 77 2.43% 68.81 %
kglobpn: child: library cache 68 2.15% 70.96 %
kglhdgc: child: library cache 67 2.11% 73.07 %
kglhdgn: child: library cache 60 1.89% 74.97 %
kgllkdl: child: multiinsta library cache 47 1.48% 76.45 %
kglic library cache 45 1.42% 77.87 %
kcbzib: multi-block read: cache buffers chains 45 1.42% 79.29 %
kcbzgb: scan from tail no cache buffers chains 43 1.36% 80.65 %
kcbgcur: kslbegin cache buffers chains 41 1.29% 81.94 %
kghalo shared pool 39 1.23% 83.18 %
kcbrls: kslbegin cache buffers chains 38 1.20% 84.38 %
kglpin library cache 37 1.17% 85.54 %
kcbzwb cache buffers chains 35 1.10% 86.65 %
kglpnal library cache pin alloca 35 1.10% 87.75 %
kglpndl: child: after proc library cache 30 0.95% 88.70 %
kcrfwr redo allocation 21 0.66% 89.36 %
kclfget KCL gc element parent la 18 0.57% 89.93 %
kclfrem KCL freelist parent latc 17 0.54% 90.47 %
kgldti: 2child library cache 16 0.51% 90.97 %
kgllkdl library cache pin alloca 14 0.44% 91.41 %
kgldte: child 0 library cache 14 0.44% 91.86 %
kclulb KCL gc element parent la 14 0.44% 92.30 %
kglpnp: child library cache 13 0.41% 92.71 %
kglpndl library cache pin 12 0.38% 93.09 %
kcbgtcr: kslbegin shared cache buffers chains 11 0.35% 93.43 %
kglget: child: KGLDSBRD library cache 10 0.32% 93.75 %
kjbralc add resource gcs resource freelist 10 0.32% 94.07 %
kclfget KCL freelist parent latc 8 0.25% 94.32 %
kclpdcl cache buffers chains 8 0.25% 94.57 %
kghfrunp: alloc: wait shared pool 8 0.25% 94.82 %
kglpnal: child: alloc spac library cache pin 7 0.22% 95.04 %
ksuprc session allocation 7 0.22% 95.27 %
ksudlc session allocation 6 0.19% 95.45 %
kclnfnd KCL name table parent la 6 0.19% 95.64 %
kclpdc_2 KCL gc element parent la 6 0.19% 95.83 %
kghfrunp: clatch: wait shared pool 6 0.19% 96.02 %
kcbnew cache buffers chains 6 0.19% 96.21 %
ksqcmi: if lk mode request enqueue hash chains 5 0.16% 96.37 %
kglupc library cache pin 5 0.16% 96.53 %
ksucrp process allocation 5 0.16% 96.69 %
kghfre shared pool 5 0.16% 96.84 %
kjrmas1: lookup master nod ges resource hash list 5 0.16% 97.00 %
ksqgtl3 enqueue hash chains 4 0.13% 97.13 %
kqrpfl: not dirty row cache objects 4 0.13% 97.25 %
kjbcropen gcs resource hash 4 0.13% 97.38 %
kjbopen gcs resource hash 4 0.13% 97.51 %
kghfrunp: clatch: nowait shared pool 4 0.13% 97.63 %
kcbzib: finish free bufs cache buffers chains 4 0.13% 97.76 %
kjcts_sedeqv: dequeue a ve KJCT flow control latch 4 0.13% 97.89 %
kjbrfr free resource gcs resource freelist 3 0.09% 97.98 %
kjgdgpar: move a group loc ges group parent 3 0.09% 98.07 %
kclfadb KCL freelist parent latc 3 0.09% 98.17 %
kcbget: exchange cache buffers chains 3 0.09% 98.26 %
kclnloc KCL gc element parent la 3 0.09% 98.36 %
kcbget: exchange rls cache buffers chains 2 0.06% 98.42 %
kcbnlc cache buffers chains 2 0.06% 98.48 %
KCLUNLNK KCL gc element parent la 2 0.06% 98.55 %
kglget: child: KGLDSBYD library cache 2 0.06% 98.61 %
kglpnc: child library cache pin 2 0.06% 98.67 %
kcrfwi: before write redo allocation 2 0.06% 98.74 %
ksuxds: KSUSFCLC not set session allocation 2 0.06% 98.80 %
ksuxds session idle bit 2 0.06% 98.86 %
kclnrem KCL name table parent la 2 0.06% 98.93 %
kjbassume gcs resource hash 2 0.06% 98.99 %
kjgagpar: move a group loc ges group parent 2 0.06% 99.05 %
kcbchg: kslbegin: call CR cache buffers chains 2 0.06% 99.12 %
kkjgnwqi job workq parent latch 2 0.06% 99.18 %
kcbgtcr cache buffers chains 2 0.06% 99.24 %
kcbbic1 cache buffers chains 1 0.03% 99.27 %
kclebs_1 cache buffers chains 1 0.03% 99.31 %
kclwcrs cache buffers chains 1 0.03% 99.34 %
kclwrt cache buffers chains 1 0.03% 99.37 %
kcbso1: set no access cache buffers chains 1 0.03% 99.40 %
kclpdcl KCL gc element parent la 1 0.03% 99.43 %
kcbgtcr:CR Scan:KCBRSKIP cache buffers lru chain 1 0.03% 99.46 %
kgllkal: child: multiinsta library cache 1 0.03% 99.49 %
kclshrsplit KCL gc element parent la 1 0.03% 99.53 %
kcbzgb: multiple sets nowa cache buffers lru chain 1 0.03% 99.56 %
kzulgt: find user res cost function 1 0.03% 99.59 %
ksqcmi: if lk mode not req enqueue hash chains 1 0.03% 99.62 %
kqreqa row cache enqueue latch 1 0.03% 99.65 %
ksqrcl enqueue hash chains 1 0.03% 99.68 %
ksqgel: create enqueue enqueues 1 0.03% 99.72 %
ksucri session allocation 1 0.03% 99.75 %
ksuxds: not user session session allocation 1 0.03% 99.78 %
ksufap: active sessions session allocation 1 0.03% 99.81 %
kjcsmpav: allocate a msg b KJC message pool free li 1 0.03% 99.84 %
kjlalc: lock allocation ges enqueue table freeli 1 0.03% 99.87 %
kjlfr: remove lock from pa ges enqueue table freeli 1 0.03% 99.91 %
kjcvscn: remove from scan ges resource hash list 1 0.03% 99.94 %
kjctcsnd: send normal mess KJCT flow control latch 1 0.03% 99.97 %
kcbs_simulate: simulate se simulator lru latch 1 0.03% 100.00 %

log switch/archive

No advice available yet.

buffer busy global CR

No advice available yet.

global cache open x

This event indicates that the database is using Releasable PCM locks. If a high number shows up it could mean that the DBA is used. A better approach would be to use Hash Releasable PCM locks (=0 should be =100R e.g.). It could also be that the working set of locks that is needed is too small and that gc_releasable_locks should be increased.

DFS lock handle

The session is waiting for the Distributed Lock Manager (DLM) to return a handle that will identify the lock. A high number of waits on this event most likely indicates a DLM that is very busy. Check the throughput and performance of the DLM.

log file sequential read

The logfile header block was read. This could happen for example when a new log file is added or when a logfile is dumped.

control file parallel write

There are many reasons why the control would/could be updated, but the most likely reason is for a checkpoint. So when this event is high on the list, check the checkpoint activity. During this interval 1 checkpoints have started and 1 completed.

control file sequential read

A foreground process had to read some data from the control file. This shouldn't happen a lot.

SQL*Net more data from clien

The client side is inserting large records and/or is using array insert. Basically the client side is sending more data than will fit in one SQL*Net package. Increasing the package size will help to reduce this event and will help to improve performance. One needs to be using Net 2.3 or higher to set the SDU and TDU sizes at client AND the server side.

global cache s to x

This event could be happening for Fixed Hash locking and for Releasable locking. It indicates that some form of pinging of blocks is going on between the instances.

global cache busy

No advice available yet.

buffer busy global cache

No advice available yet.

global cache open s

This event indicates that the database is using Releasable PCM locks. If a high number shows up it could mean that the DBA is used. A better approach would be to use Hash Releasable PCM locks (=0 should be =100R e.g.). It could also be that the working set of locks that is needed is too small and that gc_releasable_locks should be increased.

process startup

Waiting for a background process (for Parallel Query to startup e.g.) to startup. Check to see if you need to increase min_servers.

row cache lock

This latch protects the access of the datadictionary cache in the SGA. When loading, referencing and freeing objects in the datadictionary cache you need to get this latch.

Operations on the Row Cache
Parent Cache Get Request Scan Request Mod Request Gets Cumulative
dc_users 376391 0 0 63.11 %
dc_rollback_segments 100351 0 0 79.94 %
dc_object_ids 46097 0 1 87.67 %
dc_global_oids 35998 0 0 93.70 %
dc_tablespaces 12475 0 0 95.80 %
dc_segments 10374 0 0 97.53 %
dc_user_grants 4111 0 0 98.22 %
dc_objects 4093 0 2 98.91 %
dc_usernames 3554 0 0 99.51 %
dc_histogram_defs 1360 0 0 99.73 %
dc_profiles 922 0 0 99.89 %
dc_sequences 663 0 663 100.00 %
dc_table_scns 1 0 0 100.00 %

global cache null to s

No advice available yet.

buffer deadlock

This indicates index contention. An index block that was needed during index tree traversal was locked. Because this session was already holding other resources (locked during the tree traversal), a potential deadlock could arise. So instead of a getting a deadlock all the blocks were released and the index tree traversal has to start again.

library cache pin

This could indicate a potential parsing problem. Remember parse once, execute the statement many times after the parse.

SQL*Net break/reset to clien

The client is sending some bundled calls or doing an array operation that resulted in an error at the server side. The server can't receive the remaining data and notifies the client to reset the connection.

lock escalate retry

No advice available yet.

log file single write

No advice available yet.

log file switch completion

A log file was full and new log file was opened. We are waiting for the switch to complete. If this happens often, it could be that the log files are too small and cause a frequent checkpoint to occur. During this interval 1 checkpoints started and 1 finished.

SQL*Net message to client

The Oracle Server is experiencing some delays in sending data to the client side. This could happen if the network connection is slow or has some other performance problem. This send send should work without a delay.

direct path read

This event can happen during sorting, direct load or parallel query operations. If it is sorting, check the sort stats and consider using a larger sort_area_size. If it is because of any parallel operations, check the plan of the statement that is being executed.

direct path read (lob)

This event can happen during sorting, direct load or parallel query operations. If it is sorting, check the sort stats and consider using a larger sort_area_size. If it is because of any parallel operations, check the plan of the statement that is being executed.

direct path write

This event can happen during sorting, direct load or parallel query operations. If it is sorting, check the sort stats and consider using a larger sort_area_size. If it is because of any parallel operations, check the plan of the statement that is being executed.

SQL*Net more data to client

The client side is selecting large records and/or is using array fetch. Basically the Oracle Server is sending more data thand will fit in a SQL*Net package. Increasing the package size will help to reduce this event and will help to improve performance. One needs to be using Net 2.3 or higher to set the SDU and TDU sizes at client AND the server side.

Init.ora Parameters

Parameters
Parameter Value Difference Advise
aq_tm_processes 1 No advice available yet.
archive_lag_target 1800 No advice available yet.
background_dump_dest /app/oracle/admin/pdb/bdump/ No advice available yet.
cluster_database TRUE No advice available yet.
cluster_database_instances 2 No advice available yet.
compatible 9.2.0.0.0 Set the compatible parameter to the RDBMS version that you are using (9.2.0).
control_files /ora_undo/oradata/pdb/control No advice available yet.
core_dump_dest /app/oracle/admin/pdb/cdump No advice available yet.
cursor_sharing SIMILAR No advice available yet.
db_block_size 8192 No advice available yet.
db_cache_advice ON No advice available yet.
db_cache_size 805306368 No advice available yet.
db_domain hsa.co.uk No advice available yet.
db_file_multiblock_read_count 16 No advice available yet.
db_keep_cache_size 16777216 No advice available yet.
db_name pdb No advice available yet.
db_writer_processes 4 No advice available yet.
enqueue_resources 3000 No advice available yet.
event 10511 trace name context forever,
fast_start_mttr_target 1800 No advice available yet.
fast_start_parallel_rollback HIGH No advice available yet.
hash_join_enabled TRUE No advice available yet.
instance_name pdb1 No advice available yet.
instance_number 1 No advice available yet.
java_pool_size 83886080 No advice available yet.
job_queue_processes 20 No advice available yet.
large_pool_size 536870912 No advice available yet.
local_listener LISTENER_pdb1 No advice available yet.
log_archive_dest No advice available yet.
log_archive_dest_1 LOCATION=/ora_data2/arch/pdb No advice available yet.
log_archive_dest_2 SERVICE=PULSEDG LGWR ASYNC=20480 No advice available yet.
log_archive_dest_3 No advice available yet.
log_archive_dest_state_1 ENABLE No advice available yet.
log_archive_dest_state_2 ENABLE No advice available yet.
log_archive_dest_state_3 DEFER No advice available yet.
log_archive_format arch%t_%s.log No advice available yet.
log_archive_max_processes 8 No advice available yet.
log_archive_start TRUE No advice available yet.
log_buffer 5242880 This parameter can be reduced to 262144, unless you see a lot of log buffer space events.
open_cursors 5000 No advice available yet.
optimizer_index_caching 0 No advice available yet.
optimizer_index_cost_adj 80 No advice available yet.
parallel_automatic_tuning FALSE No advice available yet.
parallel_max_servers 0 No advice available yet.
parallel_min_percent 0 No advice available yet.
parallel_min_servers 0 No advice available yet.
pga_aggregate_target 536870912 No advice available yet.
processes 1500 No advice available yet.
query_rewrite_enabled TRUE No advice available yet.
query_rewrite_integrity TRUSTED No advice available yet.
remote_listener LISTENER_pdb2 No advice available yet.
remote_login_passwordfile EXCLUSIVE No advice available yet.
sessions 2000 No advice available yet.
shared_pool_size 268435456 No advice available yet.
sort_area_size 0 No advice available yet.
spfile /ora_sys/oradata/pdb/spfilePU No advice available yet.
star_transformation_enabled FALSE No advice available yet.
thread 1 No advice available yet.
timed_statistics TRUE Setting OK.
undo_management AUTO No advice available yet.
undo_retention 32400 No advice available yet.
undo_tablespace UNDOTBS1 No advice available yet.
user_dump_dest /app/oracle/admin/pdb/udump No advice available yet.

Advice

The advice is given in the order of the most impact of the total reponse time. The percentage gain is taken of the reponse time.

Maximum Gain (%) What Detail
0 Check Parallel Cache Management locking strategies Don't use DBA locking for read intensive blocks, use Hash Releasable locking.. Check to see if there are false pings or any hot blocks.. Don't use DBA locking for read intensive blocks, use Hash Releasable locking.
1 Reduce the number of buffer gets or executions Module: JDBC Thin ClientBEGIN Fin_Manual_Alloc_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END; (hash value 2351430522). Module: JDBC Thin ClientSELECT * FROM COM_TEMP_RISK WHERE TRANSID = :B1 AND (LPOLDUESEQIN (SELECT LPOLDUESEQ FROM COM_TEMP_DUE WHERE TRANSID = :B1 ANDLPOLDUESEQ IN (SELECT LPOLDUESEQ FROM COM_TEMP_FE_POL_DUE WHERETRANSID =:B1 ) OR LTRANREFNBR IN (SELECT LPOLDUESEQ FROM COM_TEMP_FE_POL_DUE WHERE TRANSID =:B1 ) OR LTRANREFNBR IN ( SELECT CTD (hash value 650378899). Module: JDBC Thin ClientSELECT 'Refund' type, (select STRCDDESC from com_param_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NRFNDRSNCD ) strnarration, TO_CHAR(fvh.dtvou,'DD/MM/YYYY') dtvou, fvh.dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARAMCD = fvc.NPMTREFTYPE (hash value 2584253257). begin int_load_recons_prc(:1,:2,:3,:4,:5,:6); end; (hash value 2484685905). DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN begin wf_com_diary_management_pkg.WF_TRANSFER_DEFFERED_ITEM_PRC; end ; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; (hash value 3246231460). Module: JDBC Thin ClientSELECT 'Refund' type, (select STRCDDESC from com_param_user_m where IPARAMTYPECD = 6714 and strparamcd = frodt.NRFNDRSNCD ) strnarration , TO_CHAR(cap.dtpay,'DD/MM/YYYY') dtvou, cap.dlocalcurramnt dvouamnt, (SELECT STRCDDESC FROM COM_PARAM_SYSTEM_M WHERE IPARAMTYPECD = 4027 AND NPARA (hash value 2239433510). SELECT NAME FROM ALL_QUEUES WHERE NAME = :B2 AND OWNER = :B1 AND ((TRIM(ENQUEUE_ENABLED) = 'NO') OR (TRIM(DEQUEUE_ENABLED) = 'NO')) (hash value 1195405947). select parent_owner,parent_name,parent_link_name,parent_type,parent_timestamp,property from ora_kglr7_dependencies where owner=:1 and name=:2 and type=:3 and obj#=:4 order by order_number (hash value 1120568383). Module: ? @pulprds002.hsa.co.uk (TNS V1-V3)SELECT DISTINCT LPAD( TO_NUMBER( cpsm.strparam1),:"SYS_B_00",:"SYS_B_01") "Benefit Code", cpsm.strcddesc"Benefit Description", PAPPD.strprodcd "Product Code", PAPPD.nprodver "Product Version", PAPPD.strclientc (hash value 2869753936). DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN sys.dbms_aqadm_sys.register_driver(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; (hash value 59992051). begin OWF_MGR.WF_STAGING_LOAD_PKG.notifyCB(context => :a1,reginfo => sys.aq$_reg_info(:a2, :a3, :a4, :a5),descr => sys.aq$_descriptor(:a6, :a7, :a8, sys.msg_prop_t(:a9, :a10, :a11, :a12, :a13, :a14, :a15, :a16, sys.aq$_agent(:a17, :a18, :a19), :a20)), payload => :a21, payloadl => :a22); end; (hash value 1473085294). Module: JDBC Thin ClientSELECT strWorkQName,strWorkQDesc,OpenNotificationCount , workQFlg,workqType FROM( SELECT strWorkQName,strWorkQDesc, ncount OpenNotificationCount ,nworkQType workQType, nworkQFlg workQFlg FROM wf_work_queue WHERE nWorkQFlg < :"SYS_B_0" UNION ALL SELECT strUserId strWorkQName,(SELECT strFirstName || :"SYS (hash value 3804933640). Module: JDBC Thin ClientSELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FROM DUAL (hash value 2496791006). Module: JDBC Thin ClientSELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NULL UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1 (hash value 1284180873). Module: JDBC Thin ClientSELECT NPMNTDTLID, NQRYSTATUS, LPOLDUESEQ, 'Premium' type, strpolnbr, Decode(npaymentterms,2,To_Char(dtduefrom,'dd/mm/yyyy'),1,To_Char(dtdueto,'dd/mm/yyyy'),To_Char(dtdue,'dd/mm/yyyy')) dtdue, To_Char(dtdue,'dd/mm/yyyy') realdtdue, To (hash value 3557186462). SELECT COUNT(TOT_CHQ) , SUM(TOT_CHQ_AMNT) FROM ( SELECT COUNT( STRPMTREFNBR ) TOT_CHQ ,SUM(NVL(FBB.DDRAMNT,0) ) TOT_CHQ_AMNT FROM FIN_BANK_BOOK FBB, FIN_VOU_HDR FVC WHERE FBB.STRVOUTYPE = FVC.STRVOUTYPE AND FBB.STRVOUSERIES = FVC.STRVOUSERIES AND FBB.LVOUNBR = FVC.LVOUNBR AND FBB.NFISCALYEAR = FVC.NFISCALYEAR AND FBB.S (hash value 2409678645). UPDATE FIN_BANK_BOOK FBB SET (NDISCREPANCYTYPE, NRECONCILED, DTR (hash value 1712146673)
3 Tune the global cache cr request event. No detailed information is available yet.
5 Tune the db file parallel read event. No detailed information is available yet.
94 Reduce the number of physical reads per executions Check SQL statement "Module: JDBC Thin ClientSELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FROM DUAL" (hash value 2496791006). Check SQL statement "Module: JDBC Thin ClientSELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, PS_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.STRALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NULL UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PAH WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1" (hash value 1284180873). Check SQL statement "SELECT COUNT(TOT_CHQ) , SUM(TOT_CHQ_AMNT) FROM ( SELECT COUNT( STRPMTREFNBR ) TOT_CHQ ,SUM(NVL(FBB.DDRAMNT,0) ) TOT_CHQ_AMNT FROM FIN_BANK_BOOK FBB, FIN_VOU_HDR FVC WHERE FBB.STRVOUTYPE = FVC.STRVOUTYPE AND FBB.STRVOUSERIES = FVC.STRVOUSERIES AND FBB.LVOUNBR = FVC.LVOUNBR AND FBB.NFISCALYEAR = FVC.NFISCALYEAR AND FBB.S" (hash value 2409678645). Check SQL statement "UPDATE FIN_BANK_BOOK FBB SET (NDISCREPANCYTYPE, NRECONCILED, DTRECONCILED, DTUPDATED, STRUPDATEDBY) = ( SELECT DISTINCT NDSCRPNCYTYPCD, NVL(NRECOSTATCD,1), CASE WHEN ( NVL(NRECOSTATCD,1) = 4 AND NDSCRPNCYTYPCD IS NULL ) THEN TRUNC(SYSDATE) WHEN ( NVL(NRECOSTATCD,1) = 2 AND ( NDSCRPNCYTYPCD = 3 OR NDSCRPNCYTYPCD = 4 ) )" (hash value 1712146673). Check SQL statement "Module: JDBC Thin ClientBEGIN Om_Chk_Prompt_Reqm_Prc(:1,:2,:3,:4,:5,:6,:7,:8); END;" (hash value 2099693177). Check SQL statement "Module: JDBC Thin ClientBEGIN Com_Queue_Insert_Prc (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13); END;" (hash value 1768391363). Check SQL statement "Module: ? @pulprds002.hsa.co.uk (TNS V1-V3)SELECT dtduefrom, dtdueto, dtddrun, damnt FROM nb_temp_insert_report_dtl WHERE ltemprepseq =:P_repseqno" (hash value 3555506540). Check SQL statement "UPDATE FIN_VOU_CSHBNK FVC SET (NPMTREFSTATCD, DTPMTREFSTAT, DTUPDATED, STRUPDATEDBY) = ( SELECT NCHQSTATCD, DECODE(DTTRANS,NULL,FVC.DTPMTREFSTAT,DTTRANS), DECODE( NDSCRPNCYTYPCD , NULL , TRUNC(SYSDATE) ,NULL ), :B1 FROM FIN_BANK_RECO_COMPARE WHERE STRVOUTYPE = FVC.STRVOUTYPE AND STRVOUSERIES = FVC.STRVOUSERIES AND LVOU" (hash value 569504725). Check SQL statement "Module: JDBC Thin ClientSELECT a.ITEM_KEY LREMINDERID, ( SELECT text_value FROM wf_item_attribute_values b WHERE item_type='DIARY' AND b.ITEM_KEY=a.ITEM_KEY AND b.NAME='SUBJECT' ) STRREMINDER, To_Date((SELECTdate_value FROM wf_item_attribute_values C WHERE item_type='DIARY' AND c.ITEM_KEY=a.ITEM_KEY AND NAME='SCHEDULE_DATE') || ' '" (hash value 2016421978). Check SQL statement "Module: SQL*PlusSELECT :"SYS_B_00"||CASE WHEN COUNT ( * ) > :"SYS_B_01" THEN :"SYS_B_02" ELSE :"SYS_B_03" END CASE FROM om_run_report_queue WHERE TRUNC ( dtsubmitted ) = TRUNC ( SYSDATE ) AND nrepstatcd IN ( :"SYS_B_04", :"SYS_B_05" ) AND ( lrepseq IN ( SELECT lrepseq" (hash value 1157065185). Check SQL statement "Module: JDBC Thin ClientBEGIN CLM_PROCESS_CLAIM_PRC(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48); END;" (hash value 4029400597). Check SQL statement "Module: JDBC Thin ClientBEGIN Ps_Alt_Insert_Tmp_Pol_Prod_Prc(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18); END;" (hash value 2341568263). Check SQL statement "Module: JDBC Thin ClientSELECT NVL ( MAX ( NSACCOUNTER ), 0 ) FROM PS_TEMP_ALT_POL_PROD_DTL WHERE STRPOLNBR = :B2 AND NSACCD = :B1" (hash value 2818421972). Check SQL statement "Module: JDBC Thin ClientSELECT * FROM ( SELECT /*Prospect Query*/ nbpd.strpropnbr strpolnbr, nbpd.strclientcd strpolholder, ccm.strclientcd, cpum.strcddesc strtitlecd, ccm.strfirstname strfirstname, ccm.strmiddlename strmiddlename, ccm.strlastname strlastname, ccm.strprefname strprefname, ccm.dtbirth dtbirth, cca.straddrline1 straddrlin" (hash value 1288008107). Check SQL statement "Module: JDBC Thin ClientSELECT fbs.dttrans FROM fin_bank_stmt fbs, fin_vou_cshbnk fvc, fin_vou_hdr fvh WHERE fvc.strcashbankcd = fbs.strcashbankcd AND fvc.strpmtrefnbr = fbs.strpmtrefnbr AND fvc.nfiscalyear = TO_CHAR(fbs.dttrans,'yyyy') AND fvc.dtpmtrefstat = fbs.DTTRANS AND fbs.nrec" (hash value 125357498). Check SQL statement "SELECT LFILEUPLDSEQ, STRFILENAME, NSTATCD FROM INT_FILE_UPLOAD_D" (hash value 227412811)

Comments

The following comments were generated while processing file C:\sp_15542_15544.txt:

  • The Buffer Cache Hit Ratio for is: 94.8519%, but ignore this ;-).
  • The maximum gain was greater than 100%. Probably due to some internal overflow.

Send questions and inquiries to support