This forum is currently locked. You can't register or post questions at this time. (read more)

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. ... 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. ... rof.php%22 ... -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:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests