One
of the things I do fairly regularly when struggling with a SQL statement that
it is not behaving well goes like this.
- Get the copy of the statement into a script file
- Add gather_plan_statistics hint or set statistics_level =all
- Bind all those inputs
- Run it
- Run xplan on it.
drop table t1 purge;
drop table t2 purge;
create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index t1_idx on t1(owner);
Lets say this sql is not behaving well from an application.
select t1.owner, max(t2.object_id), count(*)
from t1, t2
where t1.object_id = t2.object_id
and t2.owner = :x
group by t1.owner
order by t1.owner;
Then
we could follow either of these options to generate an execution plans.
Option#1 – set the parameter statistics_level=all
Script1.sql
variable x varchar2(30)
exec :x := 'SCOTT';
set serveroutput off linesize 300 pagesize 9999
alter session set statistics_level=all;
set termout off
select t1.owner,max(t1.object_id),count(*)
from t1 , t2
where t1.object_id = t2.object_id
and t2.owner = :x
group by t1.owner;
set termout on
select * from
table(dbms_xplan.display_cursor(format=>'allstats last'));
Executing
the above script from SQL*Plus produces the output like this.
demo@ORA12C> @d:\script1.sql
PL/SQL procedure successfully completed.
Session altered.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 4h1b567g4uh7h,
child number 0
-------------------------------------
select t1.owner,max(t1.object_id),count(*) from t1 , t2 where
t1.object_id = t2.object_id and t2.owner = :x group by t1.owner
Plan hash value: 51733071
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers | Reads | OMem
| 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 1 |00:00:00.59 | 2634 |
2629 | | | |
| 1 | HASH GROUP BY |
| 1 | 20 |
1 |00:00:00.59 | 2634 | 2629 |
1079K| 1079K| 487K (0)|
|* 2 | HASH JOIN |
| 1 | 3872 |
4 |00:00:00.59 | 2634 | 2629 |
2168K| 2168K| 921K (0)|
|* 3 | TABLE ACCESS FULL| T2 |
1 | 3872 | 4 |00:00:00.36 | 1317 |
1315 | | | |
| 4 | TABLE
ACCESS FULL| T1 | 1 |
77430 | 77430 |00:00:00.22 | 1317 |
1314 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 -
filter("T2"."OWNER"=:X)
23 rows selected.
demo@ORA12C>
Else,
you could introduce gather_plan_statistics hint to the sql like this.
Option#2
– add the hint gather_plan_statistics to the SQL
Script2.sql
variable x varchar2(30)
exec :x := 'SCOTT';
set serveroutput off linesize 300 pagesize 9999
set termout off
select /*+ gather_plan_statistics */ t1.owner,max(t1.object_id),count(*)
from t1 , t2
where t1.object_id = t2.object_id
and t2.owner = :x
group by t1.owner;
set termout on
select * from
table(dbms_xplan.display_cursor(format=>'allstats last'));
Executing
the above script from SQL*Plus produces the output like this.
demo@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> @d:\script2.sql
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID 7v8mwngxy82zx,
child number 0
-------------------------------------
select /*+ gather_plan_statistics */
t1.owner,max(t1.object_id),count(*) from t1 , t2 where
t1.object_id =
t2.object_id and t2.owner = :x group by t1.owner
Plan hash value: 51733071
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers | Reads | OMem
| 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |00:00:00.43 | 2634 | 2629 |
| | |
| 1 | HASH GROUP BY |
| 1 | 20 |
1 |00:00:00.43 | 2634 | 2629 |
1079K| 1079K| 453K (0)|
|* 2 | HASH JOIN |
| 1 | 3872 |
4 |00:00:00.42 | 2634 | 2629 |
2168K| 2168K| 999K (0)|
|* 3 | TABLE ACCESS FULL| T2 |
1 | 3872 | 4 |00:00:00.15 | 1317 |
1315 | | | |
| 4 | TABLE ACCESS FULL| T1 |
1 | 77430 | 77430 |00:00:00.02 | 1317 |
1314 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 -
filter("T2"."OWNER"=:X)
24 rows selected.
demo@ORA12C>
Using
this information it is very easy to see how the optimizer estimates line up
with reality, and we can see “where” the optimizer is having the problem by
comparing A-ROWS and E-ROWS column.
This
information should be sufficient to start focusing the performance problem, but
if you are interested to understand what those other columns from the execution
plan represents, have a look at them here
Addendum: added on 10/31/2018
To get an
execution plan, we need READ privilege (starting with 12c READ privilege and SELECT
privilege on prior to 12c) on the below v$ tables
grant read on
v_$sql to
YOUR_USER_ACCOUNT;
grant read on
v_$sql_plan to
YOUR_USER_ACCOUNT;
grant read on
v_$sql_plan_statistics_all to
YOUR_USER_ACCOUNT;
grant read on
v_$session to
YOUR_USER_ACCOUNT;
No comments:
Post a Comment