A tuning problem for gurus
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: A tuning problem for gurus

  1. #1
    Join Date
    Feb 2001
    Posts
    75

    Angry

    Hi,

    This is for tuning gurus. I am putting the problem below.

    I am processing some 2 million rows in a cursor and the cursor does few selects from other tables each time. These statements use indexes but due to poor clustering factor there are too many reads for data blocks. These tables are too big to be cached. Is there a way, I can reduce the physical reads and improve the performance. I can not incresae the clustering factor by pre-sorting the table.

    The following is the output of tkprof.


    SELECT MIN(ACCOUNT_NUM)
    FROM
    ODS.OES_ACCOUNT WHERE BILL_SITE = :b1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 14404 1.85 1.49 0 0 0 0
    Fetch 14404 7.77 252.44 20021 51354 0 14404
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 28809 9.62 253.93 20021 51354 0 14404

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 33 (CDW) (recursive depth: 1)

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    0 SORT (AGGREGATE)
    0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'OES_ACCOUNT'
    0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_ACCOUNTXFK1'
    (NON-UNIQUE)

    ********************************************************************************

    SELECT MAX(O.SIC_PART_ONE || O.SIC_PART_TWO ),MAX(O.SIC_PART_ONE ||
    O.SIC_PART_TWO || O.SIC_PART_THREE || O.SIC_PART_FOUR )
    FROM
    ODS.OES_SITE_SIC O WHERE O.SITE_NUM = :b1 AND O.PRIMARY_SIC = 'Y' AND
    O.ADDED_DATE = (SELECT MAX(ADDED_DATE) FROM ODS.OES_SITE_SIC WHERE
    SITE_NUM = :b1 AND PRIMARY_SIC = 'Y' AND SOURCE_ARCHIVE_DT IS NULL ) AND
    O.SOURCE_ARCHIVE_DT IS NULL


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 14404 2.10 1.61 0 0 0 0
    Fetch 14404 10.86 343.71 24131 105221 0 14404
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 28809 12.96 345.32 24131 105221 0 14404

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 33 (CDW) (recursive depth: 1)

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    0 SORT (AGGREGATE)
    0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'OES_SITE_SIC'
    0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_SITE_SICXPK'
    (UNIQUE)
    0 SORT (AGGREGATE)
    0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
    'OES_SITE_SIC'
    0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_SITE_SICXPK'
    (UNIQUE)

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why don't you post the PL/SQL block and the number of rows in table, and names of indexes.

  3. #3
    Join Date
    Feb 2001
    Posts
    75

    Tuning problem for gurus

    Hi Tamilselvan,

    The cursor is processing 2 million rows. The names of tables it is accessing & their indexes are given in TKPROF output included. These other tables also have about million rows each.

    Please get back for any other detail.


    Kailash Pareek

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Create a composite index (PRIMARY_SIC+SITE_NUM) on OES_SITE table; and change the WHERE clause aaas follows:

    SELECT MAX(O.SIC_PART_ONE || O.SIC_PART_TWO ),
    MAX(O.SIC_PART_ONE ||
    O.SIC_PART_TWO ||
    O.SIC_PART_THREE ||
    O.SIC_PART_FOUR )
    FROM ODS.OES_SITE_SIC O
    WHERE O.PRIMARY_SIC = 'Y'
    AND O.SITE_NUM = :b1
    AND O.ADDED_DATE = (SELECT MAX(ADDED_DATE)
    FROM ODS.OES_SITE_SIC
    WHERE PRIMARY_SIC = 'Y'
    AND SITE_NUM = :b1
    AND SOURCE_ARCHIVE_DT
    IS NULL )
    AND O.SOURCE_ARCHIVE_DT IS NULL


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