How Does oracle obtain the SQL Execution Plan of the client to optimize SQL? (3)

How Does oracle obtain the SQL Execution Plan of the client to optimize the SQL statement?

Today is September 25,. I continue to learn about SQL optimization and have written two notes on how to obtain the SQL Execution Plan. Although the notes are a bit rough, you can find something if you are patient.

Http://blog.csdn.net/xiaohai20102010/article/details/11694355

Http://blog.csdn.net/xiaohai20102010/article/details/11953127

Now I want to learn the third type,

That is, the use of procedure in dbms_system, a system package just learned:

See: http://blog.csdn.net/xiaohai20102010/article/details/12027793

1) to obtain the SQL statement executed by a client, perform the following steps:

1) obtain client information, such as sid and SERIAL.

2) set the session statistical time and other parameters to calculate the SQL Execution time and limit the trace size.

3) enable session SQL trace.

4) collect SQL trace information

5) Disable session SQL tracking.

6) format the SQL Execution Plan and analyze the SQL Performance for optimization.

Introduction:

1. Obtain the client session information:

Select s. sid, s. serial #, s. username, s. logon_time, s. osuser, s. machine, p. username, p. program, p. pid
From v $ session s, v $ process p
Where s. paddr = p. addr;

Of course, if you know a user, you can add this username.

Eg:

Select s. sid, s. serial #, s. username, s. logon_time, s. osuser, (select sys_context ('userenv', 'IP _ address') from dual) as ipad, s. machine, p. username, p. program, p. spid
2 from v $ session s, v $ process p
3 where s. paddr = p. addr and s. username = 'Scott ';
 
Sid serial # USERNAME LOGON_TIME OSUSER IPAD MACHINE USERNAME PROGRAM SPID
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 207 SCOTT 2013/9/25 2 Administrator 192.168.56.1 WORKGROUP \ RHYS-PC oracle @ oracle-one 3191

We can see that the user is scott, the OS user is oracle, the sid is 41, the seral # is 207, the client host user is administrator, the IP address is 192.168.56.1, and the process number is 3191, this is key information.

2. Set parameters:

If you need to set the timed_statistics parameter for statistics on the time when the SQL statement is executed on the session, the parameter user_dump_dest is used before the log file output is 11 GB, but this parameter is invalid when it reaches 11 GB, in addition, because a session is tracked, a very large trace may be generated. We can set the trace file size as needed: max_dump_file_size parameter.

Now you can use the steps in the dbms_system package you just learned to set it up. See http://blog.csdn.net/xiaohai20102010/article/details/12027793

Eg:

SQL> begin
2 dbms_system.set_int_param_in_session (
3 sid => 41,
4 serial # => 207,
5 parnam => 'max _ dump_file_size ',
6 intval => 20971520 );
7 end;
8/

PL/SQL procedure successfully completed.

SQL>

SQL> get p3. SQL
1 begin
2 dbms_system.set_bool_param_in_session (
3 sid => 41,
4 serial # => 207,
5 parnam => 'timed _ statistics ',
6 bval => true );
7 * end;
SQL> r
1 begin
2 dbms_system.set_bool_param_in_session (
3 sid => 41,
4 serial # => 207,
5 parnam => 'timed _ statistics ',
6 bval => true );
7 * end;

PL/SQL procedure successfully completed.

SQL>

Parameters have been set this time.

3. Enable the session trace function

Eg:

 

SQL> execute dbms_system.set_ SQL _trace_in_session (41,207, true );

PL/SQL procedure successfully completed.

SQL>

4. Wait for a period of time to collect the statement:

SQL> conn cott/root @ rhys
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott @ rhys
 
SQL> select * from v $ mystat where rownum <3;
 
Sid statistic # VALUE
------------------------------
41 0 0
41 1 18
 
SQL> select * from rhys. amy_dept;
 
Select * from rhys. amy_dept
 
ORA-00942 :???????
 
SQL> select * from dept;
 
DEPTNO DNAME LOC
---------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
 
SQL> select * from emp where rownum <5;
 
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------
7369 smith clerk 7902 800.00 20
7499 allen salesman 7698 1600.00 300.00 30
7521 ward salesman 7698 1250.00 500.00 30
7566 jones manager 7839 2975.00 20
 
SQL>

5. Disable session tracking:

Eg:

SQL> exec dbms_system.set_ SQL _trace_in_session (41,207, false );

PL/SQL procedure successfully completed.

SQL>

6. Collect the trace file of the session Statement:

[Oracle @ oracle-one trace] $ ls-ltr * 3191.trc
-Rw-r -----. 1 oracle oinstall 42278 Sep 25 RHYS_ora_3191.trc
[Oracle @ oracle-one trace] $

Okay. Now, you can find all the SQL statements of the session.

View the trace file:

For ease of viewing, we use the tkprof tool for viewing, which is easier to understand. For details, see oracle tkprof usage in my blog.