If you are following along from Part 0, at this point we have found a query that we need to tune, as we have found that we need to get a 43 second query down to 3 seconds to meet the requirements given. So where do we start? The first thing I like to do is to take a look at the current execution plan of the query.
So, how do you find what a query does? One option is to run Explain Plan. The easiest way to do this is with the Explain Plan command:
explain plan for
{SQL Statement};
Running this command gathers information and puts it into the plan_table. In Oracle 10g or above, by default a plan_table is created as a global temporary table owned by SYS along with a plan_table public synonym so it can be used by all users. Once explain plan has been executed, there are a few different ways to access the information:
- Query PLAN_TABLE directly
- DBMS_XPLAN package
Also, most GUI tools (TOAD, SQL Developer, etc) have some sort of button that does both run the explain plan command and display the results.
My personal preference is the DBMS_XPLAN package. I don’t have to worry about getting used to one tool, then not have that tool available. More importantly, DBMS_XPLAN also contains some helpful information that is not always included with the other tools.
DBMS_XPLAN is a package that has up to 7 functions (as of 12c). In this article, I will just focus on 2 of them.. DISPLAY and DISPLAY_CURSOR.
DBMS_XPLAN.DISPLAY –
The DISPLAY function has been around since at least 9i. It is a table function that displays the contents of the specified plan table in a formatted fashion. Technically, there are 4 parameters (as of 12c):
DBMS_XPLAN.DISPLAY( table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE', statement_id IN VARCHAR2 DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL', filter_preds IN VARCHAR2 DEFAULT NULL );
Using all of the defaults, this function will return the plan from the global temporary plan_table for the last statement explained. This means in normal usage, you will call it with no parameters immediately after you run the explain plan command:
explain plan for
select at1.owner, at1.table_name, at1.num_rows, ac1.constraint_name, at2.owner, at2.table_name, at2.num_rows
from a_tables at1
inner join a_constraints ac1
on ac1.owner = at1.owner
and ac1.table_name = at1.table_name
inner join a_constraints ac2
on ac2.owner = ac1.r_owner
and ac2.constraint_name = ac1.r_constraint_name
inner join a_tables at2
on at2.owner = ac2.owner
and at2.table_name = ac2.table_name
where
ac1.constraint_type = 'R';
select *
from table(dbms_xplan.display);
The output looks something like:
Plan hash value: 3592798741
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 154 | 62 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 154 | 62 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 105 | 60 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | 75 | 31 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | A_TABLES | 1 | 30 | 29 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | A_CONS_NUK2 | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| A_CONSTRAINTS | 1 | 45 | 2 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 30 | 58 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | A_TABLES | 1 | 30 | 29 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | A_CONS_NUK2 | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | A_CONSTRAINTS | 1 | 49 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("AT2"."OWNER"="AC2"."OWNER" AND "AT2"."TABLE_NAME"="AC2"."TABLE_NAME")
11 - access("AC1"."OWNER"="AT1"."OWNER" AND "AC1"."TABLE_NAME"="AT1"."TABLE_NAME")
12 - filter("AC1"."R_OWNER" IS NOT NULL AND "AC1"."R_CONSTRAINT_NAME" IS NOT NULL AND
"AC1"."CONSTRAINT_TYPE"='R' AND "AC2"."OWNER"="AC1"."R_OWNER" AND
"AC2"."CONSTRAINT_NAME"="AC1"."R_CONSTRAINT_NAME")
For more information on DBMS_XPLAN.DISPLAY, see the documentation
The major advantage of using the DBMS_XPLAN.DISPLAY function is that the actual SQL doesn’t have to be executed, just explained. However, there are a few drawbacks. It is important to understand that the plan that is returned may not actually be the plan that gets used when the SQL is executed. This is especially true when bind variables are involved. Also, because the SQL has not been executed, Oracle is limited in what information it can provide. The combination of these two things (especially the 2nd) is why I use DBMS_XPLAN.DISPLAY_CURSOR 99% of the time, which I will get to next time…