This query is slow!
(copyright by my Boss)
But how can you examine what’s going on? Can you “see” what Oracle is thinking?
Yes you can!
We can explain the execution plan (i.e. what the DB is going do to) by using the explain plan sql command. Naturally if you use Sql*Navigator or Toad or a similar program, you got an interface that help you a bit.
So, what we need?
We need the plan table, you can find the definition here $ORACLE_HOME/rdbms/admin/utlxplan.sql.
The plan table is the table used by Oracle to store the result of the explain plan command.
The syntax for the explain command is like:
EXPLAIN PLAN SET STATEMENT_ID = ‘ID_QUERY’ FOR Select * From Dual;
and then using a query like:
SELECT SUBSTR (LPAD (’ ‘, LEVEL - 1) ||
operation || ‘ (’ || options|| ‘)’,1,30) “Operation”,
object_name “Object”
FROM plan_table
START WITH STATEMENT_ID = ‘ID_DUAL’ AND ID = 0
CONNECT BY PRIOR ID = parent_id
you can get the execution plan of the query, something like:
Operation Object
—————————— ——————————
SELECT STATEMENT ()
TABLE ACCESS (FULL) DUAL
Here we can see that Oracle choose a Table Access Full (the full table scan) as the access path for the DUAL table.
This can help us understanding what Oracle is doing and why a query is slow.
So the explain plan can help us tuning a problematic query.
From Oracle 9i and up, there is a new package that return the execution plan, format it a bit. It’s the dbms_xplan.
The use is simple (please note the use of the table command) :
Select * from table (dbms_xplan.display(’PLAN_TABLE’,'ID_DUAL’) )
And the output is
-------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS FULL | DUAL | | | | --------------------------------------------------------------------
Note: rule based optimization
Quite nice.
