Oracel SQL _TRACE excerpt

Oracel SQL _TRACE excerpt SQL _TRACE is a powerful auxiliary diagnostic tool provided by Oracle for SQL tracking. SQL _TRACE is a very common method in daily database problem diagnosis and solution. Www.2cto.com 1. enable trace SQL> alter system set SQL _TRACE = TRUE at the database level; SQL> alter system set SQL _TRACE = FALSE; 2. SET trace SQL> alter session set SQL _TRACE = TRUE in the current SESSION; SQL> alter session set SQL _TRACE = FALSE; 3. trace EXECUTE DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION (SID, SERIAL #, FALSE); EXECUTE resume (SID, SERIAL #, FALSE) on other sessions; how to obtain the SID of the current Session, SERIAL # SQL> SELECT S ID, SERIAL # from v $ session where sid in (select distinct sid from v $ MYSTAT); sid serial # ---------- 144 11 how to obtain the generated TRACE file www.2cto.com select d. value | '\' | lower (rtrim (I. instance_name, chr (0) | '_ ora _' | p. spid | '. trc 'trace_file_name from (select p. spid from v $ mystat m, v $ session s, v $ process p where m. statistic # = 1 and s. sid = m. sid and p. addr = s. paddr) p, v $ instance I, (sele Ct value from v $ parameter where name = 'user _ dump_dest ') d. Convert the generated TRACE file to the readable format Syntax: tkprof tracefile outputfile [optional | parameters]. The parameters and options are as follows: explain = scott/scott: Use scott to connect and analyze the execution plan. Sys = [yes/no] determine whether the system lists SQL statements generated or re-tuned by sys users. sort = sort_option sort SQL trace output files in descending order according to the specified method. sort_option option prscnt sort by resolution times prscpu sort by resolution cpu time consumed by resolution prsela sort by resolution time prsdsk sort by the number of physical read operations during resolution prsqry read data in consistent mode during resolution sort the number of data blocks prscu sort by the number of times the data blocks are read during parsing execnt sort by the number of executions execpu sort by the cpu time consumed during execution exeela sort by the time consumed by the execution exedsk sort by number of physical reads execute exeqry sort by number of reads from data blocks in consistent mode execu sort by number of reads from data blocks in current mode execute exerow sort record count exemis sort by Library Buffer error sort fchcnt sort by returned Data Count fchcpu sort by returned data c Time-based sorting by pu fchela sort by time the data is returned fchdsk sort by the number of physical read operations when the data is returned fchqry sort by the number of times the data block is read in consistent mode when the data is returned fchcu sort by the number of times data blocks are read in the current mode when data is returned. fchrow sorts by the number of data processed in the returned data. The meaning of each column in The tkprof output file: (understanding the meaning below is very helpful for us to quickly locate the problem. help) parse: statements actually executed by cuteoracle, such as insert, update, and delete, modify data. For select operations, this is only to determine the number of selected rows. Fetch returns the number of rows in the query. Only select statements are collected. The Count statement is counted by the number of times of parse, execute, and fetch. The total Cpu time used by all the parse, execute, and fetch statements of www.2cto.com is measured in seconds. If TIMED_STATISTICS is disabled, the value is 0. The total time consumed by all the parse, execute, and fetch statements of Elapsed, in seconds. If TIMED_STATISTICS is disabled, the value is 0. Disk: the number of data blocks read by all the parse, execute, and fetch statements from the data files on the Disk. In consistent read mode, all the parse, execute, the number of buffer obtained by fetch (this part is read from the memory, that is, the logical read, equivalent to the consistent gets in the execution plan). In Current mode, all the parse of this statement, execute and fetch obtain the number of buffers. Generally, delect, insert, and update operations in current mode obtain the buffer. The number of Rows returned by the Rows statement, excluding the number of records returned by the subquery. For select statements, return in the fetch step. For insert, delete, update operations, the returned records are in the execute step.