Tuning Help-Urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tuning Help-Urgent

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    Thumbs down Tuning Help-Urgent

    Folks,
    I'm trying to execute a simple qry like this takes 35 mins
    Code:
    1.select  * from building_info where bld_seq_id=21261023;
    120 rows selected.
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=37)
       1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'BUILDING_INFO' (Cost=5 Card=1 Bytes=37)
       2    1     INDEX (RANGE SCAN) OF 'BD_SEQ_ID' (NON-UNIQUE) (Cost=4 Card=1)
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
        6123550  consistent gets
        6058079  physical reads
           8144  redo size
           8121  bytes sent via SQL*Net to client
            589  bytes received via SQL*Net from client
              9  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            120  rows processed
    When I pass a hint of the same Index , it returns in seconds.All the statistics are current, also I increased arraysize to 2000 still consistent gets/pIO is same. Give me some suggestions guys.Oracle 9i on Windows,Table Partitioned and Global partitioned Index.
    Thanks
    Last edited by marist89; 06-15-2005 at 04:27 PM.
    sat

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    What is the setting of OPTIMIZER_INDEX_COST_ADJ in your init.ora file?
    You should set this parameter to a low value (maybe 10).

    Try it with your query:
    Code:
    ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;
    SELECT * FROM BUILDING_INFO WHERE BLD_SEQ_ID=21261023;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    Currently my Optimizer_Index_cost_adj is 100 and even after as per your sugegstions like setting it to low value- It did'nt helped.Anything else do I need to look into
    1* ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10
    Session altered.
    120 rows selected.
    Elapsed: 00:22:52.09
    Statistics
    ----------------------------------------------------------
    798 recursive calls
    1 db block gets
    6166391 consistent gets
    6062185 physical reads
    120 redo size
    7247 bytes sent via SQL*Net to client
    512 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    38 sorts (memory)
    0 sorts (disk)
    120 rows processed
    Last edited by portal; 06-15-2005 at 04:29 PM.
    sat

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's your partition key?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    I'm sorry my partition key is the same BLD_SEQ_ID and this ID falls in last partition of the Index.My concern is if I give a hint for the same Index , retunrs result in 2 seconds. I'm confused.
    Last edited by portal; 06-15-2005 at 04:38 PM.
    sat

  6. #6
    Join Date
    Jun 2005
    Posts
    31
    > When I pass a hint of the same Index , it returns in seconds
    > 1 recursive calls
    ok, that's for parsing - if you execute the statment just using "/" a second time, that disappears. But that's neglectible.

    > 1 db block gets
    > 6,123,550 consistent gets

    6.1 Mio x 8kb = 48.8 GB
    How big is your table and how big is the index ?
    Is this one partition or all partitions of index or table ?

    > 6,058,079 physical reads

    35 min = 2,100,000 ms / 6 mio phys reads = 0.35 ms/read => your operating system cache works fine :-) - but of course you should not have such much blocks accessed at all.

    Please send this output (explain plan for...) for BOTH executions - with and without hint.
    And what happens if you force it to a full-table scan ?
    (I am just wondering if you get a INDEX-FULL scan or a Full-Table-Scan)

    A full-table scan on a 6 GB table should 5 minutes (or 1 minute if you do it in parallel :-)

    > 8144 redo size

    ==> That's most likeley from some delayed block cleanout from previous update changing more blocks than those which are "immediately on commit" cleaned.

    But even if all 120 rows are in different blocks and you must visit the rollback-segment 120 times you should not get such many blocks to access.

    > 8121 bytes sent via SQL*Net to client
    > 120 rows processed

    67 bytes/row (reasonable number)

    > 9 SQL*Net roundtrips to/from client

    Some of the 9 are for the parsing - but neglectible in our case

    > 0 sorts (memory)
    > 0 sorts (disk)

    In your 2nd mail you post:

    > 1* ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10
    > Session altered.
    > 120 rows selected.
    > Elapsed: 00:22:52.09
    > Statistics
    > ----------------------------------------------------------
    > 798 recursive calls

    !!!! 798 !!!! recursive calls !!!!!
    > 38 sorts (memory)

    do you see parallel Query slaves ?
    Once I did run into "parallel Index Range Scan" problem (after upgrade from 8.0.4 to 8.1.7 :-)) on an partitioned table.

    Please make sure that you don't run into this situation

    How do your data look like ?

    select /*+ full(B) parallel(B 4) */ count(*), bld_seq_id B from building_info group by bld_seq_id order by 1;

    (if data are not equally distributed -> Histograms...)

    How small is your DB_BLOCK_BUFFER ? (used by optimizer to calculate index-cache-hit ratio)

    OPTIMIZER_INDEX_COST_ADJ was already posted by LKBrwn_DBA

    There is a second parameter:

    OPTIMIZER_INDEX_CACHING default 0

    (alter session set ...)

    OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based
    optimization to favor nested loops joins and IN-list iterators.
    The cost of executing an index using an IN-list iterator or of executing a nested
    loops join when an index is used to access the inner table depends on the caching of
    that index in the buffer cache. The amount of caching depends on factors that the
    optimizer cannot predict, such as the load on the system and the block access
    patterns of different users.
    You can modify the optimizerís assumptions about index caching for nested loops
    joins and IN-list iterators by setting this parameter to a value between 0 and 100 to
    indicate the percentage of the index blocks the optimizer should assume are in the
    cache. Setting this parameter to a higher value makes nested loops joins and IN-list
    iterators look less expensive to the optimizer. As a result, it will be more likely to
    pick nested loops joins over hash or sort-merge joins and to pick indexes using
    IN-list iterators over other indexes or full table scans. The default for this parameter
    is 0, which results in default optimizer behavior.

  7. #7
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    To quote Tom Kyte: "Autotrace lies".
    http://asktom.oracle.com/pls/ask/f?p...31036568157622

    http://asktom.oracle.com/pls/ask/f?p...:1560806455855

    search for "autotrace lies"

    Looking Beyond Execution Plans
    The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:

    The columns of the index being used
    Their selectivity (fraction of table being accessed)
    It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption. Use Oracle Trace or the SQL trace facility and TKPROF to examine individual SQL statement performance.

    http://download-west.oracle.com/docs...x_plan.htm#838

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    6123550 consistent gets.. so many gets, doesnt it ring bell??


    I am suspecting that the run time plan is going for Index Full Scan as against your explain pan showing Index Range Scan.. And when you give the hint, its obiviously going for Index Range scans and hence seeing o/p in 2 secs..

    What you need to check is the SQL address and check the plan from v$sql_plan

    Rgds
    Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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