Oracle 11g new SQL Trace 10046 Method

10046 is a tool that anyone familiar with Oracle and SQL tuning is familiar. The 10046 and 10053 diagnostic events help us easily understand the behavior of the Oracle CBO optimizer and SQL Execution. In the case of commercial non-open-source Oracle, many of our Internal knowledge comes from these two powerful tools.

After entering 11 GB, Oracle provides a 10046 alternative Trace method, and the original event method is still supported. This article focuses on the new SQL Trace method.

Read more: Oracle SQL Trace and 10046 event tracking

1. Environment and background

We still select Oracle 11gR2 as the lab object and create the lab data table T.

SQL> select * from v $ version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

 

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

 

 

Create a data table and clear the shared pool and buffer cache information.

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_id on t (object_id );

Index created

 

SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );

PL/SQL procedure successfully completed

 

SQL> alter system flush shared_pool;

System altered

 

SQL> alter system flush buffer_cache;

System altered

 

 

2. SQL _TRACE Method

 

First, check the default method of the new interface method. In earlier Oracle versions, we have the following methods.

 

Ü Alter session set events;

Ü Dbms _ tracking package;

Ü Oradebug setting tracking events;

Ü initialize the SQL _trace parameter;

 

It should be said that these methods are very thorough for Oracle tracking. Within the range of event 10046, all SQL statements, except the target SQL and recursive SQL, will be recorded to the tracking file. Therefore, we clearly issued an SQL statement, but the trace file contains a lot of data dictionary searches. As a result, we often need to use tkprof for raw file processing.

 

Let's take a look at the usage of the new interface method. First, locate the location of the Trace file.

 

 

SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE

-------------------------------------------------------------------------

/U01/diag/rdbms/wilson/trace/wilson_ora_rj3.trc

 

 

Enable tracking.

 

-- Mark

SQL> alter session set tracefile_identifier = '000000 ';

The session has been changed.

 

SQL> alter session set timed_statistics = true;

The session has been changed.

 

SQL> alter session set statistics_level = all;

The session has been changed.

 

SQL> alter session set max_dump_file_size = unlimited;

The session has been changed.

 

-- Trace Interface

SQL> alter session set events 'SQL _ trace level 12 ';

 

The session has been changed.

 

SQL & gt; select/* + demo */count (*) from t where object_id = 1000;

 

COUNT (*)

----------

1

 

SQL> alter session set events 'SQL _ trace off ';

The session has been changed.

10046 there are several tracking levels, of which level 12 includes all the information. Generally, as a beginner, we can obtain as much information as possible for convenience. In the SQL _trace trace interface, we can set the level value.

Execute the target SQL statement. Before reading the SQL Trace file, we first find the SQL _id of the cached SQL from the shared pool. For how to use this id, let's leave a header first.

SQL> select SQL _id, executions from v $ sqlarea where SQL _text like 'select/* + demo */count (*) % ';

SQL _ID EXECUTIONS

-----------------------

94wk1cqs4g2f5 1

We can find the Trace File in the directory.

[Root @ bspdev ~] # Su-oracle

[Oracle @ bspdev ~] $ Cd/u01/diag/rdbms/wilson/trace/

[Oracle @ bspdev trace] $ ls-l | grep 3663

-Rw-r ----- 1 oracle oinstall 16783 Aug 22 wilson_ora_3663_10046.trc

-Rw-r ----- 1 oracle oinstall 158 Aug 22 wilson_ora_3663_10046.trm

Open the Trace file and you can find that all the Recursive SQL statements related to the target SQL are in it.

* ** 05:54:47. 257

WAIT #1: nam = 'SQL * Net message from client' ela = 66502048 driver id = 1413697536 # bytes = 1 p3 = 0 obj #=-1 tim = 1377122087257296

CLOSE #1: c = 0, e = 10, dep = 0, type = 1, tim = 1377122087257461

==================================

Parsing in cursor #2 len = 202 dep = 1 uid = 0 Ct = 3 lid = 0 tim = 1377122087259383 hv = 3819099649 ad = '525e44f4 'sqlid = '3nkd%3ju5ph1'

Select obj #, type #, ctime, mtime, stime, status, dataobj #, flags, oid $, spare1, spare2 from obj $ where owner # =: 1 and name =: 2 and namespace =: 3 and remoteowner is null and linkname is null and subname is null

END OF STMT

(Omitted ......)

CLOSE #2: c = 0, e = 14391, dep = 1, type = 3, tim = 1377122087295194

==================================

Parsing in cursor #1 len = 52 dep = 0 uid = 0 Ct = 3 lid = 0 tim = 1377122087365631 hv = 2957478341 ad = '525a33fc 'sqlid = '94wk1cqs4g2f5'

Select/* + demo */count (*) from t where object_id = 1000

END OF STMT

(Omitted ......)

CLOSE #1: c = 0, e = 36, dep = 0, type = 0, tim = 1377122102531891

So far, the new SQL _TRACE interface is the same as the original 10046 method. The new functions of SQL _TRACE are embodied in the tracking of targeted SQL statements.

For more details, please continue to read the highlights of page 2nd:

1 2 Next Page