Monitor Temporary Segment Usage

Page moved to ,

http://sysdbaonline.com/?p=322

TKPROF

What is the use of TKPROF utility
————————————–

The TKPROF is a utility used to convert the Oracle trace file generated during SQL Trace in to a readable format.

TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend. Each SQL statement is displayed in the report, along with counts of how many times it was parsed, executed, and fetched. CPU time, elapsed time, logical reads, physical reads, and rows processed are also reported, along with information about recursion level and misses in the library cache. TKPROF can also optionally include the execution plan for each SQL statement in the report, along with counts of how many rows were processed at each step of the execution plan.

The SQL statements can be listed in a TKPROF report in the order of how much resource they used, if desired. Also, recursive SQL statements issued by the SYS user to manage the data dictionary can be included or excluded, and TKPROF can write SQL statements from the traced session into a spool file.

what Levels the SQL Trace file for TKPROF would have been generated
———————————————————————

Either it would have been done at Instance level by setting SQL_TRACE=TRUE or setting at session level the same.At session level it can be set back to false and at instance level bouncing db back twice At initial and at final after finishing trace.

Where is SQL Trace file located used in the TKPROF
———————————————————–

This is Located in the UDUMP directory in oracle database file structure or the path specified in the USER_DUMP_DEST

What should be take care before Using TKPROF utility
————————————————————–

Without fail try to close the SQL_TRACE=False before converting the trace file to redable format.Other wise it may not reflect a proper result.

TIMED_STATISTICS=True
——————————-

Set this parameter in init.ra or for a particular session level if you require the CPU time calculations for each phase.

How to find the respective trace file from UDUMP directory
——————————————————————-

The latest trace file could be found by the latest time of creation of the file.Dont output the old files.Better clear the UDUMP directory before using the SQL Trace.

How to start of with TKPROF utility
—————————————

Simply type from command prompt TKPROF and the options as mentioned below.

A general Syntax of TKPROF
———————————

TKPROF trace_file output_file
[SORT = parameter]
[PRINT = number]
[EXPLAIN = username/password]
[INSERT = stat_file_name]
[SYS = yes/no]
[AGGREGATE=[Y|N]]
[RECORD = sql_file_name]
[TABLE = schema.table_name]

A sample syntax for formating a trace file using TKPROF
—————————————————————–

>tkprof explain=user_name/pwd

Under Standing various Options used in TKPROF Utility
————————————————————–

Tracefile==>The name of the Trace output file

Outputfile==>The name of the formatted file

Sort=option==>The order in which to sort the statements

Print=n==>Print the first n statements

Explain=Username/pwd==>Run the Explain Plan in the specified user name

Insert=filename==>Generate INSERT statements

Sys=no==>Ignore recursive SQL statements run as sys user.

Aggregate=[Y|N]==>If specified as NO then TKPROF does not aggregate multiple users of the same SQL text

Record=filename==>Record statements found int he trace file

Table=schema.tablename==>Putting execution plan in to specified table(rather than the default PLAN_TABLE).

Under Standing trace file Outputs returned by TKPROF Utility
———————————————————————

After all the sql statement you may find the following values

Call==>PARSE,EXCUTE,FETCH

COUNT==>How many times the statement was parsed or executed and the no of fetch calls issued for the part stat.

CPU==>Process time for each phase in seconds. if the stat was found in the shared pool it is 0 for the parse phase.

ELAPSED==>This is not much useful since other process also affect ellapsed time.

QUERY==>Logical Buffers retrieved for Consistent read normally for a select stat.

CURRENT==>Logical buffers retry in current mode.

ROWS==>Rows processed by the outer stat.Select stat this is shown for the fetch phase and for DML stat it is shown in the execute phase.

DISK==>Phy data blocks read from the DB files. This stst may be very low if the data was buffered.

Libr cache misses==>this states that no of times the stat was not found in the Lib Cache in the parse and execute phase.if there was no miss then this statistcis wont appear.

User==>Id of the last user to parse the statement.

Execution Plan==> when you specify the EXPLAIN parameter with TKPROF it then works out the access path for each sql statement traced and included in the output.

Optimizer Hint==>used to indicate the Optim hint which was used during the execution of the stat.If there was no hint it will show optimizer mode was used.

Recursive SQL==>Sometimes in order to execute a SQL statement issued by a user, Oracle must issue additional
statements. Such statements are called recursive calls or recursive SQL statements.

For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.

If recursive calls occur while the SQL trace facility is enabled,
TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of recursive calls
in the output file by setting the SYS statement-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for
the SQL statement that caused the recursive call. So when you are calculating the total resources required to process a SQL statement, you should consider the statistics for that statement as well as those for recursive calls caused by that statement.

Trace Your Session

Page moved to ,

http://sysdbaonline.com/?p=296

How to Configure Multiple Listener Running on Same Machine

Page moved to ,

http://sysdbaonline.com/?p=294

Manual Standby Configuration – Oracle Standard Edition ( 10.2.0.4 )

Page moved to ,

http://sysdbaonline.com/?p=331

Find what is consuming resource

I came across this scenario today wherein i could see that oracle process is using 50 % of CPU and i was asked to dig out what is it doing.

I logged in to database checked the long operations which were running using following query.

SQL> select sid,message from v$session_longops;

       SID MESSAGE
———- ————————————————–
    12 Hash Join:  : 644 out of 644 Blocks done
    12 Hash Join:  : 931 out of 931 Blocks done
    12 Hash Join:  : 1057 out of 1057 Blocks done
    12 Hash Join:  : 1127 out of 1127 Blocks done
    12 Hash Join:  : 1232 out of 1232 Blocks done
    12 Hash Join:  : 1267 out of 1267 Blocks done
    12 Hash Join:  : 1246 out of 1246 Blocks done
    12 Table Scan:    RESOURCE_OWNER.DW_ASSIGNMENTS: 57658
       out of 57658 Blocks done

    12 Table Scan:    RESOURCE_OWNER.DW_ASSIGNMENTS: 57663

       SID MESSAGE
———- ————————————————–
       out of 57663 Blocks done

9 rows selected.

I found that session with id 12 is doing some long running query.

From the prstat command i found that process callled oracle/15 is consuming 49 % CPU.

bash-2.05$ prstat
  PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP      
27541 oracle    411M  370M cpu0     0    0   1:26:18  49% oracle/15

From ps command i found that following process is the culprit for bad performance.

bash-2.05$ ps -ef | grep  27541
  oracle 27541     1 49 14:53:58 ?       86:48 ora_j000_PROD

I again logged into database and ran following query to find which job it is running.

SQL> SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, ‘mm/dd/yyyy hh:mi pm’) startdatetime, b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job AND a.sid = c.sid order by a.this_date
  2    3    4 
SQL> /

       SID    SERIAL#         JOB   FAILURES STARTDATETIME
———- ———- ———- ———- ——————-
WHAT
——————————————————————————–
    12        6         944      0 10/06/2008 02:53 pm
BEGIN
   pkg_reports.request_management (SYSDATE – 500, SYSDATE + 250);
END;

So job 944 was the culprit.