Results 1 to 15 of 15

Thread: query tuning

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: query tuning

    Hi,
    Could you please help in tuning the below query.
    I can not change the sql query , since it is generated by third party tool.
    The follwing query is not even executing. When i replace 'OR' operator with 'And',query is executing very fast.
    I also tried executing the query by creating index on MASTER_ITEMS on columns (item_number,item_desc1,clean_item),but still the query is not executing.
    Both tables are recently analyzed on 15-jun-2004.

    PHP Code:
    SELECT
      MASTER_ITEMS
    .ITEM_NUMBER,
      
    MASTER_ITEMS.CLEAN_ITEM,
      
    MASTER_ITEMS.ITEM_ID
    FROM
      datap
    .MASTER_ITEMS
    WHERE
      MASTER_ITEMS
    .ITEM_NUMBER  IN  (SELECT USER_CHAR1 FROM datap.INFO_USERS)
       OR   
    MASTER_ITEMS.ITEM_DESC1  IN  (SELECT USER_CHAR1 FROM datap.INFO_USERS)
       OR   
    MASTER_ITEMS.CLEAN_ITEM  IN  (SELECT USER_CHAR1 FROM datap.INFO_USERS);   
       
    Explain Plan:

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    ---------------------------------------------------------------------
    Id  Operation            |  Name        Rows  Bytes Cost  |
    ---------------------------------------------------------------------
    |   
    SELECT STATEMENT     |              |   304K|    13M|  6026 |
    |*  
    |  FILTER              |              |       |       |       |
    |   
    |   TABLE ACCESS FULL  MASTER_ITEMS |   304K|    13M|  6026 |
    |*  
    |   TABLE ACCESS FULL  INFO_USERS   |  1613 43551 |    76 |
    |*  
    |   TABLE ACCESS FULL  INFO_USERS   |  1613 43551 |    76 |
    |*  
    |   TABLE ACCESS FULL  INFO_USERS   |  1613 43551 |    76 |
    ---------------------------------------------------------------------

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       
    filterEXISTS (SELECT /*+ */ 0 FROM "DATAP"."INFO_USERS"
                  "INFO_USERS" 
    WHERE "INFO_USERS"."USER_CHAR1"=:B1) OR  EXISTS (SELE
    CT

                  
    /*+ */ 0 FROM "DATAP"."INFO_USERS" "INFO_USERS" WHERE
                  
    "INFO_USERS"."USER_CHAR1"=:B2) OR  EXISTS (SELECT /*+ */ 0 FROM
                  
    "DATAP"."INFO_USERS" "INFO_USERS" WHERE

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
                  
    "INFO_USERS"."USER_CHAR1"=:B3))
       
    filter("INFO_USERS"."USER_CHAR1"=:B1)
       
    filter("INFO_USERS"."USER_CHAR1"=:B1)
       
    filter("INFO_USERS"."USER_CHAR1"=:B1
    Number of records in MASTER_ITEMS & INFO_USERS

    sql@prod> select count(1) from datap.MASTER_ITEMS;

    COUNT(1)
    ----------
    2141879

    sql@prod> select count(1) from datap.INFO_USERS

    COUNT(1)
    ----------
    166170


    Thanks in Advance!!!

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What do you mean by "not even executing"? You mean it takes so long you abort it, or that it has an error?

    Of course changing the ORs to ANDs will speed it up - it can now use an index on one or many of those columns; unfortunately it will not get the right answer!

    If your goal is to get something back quickly then you could try ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    you could try something like

    SELECT
    MASTER_ITEMS.ITEM_NUMBER,
    MASTER_ITEMS.CLEAN_ITEM,
    MASTER_ITEMS.ITEM_ID
    FROM
    datap.MASTER_ITEMS , datap.INFO_USERS i
    WHERE
    MASTER_ITEMS.ITEM_NUMBER=i.USER_CHAR1
    UNION
    <then the second criteria>
    UNION
    <then the third criteria>

    You will probably need 3 separate indexes on the three columns in master_items (NOT one concatenated index).

    Alan

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    is USER_CHAR1 unique in INFO_USERS?

    First select from the INFO_USERS table once inastead of a gazillion times:

    (select USER_CHAR1 user from datap.INFO_USERS) users

    Then compare against it.

    PHP Code:
    SELECT 
      a
    .item_number
      
    a.clean_item
      
    a.item_id 
    FROM 
      datap
    .MASTER_ITEMS a,
      (
    select user_char1 user from datap.INFO_USERSb
    WHERE 
      a
    .item_number  =  b.user OR 
      
    a.item_desc1   =  b.user OR
      
    a.clean_item   =  b.user
    let me know if that works better.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thanks everyone for the reply!!

    Tony,
    1. Yes 'not even executing' means, query taking so long time thant i have to
    abort it.
    2. As suggested i changed optmiser_mode=first_rows, even this time query is
    taking long time and i have to abort it.

    Alanp,
    1. As mention in my previous quote, that i can not change the sql query, as
    query is generated by third party tool.
    2. But your correct , union is indeed working very fast.
    3. I aso tried by creating three seperate indexes on MASTER_ITEM but
    same problem.

    the_duck,
    1. I can not change sql query as query is generated by third party tool.
    2. user_char1 is not a unique column in table USER_INFO


    Thanks ,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about if you combine the FIRST_ROWS setting with the 3 separate indexes Alan suggested?

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thanks for the reply!!!
    Opp!! It does'nt work even after creating suggested indexes and changing optmiser mode to 'FIRST_ROWS'
    PHP Code:
    some indexes for item_master table.
    INDEX_NAME                     COLUMN_NAME
    ------------------------------ ---------------------
    I_CLEAN_ITEM                   CLEAN_ITEM
    I_ITEM_NUMBER                  ITEM_NUMBER
    I_ITEM_DESC1                   ITEM_DESC1 
    Thanks & Regards,
    Pagnint
    (No need to search web before posting new question)

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ... and you analyzed again after creating the 3 indexes, yes?

    According to the 9i Performance Tuning guide, an OR will be transformed into a UNION ALL if possible (and if the CBO considers it a good idea):

    http://download-west.oracle.com/docs...ps.htm#1005572

    If you are on 8i, you shold refer to the equivalent doc for that.

  9. #9
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi Tony,

    PHP Code:
    sql@prod>select from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 64bit Production
    PL
    /SQL Release 9.2.0.4.0 Production
    CORE    9.2.0.3.0       Production
    TNS 
    for HPUXVersion 9.2.0.4.0 Production
    NLSRTL Version 9.2.0.4.0 
    Production

    Elapsed
    00:00:00.07
    sql
    @prodselect last_analyzed,index_name from user_indexes where table_name='MASTER_ITEMS';

    LAST_ANAL INDEX_NAME
    --------- ------------------------------
    30-JUN-04 I_CLEAN_ITEM
    28
    -JUN-04 I_ITEM_DESC1
    30
    -JUN-04 I_ITEM_NUMBER 
    Thanks for the help.
    I ran the query by replacing OR with UNION ALL and it ran very fast.
    What i could not understand is , if optimiser converts OR into UNION ALL,
    why the sql query with OR condition is not working?

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, it may convert OR to UNION ALL, but then again it may not. It depends on what it considers the relative costs to be. Presumably here it (apparently wrongly) thinks that it should not.

    Maybe reducing the OPTIMIZER_INDEX_COST_ADJUST parameter using ALTER SESSION may help? I'm clutching at straws here...

  11. #11
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi Tony,
    I really appreciate for your help..thanks for immediate reply's to all my post.

    I have already tried by setting following parameters
    PHP Code:
    sql@prod>show parameter optimizer

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_dynamic_sampling           integer     1
    optimizer_features_enable            string      9.2.0
    optimizer_index_caching              integer     90
    optimizer_index_cost_adj             integer     5
    optimizer_max_permutations           integer     2000
    optimizer_mode                       string      FIRST_ROWS 
    thanks,
    Pagnint
    (No need to search web before posting new question)

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No problem - but I think I've reached the end of my usefulness on this one! You may want to consider posting the question on Ask Tom when he's open for questions.

  13. #13
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thank you very much for help.
    When Ask tom is open for questions? Most of the time it is not open for questions.

    Once again thanks,


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know when is the best time to submit a question to Ask Tom - I agree it almost always isn't now!

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A couple of things you could try

    1) set dbfile_multiblock_read_count(sp?) to a low number like 2 for your session.

    2) Do a more detailed analyze like 'for all indexed columns size 50' if you havent already (or if you have go for a simpler analyze). If that still doesnt work try deleting the stats altogether NOTE though this will affect other queries which use this table : )

    3) Look up plan stability so you can doctor the execution plan : )

    Alan

Posting Permissions

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