Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle CBO query cost - retrieve value?

Re: Oracle CBO query cost - retrieve value?

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: Tue, 09 Oct 2007 00:30:24 -0700
Message-ID: <1191915024.083977.55900@19g2000hsx.googlegroups.com>


On Oct 7, 10:37 pm, DA Morgan <damor..._at_psoug.org> wrote:
> csn..._at_gmail.com wrote:
> > Urm, Morgan, you didn't really think that was a question on syntax,
> > did you?
>
> > To the OP, don't look at cost. It is not for consumption by the end-
> > user.
>
> > You have a bind-variable peeking problem.
>
> > Construct say 3 different cursors each with a different stabilised
> > plan based on selectivity of the inputs, and call the appropriate
> > cursor based on the input variable. Or reject it altogether.
>
> > Comes down to: know your data.
>
> Given the syntax error ... you are assuming that what you are seeing
> is the real statement. I'm not willing to make that assumption.
>
> The OP has now stated: "I anonymised & simplified the query data
> incorrectly" so, given that it has been simplified, the real issue
> may bear no relationship to what has been posted.
>
> To the OP. Anonymizing, ok, rename the tables and columns if you wish.
> But simplifying makes the exercise meaningless.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Hi, I wouldn't even contemplate what you are trying to do the way you want to do it. However if after reviewing your requirements, you still believe you need to retrieve the cost for a specific query prior to execution, one solution is described below.

SQL>
SQL> SELECT EXTRACTVALUE(DBMS_XPLAN.BUILD_PLAN_XML(statement_id =>'something'),'/plan/operation[@id="0"]/cost/text()') theCost  FROM DUAL; THECOST



419
SQL>
SQL>
SQL>

A full trace is shown inline below.

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> EXPLAIN PLAN FOR

    SELECT value1
    FROM test3

     WHERE value2=22
       AND value3=32
       AND value4 BETWEEN 16 AND 20;

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); SQL>
SQL>
PLAN_TABLE_OUTPUT



Plan hash value: 963528627

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 669 | 10704 | 419 (0)| 00:00:06 |
| 1 | TABLE ACCESS CLUSTER| TEST3 | 669 | 10704 | 419 (0)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | TEST3IDX| 1 | | 19 (0)| 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT


   2 - access("value2"=2.2E+001F AND "value3"=3.2E+001F AND
              "value4">=1.6E+001F AND "value4"<=2.0E+001F)
       filter("value4"<=2.0E+001F AND "value4">=1.6E+001F)

16 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>EXPLAIN PLAN

 SET STATEMENT_ID='something' FOR
   SELECT value1
    FROM test3
     WHERE value2=22
       AND value3=32
       AND value4 BETWEEN 16 AND 20;

Explained.

SQL>
SQL> SELECT DBMS_XPLAN.BUILD_PLAN_XML(statement_id => 'something') AS XPLAN1
 FROM DUAL; XPLAN1



<plan>
  <operation name="SELECT STATEMENT" id="0" depth="0" pos="419">
    <card>669</card>
    <bytes>10704</bytes>
    <cost>419</cost>
    <io_cost>419</io_cost>
    <cpu_cost>2984183</cpu_cost>
    <time><![CDATA[00:00:06 ]]></time>

  </operation>
  <operation name="TABLE ACCESS" options="CLUSTER" id="1" depth="1" pos="1">

    <object><![CDATA[TEST3]]></object>

XPLAN1


    <card>669</card>
    <bytes>10704</bytes>
    <cost>419</cost>
    <io_cost>419</io_cost>
    <cpu_cost>2984183</cpu_cost>
    <time><![CDATA[00:00:06 ]]></time>
    <project><![CDATA["TEST3".ROWID[ROWID,10], "value2"[BINARY_FLOAT,
4],
"value3"[BINARY_FLOAT,4], "value1"[BINARY_FLOAT,4], "value4"[BINARY_FLOAT,4]]]></project>

    <qblock><![CDATA[SEL$1]]></qblock>
    <other_xml>
      <info type="db_version">11.1.0.6</info>

XPLAN1


      <info type="parse_schema"><![CDATA["SCOTT"]]></info>
      <info type="plan_hash">963528627</info>
      <info type="plan_hash_2">953433420</info>
      <outline_data>
        <hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "TEST3"@"SEL
$1""TEST3IDX")]]></hint>
        <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
        <hint><![CDATA[ALL_ROWS]]></hint>
        <hint><![CDATA[DB_VERSION('11.1.0.6')]]></hint>
        <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.1.0.6')]]></hint>
        <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
      </outline_data>

XPLAN1


    </other_xml>
  </operation>
  <operation name="INDEX" options="RANGE SCAN" id="2" depth="2" pos="1">

    <object><![CDATA[TEST3IDX]]></object>
    <card>1</card>
    <cost>19</cost>
    <io_cost>19</io_cost>
    <cpu_cost>135607</cpu_cost>
    <time><![CDATA[00:00:01 ]]></time>
    <project><![CDATA["TEST3".ROWID[ROWID,10]]]></project>
    <predicates type="access"><![CDATA["value2"=2.2E+001F AND
"value3"=3.2E+001F AND "value4">=1.6E+001F AND "value4"<=2.0E+001F]]></predicates>

XPLAN1


    <predicates type="filter"><![CDATA["value4"<=2.0E+001F AND "value4">=1.6E+001F]]></predicates>

    <qblock><![CDATA[SEL$1]]></qblock>
  </operation>
</plan>

SQL>
SQL>
SQL> SELECT EXTRACTVALUE(DBMS_XPLAN.BUILD_PLAN_XML(statement_id
=>'something'),'/plan/operation[@id="0"]/cost/text()') theCost  FROM DUAL; THECOST

419

SQL> SELECT *
 FROM V$VERSION; BANNER



Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
SQL>
SQL>
SQL>
SQL>
Received on Tue Oct 09 2007 - 02:30:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US