Home » RDBMS Server » Performance Tuning » Performance issue - Physical Reads / Buffer Gets - Statspack Report
| Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210172] |
Tue, 19 December 2006 15:33  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
Oracle 9i on Linux (RAC)
Please find attached statspack report and advice me.
i have few questions here
how to prioritise the SQLs for tuning, after seeing this report
SQLs with more Disk Reads first and
then SQLs with Buffer Gets?
Also for SQL statements following in sections "SQL ordered by Reads", what could be initial point? i.e.Reads/Execute to look into? please give some guideline.
i am asking this question since i have following statement too listed in above section in the statspack report (probably because of number of executions)
Same with SQL ordered by Gets
What % of buffer gets per execute could be initial point to investigate?
Also please suggest on instance parameters if changes are needed as investigated from the report.
Also please guide me on one thing.
The situation is i am on client site.
Even if i see some procedure is taking time i can't check hot spot in the procedure by dbms_profiler, since neither i can change the code to put profiler in it not can i invoke the procedures as standalone.
Also it is really difficult to find hot spot by checking the code since the procedures have many other calls in it.
Please suggest on this
also Finally suggest on one thing
if somebody complains about database is going slow (i can't track the session in this case) then what is the sequence of investigation?
is it by firing top in OS
then checking session_wait?
How to find most offesive statement at that time?
Please advice on this
Thanks in Advance
Pratap
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210258 is a reply to message #210172] |
Wed, 20 December 2006 01:18  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
You have a major problem with your SQL statements:
1. Over 50% of your application WAIT time is caused either by
db file sequential read (42.71%) or db file scattered read (10.12%).
db file scattered read = FULL table scans
db file sequential read = access via index , however in your case the index is not selective enough.
2. IMO there is no need looking for hotspots. The picture is clear enough. Start with statements causing most physical reads. (Most of them appear at Buffer Gets section anyway).
3. Look at following statements:
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
550,167 56 9,824.4 29.5 47.36 307.17 2496791006
Module: JDBC Thin Client
SELECT DECODE ( PS_DUPLICATE_REPORT_FUN ( :B1 ), 'Y', 1, 0 ) FRO
M DUAL
550,085 56 9,822.9 29.5 47.26 306.64 1284180873
Module: JDBC Thin Client
SELECT LREPSEQ FROM ( SELECT LREPSEQ FROM COM_CONTACT_HST CCH, P
S_ALT_HDR PAH WHERE CCH.STRCLIENTCD = PAH.STRCLIENTCD AND PAH.ST
RALTTRANSHDRNBR = :B1 AND CCH.NSTATUS = 1 AND LREPSEQ IS NOT NUL
L UNION SELECT LREPSEQ FROM OM_RUN_REPORT_QUEUE OM,PS_ALT_HDR PA
H WHERE OM.STRPOLNBR = PAH.STRPOLNBR AND STRALTTRANSHDRNBR = :B1
524,424 57 9,200.4 28.1 50.72 314.68 4012608250
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_
QUEUE WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTA
TCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE)) AND M
OD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD
IMO there is a very good chance that at least one of these SQL statements is called from the stored proc.
Both statements suffer from the same problem of WAITS:
CPU time 47-50 seconds vs Elapsed Time of over 300 seconds.
Make EXPLAIN and tune both of them.
4. Look for large differences between CPU and Elapse times - usually it's caused by WAITs (and in your case - it's I/O).
EXPLAIN all these statements and tune them.
HTH.
|
|
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210621 is a reply to message #210172] |
Thu, 21 December 2006 09:53 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Both your queries perform either FULL table or FULL index scan:
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=:"SYS_B_0" OR (NREPSTATCD=:"SYS_B_1" AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(:"SYS_B_2"))=:B1 ORDER BY NREPSTATCD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.07 5.29 12502 16149 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.07 5.29 12502 16149 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS FULL OM_RUN_REPORT_QUEUE
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
global cache cr request 2539 0.00 0.72
db file sequential read 101 0.00 0.07
db file scattered read 928 0.01 1.71
db file parallel read 494 0.02 2.26
SQL*Net message from client 1 9.28 9.28
library cache lock 1 0.00 0.00
********************************************************************************
SELECT /*+ INDEX_JOIN(ORRQ XPKOM_RUN_REPORT_QUEUE,OM_RUN_REPORT_QUEUE_IDX,NU_DTSUBMITTED_ORRQ_IDX) */ LREPQUESEQ,
LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE ORRQ
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=:"SYS_B_0" OR (NREPSTATCD=:"SYS_B_1" AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(:"SYS_B_2"))=:B1 ORDER BY NREPSTATCD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 6.34 6.63 0 1890 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 6.34 6.63 0 1890 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS BY INDEX ROWID OM_RUN_REPORT_QUEUE
0 INDEX FULL SCAN XPKOM_RUN_REPORT_QUEUE (object id 190547)
1. Your application is NOT using bind variables, so SOFT parse is performed.
2. How many rows are in OM_RUN_REPORT_QUEUE and how many blocks are there?
3. What OM_GET_CONF_INFO_FNC(:"SYS_B_2") function does and why you have to call it inside SQL statement?
-- Call it before the statement and use it's result as parameter.
4. post the existing indexes for OM_RUN_REPORT_QUEUE table.
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210626 is a reply to message #210172] |
Thu, 21 December 2006 10:11 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
I didn't see that you posted the indexes.
Try following:
1. CREATE INDEX OM_RUN_REPORT_QUEUE_IDX2 ON OM_RUN_REPORT_QUEUE
( NREPSTATCD, DTSUBMITTED, DTNEXTRUN , NINTERVAL , LREPQUESEQ ) ...
2. Rewrite the query as:
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID
FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND NREPSTATCD=:"SYS_B_0"
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(:"SYS_B_2"))=:B1
UNION ALL
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID
FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND
NREPSTATCD=:"SYS_B_1" AND NINTERVAL > 0 AND DTNEXTRUN <= SYSDATE
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(:"SYS_B_2"))=:B1
3. Verify that a NEW index is used in both parts of UNION ( use hints if necessary ).
HTH.
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210675 is a reply to message #210621] |
Thu, 21 December 2006 13:48 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Michael,
Many thanks for your guidence
Here are the details as needed
om_run_report_queue table details
Blocks : 14429
Rows : 31990
Existing Index Details (since i copied script from TOAD it is appearing as "Create Index...."
1)
CREATE INDEX NU_DTSUBMITTED_ORRQ_IDX ON OM_RUN_REPORT_QUEUE
(DTSUBMITTED)
2)
CREATE INDEX OM_RUN_REPORT_QUEUE_IDX ON OM_RUN_REPORT_QUEUE
(LREPSEQ, NREPSTATCD)
3)
Primary Key Index
CREATE UNIQUE INDEX XPKOM_RUN_REPORT_QUEUE ON OM_RUN_REPORT_QUEUE
(LREPQUESEQ)
Om_Get_Conf_Info_Fnc Details
******************************************************************
CREATE OR REPLACE FUNCTION Om_Get_Conf_Info_Fnc(p_paramkey IN com_policy_m.strpolnbr%TYPE,
p_process_id IN Varchar2 DEFAULT 997)
RETURN Varchar2
IS
BEGIN
SELECT strparamvalue
INTO v_param_value
FROM om_configuration
WHERE nparamkey = p_paramkey;
RETURN v_param_value ;
EXCEPTION
WHEN OTHERS
THEN
Com_Insert_Exception_Prc(11,
p_process_id,
Sysdate,
p_paramkey,
1,
SQLERRM||' from Om_Get_Conf_Info_Fnc;'||p_paramkey );
RETURN NULL;
END Om_Get_Conf_Info_Fnc;
****************************************************************Note :
1)om_configuration table in above FUNCTION has few columns with 44 rows only;
2)Removing this FUNCTION from the query and giving hard code value (not bind FOR test) gave me inverse results as you you can see below
SQL> exec runstats_pkg.rs_start
SQL> SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
2 DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
3 WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTATCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
4 AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD;
SQL> exec runstats_pkg.rs_middle
SQL> SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
2 DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
3 WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTATCD=3 AND NINTERVAL not in (1,4,7) AND DTNEXTRUN <= SYSDATE))
4 AND MOD(LREPQUESEQ,4)=4 ORDER BY NREPSTATCD;
SQL> exec runstats_pkg.rs_stop
Run1 ran in 1638 hsecs
Run2 ran in 1861 hsecs
run 1 ran in 88.02% of the time
Name Run1 Run2 Diff
LATCH.kwqit: protect wakeup ti 0 1 1
LATCH.post/wait queue 3 2 -1
LATCH.spilled msgs queues list 0 1 1
STAT...enqueue releases 0 1 1
STAT...cursor authentications 0 1 1
STAT...calls to kcmgas 1 0 -1
STAT...enqueue requests 0 1 1
STAT...parse time elapsed 1 0 -1
STAT...redo ordering marks 1 0 -1
STAT...global cache current bl 7 8 1
LATCH.dummy allocation 4 2 -2
LATCH.gcs shadows freelist 3 1 -2
LATCH.session timer 5 7 2
LATCH.list of block allocation 26 28 2
LATCH.longop free list parent 5 7 2
LATCH.job_queue_processes para 3 1 -2
STAT...global lock sync gets 0 2 2
STAT...global lock releases 0 2 2
LATCH.user lock 4 2 -2
LATCH.transaction allocation 4 2 -2
LATCH.active checkpoint queue 56 60 4
LATCH.name-service memory obje 8 4 -4
LATCH.name-service pending que 8 4 -4
STAT...CPU used by this sessio 80 86 6
STAT...redo entries 6 12 6
STAT...CPU used when call star 80 86 6
LATCH.ges deadlock list 62 52 -10
STAT...active txn count during 2 13 11
STAT...cleanout - number of kt 2 13 11
STAT...consistent gets - exami 2 13 11
STAT...calls to kcmgcs 2 13 11
LATCH.name-service request que 159 174 15
LATCH.name-service namespace b 151 168 17
STAT...consistent gets 16,140 16,157 17
LATCH.process allocation 146 167 21
STAT...global cache current bl 77 99 22
STAT...global cache get time 405 438 33
STAT...consistent changes 10 44 34
STAT...db block gets 12 47 35
STAT...db block changes 17 56 39
LATCH.Consistent RBA 217 262 45
LATCH.lgwr LWN SCN 217 262 45
LATCH.mostly latch-free SCN 217 263 46
LATCH.session idle bit 408 459 51
STAT...session logical reads 16,152 16,204 52
LATCH.cache buffer handles 323 376 53
LATCH.enqueues 490 548 58
STAT...bytes received via SQL* 953 894 -59
LATCH.simulator lru latch 455 356 -99
LATCH.redo writing 718 858 140
STAT...Elapsed Time 1,644 1,867 223
LATCH.sequence cache 1,805 2,050 245
LATCH.SQL memory manager worka 1,431 1,700 269
LATCH.message pool operations 2,254 2,546 292
LATCH.messages 2,136 2,464 328
LATCH.LGWR NS Write 2,343 2,672 329
LATCH.undo global data 1,423 1,828 405
STAT...redo size 1,868 2,584 716
LATCH.channel operations paren 6,212 7,047 835
LATCH.multiblock read objects 9,806 8,950 -856
LATCH.row cache objects 3,753 2,893 -860
STAT...gcs messages sent 5,652 6,591 939
LATCH.ges caches resource list 3,513 2,550 -963
LATCH.ges resource table freel 2,775 1,789 -986
LATCH.session allocation 2,048 1,013 -1,035
LATCH.row cache enqueue latch 1,644 462 -1,182
LATCH.ges domain table 3,644 2,130 -1,514
LATCH.ges group table 3,660 2,144 -1,516
LATCH.ges group parent 3,650 2,132 -1,518
LATCH.dml lock allocation 2,780 1,068 -1,712
LATCH.ksxp tid allocation 12,464 14,200 1,736
LATCH.redo allocation 11,436 13,439 2,003
LATCH.ges enqueue table freeli 4,856 2,718 -2,138
LATCH.KJCT flow control latch 18,671 20,813 2,142
LATCH.KJC message pool free li 10,282 12,639 2,357
STAT...physical reads 10,959 13,344 2,385
STAT...global cache gets 11,036 13,443 2,407
STAT...free buffer requested 11,037 13,448 2,411
STAT...prefetched blocks 8,550 11,227 2,677
LATCH.shared pool 36,851 39,695 2,844
LATCH.enqueue hash chains 7,396 4,388 -3,008
LATCH.ges resource hash list 7,642 4,493 -3,149
LATCH.checkpoint queue latch 3,315 6,524 3,209
LATCH.cache buffers lru chain 626 4,044 3,418
LATCH.library cache pin alloca 31,867 36,183 4,316
LATCH.library cache pin 55,618 60,020 4,402
LATCH.KCL name table parent la 24,417 29,035 4,618
LATCH.gcs resource freelist 23,560 28,901 5,341
LATCH.library cache 84,102 91,003 6,901
LATCH.ges process parent latch 17,690 10,776 -6,914
LATCH.KCL gc element parent la 111,461 121,823 10,362
LATCH.gcs resource hash 49,881 61,040 11,159
LATCH.simulator hash latch 27,828 10,673 -17,155
LATCH.KCL freelist parent latc 60,816 85,041 24,225
LATCH.cache buffers chains 1,515,217 340,307 -1,174,910
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
2,174,585 1,047,280 -1,127,305 207.64%
SQL>
i think adding more queries will kill the performance of inserts and updates on om_run_report_queue table
since it is updated many times
(it is report execution status table where, reports are kept scheduled daily,weekly fortnightly,monthly ad Hoc and Oracle jobs put these request to reports server table all time for report execution)
Thanks and Regards,
Pratap
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210693 is a reply to message #210626] |
Thu, 21 December 2006 16:48 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Michael,
Please refer attached tkprof results files.
After your advice i have done following things
4 steps..
Step 1>
Regathered Statistics for table
Step 2>
******
TKprof : pulsedb1_ora_541
for Comparing following 2 Queries .........
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTATCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD;
SELECT /*+ INDEX_JOIN(ORRQ XPKOM_RUN_REPORT_QUEUE,OM_RUN_REPORT_QUEUE_IDX,NU_DTSUBMITTED_ORRQ_IDX) */ LREPQUESEQ,
LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE ORRQ
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTATCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD;
********************************************
Step 3>
*******
CREATE INDEX OM_RUN_REPORT_QUEUE_IDX2 ON OM_RUN_REPORT_QUEUE
( NREPSTATCD, DTSUBMITTED, DTNEXTRUN , NINTERVAL , LREPQUESEQ ) tablespace hsaindx
exec dbms_stats.gather_index_stats('HSASYS','OM_RUN_REPORT_QUEUE_IDX2')
*************
Step 4>
********
TKprof :pulsedb1_ora_7810
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=2 OR (NREPSTATCD=3 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1 ORDER BY NREPSTATCD;
---Query as suggested by you
-- Hint added so that index as suggested by you (only will get used)
SELECT /*+ INDEX(orrq OM_RUN_REPORT_QUEUE_IDX2) */ LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID
FROM OM_RUN_REPORT_QUEUE orrq
WHERE DTSUBMITTED <= SYSDATE AND NREPSTATCD=2
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1
UNION ALL
SELECT /*+ INDEX(orrq OM_RUN_REPORT_QUEUE_IDX2) */ LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID
FROM OM_RUN_REPORT_QUEUE orrq
WHERE DTSUBMITTED <= SYSDATE AND
NREPSTATCD=3 AND NINTERVAL > 0 AND DTNEXTRUN <= SYSDATE
AND MOD(LREPQUESEQ,OM_GET_CONF_INFO_FNC(34))=:B1
Note : I tried using Runstats, however every run it gave me varrying results (users were less by this time , almost nil)
sometimes it even gave me contrast results for successive runs
{here also would need your advice}
Also i still unable to know, when time elapsed is less shall i go for that Plan rather than another with more elapsed time and less reads
Please advice further
Thanks and Regards,
Pratap
|
|
|
|
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210846 is a reply to message #210172] |
Fri, 22 December 2006 11:24 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
The best solution is :
SELECT LREPQUESEQ, LREPSEQ, STRPARAMVAL, NTRYCOUNT, NREPSTATCD,
DTSUBMITTED, DTNEXTRUN, NINTERVAL, STRUSERID FROM OM_RUN_REPORT_QUEUE
WHERE DTSUBMITTED <= SYSDATE AND (NREPSTATCD=:A1 OR (NREPSTATCD=:A2 AND NINTERVAL IS NOT NULL AND DTNEXTRUN <= SYSDATE))
AND MOD(LREPQUESEQ,(SELECT strparamvalue FROM om_configuration WHERE nparamkey = :C1))=:B1 ORDER BY NREPSTATCD
It uses the new index instead of performing full table scan.
HTH.
Michael
|
|
|
|
| Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #211290 is a reply to message #210846] |
Wed, 27 December 2006 17:36 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Michael,
Thank you very much for your guidence
i could see that the query is running faster!
(however haven't taken statspack for full-load, since almost no business users using the system today)
however considering the fact that pesently following indexes are already there, wan't it slow the DMLs on the table?
Existing 3 indexes :
CREATE INDEX NU_DTSUBMITTED_ORRQ_IDX ON OM_RUN_REPORT_QUEUE
(DTSUBMITTED)
CREATE INDEX OM_RUN_REPORT_QUEUE_IDX ON OM_RUN_REPORT_QUEUE
(LREPSEQ, NREPSTATCD)
CREATE UNIQUE INDEX XPKOM_RUN_REPORT_QUEUE ON OM_RUN_REPORT_QUEUE
(LREPQUESEQ)
The new one we added :
CREATE INDEX OM_RUN_REPORT_QUEUE_IDX2 ON OM_RUN_REPORT_QUEUE
(NREPSTATCD, DTSUBMITTED, DTNEXTRUN, NINTERVAL, LREPQUESEQ)
Lastly is it feasible to keep following table with 30000 rows in keep pool which is updated for few columns almost all the day.
CREATE TABLE OM_RUN_REPORT_QUEUE
(
LREPQUESEQ NUMBER(12) NOT NULL,
LREPSEQ NUMBER(12),
STRPARAMVAL VARCHAR2(2000),
LEVENTSEQ NUMBER,
NREPSTATCD VARCHAR2(1),
DTSUBMITTED DATE,
NTRYCOUNT NUMBER DEFAULT 0,
NINTERVAL VARCHAR2(250),
DTNEXTRUN DATE,
STRERRCD VARCHAR2(10),
STRERRDESC VARCHAR2(2000),
NEVENTCLUBSEQ NUMBER(12),
STRUSERID VARCHAR2(15),
STRPOLNBR VARCHAR2(17)
)
Once again thanks for your support
Thanks and Regards,
Pratap
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 16 20:52:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
