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 Go to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #210601 is a reply to message #210258] Thu, 21 December 2006 08:36 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Michael,

Thanks for your guidence

As a start i started doing the things.

However some confusion here.

Please find attached Statistics results for a Query

can you suggest me which is better query?

thanks and Regards,
Pratap
Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210621 is a reply to message #210172] Thu, 21 December 2006 09:53 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #210695 is a reply to message #210172] Thu, 21 December 2006 17:41 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Remove the function call from WHERE.
-- The statement ( called from that function )

SELECT STRPARAMVALUE
FROM
OM_CONFIGURATION WHERE NPARAMKEY = :B1

is called over 80K times and requires about 9 seconds.

2. Post TKPROF - WITHOUT function call for 2 cases:
-- A. Your old query
-- B. Query as I suggested ( with UNION )

Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #210832 is a reply to message #210695] Fri, 22 December 2006 10:27 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Michael,

Please find attached file containing tkprof results taken as per your suggestions

There are two different runs from different sessions

Note : Less database load today
Many people left for Merry Cristmas Preparation

Please go through the attachment and advice

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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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
Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #211604 is a reply to message #211290] Fri, 29 December 2006 17:11 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi,

1. IMHO you will NOT "feel" the additional index.
2. About "Lastly is it feasible to keep following table with 30000 rows in keep pool " - depends on the size of your keep pool and rows size of the table.

HTH.
Re: Performance issue - Physical Reads / Buffer Gets - Statspack Report [message #211720 is a reply to message #211604] Mon, 01 January 2007 14:36 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Michael,

Many thanks.

Things are clear for me now.

Thanks and Regards,
Pratap
Previous Topic: Tablespace for table with 1,000,000 rows
Next Topic: UTLBSTAT UTLESTAT
Goto Forum:
  


Current Time: Mon Dec 05 06:53:43 CST 2016

Total time taken to generate the page: 0.05826 seconds