Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14

    Question Unanswered: Query optimization

    Hello Everyone,

    I am using the Oracle 9.2.0.4. I have a table X having the next columns:
    AWRB_WEEK_ID NUMBER(2) --> PK
    AWRB_YEAR_ID NUMBER(4) --> PK
    AWRB_PRODUCT_MAIN_NUMBER VARCHAR2(40) --> PK
    AWRB_PRODUCT_OPTION VARCHAR2(3) --> PK AWRB_PRODUCT_BRAND VARCHAR2(10) --> PK
    AWRB_OUTLET_ID NUMBER(10) --> PK
    AWRB_RETAILER_ID VARCHAR2(15)
    AWRB_ACTION_FLAG NUMBER(10)
    AWRB_COMPLETION_FLAG NUMBER(1) DEFAULT 0
    AWRB_VAL1 NUMBER(15)
    AWRB_VAL2 NUMBER(15)
    AWRB_VAL3 NUMBER(15)
    AWRB_VAL4 NUMBER(15)
    AWRB_VAL5 NUMBER(15)
    AWRB_VAL6 NUMBER(15)
    AWRB_VAL7 NUMBER(15)
    AWRB_VAL8 NUMBER(15)

    On this table I run the next query with very bad results:
    SELECT
    WR1.AWRB_RETAILER_ID, WR1.AWRB_PRODUCT_MAIN_NUMBER,
    WR1.AWRB_PRODUCT_OPTION, WR1.AWRB_PRODUCT_BRAND,
    WR1.AWRB_WEEK_ID, WR1.AWRB_YEAR_ID,
    SUM(NVL(WR1.AWRB_VAL1, 0)), SUM(NVL(WR1.AWRB_VAL2, 0)),
    SUM(NVL(WR1.AWRB_VAL3, 0)), SUM(NVL(WR1.AWRB_VAL4, 0)),
    SUM(NVL(WR1.AWRB_VAL5, 0)), SUM(NVL(WR1.AWRB_VAL6, 0)),
    SUM(NVL(WR1.AWRB_VAL7, 0)), SUM(NVL(WR1.AWRB_VAL8, 0))
    FROM X WR1
    WHERE wr1.AWRB_COMPLETION_FLAG <> 2
    AND
    ( WR1.AWRB_RETAILER_ID,
    WR1.AWRB_PRODUCT_MAIN_NUMBER,
    WR1.AWRB_PRODUCT_OPTION,
    WR1.AWRB_PRODUCT_BRAND,
    WR1.AWRB_WEEK_ID,
    WR1.AWRB_YEAR_ID
    ) IN
    ( SELECT DISTINCT
    WR2.AWRB_RETAILER_ID,
    WR2.AWRB_PRODUCT_MAIN_NUMBER,
    WR2.AWRB_PRODUCT_OPTION,
    WR2.AWRB_PRODUCT_BRAND,
    WR2.AWRB_WEEK_ID,
    WR2.AWRB_YEAR_ID
    FROM X WR2
    WHERE
    BITAND(NVL(WR2.AWRB_ACTION_FLAG, 8192), 8192) = 8192
    )
    GROUP BY
    WR1.AWRB_RETAILER_ID,
    WR1.AWRB_PRODUCT_MAIN_NUMBER,
    WR1.AWRB_PRODUCT_OPTION,
    WR1.AWRB_PRODUCT_BRAND,
    WR1.AWRB_WEEK_ID,
    WR1.AWRB_YEAR_ID;
    ----------------
    With the result set I obtained from this complex query, I updat in the same table X the obtaining results. I think that this is another reason for slower the process ...
    ---------------
    The query is very bad at the performance level. I would like to increase the performance of the query by writing another equivalent query (to get the same result set), but to run faster. The volume of the X table is about 15 millions of rows.
    It is also, ok, if I could use a stored procedure if will run faster ...

    Thank you for any help or suggestion.

    Adrian

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post the EXPLAIN_PLAN here for the query
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    How could I obtain the EXPLAIN_PLAN for the query below?
    Thanks,
    Adrian

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you too stupid or too lazy to do a GOOGLE search for "ORACLE EXPLAIN_PLAN"?

    A keyword search (EXPLAIN_PLAN) of http://asktom.oracle.com returns 1270 articles.
    The 1st one is titled " Tuning SQL Statements Using Explain Plan"
    The 2nd one is titled "using explain plan"
    etc, etc, etc.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I dont understand why you have the table x in the subquery a second time.

    Cant you do

    SELECT
    WR1.AWRB_RETAILER_ID, WR1.AWRB_PRODUCT_MAIN_NUMBER,
    WR1.AWRB_PRODUCT_OPTION, WR1.AWRB_PRODUCT_BRAND,
    WR1.AWRB_WEEK_ID, WR1.AWRB_YEAR_ID,
    SUM(NVL(WR1.AWRB_VAL1, 0)), SUM(NVL(WR1.AWRB_VAL2, 0)),
    SUM(NVL(WR1.AWRB_VAL3, 0)), SUM(NVL(WR1.AWRB_VAL4, 0)),
    SUM(NVL(WR1.AWRB_VAL5, 0)), SUM(NVL(WR1.AWRB_VAL6, 0)),
    SUM(NVL(WR1.AWRB_VAL7, 0)), SUM(NVL(WR1.AWRB_VAL8, 0))
    FROM X WR1
    WHERE wr1.AWRB_COMPLETION_FLAG <> 2
    AND BITAND(NVL(WR2.AWRB_ACTION_FLAG, 8192), 8192) = 8192
    GROUP BY
    WR1.AWRB_RETAILER_ID,
    WR1.AWRB_PRODUCT_MAIN_NUMBER,
    WR1.AWRB_PRODUCT_OPTION,
    WR1.AWRB_PRODUCT_BRAND,
    WR1.AWRB_WEEK_ID,
    WR1.AWRB_YEAR_ID;

    This should be faster.

    To improve it further try a functional index on BITAND(NVL(WR2.AWRB_ACTION_FLAG, 8192), 8192)

    Alan

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by anacedent
    Are you too stupid or too lazy to do a GOOGLE search for "ORACLE EXPLAIN_PLAN"?

    A keyword search (EXPLAIN_PLAN) of http://asktom.oracle.com returns 1270 articles.
    The 1st one is titled " Tuning SQL Statements Using Explain Plan"
    The 2nd one is titled "using explain plan"
    etc, etc, etc.
    Hey guy, you need to take a chill pill. I have also been programming for 25 years and one of the duties of senior programmers is to help the newbie. Point them to google (www.google.com a great resource newbies!!), but there is no need to get rude.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    in Alan's code, please change:
    BITAND(NVL(WR2.AWRB_ACTION_FLAG, 8192), 8192) = 8192
    to
    BITAND(NVL(WR1.AWRB_ACTION_FLAG, 8192), 8192) = 8192
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Good spot, Thanks The Duck.

  9. #9
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    Thank you AllanP for the idea! Yes, the query you showed me (with the correction made by The_Duck [many thanks!!!] ) is equivalent with the one I presented. Anyway, I will make some more tests to see if the result set from the initial query is the same with the result set for the second one.

    The process was not written by me, it is a inherited one, but I did not see this easy and very good idea...

    Adrian

  10. #10
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    Quote Originally Posted by beilstwh
    Hey guy, you need to take a chill pill. I have also been programming for 25 years and one of the duties of senior programmers is to help the newbie. Point them to google (www.google.com a great resource newbies!!), but there is no need to get rude.
    Thanks beilstwh for your reaction!
    And also, thanks to anacedent for your advice! I will try to learn deeply about the EXPLAIN_PLAN method to improve the Oracle SQL queries. It seems is working wonderful I had a quickly look on the sites you indicated me!

    Adrian

  11. #11
    Join Date
    Aug 2004
    Posts
    9
    If you make the functional index for the BITAND(NVL(WR1.AWRB_ACTION_FLAG, 8192), 8192) = 8192

    You may also chaging the places of statements in the WHERE clause like this:

    ...
    WHERE BITAND(NVL(WR1.AWRB_ACTION_FLAG, 8192), 8192) = 8192
    AND wr1.AWRB_COMPLETION_FLAG <> 2

    try to get the explain plan like this:

    1. Logon as SYSTEM
    2. grant plustrace to <user who will exec the SQL>
    3. logon as the user that you plan to exec this SQL
    4. Exec the script $ORACLE_HOME/rdbms/admin/utlxplan.sql
    5. Exec following command:
    set autotrace traceonly explain
    6. exec the query

    This will just give you the explain plan it will NOT execute the query.

Posting Permissions

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