buffer busy waits - enqueue - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: buffer busy waits - enqueue

  1. #11
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    From what i understand from your statspack report.

    global cache cr request and db file scattered read are contributing to your wait times.

    I would suggest SQL tuning esp those that are on this table
    USERENTITY

    Top sql's to tune would be

    Code:
    1)SELECT COUNT(USERENTITYID) 
      FROM USERENTITY 
      WHERE USERID = :B1
    
    2)SELECT ENTITYPRIVILEGE.PRIVID 
    FROM USERPRIVILEGES, ENTITYPRIVILEGE 
    WHERE 
    USERPRIVILEGES.USERENTITYID = :B1 
    AND 
    USERPRIVILEGES.PRIVID = ENTITYPRIVILEGE.PRIVID
    
    3)SELECT A.USERENTITYID, A.USERID, A.FIRSTNAME, 
    A.LASTNAME, A.EMAIL, A.ISDELETED, 
    B.DESIGNATION, B.ENTITYID, 
    B.PARENTID, B.ISUSERACTIVE, B.ISADMIN 
    FROM USERMAST A, USERENTITY B 
    WHERE A.USERENTITYID = B.USERENTITYID 
    AND LOGINID = :B2 AND PASSWORD = :B1
    Hope you have set segment space management for the BIDRXDV1 tablespace to auto.

    Code:
                         bobject  Obj.      Physical
    Owner    Tablespe  Obj Name     Type   Reads  	   %Total
    -------- --------- ------------ -----  -----       ------
    BIDRXDV1 BIDRXDV1  USERENTITY       3,782,634      99.91 91
    Another object where you would expect marginal performance improvement would be RESPONSE time.

    regards
    Hrishy


    regards
    Hrishy
    Last edited by hrishy; 06-11-2005 at 06:28 AM.

  2. #12
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai Hrishy.
    Thanks for your suggestions. Yes, I can see to from the Statspack report that USERENTITY table was a big trouble. I have created an index on USERID column without which it was causing lot of consistent gets(3915). After creating Index I have seen a drsatic improvement in the query. There are only 2 consistent gets.

    BEFORE INDEX (COST=584, consistent gets=3915, Full Table Scan)
    -------------

    SQL> select count(userentityid)
    from userentity where userid = 8;

    COUNT(USERENTITYID)
    -------------------
    0


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=584 Card=1 Bytes
    =5)

    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'USERENTITY' (Cost=584 Card=1 Byt
    es=5)





    Statistics
    ----------------------------------------------------------
    239 recursive calls
    0 db block gets
    3915 consistent gets
    0 physical reads
    0 redo size
    389 bytes sent via SQL*Net to client
    500 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    6 sorts (memory)
    0 sorts (disk)
    1 rows processed


    After Creating Index (2 consistent gets, COST=2, Index range Scan )
    -----------------------

    SQL> select count(userentityid)
    from userentity where userid = 1;
    2
    COUNT(USERENTITYID)
    -------------------
    9


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=5
    )

    1 0 SORT (AGGREGATE)
    2 1 INDEX (RANGE SCAN) OF 'IND_USERENTITY_USERID' (NON-UNIQU
    E) (Cost=1 Card=1 Bytes=5)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    2 consistent gets
    0 physical reads
    0 redo size
    390 bytes sent via SQL*Net to client
    500 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed


    Even we changed the other query. I'll get back to you after doing another load test.



    regards,
    Dileep T.V.R.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width