The "test" tracks the sessions of the Scott user through the SYS user, analyzes and gives recommendations for optimization by analyzing the high performance-intensive SQL in this session.

① Connect to under Scott, query Scott for the corresponding sid,serial#

SQL>Selectfromwhere username='SCOTT' ;       SID    serial# USERNAME--------------------------------------------------       133          SCOTT

② Open the Tracking for Scott users:

SQL>exec dbms_system.set_sql_trace_in_session (133,,true);P l / procedure successfully completed.

③ in take action under Scott

Sql> Select *  fromtab; Tname Tabtype Clusterid------------------------------ ------- ----------BONUSTABLEDEPTTABLEEMPTABLESalgradeTABLET1TABLET2TABLET3TABLETESTTABLETest_parTABLE9rows selected. SQL> Create TableDt as Select *  fromDept;TableCreated.

④ Close the Tracking for Scott users:

SQL>exec dbms_system.set_sql_trace_in_session (133,o, false);P L  /procedure successfully completed.

⑤ Query trace File Location:

Sql> SelectValue fromV$diag_infowhereName like 'Default Trace File'; VALUE--------------------------------------------------------------------------------/U01/App/Oracle/Diag/Rdbms/Ora11gr2/Ora11gr2/Trace/Ora11gr2_ora_4264.trc

⑥ View the generated trace file directory:

[[email protected] ~]$ cd/U01/App/Oracle/Diag/Rdbms/Ora11gr2/Ora11gr2/Trace[[email protected] Trace]$ ls-LRT-rw-R-----1 oracle oinstall 3290 Oct 15:53 ora11gr2_ora_4318.trm-rw-R-----1 oracle oinstall 833329 Oct 15:53 ora11gr2_ora_4318.trc

⑦ to view the generated trace file with Tkporf:

[[email protected] Trace]$ tkprof ora11gr2_ora_4318.trc trace1tkprof:release11.2.0.4.0 -Development onMon Oct -  -: .: $  .Copyright (c)1982, ., Oracleand/orits affiliates. Allrights reserved.[[email protected] Trace]$ ls trace1.prftrace1.prf[[email protected] Trace]$ cat trace1.prf too big, I don't stick. 

The "test" tracks the sessions of the Scott user through the SYS user, analyzes and gives recommendations for optimization by analyzing the high performance-intensive SQL in this session.