Are You Recruiter/Hiring Manager

*Filter candidates using TestYourCandidate.com and save 80% time ( saving time for recruiters and interviewers)
*More than 50% candidates can be filtered on Candidate Screening online tests
*Inbuilt exams are available in library, created and tested by experts Try Today...

ORACLE Tutorials

Performance Tuning Simplified

When module/application/report that has bad performance issue is known.

Steps

1. First of all we need to identify sql statements that perform very badly. To do this, start sql trace
SQL>alter session set sql_trace=true;

2. Then, re-run the module/application/report. This is performed so that the related sql statement can be captured.

3. Stop the sql tracing
SQL>alter session set sql_trace=false;

4. The trace should be created in admin/orcl/udump

5. The trace file is hard to be read by human. To translate this file into more human readable format, use TKPROF. Use sort=exeela to sort by elapsed execution time
root@localhost $ tkprof /mnt/disk2/u01/app/oracle/admin/orcl/udump/xxxxx.trc output_file.txt sort =exeela

6. Then, the output file will contain details explanation on the running sql statement including optimizer mode. Then from here, we can start to tuning the sql. On how to tuning the sql, I will write it later.