How to Start tuning: Server Level (Focus from Oracle Database)
Whenever there is server level issue, The tuning starts from the server
.. Just remember the phrase “The person who has the problems has the symptoms for solutions” So let us start the tuning from server..
1. First we need to find out what the server utilization is and who is utilizing the most.
How to achieve it: we can use os specific commands like Topas in AIX,TOP in linux environments,Prstat/topas in solaris, task manager in windows.
What you need: once you found what is the utilization and who is doing that..
Then you need to get the server process id (spid) from the os level of the specific process who is utilizing the most.
2. After that login to any user session who can access v$ views.
Execute: ( I attached my queries ,, you can add the columns you need to add )
Select module,action,machine,sql_address,sql_id,status from v$session vs
Where paddr=(select addr from v$process where spid=’&spid’);
3. find out what the sql which is consuming resource.
Select sql_text,sql_fulltext,optimizer_cost from v$sql where sql_id=’&sql_id’;
4. Now you got the details of the sessions,, if you want to do kill the session .. go to your server console as the specific user and then kill the session
How should I do: use ‘’kill -9 ”
5. Don’t stop here .. tune the query and take complete solution to the issue .
How to Start tuning: Session Level (focus from oracle Database)
Refer to the post… How to Start tuning: Server Level (Focus from Oracle Database)
When we found that the specific session taking more bottleneck towards performance. Then The best step to be taken is the tracing the session (ie getting more details) before killing it.
For Example FNDWFBG is taking more cpu usage.. what we need to take step is..
1. As per the guidelines in the post.. first we need to take the Os process id.
2. Login as sysdba
3. i. oradebug setospid &spid
ii. oradebug unlimit
iii. oradebug Event 10046 trace name context forever, level 12
Trace file will be generated with the process id in its name in User dump location.
4. Once the session got completed then execute the below command in the sysdba session where you initiated the oradebug.
oradebug Event 10046 trace name context off;
5. What next.. Generate tkprof for the session dump file to find the expensive query.
Suggested to use as below:
Tkprof explain=/ sys=no sort='(prsela, exeela, fchela)’
Example: tkprof PROD_ora_950460.trc PROD_ora_950460.tkp explain=apps/apps sys=no
sort='(prsela, exeela, fchela)’
The above tkprof options are very usefull.. it will give us the query details in the order of its expensiveness.
6. Beyond this.. DBA needs to work with the respective technical teams to tune the expensive query.
How to start tuning: Code Level
Refer to the post… How to Start tuning: Session Level (focus from oracle Database)
Once we found the most expensive query, we need to tune the query and test it again..
Always advisable to tune and test in your test instance.
Follow the below steps to test and find out whether it is tuned or not…
1. Login to your Application user through which you can execute the statements
2. alter session set tracefile_identifier=’10046′;
3. alter session set timed_statistics = true;
4. alter session set statistics_level=all;
5. alter session set max_dump_file_size = unlimited;
6. alter session set events ‘10046 trace name context forever,level 12’;
7. Execute the expensive statement
8. select * from dual; — to ensure the previous cursor is closed
9. alter session set events ‘10046 trace name context off’;
10. Generate the tkprof
Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.
How to start tuning: Oracle Ebs Application Session
As per the previous posts.. we need to trace the session to find out what is the problem.
For E-Business Suite Session .. Here we go.
1. Navigate Responsibility: System Administrator > Profile > System >Query
User: User submitting the Journal entries Report
Profile: Initialization SQL Statement – Custom
2. Click on User column – Edit Field and enter
begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=”””10046 TRACE NAME CONTEXT FOREVER,LEVEL 12””’); end;
3. Save.
4. Reproduce the problem through the user session to find out cause for performance problem.
5 Trace file will be generated with the process id in its name.
6 Generate tkprof for the trace file.
Generate the tkprof as explained in the post How to Start tuning: Session Level (focus from oracle Database).. itll the give the details in the order of its expensiveness.
