Re: Re: getting estimate of result set from v$sql_plan
I found an error from my yesterdays post: Basically, in 9i there are four ways of finding out how many rows will any query return: 1) select from the query and count 2) use v$sql_plan_statistics column output_rows for already executed queries output_rows shows cumulative outrows statistics for specific query, last_output_rows shows rowcount for last execution of a query. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: getting estimate of result set from v$sql_plan
[EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Re: getting estimate of result set from v$sql_plan
i need to return the cardinality estimate to the user as a number. how do i do that? From: Stephane Faroult [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 04:29:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: getting estimate of result set from v$sql_plan [EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: getting estimate of result set from v$sql_plan
DBMS_XPLAN Stephane Faroult [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] com cc: Sent by: Subject: Re: getting estimate of result set from v$sql_plan [EMAIL PROTECTED] .com 12/29/2003 01:29 PM Please respond to ORACLE-L [EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Re: getting estimate of result set from v$sql_plan
let me be clearer. I need to return an estimate of the number of rows for 'pagination'. The user will page through 25 rows a time, but wants an estimate on the total number of rows returned. I want to avoid counts. tom kytes book says to use v$sql_plan, but how do i get my exact query? Id prefer to do it without table joins. Since I have a very strict SLA. dbms_xplan is returning the whole thing. I just want the cardinality and I have to put it in a variable. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 4:59 PM i need to return the cardinality estimate to the user as a number. how do i do that? From: Stephane Faroult [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 04:29:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: getting estimate of result set from v$sql_plan [EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Re: getting estimate of result set from v$sql_plan
oh forget it. stupid question. I figured it out. Sorry. been really busy today. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 4:59 PM i need to return the cardinality estimate to the user as a number. how do i do that? From: Stephane Faroult [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 04:29:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: getting estimate of result set from v$sql_plan [EMAIL PROTECTED] wrote: can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Re: getting estimate of result set from v$sql_plan
Hi!
Comments below:
let me be clearer. I need to return an
estimate of the number of rows for 'pagination'. The user will page
through 25 rows a time, but wants an estimate on the total number of
rows returned. I want to avoid counts. tom kytes book says to
use v$sql_plan, but how do i get my exact query? Id prefer to do it
without table joins. Since I have a very strict SLA. dbms_xplan
is returning the whole thing. I just want the cardinality and I have to
put it in a variable.
I'll just post a quick raw idea how to do it
(although there are many problems which may render this functionality useless)
with a longer example.
Basically, in 9i there are four ways of finding out
how many rows will any query return:
1) select from the query and count
2) use v$sql_plan_statistics column output_rows for
already executed queries
3) use CBO estimates for parsed queries from
v$sql_plan
4)askLarry Ellison
1st is the most accurate, but
resource-hungry
2nd has the problem that it only records last
rowcount for a given query (which means it's useless with bind
variables)
3rd is probably quite inaccurate, especially when
histograms aren't calculated on non-single row predicate columns (again, with
bind variables is useless). Also, if you want to use it, you have to find the
right row for the
4th isn't implemented before 11g
Anyway, here are few samples how Oracle estimates
records rowcounts (this is long):
--
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 1s 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
Im 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 2s 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 therell be 3606 2s 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
Re: Re: getting estimate of result set from v$sql_plan
I'll add the missing part to oneof statements in the beginning of my last mail: 3rd is probably quite inaccurate, especially when histograms aren't calculated on non-single row predicate columns (again,with bind variables is useless). Also, if you want to use it, you have to find the right row for the ... rowcount (cardinality). For select queries this is normally the execution plan row with id = 1 in v$sql_plan, but for update for example, it's the id=0 row that shows cardinality. Tanel.
