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).
getting estimate of result set from v$sql_plan
can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? -- 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
[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.