dbms_system.set_sql_trace_in_session

最近在学习盖总的Oracle性能优化与诊断案例精选这本书，将其中精华的部分做了实验进行实践

DBMS_SYSTEM是ORACLE提供的一个非常强大的跟踪工具之一。以下实验就是通过该工具进行跟踪、分析：

实验：
[oracle@mzf admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 3 09:55:16 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB_MZF1                       READ WRITE NO
SQL> 
SQL> alter session set container=PDB_MZF1;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB_MZF1                       READ WRITE NO
SQL> 
SQL> 
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/mzf/PDB_MZF1/system01.dbf
/u01/app/oracle/oradata/mzf/PDB_MZF1/sysaux01.dbf
 
SQL> create tablespace rosan datafile '/u01/app/oracle/oradata/mzf/PDB_MZF1/rosan01.dbf' size 500m autoextend on;

Tablespace created.

SQL> create user rosan identified by rosan default tablespace rosan;

User created.

SQL> grant dba to rosan;

Grant succeeded.

SQL> conn rosan/rosan@pdb_mzf1
Connected.
ROSAN@pdb_mzf1> 
ROSAN@pdb_mzf1> select * from tab;

no rows selected

ROSAN@pdb_mzf1> show user
USER is "ROSAN"
ROSAN@pdb_mzf1> 
ROSAN@pdb_mzf1> 
ROSAN@pdb_mzf1> create table test1(id number primary key, xid varchar2(14), xtype varchar2(1), xstatus number not null);

Table created.

ROSAN@pdb_mzf1> create index idx_t1_xid on test1(xid); 

Index created.

ROSAN@pdb_mzf1> insert into test1 values(1,'1','Y',0);

1 row created.

ROSAN@pdb_mzf1> insert into test1 values(2,'2','Y',1);

1 row created.

ROSAN@pdb_mzf1> insert into test1 values(3,'3','N',0);

1 row created.

ROSAN@pdb_mzf1> insert into test1 values(4,'4','N',1);

1 row created.

ROSAN@pdb_mzf1> insert into test1 values(5,'5','N',0);

1 row created.

ROSAN@pdb_mzf1> commit;

Commit complete.

ROSAN@pdb_mzf1> select * from test1;

        ID XID            X    XSTATUS
---------- -------------- - ----------
         1 1              Y          0
         2 2              Y          1
         3 3              N          0
         4 4              N          1
         5 5              N          0

SYS@pdb_mzf1> select sid, serial#, username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        14      40384 ROSAN
        33      50314 SYS

SYS@pdb_mzf1> exec dbms_system.set_sql_trace_in_session(14,40384,true);

PL/SQL procedure successfully completed.
 
ROSAN@pdb_mzf1> select xstatus, id, xid, xtype from test1
  2   where xid=2 and xstatus > 0;

   XSTATUS         ID XID            X
---------- ---------- -------------- -
         1          2 2              Y

SYS@pdb_mzf1> exec dbms_system.set_sql_trace_in_session(14,40384,false);

PL/SQL procedure successfully completed.
 
[oracle@mzf trace]$ ls -ltr
-rw-r----- 1 oracle oinstall    117 Jan  3 10:10 mzf_ora_19732.trm
-rw-r----- 1 oracle oinstall   5095 Jan  3 10:10 mzf_ora_19732.trc
-rw-r----- 1 oracle oinstall   1200 Jan  3 10:12 mzf_mmon_14622.trm
-rw-r----- 1 oracle oinstall  12217 Jan  3 10:12 mzf_mmon_14622.trc
......

[oracle@mzf ~]$ tkprof /u01/app/oracle/diag/rdbms/mzf/mzf/trace/mzf_ora_19732.trc /home/oracle/mzf_ora_19732.txt

TKPROF: Release 12.1.0.2.0 - Development on Tue Jan 3 10:14:37 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

[oracle@mzf ~]$ ls
Desktop  mzf_ora_19732.txt  soft
[oracle@mzf ~]$ 
[oracle@mzf ~]$ vi mzf_ora_19732.txt 
TKPROF: Release 12.1.0.2.0 - Development on Tue Jan 3 10:18:18 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Trace file: mzf_ora_19732.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
select xstatus, id, xid, xtype from test1
 where xid=2 and xstatus > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         10          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          8          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         18          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS FULL TEST1 (cr=8 pr=0 pw=0 time=34 us cost=3 size=37 card=1)
 
SYS@pdb_mzf1> exec dbms_system.set_sql_trace_in_session(14,40384,true);

PL/SQL procedure successfully completed.

ROSAN@pdb_mzf1> select xstatus, id, xid, xtype from test1
  2   where xid='2' and xstatus > 0;

   XSTATUS         ID XID            X
---------- ---------- -------------- -
         1          2 2              Y

SYS@pdb_mzf1> exec dbms_system.set_sql_trace_in_session(14,40384,false);

PL/SQL procedure successfully completed.

[oracle@mzf trace]$ls -ltr
-rw-r----- 1 oracle oinstall    137 Jan  3 10:16 mzf_ora_19732.trm
-rw-r----- 1 oracle oinstall   7516 Jan  3 10:16 mzf_ora_19732.trc
-rw-r----- 1 oracle oinstall   1212 Jan  3 10:17 mzf_mmon_14622.trm
-rw-r----- 1 oracle oinstall  12348 Jan  3 10:17 mzf_mmon_14622.trc
......

[oracle@mzf trace]$ tkprof mzf_ora_19732.trc /home/oracle/mzf_ora_19732.txt

TKPROF: Release 12.1.0.2.0 - Development on Tue Jan 3 10:18:18 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

[oracle@mzf ~]$ ls
Desktop  mzf_ora_19732.txt  soft
[oracle@mzf ~]$ 
[oracle@mzf ~]$ vi mzf_ora_19732.txt 

select xstatus, id, xid, xtype from test1
 where xid='2' and xstatus > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID BATCHED TEST1 (cr=3 pr=0 pw=0 time=17 us cost=1 size=37 card=1)
         1          1          1   INDEX RANGE SCAN IDX_T1_XID (cr=2 pr=0 pw=0 time=13 us cost=1 size=0 card=1)(object id 91791)

以上问题主要是字段类型不匹配进行了隐式转换导致了索引不被采用，产生了全表扫描的执行计划。

通常一个表达式不能包含不同的数据类型，例如一个表达式不能计算1X1之后再加上'AA'，数字和字符无法进行联合的计算。然后，oracle支持显示和隐式的数据类型转换。
可以将一种数据类型转换为另一种，从而使得某些表达式的运算可以正确执行，显示转换是指通过函数明确指定的数据类型转换，而隐式转换则指未明确指定，依赖Oracle
自动进行的数据类型转换。

基于以下几个原因，Oracle推荐使用显示类型转换而不是依赖隐式转换。
（1）、使用显示转换使得SQL含义更容易被理解
（2）、隐式数据类型转换会产生负面的性能影响，尤其是当列值被转换成其他敞亮的数据类型时。
（3）、隐式转换的行为依赖每次转换时的环境如数据库参数设置等，其行为可能多变而不确定，例如将datatime值隐式转换为varchar2，这个转换的格式和NLS_DATA_FORMAT
参数有关，可能产生不可预期的结果。
（4）、隐式转换的算法会因数据库版本而不同，而显示转换则可以预期。
（5）、如果隐式转换发生在索引列，则Oracle可能用不到索引而影响性能。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30192548/viewspace-2131777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30192548/viewspace-2131777/