Working with Trace file for a concurrent program
-----------------------------------------------------------------
Enable trace file for a concurrent program
-----------------------------------------------------------------
Log on to oracle application
1. Go to System Administrator -> Concurrent -> Program -> Define.
2. Query the concurrent program for which you want to enable the trace.
3. Enable the check box “Enable Trace” and save it.
4. Now from the appropriate responsibility, run the concurrent program.
5. A SQL Trace will be Saved in the ../udump directory of the database server
-----------------------------------------------------------------
How to extract the trace file of a concurrent program?
-----------------------------------------------------------------
Following steps need to be performed:
1. Setup the profile value at user level with the following value
Profile name: "Initialization SQL Statement - Custom"
User: abc
Profile value:
BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER = abc MAX_DUMP_FILE_SIZE = 5000000 EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');END;
2. Now on ward all the trace will be tracked in the .trc file. Find out where the trace file resides in the DB.
Go to Help -> Diagnosics -> Trace. enable trace. After doing so, a pop up will show the server path where the trace will get create..
usually .trc file will get created in DB server. The file path may be say "/u01/app/oracle/admin/RAC11D/udump/RAC11D_ora_17181_abc.trc". the path will be stored along with the trc file name. usually the value in the profile value TRACEFILE_IDENTIFIER=AKONDURU is used in the trace file naming
3. Now we have enabled trace for your user login. So what ever you do. say run a concurrent program. how the concurrent program is executed? the sql queries executed. how the control is going... every thing is tracked in the trace file.
4. Log in to the DB server. extract the file and convert to readable format.
Go to the following path in the DB server -> '/u01/app/oracle/admin/RAC11D/udump/'. Identify the file name which is prompted while enabling the trace (look point 2).
the .trc file is not readable. You have to convert it (tkprof) to make it readable. use the following command to do so.
tkprof (input).trc output.txt EXPLAIN=()
where input.trc is the trace file and output.txt is in readable format
5. After you get the trace file. make sure to remove the profile value that you have set for the profile : "Initialization SQL Statement - Custom".. else the system will go on creating a huge trace file.
--------------------------------------------------------------------------
Query to locate the trace file, it takes as input the ' Concurrent Request id'
--------------------------------------------------------------------------
SELECT
'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.
In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.
by checking that option we can get Trace file .by using Tk Proof we can open that file because it may be a Raw Data file.
by analysing the trace file we can observe which part of code is taking maximum time and cost .
tips i have followed like
If it is a Report kind of Rice Object then
following tips can be consider
1.joins of tables should be like parent to child and child to detail as well.
2.we will use exitsts operator instead of using IN operator.
--this statement needs to check the entire table
select count(*) from [table] where ...
--this statement is true as soon as one match is found
exists ( select * from [table] where ... )
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items3.Maximum we need to avoid to use Dual Table.
4.huge amount of table columns we will place at the end of columns list.
5.we will place hardcoding conditions at end of all joins.
6.if we are using subqueries we will place at starting of join conditions.
7.using views we can improve performance of query
8.Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;
9.Be careful while using conditions in WHERE clause.
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;10. Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';11. Use JOIN instead of subqueries. As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:
SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post FROM authors a
Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.
SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
ON (a.id = p.author_id)
GROUP BY a.idIf it is Interface or Conversion.
1.By using PL sql tables we can improive the preformance.
2.avoiding use of Dual Table.
3.Bulk collect and bulk bind.
4.using parameter cursors
5.using indexes on large data base columns.
6.using materailized views we can reduce the network traffic.
The Effect of Full Table Scans When Querying Very Large Tables
Let's look at using this technique for querying very large tables in your Oracle database. Surely they should use an index?
Otherwise you might have to read thousands of blocks. It is correct to say that a full table scan of a very large table could read many thousands of data blocks,
but as we shall see it may be better to do this than to perform an index scan and table lookup.
The situation when the a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data
in the table and it may perform better even when you are retrieving as little as 1% of the table data.
Of course if you only want to retrieve one row in the table, then you would want to use an index.
---------------------------------------------------------------------
Purity level Function:-
------------------------------
The function purity level defines what structures the function reads or modifies.
Following are the purity levels
1) WNDS - Writes No Database State i.e. Function does not modify any database tables (No DML)
2) RNDS - Reads No Database State i.e. Function does not read any tables (No select)
3) WNPS - Writes No Package State i.e. Function does not modify any packaged variables (packaged variables are variables declared in a package specification)
4) RNPS - Reads No Package State i.e. Function does not read any packaged variables
You can write the following in your package specification
PRAGMA RESTRICT_REFERENCES(program_unit_name, purity levels,…)
The package will execute depeending on the purity level you have specified. You can give multiple purity levels too.
-----------------------------------------------------------------------------------------------------
