OraFAQ Forum: Performance Tuning » TUNING HIGH DISK READ

Home » RDBMS Server » Performance Tuning » TUNING HIGH DISK READ

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TUNING HIGH DISK READ [message #162522]

Fri, 10 March 2006 14:53

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

We have a package ,Currently working but slow and lot of activities, I check indexes are there on tables, stats are updated daily, our senior DBA gave this stats

"  it appears this package has been doing lot of physical reads 
and probably inside the package there are statements doing a full table scan. May be you would want to tune it in QA and see whether any improvements can be done. Stats show for 5184 executions it did about 35 million disk reads. 
Though CPU time it used shows as 4688 seconds but elapsed time of 12601 seconds nearly shows lot of time was spent on physical reads.

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     ---------------  ---------------  ------
        Buffer Gets:      60,504,640         11,671.4   15.40
         Disk Reads:      35,317,685          6,812.8   80.66
     Rows processed:           5,184              1.0
     CPU Time(s/ms):           4,688            904.3
 Elapsed Time(s/ms):          12,601          2,430.8
              Sorts:               0               .0
        Parse Calls:           5,184              1.0
      Invalidations:               0
      Version count:               1
    Sharable Mem(K):              15
         Executions:           5,184

What is wrong with this call to package,

begin PKG_ALERT_ENGINE.SP_GET_BATCH_IDS(P_ALERT_ID=>:P_ALERT_ID,
 P_PROCESS_ID=>:P_PROCESS_ID, P_BATCH_CUR=>:P_BATCH_CUR); end;

.
I am attaching the procedure of the package,
What can be done, You experts please have a look, and give some opinions how to reduce disk reads, other things.

Thanks

Attachment: prco.txt
(Size: 1.89KB, Downloaded 1365 times)

Report message to a moderator

Re: TUNING HIGH DISK READ [message #162600 is a reply to message #162522]

Sat, 11 March 2006 16:33

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

First things first, how many rows are in the various tables / views, and are stats on the various tables etc ran correctly and
often? Also, have you ran timings for the various operational steps?

Without seeing the plans for the queries, it's difficult to say with any certainty what could be causing this. A few comments I could say would be the code :

  SELECT COUNT(BAT_ID) INTO V_COUNT FROM ALRT_PYMT_TMP WHERE PROC_ID=P_PROCESS_ID;
  IF V_COUNT=0 THEN
...

Since all you're doing is an existence query, and issuing the relevant UPDATE if there's no row where proc_id = p_process_id, then why count them all?
A simple check for at least one row would suffice :

DECLARE
  l_dummy  VARCHAR2(1);
BEGIN
  SELECT 'x' 
    INTO l_dummy
    FROM alrt_pymt_tmp
   WHERE proc_id = p_process_id
     AND rownum = 1;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN 
    -- Issue UPDATE.
END;

But, again, without knowing the relevant plans / cardinalities etc., it'd be difficult to ascertain whether or not this would make a huge difference...

Rgds

Report message to a moderator

Re: TUNING HIGH DISK READ [message #162768 is a reply to message #162600]

Mon, 13 March 2006 10:13

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

Thanks for responding,
Here is the SQL statment and Exccution plan for it
(From Development, I don't have access on Production)

SELECT ALRT_PYMT_TMP.BAT_ID
   FROM ALRT, LOCKBOX_ACL, USR, LOCKBOX, LOCKBOX_CUST, ALRT_PYMT_TMP
   WHERE ALRT.USR_ID = USR.USR_ID
   AND  USR.USR_ID = LOCKBOX_ACL.USR_ID
   AND  USR.CUST_ID =  LOCKBOX_CUST.CUST_ID
   AND  LOCKBOX_CUST.LOCKBOX_ID=ALRT_PYMT_TMP.LOCKBOX_ID
   AND  LOCKBOX_ACL.ALL_LOCKBOX_IN=1
   AND  ALRT.STS_CD=1
   AND  ALRT_PYMT_TMP.PROC_ID=bcd4340'
   AND  ALRT.ALRT_ID=49
   UNION
   SELECT ALRT_PYMT_TMP.BAT_ID
   FROM ALRT, LOCKBOX_ACL, ALRT_PYMT_TMP, LOCKBOX_CUST
   WHERE   ALRT.USR_ID = LOCKBOX_ACL.USR_ID
   AND  LOCKBOX_ACL.LOCKBOX_CUST_ID =  LOCKBOX_CUST.LOCKBOX_CUST_ID
   AND  LOCKBOX_CUST.LOCKBOX_ID=ALRT_PYMT_TMP.LOCKBOX_ID
   AND  ((ALL_LOCKBOX_IN <> 1) OR (ALL_LOCKBOX_IN IS NULL))
   AND  ALRT.STS_CD=1
   AND  ALRT_PYMT_TMP.PROC_ID='bcd4340'
   AND  ALRT.ALRT_ID=49;

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		7  	 	14  	 	      	             	 
  SORT UNIQUE		7  	558  	14  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      MERGE JOIN CARTESIAN		6  	480  	6  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	R1APP60.LOCKBOX_ACL	1  	6  	1  	 	      	             	 
          NESTED LOOPS		1  	80  	4  	 	      	             	 
            NESTED LOOPS		1  	74  	3  	 	      	             	 
              NESTED LOOPS		3  	195  	3  	 	      	             	 
                NESTED LOOPS		1  	18  	2  	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	R1APP60.ALRT	1  	10  	1  	 	      	             	 
                    INDEX UNIQUE SCAN	R1APP60.XPK_ALR	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	R1APP60.USR	1  	8  	1  	 	      	             	 
                    INDEX UNIQUE SCAN	R1APP60.XPK_USR	1  	 	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	R1APP60.ALRT_PYMT_TMP	3  	141  	1  	 	      	             	 
                  INDEX RANGE SCAN	R1APP60.XIE_ALRPYMTMP_PRCID	3  	 	 	 	      	             	 
              INDEX UNIQUE SCAN	R1APP60.XAK_LOCCUS_LOCID_CUSID	1  	9  	 	 	      	             	 
            INDEX RANGE SCAN	R1APP60.XIF_LOCACL_USRID	2  	 	 	 	      	             	 
        BUFFER SORT		2 K	 	5  	 	      	             	 
          INDEX FAST FULL SCAN	R1APP60.XAK_LOC_LOCNB_SITID	2 K	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	R1APP60.ALRT_PYMT_TMP	1  	47  	1  	 	      	             	 
        NESTED LOOPS		1  	78  	4  	 	      	             	 
          NESTED LOOPS		1  	31  	3  	 	      	             	 
            NESTED LOOPS		1  	21  	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	R1APP60.ALRT	1  	10  	1  	 	      	             	 
                INDEX UNIQUE SCAN	R1APP60.XPK_ALR	1  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	R1APP60.LOCKBOX_ACL	1  	11  	1  	 	      	             	 
                INDEX RANGE SCAN	R1APP60.XIF_LOCACL_USRID	2  	 	 	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP60.LOCKBOX_CUST	1  	10  	1  	 	      	             	 
              INDEX UNIQUE SCAN	R1APP60.XPK_LOCCUS	1  	 	 	 	      	             	 
          INDEX RANGE SCAN	R1APP60.XIE_ALRPYMTMP_LOCID	1

You were asking for number of rows, here it is, this is from Production

     ALRT_PYMT_TMP  800000
     alrt - 150
     LOCKBOX_ACL 8500
     user  8000 
     LOCKBOX 6000
     LOCKBOX_CUST  4950

One more thing i want to add is this procedure from the package is executed may be every 5-10 minutes.
What can be tuned or can be done to reduce those disk reads and gets.. or do some thing more good with this package.

Thanks

Thanks

Report message to a moderator

Re: TUNING HIGH DISK READ [message #162820 is a reply to message #162522]

Mon, 13 March 2006 14:31

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Well, one thing I would say is that you need to get the plans from production (unless development is an exact copy of it of course). How long does the query take on development, for example?

Just out of interest, though, how fast is the select count(*)
logic compared with the existence query approach I mentioned earlier?

Rgds

Report message to a moderator

Re: TUNING HIGH DISK READ [message #162836 is a reply to message #162820]

Mon, 13 March 2006 15:26

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi

Count(*) from large tables take less than
1 sec (Elapsed: 00:00:00.05),

I will try to ask for Explain plan from production, Development data is old may be 1 month old so can't say .
But still can you highlight some thing on that Explain plan.
And as i said earlier this package is used almmost every 5 minutes through out the Day, so the concern of DBA is to do some thing since it is doing lot of Disk reads,

Thanks

Report message to a moderator

Re: TUNING HIGH DISK READ [message #164469 is a reply to message #162836]

Thu, 23 March 2006 09:49

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

This code is from Package and all the individual execution Plan,
As this package runs almost every 5 min through out
the day, the concern of DBA is high disk reads, the STATS posted above at first show high disk reads.
How can we tune high disks and what else can be done on this.

Mr mchadder i tried with your suggestion of avoiding count(*)
it didn't gave much differnce, becz it is getting 450000 rows from that count(*)
and that single count select statement is very fast.
please have a look and suggest some thing.
This Plan is from Production.

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     ---------------  ---------------  ------
        Buffer Gets:      60,504,640         11,671.4   15.40
         Disk Reads:      35,317,685          6,812.8   80.66
     Rows processed:           5,184              1.0
     CPU Time(s/ms):           4,688            904.3
 Elapsed Time(s/ms):          12,601          2,430.8
              Sorts:               0               .0
        Parse Calls:           5,184              1.0
      Invalidations:               0
      Version count:               1
    Sharable Mem(K):              15
         Executions:           5,184

SELECT COUNT(BAT_ID) FROM ALRT_PYMT_TMP WHERE PROC_ID='123';

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	3  	 	      	             	 
  SORT AGGREGATE		1  	37  	 	 	      	             	 
    INDEX RANGE SCAN	R1APP.XIE_ALRPYMTMP_PRCID	11  	407  	3

UPDATE ALRT_PYMT_TMP
   SET  PROC_IN=1,
     ALRT_PROC_TS=sysdate,
     PROC_ID='123'
   WHERE BAT_IMPR_TS<sysdate
   AND  ALRT_PROC_TS IS NULL;

PLAN for Above Update

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

UPDATE STATEMENT Optimizer Mode=CHOOSE		1  	 	419  	 	      	             	 
  UPDATE	R1APP.ALRT_PYMT_TMP	  	 	 	 	      	             	 
    TABLE ACCESS FULL	R1APP.ALRT_PYMT_TMP	1  	56  	419


 SELECT ALRT_PYMT_TMP.BAT_ID
   FROM ALRT, LOCKBOX_ACL, USR, LOCKBOX, LOCKBOX_CUST, ALRT_PYMT_TMP
   WHERE ALRT.USR_ID = USR.USR_ID
   AND  USR.USR_ID = LOCKBOX_ACL.USR_ID
   AND  USR.CUST_ID =  LOCKBOX_CUST.CUST_ID
   AND  LOCKBOX_CUST.LOCKBOX_ID=ALRT_PYMT_TMP.LOCKBOX_ID
   AND  LOCKBOX_ACL.ALL_LOCKBOX_IN=1
   AND  ALRT.STS_CD=1
   AND  ALRT_PYMT_TMP.PROC_ID='123'
   AND  ALRT.ALRT_ID=245
   UNION
   SELECT ALRT_PYMT_TMP.BAT_ID
   FROM ALRT, LOCKBOX_ACL, ALRT_PYMT_TMP, LOCKBOX_CUST
   WHERE   ALRT.USR_ID = LOCKBOX_ACL.USR_ID
   AND  LOCKBOX_ACL.LOCKBOX_CUST_ID =  LOCKBOX_CUST.LOCKBOX_CUST_ID
   AND  LOCKBOX_CUST.LOCKBOX_ID=ALRT_PYMT_TMP.LOCKBOX_ID
   AND  ((ALL_LOCKBOX_IN <> 1) OR (ALL_LOCKBOX_IN IS NULL))
   AND  ALRT.STS_CD=1
   AND  ALRT_PYMT_TMP.PROC_ID='123'
   AND  ALRT.ALRT_ID=245;

PLAN FOR ABOVE STATEMENT

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		28  	 	29  	 	      	             	 
  SORT UNIQUE		28  	2 K	29  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      MERGE JOIN CARTESIAN		27  	2 K	14  	 	      	             	 
        NESTED LOOPS		1  	80  	11  	 	      	             	 
          MERGE JOIN CARTESIAN		7  	504  	11  	 	      	             	 
            NESTED LOOPS		1  	25  	4  	 	      	             	 
              NESTED LOOPS		1  	18  	2  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	R1APP.ALRT	1  	10  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XPK_ALR	1  	 	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	R1APP.USR	1  	8  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XPK_USR	1  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	R1APP.LOCKBOX_ACL	1  	7  	2  	 	      	             	 
                INDEX RANGE SCAN	R1APP.XAK_LOCACL_USRID_LOCACLID	1  	 	1  	 	      	             	 
            BUFFER SORT		11  	517  	9  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	R1APP.ALRT_PYMT_TMP	11  	517  	7  	 	      	             	 
                INDEX RANGE SCAN	R1APP.XIE_ALRPYMTMP_PRCID	11  	 	2  	 	      	             	 
          INDEX UNIQUE SCAN	R1APP.XAK_LOCCUS_LOCID_CUSID	1  	8  	 	 	      	             	 
        BUFFER SORT		5 K	 	14  	 	      	             	 
          INDEX FAST FULL SCAN	R1APP.XAK_LOC_LOCNB_SITID	5 K	 	3  	 	      	             	 
      NESTED LOOPS		1  	75  	9  	 	      	             	 
        NESTED LOOPS		1  	28  	4  	 	      	             	 
          NESTED LOOPS		1  	20  	3  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.ALRT	1  	10  	1  	 	      	             	 
              INDEX UNIQUE SCAN	R1APP.XPK_ALR	1  	 	 	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	R1APP.LOCKBOX_ACL	1  	10  	2  	 	      	             	 
              INDEX RANGE SCAN	R1APP.XAK_LOCACL_USRID_LOCACLID	1  	 	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	R1APP.LOCKBOX_CUST	1  	8  	1  	 	      	             	 
            INDEX UNIQUE SCAN	R1APP.XPK_LOCCUS	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	R1APP.ALRT_PYMT_TMP	1  	47  	9  	 	      	             	 
          AND-EQUAL		  	 	 	 	      	             	 
            INDEX RANGE SCAN	R1APP.XIE_ALRPYMTMP_LOCID	11  	 	3  	 	      	             	 
            INDEX RANGE SCAN	R1APP.XIE_ALRPYMTMP_PRCID	11  	 	3

.

The Stats and Plan is from Production.

Thanks

Report message to a moderator

Previous Topic:	Tablespace Fragmentation !!
Next Topic:	Table scan in only 1 of many partitions?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 08:22:56 CST 2024