create table bricks (
colour varchar2(10),
shape varchar2(10)
);
create table colours (
colour varchar2(10),
rgb_hex_value varchar2(6)
);
create table cuddly_toys (
toy_name varchar2(20),
colour varchar2(10)
);
create table pens (
colour varchar2(10),
pen_type varchar2(10)
);
insert into cuddly_toys values ( 'Miss Snuggles', 'pink' ) ;
insert into cuddly_toys values ( 'Cuteasaurus', 'blue' ) ;
insert into cuddly_toys values ( 'Baby Turtle', 'green' ) ;
insert into cuddly_toys values ( 'Green Rabbit', 'green' ) ;
insert into cuddly_toys values ( 'White Rabbit', 'white' ) ;
insert into colours values ( 'red' , 'FF0000' );
insert into colours values ( 'blue' , '0000FF' );
insert into colours values ( 'green' , '00FF00' );
insert into bricks values ( 'red', 'cylinder' );
insert into bricks values ( 'blue', 'cube' );
insert into bricks values ( 'green', 'cube' );
insert into bricks
select * from bricks;
insert into bricks
select * from bricks;
insert into bricks
select * from bricks;
insert into pens values ( 'black', 'ball point' );
insert into pens values ( 'black', 'permanent' );
insert into pens values ( 'blue', 'ball point' );
insert into pens values ( 'green', 'permanent' );
insert into pens values ( 'green', 'dry-wipe' );
insert into pens values ( 'red', 'permanent' );
insert into pens values ( 'red', 'dry-wipe' );
insert into pens values ( 'blue', 'permanent' );
insert into pens values ( 'blue', 'dry-wipe' );
commit;
exec dbms_stats.gather_table_stats ( null, 'pens' ) ;
exec dbms_stats.gather_table_stats ( null, 'colours' ) ;
exec dbms_stats.gather_table_stats ( null, 'bricks' ) ;
exec dbms_stats.gather_table_stats ( null, 'cuddly_toys' ) ;
You can get the execution plan for a query using DBMS_XPlan. The table function DISPLAY_CURSOR fetches the plan from memory for the requested SQL statement.
Run this to get the execution plan for the join:
select *
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID));
The first argument of the DISPLAY_CURSOR is the SQL ID for the statement. LIVESQL_LAST_SQL_ID is a bind variable specific to Live SQL. Only use this in Live SQL. Normally you'll want to pass NULL or a SQL ID instead.
Passing NULL gets the plan for the last SQL statement run in this session. Using a SQL ID searches for plans in the cursor cache for that statement.
To get the SQL ID for a statement, search for it in v$sql:
select sql_id, sql_text
from v$sql
where sql_text like 'select *%bricks b%'
/* exclude this query */
and sql_text not like '%not this%';
The SQL ID is a hash of the statement's text. This means that changes to the formatting of a SQL statement will give it a new SQL ID, even though its meaning is identical. For example the only difference between these queries is the case of select. But they have different SQL IDs!
select *
from bricks b
join colours c
on b.colour = c.colour;
SELECT *
from bricks b
join colours c
on b.colour = c.colour;
select sql_id, sql_text
from v$sql
where sql_text like '%bricks%join%colours%'
/* exclude this query */
and sql_text not like '%not this%';
If you know the SQL ID for a statement, you can pass it directly to DBMS_XPlan:
select *
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor('1jqgaskqzc3tq'));
This is handy if you want to get the plan for a statement running in a different session.
If you don't know the SQL ID, but have the statement's text, you can lookup its SQL ID and pass it to DBMS_XPlan in one statement with this query:
select /* colours query */* from colours;
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'BASIC'
)
) p
where s.sql_text like '%colours query%' /* enter text from the target statement here */
and s.sql_text not like '%not this%';
An execution plan is a tree. The database uses depth-first search to traverse it.
This starts from the SELECT operation at the top of the plan. Then travels down to the top-most leaf. After reading the data from this source, it goes back up to its parent.
At this point the process repeats. Travel down the plan to find the next unvisited leaf. After reading, this, pass the rows back up to its parent.
select *
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID));
Start from the top (SELECT STATEMENT) and go down the tree to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table.
Pass the rows from this table up to the first leaf’s parent, the HASH JOIN.
Look for the next unvisited child of step 1. This is the TABLE ACCESS FULL of the BRICKS table.
Pass the rows from this table up to its parent, the HASH JOIN.
All the children of step 1 have been accessed, so pass the rows that survive the join to the SELECT STATEMENT and back to the client.
Run this four table join to get its plan:
select c.*, pen_type, shape, toy_name
from colours c
join pens p
on c.colour = p.colour
join cuddly_toys t
on c.colour = t.colour
join bricks b
on c.colour = b.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID));
/*
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL| COLOURS |
| 5 | TABLE ACCESS FULL| CUDDLY_TOYS |
| 6 | TABLE ACCESS FULL | PENS |
| 7 | TABLE ACCESS FULL | BRICKS |
--------------------------------------------
*/
The order of operations in this plan is:
Start from the top of the plan (SELECT STATEMENT) and go down to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table in execution plan step 4.
Pass the rows from this table up to the first leaf’s parent, which is the HASH JOIN in step 3.
Find the next unvisited child, which is the TABLE ACCESS FULL of the CUDDLY_TOYS table in step 5.
Pass the rows to the HASH JOIN in step 3. Step 3 has no more children, so return the rows that survive the HASH JOIN in step 3 to the HASH JOIN in step 2.
Search for the next child of step 2. This is the TABLE ACCESS FULL of the PENS table in step 6.
Pass these rows to the HASH JOIN in step 2. Step 2 has no more children, so return the rows that survive the HASH JOIN in step 2 to the HASH JOIN in step 1.
Repeat the process until you’ve run all the operations. So the complete order for accessing the execution plan step IDs is: 4, 3, 5, 3, 2, 6, 2, 1, 7, 1, and 0.
Run this query and get its plan:
select c.colour, count(*)
from colours c
join (
select colour, shape from bricks
union all
select colour, toy_name from cuddly_toys
union all
select colour, pen_type from pens
) t
on t.colour = c.colour
group by c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID));
/*
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | COLOURS |
| 4 | VIEW | |
| 5 | UNION-ALL | |
| 6 | TABLE ACCESS FULL| BRICKS |
| 7 | TABLE ACCESS FULL| CUDDLY_TOYS |
| 8 | TABLE ACCESS FULL| PENS |
---------------------------------------------
*/
This uses the same four tables as the previous query. But accesses them in a different way. Here the order of operations is:
Travel down the plan to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table in step 3.
Pass the rows from this table up to the first leaf’s parent, the HASH JOIN in step 2.
Find the next leaf, which is the TABLE ACCESS FULL of the BRICKS table in step 6.
Its parent is a multichild operation—UNION-ALL—so the database will next execute steps 7 and 8. (There is an optimization—concurrent execution of UNION-ALL—that means that the database can run all of these table scans at the same time in parallel queries.)
Pass the rows from the tables at steps 6, 7, and 8 up to the UNION-ALL in step 5. This step combines the rows into one dataset.
Work back up the tree to the HASH JOIN in step 2.
Join the rows from steps 3 and 5, passing the surviving rows up to the HASH GROUP BY in step 1.
Finally, return the data to the client.
Note that UNION-ALL can combine many tables in one operation. This is different to joins, which always combine exactly two data sources. Joins and unions are separate operations - the optimizer can't swap one for another.
A basic plan only gives you its shape. To assess whether the plan is good, you need to see the number of rows flowing out of each step of the plan.
You do this with the format parameter of DBMS_XPlan. Using ROWSTATS adds the estimated and actual number of rows to each step.
You should also specify which execution details you want. Control this with LAST or ALL:
This gets the row details for the previous execution of the query:
select *
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));
But it only shows the E(stimated)-rows column. The A(ctual)-rows are missing!
As the note indicates, to capture this information either you need to:
This captures row stats by adding the hint to the query:
select /*+ gather_plan_statistics */*
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));
Replace TODO in this code to get the row statistics for every execution of the query:
select /*+ gather_plan_statistics */*
from bricks b
join colours c
on b.colour = c.colour;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => ' TODO '));
When this displays the stats for every execution, the Starts and A-rows figures will increase each time you run the query.
In the queries so far, the database reads each table once during execution. In some queries the database will read the same table many times.
To check this, look at the Starts column included in the row stats. This states how many times the operation began while the query was running.
This query uses a scalar subquery. The database may run this once for each row from the colours table. Verify this by looking at the starts column:
select /*+ gather_plan_statistics */c.rgb_hex_value,
( select count (*)
from bricks b
where b.colour = c.colour
) brick#
from colours c;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));
Note that this plan also breaks the read the top-most leaf first rule! BRICKS appears above COLOURS in the plan. But it receives rows from the colours table. So the database must read COLOURS before BRICKS.
Be aware that there are many special cases when it comes to execution plans. This series discusses general principles. But you will be able to find cases where the rules don't apply.
Can you rewrite the scalar subquery in "Starts Statistic" module so it only accesses BRICKS once? Replace the /* TODO */ sections to do this:
select /*+ gather_plan_statistics */c.rgb_hex_value,
/* TODO */
from colours c
/* TODO */;
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, format => 'ROWSTATS LAST'));
HINT: Can you change the query to use a join instead of a subquery? If you are struggling with this, take these free SQL courses: