SQL TUNING
Saturday, July 2, 2016
explain plan for
Customizing Execution Plan Output
explain plan for
SELECT last_name, first_name
FROM employees JOIN departments USING(department_id)
WHERE employee_id = 101;
Explained.
SELECT * FROM table(dbms_xplan.display);
How It Works
The DBMS_XPLAN.DISPLAY function has a lot of built-in functionality to provide customized output based on your needs. The function provides four basic levels of output detail:
• BASIC
• TYPICAL (default)
• SERIAL
• ALL
The display Function
The display function returns execution plans stored in a plan table. The return value is an instance of the
dbms_xplan_type_table collection. The elements of the collection are instances of the dbms_xplan_type object type. The only attribute of this object type, named plan_table_output, is of type VARCHAR2(300). The function has the following input parameters:
• table_name specifies the name of the plan table. The default value is plan_table. If NULL is
specified, the default value is used.
• statement_id specifies the SQL statement name, optionally given as a parameter, when the
EXPLAIN PLAN statement is executed. The default value is NULL. If the default value is used, the
execution plan most recently inserted into the plan table is displayed (provided the
filter_preds parameter isn’t specified).
• format specifies which information is provided in the output. There are primitive values
(basic, typical, serial, all, and advanced) and, for finer control, additional modifiers
(adaptive, alias, bytes, cost, note, outline, parallel, partition, peeked_binds,
predicate, projection, remote, report, and rows) that can be added to them. If information
should be added, modifiers are optionally prefixed by the + character (for example, basic
+predicate). If information should be removed, modifiers have to be prefixed by
the - character (for example, typical -bytes). Multiple modifiers can be specified at the
same time (for example, typical +alias -bytes -cost).
If you simply want the default output format, there is no need to pass in any special format options:
SELECT * FROM table(dbms_xplan.display);
If you want to get all available output for a query, use the ALL level of detail format output option:
SELECT * FROM table(dbms_xplan.display(null,null,'ALL'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$38D4D5F3 / EMPLOYEES@SEL$1
2 - SEL$38D4D5F3 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEES"."DEPARTMENT_ID" IS NOT NULL)
2 - access("EMPLOYEES"."EMPLOYEE_ID"=101)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20], "EMPLOYEES"."LAST_NAME"[VARCHAR2,25]
2 - "EMPLOYEES".ROWID[ROWID,10]
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
SELECT * FROM table(dbms_xplan.display(null,null,'TYPICAL -BYTES -ROWS'));
Reading an Execution Plan
The display_cursor Function
The display_cursor function returns execution plans stored in the library cache. Note that, in a Real Application Clusters environment, it’s not possible to get an execution plan stored in a remote instance. As for the display function, the return value is an instance of the dbms_xplan_type_table collection. The function has the followinginput parameters:
• sql_id specifies the parent cursor whose execution plan is returned. The default value is NULL. If the default value is used, the execution plan of the last SQL statement executed by thecurrent session is returned.
• cursor_child_no specifies the child number that, along with sql_id, identifies the child cursor whose execution plan is returned. The default value is 0. If NULL is specified, all childcursors of the parent cursor identified by the sql_id parameter are returned.
• format specifies which information is displayed. The same values are supported as in the parameter format of the display function. In addition, if execution statistics are available (in other words, if the statistics_level initialization parameter is set to all or the gather_plan_statistics hint is specified in the SQL statement), the modifiers described in
Table 10-4 are also supported. The default value is typical.
The display_awr Function
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', NULL, NULL, 'basic'));
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', 2966233522, NULL, 'basic'));
WITH
emps AS (SELECT /*+ qb_name(sq) */ deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT /*+ qb_name(main) full(@main dept) full(@sq emp) */ dept.dname, emps.cnt
FROM dept, emps
WHERE dept.deptno = emps.deptno
The previous example showed how to specify your own names. Now let’s see how you can use the names
generated by the query optimizer. First, you have to know what they are. For that, you can use the EXPLAIN PLAN statement and the dbms_xplan package, as shown in the following example. Note that the alias option is passed to the display function to make sure that the query block names and aliases are part of the output:
SQL> EXPLAIN PLAN FOR
2 WITH emps AS (SELECT deptno, count(*) AS cnt
3 FROM emp
4 GROUP BY deptno)
5 SELECT dept.dname, emps.cnt
6 FROM dept, emps
7 WHERE dept.deptno = emps.deptno;
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic +alias'));
Bind Variables Trap
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = 7788
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = :B1
Seeing Execution Statistics for Currently Running SQL
SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';
SID SQL_TEXT
---------- --------------------------------------------------------
100 select department_name, city, avg(salary)
from employees_big join departments using(department_id)
join locations using (location_id)
group by department_name, city
having avg(salary) > 2000
order by 2,1
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID=100
AND status = 'EXECUTING';
SID BUFFER_GETS DISK_READS CPU_SECONDS
---------- ----------- ---------- -----------
100 149372 4732 39.1
SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum <= 5;
If we wanted to see all executions for a given query (based on the SQL_ID column), we can get that information by querying on the three necessary columns to drill to a given execution of a SQL query:
SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <= 5;
Keep in mind that if a statement is running in parallel, one row will appear for each parallel thread for the query, including one for the query coordinator. However, they will share the same SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID values. In this case, you could perform an aggregation on a particular statistic, if desired. See the following example for a parallelized query, along with parallel slave information denoted by the PX_SERVER# column:
SELECT sql_id, sql_exec_start, sql_exec_id, px_server# px#, disk_reads,
cpu_time/1000000 cpu_secs, buffer_gets
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY px_server#;
Then, to perform a simple aggregation for a given query, in this case, our parallelized query, the aggregation is done on the three key columns that make up a single execution of a given SQL statement:
SELECT sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id;
If you wanted to perform an aggregation for one SQL statement, regardless of the number of times is has been executed, simply run the aggregate query only on the SQL_ID column, as shown here:
SELECT sql_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp '
GROUP BY sql_id;
Initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL, and
CONTROL_MANAGEMENT_PACK_ACCESS must be set to DIAGNOSTIC+TUNING for SQL monitoring to occur. Diagnostic and Tuning Pack Licenses are required to use V$SQL_MONITOR.
You want to see the progress a query is making from within the execution plan used.
To see information for the foregoing query while it is currently running, you can issue a query like the one
shown here (some rows have been removed for conciseness):
column operation format a25
column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;
to view information on previously run SQL statements to aid in identifying resource-intensive operations.
SELECT * FROM (
SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,
sum(disk_reads_total) disk_reads_total,
sum(executions_delta) execs_delta,
sum(executions_total) execs_total
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY 2 desc)
WHERE rownum <= 5;
Customizing Execution Plan Output
explain plan for
SELECT last_name, first_name
FROM employees JOIN departments USING(department_id)
WHERE employee_id = 101;
Explained.
SELECT * FROM table(dbms_xplan.display);
How It Works
The DBMS_XPLAN.DISPLAY function has a lot of built-in functionality to provide customized output based on your needs. The function provides four basic levels of output detail:
• BASIC
• TYPICAL (default)
• SERIAL
• ALL
The display Function
The display function returns execution plans stored in a plan table. The return value is an instance of the
dbms_xplan_type_table collection. The elements of the collection are instances of the dbms_xplan_type object type. The only attribute of this object type, named plan_table_output, is of type VARCHAR2(300). The function has the following input parameters:
• table_name specifies the name of the plan table. The default value is plan_table. If NULL is
specified, the default value is used.
• statement_id specifies the SQL statement name, optionally given as a parameter, when the
EXPLAIN PLAN statement is executed. The default value is NULL. If the default value is used, the
execution plan most recently inserted into the plan table is displayed (provided the
filter_preds parameter isn’t specified).
• format specifies which information is provided in the output. There are primitive values
(basic, typical, serial, all, and advanced) and, for finer control, additional modifiers
(adaptive, alias, bytes, cost, note, outline, parallel, partition, peeked_binds,
predicate, projection, remote, report, and rows) that can be added to them. If information
should be added, modifiers are optionally prefixed by the + character (for example, basic
+predicate). If information should be removed, modifiers have to be prefixed by
the - character (for example, typical -bytes). Multiple modifiers can be specified at the
same time (for example, typical +alias -bytes -cost).
If you simply want the default output format, there is no need to pass in any special format options:
SELECT * FROM table(dbms_xplan.display);
If you want to get all available output for a query, use the ALL level of detail format output option:
SELECT * FROM table(dbms_xplan.display(null,null,'ALL'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$38D4D5F3 / EMPLOYEES@SEL$1
2 - SEL$38D4D5F3 / EMPLOYEES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEES"."DEPARTMENT_ID" IS NOT NULL)
2 - access("EMPLOYEES"."EMPLOYEE_ID"=101)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20], "EMPLOYEES"."LAST_NAME"[VARCHAR2,25]
2 - "EMPLOYEES".ROWID[ROWID,10]
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
SELECT * FROM table(dbms_xplan.display(null,null,'TYPICAL -BYTES -ROWS'));
Reading an Execution Plan
The display_cursor Function
The display_cursor function returns execution plans stored in the library cache. Note that, in a Real Application Clusters environment, it’s not possible to get an execution plan stored in a remote instance. As for the display function, the return value is an instance of the dbms_xplan_type_table collection. The function has the followinginput parameters:
• sql_id specifies the parent cursor whose execution plan is returned. The default value is NULL. If the default value is used, the execution plan of the last SQL statement executed by thecurrent session is returned.
• cursor_child_no specifies the child number that, along with sql_id, identifies the child cursor whose execution plan is returned. The default value is 0. If NULL is specified, all childcursors of the parent cursor identified by the sql_id parameter are returned.
• format specifies which information is displayed. The same values are supported as in the parameter format of the display function. In addition, if execution statistics are available (in other words, if the statistics_level initialization parameter is set to all or the gather_plan_statistics hint is specified in the SQL statement), the modifiers described in
Table 10-4 are also supported. The default value is typical.
The display_awr Function
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', NULL, NULL, 'basic'));
SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', 2966233522, NULL, 'basic'));
WITH
emps AS (SELECT /*+ qb_name(sq) */ deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT /*+ qb_name(main) full(@main dept) full(@sq emp) */ dept.dname, emps.cnt
FROM dept, emps
WHERE dept.deptno = emps.deptno
The previous example showed how to specify your own names. Now let’s see how you can use the names
generated by the query optimizer. First, you have to know what they are. For that, you can use the EXPLAIN PLAN statement and the dbms_xplan package, as shown in the following example. Note that the alias option is passed to the display function to make sure that the query block names and aliases are part of the output:
SQL> EXPLAIN PLAN FOR
2 WITH emps AS (SELECT deptno, count(*) AS cnt
3 FROM emp
4 GROUP BY deptno)
5 SELECT dept.dname, emps.cnt
6 FROM dept, emps
7 WHERE dept.deptno = emps.deptno;
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic +alias'));
Bind Variables Trap
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = 7788
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno = :B1
Seeing Execution Statistics for Currently Running SQL
SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';
SID SQL_TEXT
---------- --------------------------------------------------------
100 select department_name, city, avg(salary)
from employees_big join departments using(department_id)
join locations using (location_id)
group by department_name, city
having avg(salary) > 2000
order by 2,1
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID=100
AND status = 'EXECUTING';
SID BUFFER_GETS DISK_READS CPU_SECONDS
---------- ----------- ---------- -----------
100 149372 4732 39.1
SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum <= 5;
If we wanted to see all executions for a given query (based on the SQL_ID column), we can get that information by querying on the three necessary columns to drill to a given execution of a SQL query:
SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <= 5;
Keep in mind that if a statement is running in parallel, one row will appear for each parallel thread for the query, including one for the query coordinator. However, they will share the same SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID values. In this case, you could perform an aggregation on a particular statistic, if desired. See the following example for a parallelized query, along with parallel slave information denoted by the PX_SERVER# column:
SELECT sql_id, sql_exec_start, sql_exec_id, px_server# px#, disk_reads,
cpu_time/1000000 cpu_secs, buffer_gets
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY px_server#;
Then, to perform a simple aggregation for a given query, in this case, our parallelized query, the aggregation is done on the three key columns that make up a single execution of a given SQL statement:
SELECT sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id;
If you wanted to perform an aggregation for one SQL statement, regardless of the number of times is has been executed, simply run the aggregate query only on the SQL_ID column, as shown here:
SELECT sql_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp '
GROUP BY sql_id;
Initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL, and
CONTROL_MANAGEMENT_PACK_ACCESS must be set to DIAGNOSTIC+TUNING for SQL monitoring to occur. Diagnostic and Tuning Pack Licenses are required to use V$SQL_MONITOR.
You want to see the progress a query is making from within the execution plan used.
To see information for the foregoing query while it is currently running, you can issue a query like the one
shown here (some rows have been removed for conciseness):
column operation format a25
column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;
to view information on previously run SQL statements to aid in identifying resource-intensive operations.
SELECT * FROM (
SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,
sum(disk_reads_total) disk_reads_total,
sum(executions_delta) execs_delta,
sum(executions_total) execs_total
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY 2 desc)
WHERE rownum <= 5;
Subscribe to:
Posts (Atom)