Re: Re: getting estimate of result set from v$sql_plan

 

 

 

 

 

 

 

 

SQL> create table t (a, b) as select 1, 'A' from sys.obj$;

 

Table created.

 

SQL> insert into t values (2, 'B');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select a, count(*) from t group by a;

 

         A   COUNT(*)

---------- ----------

         1       7211

         2          1

 

We got one ‘2’ and lots of ‘1’s in the table, 7212 records in total.

 

SQL> var v number;

SQL> exec :v:=2;

 

Lets use bind variables for our query

 

PL/SQL procedure successfully completed.

 

SQL> analyze table t compute statistics;

 

Calculate stats without histograms

 

Table analyzed.

 

SQL> select /* taneltest */ * from t where a=:v;

 

         A B

---------- -

         2 B

 

SQL> select output_rows

  2  from v$sql_plan_statistics p

  3  where (address, hash_value) in (

  4      select address, hash_value from v$sql where sql_text like '%/* taneltest */%'

  5      and sql_text not like '%hash_value%'

  6  );

 

OUTPUT_ROWS

-----------

          1

 

v$sql_plan_statistics showed that last time the statement was executed, it returned 1 row.

 

SQL> exec :v:=1;

 

PL/SQL procedure successfully completed.

 

SQL> select /* taneltest */ * from t where a=:v;

 

         A B

---------- -

         1 A

         1 A

         1 A

         1 A

...

(pressed CTRL-C)

 

682 rows selected.

 

SQL> select output_rows

  2  from v$sql_plan_statistics p

  3  where (address, hash_value) in (

  4      select address, hash_value from v$sql where sql_text like '%/* taneltest */%'

  5      and sql_text not like '%hash_value%'

  6  );

 

OUTPUT_ROWS

-----------

        692

 

The same statement now has returned 692 rows (less was displayed because of my ctrl-c)

If we need an estimate without executions then we just have to parse the statement and rely on CBO calculations (note that because my lazyness I still executed the select without parsing it. Also in this example I’m using literal values, with binds the execution plan is probably not generated before the first bind - and this execution plan will remain despite bind value changes until it is invalidated by some reason).

 

SQL> select /* taneltest2 */ * from t where a=2;

 

         A B

---------- -

         2 B

 

Lets estimate how many ‘2’s we have (without any histograms)

 

SQL> select p.child_number, p.id, rpad(' ', p.depth) || p.operation || ' ' || p.options operation,

  2      p.cost, p.cardinality, p.bytes, p.temp_space

  3  from v$sql_plan p

  4  where (address, hash_value) in (

  5      select address, hash_value from v$sql where sql_text like '%/* taneltest2 */%'

  6      and sql_text not like '%hash_value%'

  7  );

 

CHILD_NUMBER         ID OPERATION                            COST CARDINALITY      BYTES TEMP_SPACE

------------ ---------- ------------------------------ ---------- ----------- ---------- ----------

           0          0 SELECT STATEMENT                        6

           0          1  TABLE ACCESS FULL                      6        3606      10818

 

CBO estimates that there’ll be 3606 “2”s in the table. Note that 3606 is exactly half of 7212, the number of rows in the table (despite no histograms we have rowcnt populated in tab$ and distcnt$ populated in hist_head$, thus CBO can find the density by simply dividing these two)

 

SQL> analyze table t compute statistics for columns a size 100;

 

Lets generate a histogram:

 

Table analyzed.

 

SQL> select /* taneltest2 */ * from t where a=2;

 

         A B

---------- -

         2 B

 

SQL> select p.child_number, p.id, rpad(' ', p.depth) || p.operation || ' ' || p.options operation,

  2      p.cost, p.cardinality, p.bytes, p.temp_space

  3  from v$sql_plan p

  4  where (address, hash_value) in (

  5      select address, hash_value from v$sql where sql_text like '%/* taneltest2 */%'

  6      and sql_text not like '%hash_value%'

  7  );

 

CHILD_NUMBER         ID OPERATION                            COST CARDINALITY      BYTES TEMP_SPACE

------------ ---------- ------------------------------ ---------- ----------- ---------- ----------

           0          0 SELECT STATEMENT                        6

           0          1  TABLE ACCESS FULL                      6           1          3

 

SQL>

 

There’s less distinct values in column than histogram buckets allowed on it, thus CBO knows exactly how many rows are for specific column value (now histgrm$ base table can be used).

 

Conclusion? It is not possible to achieve accurate rowcount predictions with Oracle 9i in real world situations and within real world constraints. But if your business requirements let you compromize performance and accuracy, then sure you can estimate rowcounts like that, but it might be that going with method 4 described in beginning of the letter is actually a better way to go.

 

 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).