Question: My application is an ad hoc
query system and I want to estimate the number of rows returned by a
SQL statement before running the SQL. I know that this is the
cardinality (rows returned estimates) for the SQL statement, but I
need to know how to programmatically check the plan_table before
running the query. How can I estimate the size of the SQL result
set?
Answer: To estimate the number of rows
that the optimizer thinks that a query will return, you can use the
explain plan syntax and read the plan_table for the
cardinality column. Each use in the application will need a
plan_table:
SQL> CONN sys/password AS
SYSDBA
Connected
SQL>
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
SQL> GRANT ALL ON sys.plan_table TO public;
Grant
succeeded.
SQL> CREATE PUBLIC SYNONYM plan_table FOR
sys.plan_table;
Synonym created.
At this point, your application can populate the plan_table
with the cardinality estimates:
SQL> explain plan set
statement_id='estimate_rows' for select stuff from mmytab where
myval = 12;
Explained.
Now, you can query the plan table. You can modify this standard
query to get the cardinality for the last step of the execution
plan, which will display the estimated number of rows returned for
the SQL statement:
select lpad('
',2*level)||operation||' '||options||' '||object_name||
decode(partition_start,NULL,NULL,
'
PARTS('||partition_start||'-'||partition_stop||')')||
decode(level,1,' [Cost = '||Cost||']',Null) QUERY_PLAN,
Object_Node, Other_Tag
from plan_table
where statement_id =
'estimate_rows'
connect by prior statement_id = statement_id and
prior id = parent_id
start with id = 1
order by
statement_id;
Please note: The estimates for the number of rows returned will
only be as current as your optimizer statistics as gathered by your
dbms_stats utility.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
