Oracle Performance Tuning Tips at atoz help blog-Tuning Tips & Samples

Oracle xx Tuning Tips & Samples

•Get AWR report from the environment for the time code executed and identify expensive code and fine tune the same.

 

•Make sure all table stats are up to date. If not gather table level stats and check execution time.

 

•Try to use multiple simple select query instead of huge select statement.

 

•Avoid execution same block / code again and again for same purpose. Try to get all required details from table and store in variable or Array.

 

•Try to use ROWID for Update/Delete for better performance.

 

•Try to use ROWID if same table used in consecution PLSQL block for better performance.

 

•Do commit for huge DML operation on a regular interval.

 

•Don’t use function or conversion function in where clause.

 

•Some SELECT statement WHERE clauses do not use indexes at all. If you have specified an index over a table that is referenced by a clause of type shown in this section Oracle will simply ignore the index.

 

•For each clause that cannot use an index, an alternative approach, which will allow you to get better performance out of your SELECT statements is suggested.

 

Sample code:1

Old:

  SELECT DISTINCT cs_bunit_num

          FROM pfe_cs_xref.gr_xref_cust xref

           WHERE xref.pfe_bunit_num = l_ctorg_bunit_num

           AND curr_rec_flag = 'Y';

As per code we are trying to compare number column with varchar2. We need to provide explicit

TO_CHAR conversion to avoid explicit conversion. Post changes we saved around 80% of execution time.

New:

    SELECT DISTINCT cs_bunit_num

           FROM pfe_cs_xref.gr_xref_cust xref

           WHERE xref.pfe_bunit_num = TO_CHAR(l_ctorg_bunit_num)

           AND curr_rec_flag = 'Y';

Sample code:2

Use ROWID & Bulk Collect

Below code taken more than 5 Days to update 150 Millions of records.

UPDATE dna_script

   SET program_cd = 'MEDICAID-W'

 WHERE program_cd = 'MEDICAID‘

Huge DML we need commit on regular interval. Changed SQL to PLSQL block and used frequent

commit, ROWID and Bulk collect for improvement. Post change update completed in 3.5 Hrs.

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet