Directory where trace files reside is defined by variable USER_DUMP_DEST, if it is not set, execute sql command below from sqlplus (apps account):
select value from v$parameter where name='user_dump_dest';
or execute command below under svrmgrl :
show parameters user_dump_dest
Trace files are named ora_<SPID>.trc. To retrieve your trace file you can try unix command :
============================================================================
A more precise method consists to find PID (Oracle Process Id) and determine corresponding SPID (Session Process Id):
- under System Administrator responsibility,
menu Security => User => Monitor
column 'Oracle' (bloc 'SIGNON_AUDIT_VIEW', field 'PID') gives you the PID
corresponding to your user name.
- under sqlplus, apps or system account, run :
select p.pid, p.spid
from v$process p , v$session s
where p.addr = s.paddr and p.pid = &your_pid;
=> enter PID found before it will give you SPID
- now you can retrieve precisely ora_<SPID>.trc file.
===============================================================================
Now that you have a trace file, your analyst will need to see the tkprof?d output in order to analyze the problem. A tkprof?d file has important information in readable format for the technical analyst to use for trouble-shooting purposes. When troubleshooting a performance problem, it is best to issue the tkprof command at the UNIX prompt using the following syntax: tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' The above mentioned command sorts the SQL by placing the worst performing SQL statement at the top of the output file. (Please note: You can only issue the tkprof command within a directory in which you have write permission. If you do not have write permission in the directory where the raw trace is stored, copy the trace file to your $HOME directory, and then issue the following tkprof command from there. To copy the .trc file to your $HOME directory, use the following syntax): cp <filename.trc> $HOME Example of tkprof'd output: UPDATE mrp_relief_interface SET request_id = :sql_req_id, process_status = 3 WHERE inventory_item_id IN (SELECT inventory_item_id FROM mrp_relief_interface rel2 WHERE rel2.request_id IS NULL AND rel2.error_message IS NULL AND rel2.relief_type = 1 AND rel2.process_status = 2 AND rownum <= :batch_size AND NOT EXISTS (SELECT 'x' FROM mrp_form_query WHERE query_id = :in_process_items AND number1 = rel2.inventory_item_id)) AND request_id IS NULL AND error_message IS NULL AND relief_type = 1 AND process_status = 2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- --------- Parse 2 0.02 0.02 0 0 0 0 Execute 2 239.39 1003.16 274981 3792129 534 242 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- --------- total 4 239.41 1003.18 274981 3792129 534 242 Misses in library cache during parse: 1 Optimizer goal: RULE Parsing user id: 41 (APPS) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: RULE 0 UPDATE OF 'MRP_RELIEF_INTERFACE' 242 NESTED LOOPS 234 VIEW 242 SORT (UNIQUE) 242 COUNT (STOPKEY) 242 FILTER 1886651 TABLE ACCESS (BY INDEX ROWID) OF 'MRP_RELIEF_INTERFACE' 1886652 INDEX (RANGE SCAN) OF 'MRP_RELIEF_INTERFACE_N2' (NON-UNIQUE) 234 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'MRP_FORM_QUERY_N89' (NON-UNIQUE) 3597 TABLE ACCESS (BY INDEX ROWID) OF 'MRP_RELIEF_INTERFACE' 3831 INDEX (RANGE SCAN) OF 'MRP_RELIEF_INTERFACE_N2' (NON-UNIQUE) PART II : Analyzing The Results of The Trace: --------------------------------------------- Deciding if there is a problem with the code vs. the condition of your database: -------------------------------------------------------------------------------- Each SQL statement in the tkprof?d output file has three sections: a. The SQL statement itself. b. Diagnostics including cpu and elapsed time, disk reads and logical reads, and number of rows returned from the query. c. An execution plan listing all of the tables, views, and indexes that were accessed by the query. One of the first things you can do with your tkprof?d output is find out what your hit ratio was. The hit ratio is an indication of how often your query had to go to the disk vs. memory to get the information it needed. A ?good? hit ratio will fall somewhere between 99% and 100%. Anything lower could indicate fragmentation or a problem with the size of your memory buffer. The hit ratio is calculated as follows: Logical Reads - Physical Reads or (Query + Current) - Disk ------------------------------ ------------------------ Logical Reads (Query + Current) In the tkprof?d example on the previous page, the hit ratio = 3517682/3792663 = 92% If your hit ratio is lower than 99%, you should check to see how many extents you have on each and every object listed in the explain plan. In Oracle 7 and Oracle 8 it is always a good idea to keep your extents below double digits to maintain optimum performance. Following is a simple query you can run to find out the number of extents on an object: select substr (tablespace_name,1,15) tname, substr(segment_name,1,25) segname, substr(segment_type,1,10) segtype, substr(owner,1,5) "OWNER", count(*) "Extents", blocks from dba_extents where segment_name = '&your_object_name' group by tablespace_name, segment_name, segment_type, owner, blocks; Example Output: TNAME SEGNAME SEGTYPE OWNER Extents BLOCKS --------------- ------------------------- ---------- ----- --------- --------- SHR QA_RESULTS TABLE QA 187 513 SHR QA_RESULTS TABLE QA 139 515 The query results above indicate some disk fragmentation. The recommendation was to copy, drop and recreate the table, qa_results, into fewer extents. For detailed information about how to maintain your database for optimum performance, please refer note 100964.1 and note 100960.1. If your hit ratio is > 99%, or if you have tuned the database to the best of your knowledge and performance is still an issue, there is a strong possibility that the code can be tuned to improve performance. Before calling Oracle Support, check to see if the SQL statement with the performance problem was using the cost-based or the rule-based optimizer. It is hard to tell from the tkprof?d output?s explain plan whether the rule or cost-based optimizer was used because the explain plan always says, "Goal: Rule". If there are any hints in the SQL statement other than /*+Choose or /*+Rule, then you can assume that the cost-based optimizer was used to create the explain plan. You can tell if there is a hint in the code, because a hint always follows this sequence of characters: /*+. If a hint was not used, and if you are on Oracle 7 or Oracle 8, the rule-based optimizer was used to create the execution plan. You are probably asking, "What is the difference between the Cost-based and Rule-based Optimizers?." This article will not go into detail, but to explain it very briefly, the rule and cost-based optimizers use different methods to determine what indexes or tables should be used when searching for data. The optimizer also determines how the index will be scanned (full, range, etc..) when searching for data. The cost-based optimizer depends on whether or not statistics have been collected on the database objects in order to determine the best execution plan for the SQL statement in question. If statistics have not been recently collected, the cost-based optimizer may not always make a good decision with regards to the use of indexes, etc. when creating an execution plan for a SQL statement. Therefore, if you have determined that the cost-based optimizer was used on the SQL statement with the problem, please make sure that statistics have been collected on all of the tables in the execution plan before calling Oracle Support. You might discover that simply collecting statistics on the tables in the execution plan would fix the problem.