Optimized SQL instance for Oracle Sql_trace and 10046 events

A database version

Leo1@leo1>select * from V$version;

BANNER

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

Oracle database11g Enterprise Edition release 11.2.0.1.0-64bit Production

Pl/sql release11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl version11.2.0.1.0-production

The second demo uses Sql_trace and 10046 events to track other sessions and gives trace results

Sql_trace:oracle This function is mainly to track the execution of SQL, analysis of SQL performance, resource consumption.

1. See how SQL operates data processing

2. View the waiting events that SQL generated during execution

3. View SQL Execution Process resource consumption

4. View the actual execution plan for SQL

5. View SQL recursive statements

6. If you want to explore how SQL executes, you can look at it in detail

10046: Used to analyze the performance consumption during SQL execution, you can view the binding variable information, you can view the wait event information, it is more than sql_trace input output parameters.

Use occasion of the above tools: 1. Optimizing SQL statements

2. View SQL statement Execution plan

3. Trace SQL statement Execution process

4. Redirect SQL information in a session to a file

SET AUTO trace:1. Output SQL statement estimate execution Plan (guessed)

The 2.SQL statement is not really executing, just focus on this SQL execution plan right

3. Only used to estimate the implementation plan

Experiment

Using Sql_trace to track other sessions

If the current session is tracked with only alter session set SQL_TRACE=TRUE, additional parameters need to be set if the other sessions are tracked.

Let's do the SQL that tracks 12 sessions from 144 sessions

144 Sessions We use LEO1 user actions

12 Sessions We use LEO2 user actions

144 Sessions

leo1@leo1> selectdistinct sid from V$mystat; You can query the current session ID

Sid

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

144

We use the session ID and the serial number to uniquely locate a session, and now we have 2 session information displayed.

Leo1@leo1>select sid,serial# from V$session where Sid in (144,12);

SID serial#

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

12 4472

144 979

At this time I have a question, locate a session generally see SID on it, then why followed by a serial, this serial is what to use it, consulted a bit alantany check the official documents

SID Number:sessionidentifier is the session ID

serial# number: Is used to identify a single session action object, ensuring that the commands emitted by this session are correctly applied to the corresponding session object.

Occasion the end of a session and the beginning of another session are all using the same SID, distinguishing between 2 different sessions

Example

The first time leonarding landing sid=12, operation of the Leo1 table, exit

SID serial#

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

12 4472

The second time Alan landed Sid=12, and then operated the Leo2 table, withdrew

SID serial#

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

12 4777

If we're just looking at SID, we can't tell who's logged in. The session operates the LEO1 table and the Leo2 table, and serial can distinguish between the commands for different sessions correctly applied to the corresponding object, distinguishing between 2 different people logged in to the session.

Leo1@leo1> droptable Leo1; Clean up the environment

Table dropped.

Leo1@leo1>create table Leo1 as SELECT * from Dba_objects; Creating Leo1 tables with LEO1 users

Table created.

Leo1@leo1>select Count (*) from LEO1; See how many records there are.

COUNT (*)

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

72007

Leo1@leo1>execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO1 ',method_opt=> ' for allcolumns size 254 ');

Pl/sql proceduresuccessfully completed.

Do a table analysis and histogram

leo1@leo1> conn/as SYSDBA Switch to Administrator