Query Perfomance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Query Perfomance

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Query Perfomance

    Hi all,

    I have a query which has been running fine until yesterday when it began to run really slowly. I looked at the explain plan and saw that one of the indexes it used to use is not being used at all now thus increasing the cost by a huge amount. Indexes and Tables are analyzed daily so I dont know what could have caused the execution path to miss out the index. Could it be that i need to rebuild the index??

    Thanks in advance,
    Chucks_k

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Was there any large data load yesterday?

    Did you change analyze method?

    Tamil

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Hi there,

    There was no dataload as i am aware of, and the analyze method has not changed at all. We run a daily analyze of the schema (estimate) via script. Even if there was a huge dataload would it make a big difference? Or would it screw up the stats?

    Thanks in advance
    Chucks_k

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Code:
    select index_name, leaf_blocks,
           AVG_LEAF_BLOCKS_PER_KEY,
           AVG_DATA_BLOCKS_PER_KEY,
           CLUSTERING_FACTOR,
           status,
           last_analyzed
    from dba_indexes
    where index_name = 'XXXXX'
    /
    Can you post the result of the above query.

    Tamil

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Hi Tamil,

    This the result for of query:

    INDEX_NAME LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
    ------------------------------ ----------- -----------------------
    AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS LAST_ANAL
    ----------------------- ----------------- -------- ---------
    AOP_CUS_DK_I 11008 1
    1 1444800 VALID 08-FEB-05


    Thanks,
    Chucks

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    The clustering_factor,1444800 seems to be high.
    If this is equivalent to table's blocks, then it may not be a good index.

    select table_name, blcoks
    from dba_tables
    where table_name ='XXXXX';

    Run this query and let me know the value.

    Tamil

  7. #7
    Join Date
    Dec 2001
    Posts
    337
    Hi Tamil,

    This is the result of the query:

    SQL> select table_name,blocks
    2 from dba_tables
    3 where table_name='ACCOUNT_OWNERSHIPS';

    TABLE_NAME BLOCKS
    ------------------------------ ----------
    ACCOUNT_OWNERSHIPS 12505

    Which points towards a good index right?. Seems very strange that index is suddenly not used!

    SELECT * FROM CUSTOMERS c, ACCOUNT_OWNERSHIPS ao
    WHERE ao.scv_cust_id = c.SCV_CUST_ID
    AND c.CUSTOMER_NUMBER = 'xxxxxxxxxx';

    Below are the 2 explain plans. The first one for when the query is run now and the second one for when it was run before.

    Explain Plan (full table scan on ACCOUNT_OWNERSHIPS) ...

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
    SELECT STATEMENT Optimizer Mode=CHOOSE 3 M 1899
    NESTED LOOPS 3 M 305 M 1899
    TABLE ACCESS FULL SCV_OWNER.ACCOUNT_OWNERSHIPS 3 M 64 M 1899
    TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 45 K 3 M
    INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 45 K

    Expect to see something like (no full table scans) ...

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
    SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 61
    NESTED LOOPS 6 K 750 K 61
    TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 19 1 K 4
    INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 19 3
    TABLE ACCESS BY INDEX ROWID SCV_OWNER.ACCOUNT_OWNERSHIPS 10 M 259 M 3
    INDEX RANGE SCAN SCV_OWNER.AOP_CUS_DK_I 10 M 2

    Thanks in advance,
    Chucks_k

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Some thing went wrong when collecting statistics on the index.
    Re-analyze the index and see FTS is appearing again.

    Tamil

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    The clustering_factor,1444800 seems to be high.
    If this is equivalent to table's blocks, then it may not be a good index.
    That's a crazy assertion when you don't even know what the problematic query is.

    Also, you probably mean "if it's equivalent to the number of rows", not blocks.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2001
    Posts
    337
    Hi Tamil,

    I ran an analyze on the index:

    ANALYZE INDEX index_name VALIDATE STRUCTURE;

    And re-ran the query with no avail. Its very strange. Do u think a rebuild of the query will help?.

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