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

Thread: Performance Problem ?

  1. #1
    Join Date
    Nov 2000
    Posts
    79
    Hi All,

    I am in a trouble doing the performace tuning of a table(Oracle 8.1.7.0) having 200 Millions Records. The application which uses the table creates ad-hoc queries(queries which are not defined beforehand). This leads to create individual indexes on each of the fields (b*tree or bitmap) not composite as queries are not known. The range queries and aggregate queries are very slow and takes upto 9 minutes.

    The same application is running on Sybase IQ Server 12 having a similar setup of indexes and gives the result back in 2-6 seconds. I tried to look read about the indexes in Sybase, Sybase has two other kind of indexes which are call HG (High Group ) and HNG(High Non Group). These indexes helps in doing aggregate and range queries and birngs the results back in seconds.


    Does any body has an idea to how to go about this problem and suggest something to improve the performance ?.

    I would really appreciate help from all of you .


    Thanks and Regards,

    gtm






  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    For your situation, I belive you should investigate partitioning and (above all) materialized views.

    I don't belive creating separate non-composite indexes for all columns is very efficient way to deal with your situation, unless your ad-hoc queries always reference only one column of that table in a where condition. Remember that oracle can only use one index to access the table row, it can not combine two separate indexes in a data access path. At least you should try to run a tkprof over a trace of a typical session to find out which composite indexes will be most benefitial.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Analyze your tables and indices. When you analyze, choose the estimate statistics, instead of compute statistics. Also set the CURSOR_SHARING from exact to force. IF your optimizer is choose, I would suggest you to change it to FIRST_ROW. But you would want to leave it as CHOOSE if it were of DWH database.

    You can also check the type of queries that had been run on your database from querying the V$SQLAREA. Then decide what to do from that point.

    Good luck,
    Sam

    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Nov 2000
    Posts
    79

    Thanks Jmodic & Sambavan, Please see the xplan

    Please see one of the simple query and its explain plan below. Probably this would give an idea to suggest me something.


    Thanks and Regards,

    gtm

    SELECT DISTINCT gender, count(*) FROM customer
    WHERE zip BETWEEN '11700' AND '11799'
    GROUP BY gender;


    Time Taken : 7 min

    G COUNT(*)
    - ----------
    F 499791
    I 164926
    M 499396


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4266 Card=3 Bytes=18
    )

    1 0 SORT (GROUP BY) (Cost=4266 Card=3 Bytes=18)
    2 1 PARTITION RANGE (ALL)
    3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CUSTOMER' (Cos
    t=4171 Card=12435 Bytes=74610)

    4 3 BITMAP CONVERSION (TO ROWIDS)
    5 4 BITMAP INDEX (RANGE SCAN) OF 'ZIP_IDX'




    Statistics
    ----------------------------------------------------------
    23 recursive calls
    0 db block gets
    156562 consistent gets
    129468 physical reads
    0 redo size
    519 bytes sent via SQL*Net to client
    425 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    3 rows processed

  5. #5
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759

    Talking

    Just wondering:
    Gender types of 'F' and 'M' are understood. What in the world is 'I'?? INTERMEDIATE?? hehe

  6. #6
    Join Date
    Nov 2000
    Posts
    79
    yes, 'I' is something which is in between M and F



    gtm

  7. #7
    Join Date
    Nov 2000
    Posts
    212
    b-tree index on (zip) or even (zip, gender) should help.
    I mean, index on zip does range query. Distinct is then done by oracle, but as only 3 values exist, it should not be a problem.

    Index on (zip, gender) could make sense if much more than 3 genders exists :-).

    Partition pruning could help, but for this you need to change query.

  8. #8
    Join Date
    Feb 2001
    Posts
    389
    I see two major things happening:

    1) Partition range is all, that means it is accessing all partitions.

    2) physical reads is very high, that is too much disk I/O.

    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CUSTOMER' (Cos
    t=4171 , cost is very high.

    Drop the index on Zip and do full table access.Or try local non-prefixed index on zip.

    Take Care
    GP

  9. #9
    Join Date
    Nov 2000
    Posts
    79

    thanks gpsingh & lnd

    infact i tried with b*tree index on zip and w/o the index as well, and its more slower. Currently the index on zip is a non-prefix local index. I have also tried with querying in its own partition.

    The buffer hit ratio goes into -tive and increasing the buffer cache hasn't helped.

    What should i try now ?.

    thanks

    gtm

  10. #10
    Join Date
    Feb 2001
    Posts
    389
    Write in the table, partition and index details.

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