DBMS_XPLAN - Comparando planos no Oracle Database 19c e 21c com COMPARE_PLANS, COMPARE_CURSORS e COMPARE_EXPLAIN
Olá pessoal, venho mostrar uma funcionalidade bacana que está disponível a partir do Oracle 19c, onde nos vemos em um cenário de análise de performance e um SQL_ID com vários planos e precisamos identificar desvios entre eles para decidir qual usar. Dessa forma comparar planos se torna simples e efetivo com dbms_xplan.compare.
Vamos lá...
Para essa demonstração vou usar um ambientes Oracle Container na versão 21.03.0.0.0, Pluggable database: ORCLPDB.
INSTANCE_NAME HOST_NAME VERSION_FULL EDITION INSTANCE_ROLE STATUS
---------------- ---------------------------------------------------------------- ----------------- ------- ------------------ ------------
orcl ol8-vm1.localdomain 21.3.0.0.0 EE PRIMARY_INSTANCE OPEN
SQL> show con_name; CON_NAME ------------------------------ CDB$ROOT SQL> SQL> set lines 999 pages 999 SQL> select name, pdb from v$services; NAME PDB ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- orclXDB CDB$ROOT orcl CDB$ROOT SYS$BACKGROUND CDB$ROOT SYS$USERS CDB$ROOT orclpdb ORCLPDB SQL> alter session set container = ORCLPDB; Session altered. SQL>
Para essa demonstração vou criar um novo user "testeuser1" onde vou criar a tabela EMP:
drop user testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant connect, resource to testuser1; grant select_catalog_role to testuser1;
Vamos criar a tabela agora, abaixo desse usuário:
conn testuser1/testuser1@//localhost:1521/orclpdb
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) );
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;Agora vamos executar uma consulta simples nessa tabela fazendo o uso do index criado no campo EMPNO:
select * from emp where empno = 7369;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 3vksfnydr3639, child number 0
-------------------------------------
select * from emp where empno = 7369
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
2 - access("EMPNO"=7369)
19 rows selected.
SQL>Agora vamos executar a mesma consulta mas forçando o FULL SCAN que em alguns casos e mais custoso para o Banco:
select /*+ full(emp) */ * from emp where empno = 7369;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 8s0n2z0trbub4, child number 0
-------------------------------------
select /*+ full(emp) */ * from emp where empno = 7369
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
18 rows selected.
SQL>COMPARE_PLANS
Usamos o objeto CURSOR_CACHE_OBJECT para retornar tipos GENERIC_PLAN_OBJECT para uso como parâmetros. O método construtor desse objeto aceita atributos SQL_ID e CHILD_NUMBER.
dbms_xplan.compare_plans(
reference_plan in generic_plan_object,
compare_plan_list in plan_object_list,
type in VARCHAR2 := 'TEXT',
level in VARCHAR2 := 'TYPICAL',
section in VARCHAR2 := 'ALL')
return clob;Então, usando os SQL_IDs das duas consultas que executamos antes e assumindo CHILD_NUMBER "0" para cada uma, podemos comparar os planos da seguinte forma. Incluímos a versão simples e a detalhada da sintaxe, que resultam no mesmo relatório:
var l_report clob;
-- Simple
begin
:l_report := dbms_xplan.compare_plans(
cursor_cache_object('3vksfnydr3639', 0),
plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0))
);
end;
/
-- Verbose
begin
:l_report := dbms_xplan.compare_plans(
reference_plan => cursor_cache_object('3vksfnydr3639', 0),
compare_plan_list => plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL'
);
end;
/
print l_reportCOMPARE_CURSOR
A função COMPARE_CURSOR foi introduzida no Oracle 21c, mas atualmente não possui documentação.
dbms_xplan.compare_cursor(
sql_id1 in varchar2 default null,
sql_id2 in varchar2 default null,
childnum1 in integer default null,
childnum2 in integer default null,
type in varchar2 := 'TEXT',
level in varchar2 := 'TYPICAL',
section in varchar2 := 'ALL')
return clob;Nos exemplos a seguir, usamos COMPARE_CURSOR para comparar os dois planos usando os SQL_IDs. Incluímos a versão simples e detalhada da sintaxe, que resultam no mesmo relatório:
var l_report clob;
-- Simple
begin
:l_report := dbms_xplan.compare_cursor('3vksfnydr3639','8s0n2z0trbub4');
end;
/
-- Verbose
begin
:l_report := dbms_xplan.compare_cursor(sql_id1 => '3vksfnydr3639',
sql_id2 => '8s0n2z0trbub4',
childnum1 => 0,
childnum2 => 0,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
end;
/
print l_reportCOMPARE_EXPLAIN
A função COMPARE_EXPLAIN foi introduzida no Oracle 21c, mas atualmente não está documentada.
dbms_xplan.compare_explain(
statement_id1 in varchar2 default null,
statement_id2 in varchar2 default null,
plan_id1 in number default null,
plan_id2 in number default null,
type in varchar2 := 'TEXT',
level in varchar2 := 'TYPICAL',
section in varchar2 := 'ALL')
return clob;Para usar esta função, devemos primeiro executar EXPLAIN PLAN para cada instrução:
explain plan set statement_id = 'emp1' for select * from emp where empno = 7369; explain plan set statement_id = 'emp2' for select /*+ full(emp) */ * from emp where empno = 7369;
No exemplo a seguir vamos usar COMPARE_EXPLAIN para comparar os dois planos usando os STATEMENT_IDs. Incluímos a versão simples e a detalhada da sintaxe, que resultam no mesmo relatório:
var l_report clob;
-- Simple
begin
:l_report := dbms_xplan.compare_explain('emp1','emp2');
end;
/
-- Verbose
begin
:l_report := dbms_xplan.compare_explain(statement_id1 => 'emp1',
statement_id2 => 'emp2',
plan_id1 => null,
plan_id2 => null,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
end;
/
print l_reportRELATÓRIO GERADO
Os parâmetros TYPE, LEVEL e SECTION permitem que a saída seja personalizada de acordo com suas necessidades. Os valores permitidos são mostrados abaixo.
- TYPE: TEXT, HTML, XML
- LEVEL: BASIC, TYPICAL, ALL
- SECTION: SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALL
L_REPORT
--------------------------------------------------------------------------------
COMPARE PLANS REPORT
--------------------------------------------------------------------------------
Current user : TESTUSER1
Total number of plans : 2
Number of findings : 1
--------------------------------------------------------------------------------
COMPARISON DETAILS
--------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : TESTUSER1
Plan Table Name : PLAN_TABLE
Statement ID : emp1
Plan ID : 1
Plan Database Version : 21.0.0.0
Parsing Schema : "TESTUSER1"
SQL Text : select * from emp where empno = 7369
Plan
-----------------------------
Plan Hash Value : 2949544139
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 | 00:00:01 |
| * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("EMPNO"=7369)
--------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : TESTUSER1
Plan Table Name : PLAN_TABLE
Statement ID : emp2
Plan ID : 2
Plan Database Version : 21.0.0.0
Parsing Schema : "TESTUSER1"
SQL Text : select /*+ full(emp) */ * from emp where empno = 7369
Plan
-----------------------------
Plan Hash Value : 3956160932
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 | 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("EMPNO"=7369)
Comparison Results (1):
-----------------------------
1. Query block SEL$1, Alias "EMP"@"SEL$1": Access path is different -
reference plan: INDEX_RS_ASC (lines: 1, 2), current plan: FULL (line: 1).
--------------------------------------------------------------------------------
SQL>Ao final, caso seja necessário, drope o user e a tabela caso esteja testando a funcionalidade.
drop user testuser1 cascade;
REFERÊNCIAS
Espero que esse material ajude com mais uma ferramenta útil no dia a dia. Caso tenha alguma dúvida ou sugestão de postagem, deixo aqui meu E-mail - pedrohvido@gmail.com para contato.
Nos vemos no próximo post!
Comentários
Postar um comentário