Analyzing index increase Query cost
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Analyzing index increase Query cost

  1. #1
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96

    Analyzing index increase Query cost

    Following is my experience.

    SQL> explain plan for
    2 update prtcpnt
    3 set f9= 1
    4 WHERE f3 = :b1
    5 AND f4 = :b2
    6 AND f2 != :b3
    7 /

    Explained.

    SQL> @showplan
    SQL> set echo off

    Query Plan COST
    -------------------------------------------------------------------------------- ----------
    UPDATE STATEMENT [CHOOSE] Cost = 27 27
    UPDATE PRTCPNT
    INDEX RANGE SCAN PR_TEST 2

    SQL>

    After that I am analyzing Index, and then Plan shows cost more. What is the reason.

    SQL> analyze index pr_test compute statistics;

    Index analyzed.

    SQL> @expl
    SQL> truncate table plan_table;

    Table truncated.

    SQL> explain plan for
    2 update prtcpnt
    3 set f9= 1
    4 WHERE f3 = :b1
    5 AND f4 = :b2
    6 AND f2 != :b3
    7 /

    Explained.

    SQL> @showplan
    SQL> set echo off

    Query Plan COST
    -------------------------------------------------------------------------------- ----------
    UPDATE STATEMENT [CHOOSE] Cost = 445 445
    UPDATE PRTCPNT
    INDEX RANGE SCAN PR_TEST [ANALYZED] 56

    SQL>

    The table contains more than 500000 records.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  2. #2
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    The update query is used in batch kind of operation.
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The statistics changed on the index, and the statistics are part of what determines the cost.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you look around on "Ask Tom", you find him saying that this is exactly the situation where comparing "costs" is pretty meaningless. As far as I understand him the only time when we (as opposed to the CBO) should look at them is when comparing the same query at a given moment with different hints - i.e. when doing the job of the CBO!

  5. #5
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    As DaPi said, please refer to this thread on asktom.

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

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Re: Analyzing index increase Query cost

    Originally posted by Dilippatel

    After that I am analyzing Index, and then Plan shows cost more. What is the reason.
    Try rebuilding the index and then analyze and then try getting execution plan.
    -nagarjuna

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: Analyzing index increase Query cost

    Originally posted by nagarjuna
    Try rebuilding the index and then analyze and then try getting execution plan.
    What do you expect that to do?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Take this example.

    1. I have created table with some no. of rows and created one index on one of the columns. And I use this column in one of the select queries. I have execution plan for this.

    2. I have updated the indexed column many times in between. So, index has grown in size. Still the execution plan for the select statement mentioned in above shows same.

    3. Now, I analyze the table (so the indexes get...) and populate the dictionary with new statistics. Now, I am taking the execution plan for the select query used in step 1. It shows with higher cost as the size of index is increased as per the detail available to optimizer.

    4. Now, I rebuild the index and it has been reduced to its origional size. I analyze analyze the index now and populate the dictionary with latest statistics. I am getting execution plan for select statement used in step 1 and it has different cost. It's likely that the new cost could of lesser than what we got in step 3.

    got something????
    -nagarjuna

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Dilippatel isn't getting a different execution plan -- he just wanted to know why the cost had changed. I don't think he has any problem other than understanding why this is so, and it seems like rebuilding indexes isn't apropriate to his situation.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    It's not solution. It's just explanation.
    -nagarjuna

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