New Year's greetings + scattered distribution of OracleSQL_TRACE and 10046 event-optimized SQL instances

1. database version LEO1 @ LEO1select * fromv $ version; BANNER -------------------------------------------------------------------------------- OracleDatabase11gEnter

1. database version LEO1 @ LEO1select * fromv $ version; BANNER -------------------------------------------------------------------------------- OracleDatabase11gEnter

1. 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

2. Use SQL _TRACE and 10046 to trace other sessions and provide trace results.

SQL _TRACE: Oracle is used to track SQL Execution and analyze SQL Performance and resource consumption.

1. view how SQL statements process data

2. view the wait events generated during SQL Execution.

3. view the resource consumption during SQL Execution

4. view the actual SQL Execution Plan

5. View SQL recursive statements

6. If you want to explore how to execute SQL statements, you can take a look at it in detail.

10046: used to analyze the performance consumption during SQL Execution. You can view the bound variable information and wait event information. It has more parameters than SQL _TRACE input and output.

Use Cases of the above tools: 1. Optimize SQL statements

2. view the SQL statement execution plan

3. Tracking the SQL statement execution process

4. Redirect the SQL information in the session to a file.

Set auto trace: 1. output the execution plan estimated by the SQL statement (guessed)

2. the SQL statement is not actually executed. Pay attention to the execution plan of this SQL statement.

3. It is only used to estimate the execution plan.

Lab

Use SQL _TRACE to trace other sessions

If you want to track the current session, you only need to alter session set SQL _trace = true;. If you want to track other sessions, you need to set other parameters.

Now let's take a look at the SQL statement used to track 12 sessions from 144 sessions.

144 sessions we use leo1 user operations

12 sessions we use leo2 user operations

144 sessions

LEO1 @ LEO1> selectdistinct sid from v $ mystat; you can query the current session ID.

SID

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

144

We use the session ID and serial number to uniquely locate a session. Now we display the information of both sessions.

LEO1 @ LEO1> select sid, serial # from v $ session where sid in (144,12 );

Sid serial #

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

12 4472

144 979

Now I have a question: locating a session is usually done by looking at sid. Why is there a serial? What is this serial, I consulted Alantany and checked the official documentation.

Sid number: Sessionidentifier indicates the session ID.

SERIAL # NUMBER: identifies a unique session operation object. Make sure that the commands sent by this session can be correctly applied to the corresponding session object.

The same SID is used for the end of a session and the start of another session.

Example

Login sid = 12 for the first leonarding, operate the leo1 table, and exit

Sid serial #

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

12 4472

The second Alan login sid = 12, operate the leo2 table again, and exit

Sid serial #

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

12 4777

If we only check the SID, we cannot tell who logged on to the leo1 table and the leo2 table, and serial can tell that commands of different sessions are correctly applied to the corresponding objects, distinguish between two different logon sessions.

LEO1 @ LEO1> droptable leo1; clear the environment

Table dropped.

LEO1 @ LEO1> create table leo1 as select * from dba_objects; Use leo1 to create a leo1 table

Table created.

LEO1 @ LEO1> select count (*) from leo1; check the number of records

COUNT (*)

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

72007

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

PL/SQL proceduresuccessfully completed.

Just make a table analysis and histogram.

LEO1 @ LEO1> conn/as sysdba switch to administrator

Connected.

SYS @ LEO1> grantexecute on dbms_system to leo1; grant the user permission to run the "system package" to leo1. Otherwise, an error is reported.

ERROR atline 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'dbms _ SYSTEM. SET_ SQL _TRACE_IN_SESSION 'must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SYS @ LEO1> connleo1/leo1. We are switching back.

LEO1 @ LEO1> execute sys. dbms_system.set_ SQL _trace_in_session (12, 4472, true );

PL/SQL proceduresuccessfully completed.

Start the SQL statement of the trail session ID = 12, SERIAL = 4472

Declaration: This stored procedure is exclusive to SYS users and has a website space. Therefore, a schema must be included in the reference. If this parameter is not included, an error is returned as follows:

ERROR atline 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'sys. DBMS_SYSTEM 'must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

12 sessions

LEO2 @ LEO1> select/* + trace_by_leo1_session */count (*) from leo1.leo1; leo2 users query the leo1 table

COUNT (*)

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