Optimize Query Performance

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Optimize Query Performance

Postby Anand » Fri Feb 15, 2013 7:19 am

Hi Tim,

I need your help to optimize the following query.
Please note that:-
1) The table SWCDR is range-partitioned on column "ORIG_DT" and is also having table level parallel degree 2.
2) The table SWCDR contains 10 lacs data into it.
3) Composite BTREE Index (SWCDR_IDX_10) is created on columns (INSERT_DT & ORIG_DT) of SWCDR table.
4) Composite BTREE Index is created on columns (DS_NUMBER, TABLE_ID, LOG_DT) Of SUMMARY_LOG_HIST table.

The below query is taking 40 minutes for giving output on screen.

Can you please guide me (via looking into the explain plan as shown below) the following:-
1. What HINT/ INDEXES should I introuduce in order to optimize the below query.
2. Is there any alternative ways to write the same query as shown below:-.

Code: Select all

SELECT a.insert_dt
     JOIN (SELECT ds_number,
               max(log_dt) log_dt,
           FROM summary_log_hist
          GROUP BY ds_number,
                 last_msg) b
      ON (a.insert_dt > b.log_dt AND
         (a.orig_dt >= b.last_exec_dt AND a.orig_dt < b.next_exec_dt))
    WHERE b.ds_number = 0
      AND b.table_id = 1
      AND b.last_msg = 'Complete'
      AND a.insert_dt BETWEEN :start_dt AND :end_dt

Explain Plan for the above query is shown below:-

Code: Select all

                                                                                                                                                                                                     COST           CARDINALITY
SELECT STATEMENT, GOAL = ALL_ROWS                                         923065      266827
PX COORDINATOR               
   PX SEND QC (RANDOM)            SYS            :TQ10001                 923065      266827
      MERGE JOIN                                                          923065      266827
      SORT JOIN                                                           257       2595
         PX RECEIVE                                                       256      2595
            PX SEND BROADCAST     SYS            :TQ10000                 256      2595
            VIEW                  DWH_SCHEMA1                             256      2595
            HASH GROUP BY                                                 256      2595
            TABLE ACCESS FULL     DWH_SCHEMA1     SUMMARY_LOG_HIST        248      51886
         SORT JOIN                                                        897512      30297257
         PX PARTITION RANGE ALL                                           122072     30297257
         TABLE ACCESS BY LOCAL INDEX ROWID DWH_SCHEMA1 SWCDR              122072          30297257
         INDEX RANGE SCAN                   DWH_SCHEMA1 SWCDR_IDX_10      8506       30297257

Thanks in advance.

Thanks & Regards,
Anand Kumar Ojha

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Optimize Query Performance

Postby Tim... » Fri Feb 15, 2013 7:47 pm


I would suggest you try a few of things:

1) It would be a lot easier if you used DBMS_XPLAN to get the execution plan. Also, run it using GATHER_PLAN_STATISTICS to get some information about the expected and actual cardinalities. Remember to use set tab off, so the format is retained when you post it on the forum.

https://oracle-base.com/articles/9i/ ... stics_hint

2) You need to use SQL trace or Real-Time SQL Monitoring to see where the time is actually being spent, so you can focus on the steps that actually are the problems.

https://oracle-base.com/articles/mis ... rof.php%22
https://oracle-base.com/articles/11g ... -11gr1.php

3) I often find it easier to look at execution plans with parallel query to see that the basic route through the data looks sensible before I worry about the parallel execution itself. It doesn't always help, but sometimes it can give you some ideas.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests