Home » RDBMS Server » Performance Tuning » Please help me my query performance is bad. (Oracle 11g)
Please help me my query performance is bad. [message #601388] Wed, 20 November 2013 07:57 Go to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi Experts,

The below query is taking 5 to 6 minutes to execute.

Please help me to improve the performance of the query.

SELECT /*+ GATHER_PLAN_STATISTICS */ SECOND_LIST.*
  FROM   (SELECT   ROW_NUMBER () OVER (ORDER BY FIRST_LIST.NAD_ID) RN,
                   FIRST_LIST.*
            FROM   (SELECT
                          NL .ID AS NAD_ID,
                             US.JOURNAL_CODE,
                             US.PERFE,
                             US.UNIT_NUMBER AS unit_num,
                             US.COMMENTS,
                             OD.SYSTEM_NUMBER AS SYSTEM_NUMBER,
                             OD.BUY_SYSTEM_NUMBER,
                             OD.ID AS OD_ID,
                             OD.PROPOSE_NUMBER,
                             OD.VISION,
                             OD.RENDER_NO,
                             OD.SELL_SYSTEM_NUMBER,
                             OD.WORTH,
                             OD.MAN_SYSTEM,
                             OD.MANF_DATE,
                             OD.DATE_RECENT,
                             OD.MANF_BY,
                             OD.RECENT_BY,
                             WG.NAME AS STATUS,
                             OH.SYSTEM_TYPE,
                             OH.SYSTEM_TYPE_ID,
                             OH.PROPPOSED_DATE,
                             NL.CONFIRMED,
                             NL.CLIENT_NUMBER,
                             NL.CONF_CLIENT_NUMBER,
                             NL.CLIENT_LOCATION,
                             NL.CLIENT_NAME,
                             NL.RECEIPT_ID,
                             FROM_TZ (NL.DATE_REACHED, 'UTC') AT TIME ZONE WBS.ORACLE_TZ_NAME
                                AS DATE_REACHED,
                             NL.JOURNAL_TYPE,
                             RECEIEVE_REP_ID (
                                WBS.REG_ID,
                                NL.REP_PERSON
                             ).REP_NAME
                                AS REP_PERSON,
                             NL.AMOUNTPAID_METHOD,
                             NL.MAINUNIT_ID,
                             RECEIEVE_REP_ID (WBS.REG_ID,
                                                  NL.REP_PERSON).SECTOR_NAME
                                AS SECTOR_NAME,
                             RECEIEVE_REP_ID (
                                WBS.REG_ID,
                                NL.REP_PERSON
                             ).SECTOR_LOC
                                AS SECTOR_LOC,
                             WBS.NATION_NAME,
                             FROM_TZ ( (SELECT   MAX (MODIFIED_DATE)
                                          FROM   TRANSACTIONS_VW OFT
                                         WHERE   OFT.NAD_ID = NL.ID), 'UTC') AT TIME ZONE WBS.ORACLE_TZ_NAME
                                AS MODIFIED_DATE,
                             NULL AS DATA_VALUE,
                             (SELECT   SUM (WORTH)
                                FROM   NAD_DETAILS OD
                               WHERE   OD.NAD_ID = NL.ID
                                       AND id NOT IN
                                                (SELECT   ID
                                                   FROM   NAD_DETAILS
                                                  WHERE   RENDER_NO IN
                                                                (  SELECT   RENDER_NO
                                                                     FROM   NAD_DETAILS
                                                                    WHERE   NAD_ID = NL.ID
                                                                 GROUP BY   RENDER_NO
                                                                   HAVING   COUNT(*) > 1)
                                                          AND SYSTEM_NUMBER IS NULL))
                                AS ORDER_VALUE
                      FROM                  NAD_LISTER NL
                                         INNER JOIN
                                            MAINUNIT WG
                                         ON (WG.ID = NL.MAINUNIT_ID)
                                      INNER JOIN
                                         WEDB_SOURCE WBS
                                     ON (WBS.BU_ID = WG.BUID
                                          AND WG.BUID = MANAGECONFIG.BU_ID)
                                   LEFT JOIN
                                      NAD_DETAILS_ND_VW OD
                                   ON (OD.NAD_ID = NL.ID)
                                LEFT JOIN
                                   WEDB_MAN_UNIT OH
                                ON (OH.SYSTEM_NO = OD.SYSTEM_NUMBER
                                    AND OH.SYSTEM_NO > 0)
                             LEFT JOIN
                                UNIT_SOURCE US
                             ON (US.ID = NL.UNIT_SOURCE)
                     WHERE   NL.MAINUNIT_ID = 262
                             AND NL.DATE_REACHED >= TRUNC (TO_DATE('01-MAR-2012','DD-MON-YYYY'))
                             AND NL.DATE_REACHED < TRUNC (TO_DATE('31-AUG-2012','DD-MON-YYYY'))+1)
                   FIRST_LIST) SECOND_LIST
WHERE   RN BETWEEN 1 AND 20;
 
 
20 rows selected.
 
 
Elapsed: 00:05:14.75
 
 
Plan hash value: 1828662143
 
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                                |      1 |        |     20 |00:05:13.35 |    3194K|  94412 |       |       |          |
|   1 |  SORT AGGREGATE                       |                                |     14 |      1 |     14 |00:00:00.39 |    2874 |     45 |       |       |          |
|   2 |   VIEW                                | TRANSACTIONS_VW                |     14 |      2 |     54 |00:00:00.39 |    2874 |     45 |       |       |          |
|   3 |    UNION-ALL                          |                                |     14 |        |     54 |00:00:00.39 |    2874 |     45 |       |       |          |
|*  4 |     HASH JOIN OUTER                   |                                |     14 |      1 |     14 |00:00:00.39 |    2704 |     45 |  1035K|  1035K|  389K (0)|
|   5 |      NESTED LOOPS OUTER               |                                |     14 |      1 |     14 |00:00:00.01 |    1597 |      0 |       |       |          |
|   6 |       NESTED LOOPS                    |                                |     14 |      1 |     14 |00:00:00.01 |      57 |      0 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID    | NAD_LISTER                     |     14 |      1 |     14 |00:00:00.01 |      38 |      0 |       |       |          |
|*  8 |         INDEX UNIQUE SCAN             | PK_NAD_LISTER                  |     14 |      1 |     14 |00:00:00.01 |      24 |      0 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN              | PK_MAINUNIT                    |     14 |   1388 |     14 |00:00:00.01 |      19 |      0 |       |       |          |
|* 10 |       TABLE ACCESS FULL               | ROUND_UNIT                     |     14 |      1 |     13 |00:00:00.01 |    1540 |      0 |       |       |          |
|  11 |      VIEW                             |                                |     14 |      1 |     14 |00:00:00.38 |    1107 |     45 |       |       |          |
|* 12 |       FILTER                          |                                |     14 |        |     14 |00:00:00.38 |    1107 |     45 |       |       |          |
|  13 |        SORT GROUP BY                  |                                |     14 |      1 |     40 |00:00:00.38 |    1107 |     45 |  2048 |  2048 | 2048  (0)|
|  14 |         NESTED LOOPS                  |                                |     14 |      1 |    244 |00:00:00.38 |    1107 |     45 |       |       |          |
|  15 |          NESTED LOOPS                 |                                |     14 |      1 |    244 |00:00:00.35 |     861 |     43 |       |       |          |
|  16 |           NESTED LOOPS                |                                |     14 |      1 |    244 |00:00:00.32 |     358 |     39 |       |       |          |
|  17 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     14 |      1 |     40 |00:00:00.10 |      75 |     15 |       |       |          |
|* 18 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     14 |      1 |     40 |00:00:00.02 |      35 |      3 |       |       |          |
|  19 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     40 |      1 |    244 |00:00:00.22 |     283 |     24 |       |       |          |
|* 20 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     40 |      1 |    244 |00:00:00.01 |      38 |      0 |       |       |          |
|  21 |           TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON     |    244 |      1 |    244 |00:00:00.03 |     503 |      4 |       |       |          |
|* 22 |            INDEX UNIQUE SCAN          | PK_MAINUNIT_TRANSITION_REASON  |    244 |      1 |    244 |00:00:00.03 |     259 |      4 |       |       |          |
|* 23 |          INDEX UNIQUE SCAN            | PK_MAINUNIT_TRANSITION         |    244 |      1 |    244 |00:00:00.03 |     246 |      2 |       |       |          |
|  24 |     NESTED LOOPS                      |                                |     14 |        |     40 |00:00:00.01 |     170 |      0 |       |       |          |
|  25 |      NESTED LOOPS                     |                                |     14 |      1 |     40 |00:00:00.01 |     130 |      0 |       |       |          |
|  26 |       TABLE ACCESS BY INDEX ROWID     | TRANSACTION_HISTORY            |     14 |      1 |     40 |00:00:00.01 |      75 |      0 |       |       |          |
|* 27 |        INDEX RANGE SCAN               | TRANSACTION_HISTORY_IDX1       |     14 |      1 |     40 |00:00:00.01 |      35 |      0 |       |       |          |
|* 28 |       INDEX UNIQUE SCAN               | PK_MAINUNIT_TRANSITION_REASON  |     40 |      1 |     40 |00:00:00.01 |      55 |      0 |       |       |          |
|* 29 |      TABLE ACCESS BY INDEX ROWID      | MAINUNIT_TRANSITION_REASON     |     40 |      1 |     40 |00:00:00.01 |      40 |      0 |       |       |          |
|  30 |  SORT AGGREGATE                       |                                |     14 |      1 |     14 |00:00:00.18 |     159 |     30 |       |       |          |
|* 31 |   FILTER                              |                                |     14 |        |     18 |00:00:00.18 |     159 |     30 |       |       |          |
|  32 |    TABLE ACCESS BY INDEX ROWID        | NAD_DETAILS                    |     14 |      1 |     20 |00:00:00.11 |      44 |     17 |       |       |          |
|* 33 |     INDEX RANGE SCAN                  | NAD_DETAILS_IDX1               |     14 |      1 |     20 |00:00:00.02 |      25 |      3 |       |       |          |
|* 34 |    FILTER                             |                                |     20 |        |      2 |00:00:00.07 |     115 |     13 |       |       |          |
|* 35 |     TABLE ACCESS BY INDEX ROWID       | NAD_DETAILS                    |     20 |      1 |      7 |00:00:00.04 |      80 |     10 |       |       |          |
|* 36 |      INDEX UNIQUE SCAN                | PK_NAD_DETAILS                 |     20 |      1 |     20 |00:00:00.04 |      60 |     10 |       |       |          |
|* 37 |     FILTER                            |                                |      7 |        |      2 |00:00:00.02 |      35 |      3 |       |       |          |
|  38 |      HASH GROUP BY                    |                                |      7 |      1 |     12 |00:00:00.02 |      35 |      3 |  1079K|  1079K|  626K (0)|
|  39 |       TABLE ACCESS BY INDEX ROWID     | NAD_DETAILS                    |      7 |      1 |     14 |00:00:00.02 |      35 |      3 |       |       |          |
|* 40 |        INDEX RANGE SCAN               | NAD_DETAILS_IDX1               |      7 |      1 |     14 |00:00:00.01 |      21 |      0 |       |       |          |
|* 41 |  VIEW                                 |                                |      1 |  90346 |     20 |00:05:13.35 |    3194K|  94412 |       |       |          |
|* 42 |   WINDOW SORT PUSHED RANK             |                                |      1 |  90346 |     21 |00:05:12.69 |    3190K|  94333 | 66560 | 66560 |59392  (0)|
|  43 |    NESTED LOOPS OUTER                 |                                |      1 |  90346 |  10055 |00:05:12.63 |    3190K|  94333 |       |       |          |
|* 44 |     HASH JOIN RIGHT OUTER             |                                |      1 |  12537 |  10055 |00:00:38.51 |   76594 |  66549 |   837K|   837K| 1395K (0)|
|  45 |      TABLE ACCESS FULL                | UNIT_SOURCE                    |      1 |   2368 |   2368 |00:00:00.01 |      70 |      0 |       |       |          |
|* 46 |      HASH JOIN OUTER                  |                                |      1 |  12537 |  10055 |00:00:38.42 |   76524 |  66549 |  3267K|   934K| 3813K (0)|
|  47 |       MERGE JOIN CARTESIAN            |                                |      1 |   8632 |   6907 |00:00:01.41 |    4734 |    217 |       |       |          |
|  48 |        NESTED LOOPS                   |                                |      1 |      1 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
|* 49 |         TABLE ACCESS BY INDEX ROWID   | MAINUNIT                       |      1 |      1 |      1 |00:00:00.01 |       5 |      0 |       |       |          |
|* 50 |          INDEX UNIQUE SCAN            | PK_MAINUNIT                    |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|  51 |         TABLE ACCESS BY INDEX ROWID   | WEDB_SOURCE                    |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 52 |          INDEX RANGE SCAN             | WEDB_SOURCE_BUID               |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|  53 |        BUFFER SORT                    |                                |      1 |   7673 |   6907 |00:00:01.40 |    4727 |    217 |  2391K|   711K| 2125K (0)|
|  54 |         TABLE ACCESS BY INDEX ROWID   | NAD_LISTER                     |      1 |   7673 |   6907 |00:00:01.37 |    4727 |    217 |       |       |          |
|* 55 |          INDEX RANGE SCAN             | IDX_MAINUNIT_ID2               |      1 |   7673 |   6907 |00:00:00.08 |      29 |     27 |       |       |          |
|  56 |       VIEW                            | NAD_DETAILS_ND_VW              |      1 |   3210K|   3210K|00:00:29.22 |   71790 |  66332 |       |       |          |
|* 57 |        HASH JOIN RIGHT SEMI           |                                |      1 |   3210K|   3210K|00:00:24.07 |   71790 |  66332 |    68M|  7188K|  111M (0)|
|  58 |         INDEX FAST FULL SCAN          | PK_NAD_LISTER                  |      1 |   2423K|   2423K|00:00:02.03 |    5453 |      0 |       |       |          |
|  59 |         TABLE ACCESS FULL             | NAD_DETAILS                    |      1 |   3210K|   3210K|00:00:04.43 |   66337 |  66332 |       |       |          |
|  60 |     TABLE ACCESS BY INDEX ROWID       | WEDB_MAN_UNIT                  |  10055 |      7 |   8481 |00:04:34.07 |    3113K|  27784 |       |       |          |
|* 61 |      INDEX RANGE SCAN                 | WEDB_GMT_UNIT_N2               |  10055 |      1 |   8481 |00:03:41.59 |    3105K|  22456 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
   4 - access("FTH"."NAD_ID"="NL"."ID")
   8 - access("NL"."ID"=:B1)
   9 - access("W1"."ID"="NL"."MAINUNIT_ID")
  10 - filter("RR"."ID"="NL"."APPLIED_ROUND_UNIT")
  12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
  18 - access("NAD_ID"=:B1)
  20 - access("FHIS"."NAD_ID"=:B1)
       filter("FHIS"."NAD_ID"="NAD_ID")
  22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
  27 - access("FHIS"."NAD_ID"=:B1)
  28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
  31 - filter( IS NULL)
  33 - access("OD"."NAD_ID"=:B1)
  34 - filter( IS NOT NULL)
  35 - filter("SYSTEM_NUMBER" IS NULL)
  36 - access("ID"=:B1)
  37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
  40 - access("NAD_ID"=:B1)
  41 - filter(("RN">=1 AND "RN"<=20))
  42 - filter(ROW_NUMBER() OVER ( ORDER BY "NL"."ID")<=20)
  44 - access("US"."ID"="NL"."UNIT_SOURCE")
  46 - access("OD"."NAD_ID"="NL"."ID")
  49 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
  50 - access("WG"."ID"=262)
  52 - access("WBS"."BU_ID"="MANAGECONFIG"."BU_ID"())
  55 - access("NL"."MAINUNIT_ID"=262 AND "NL"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "NL"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
  57 - access("FHH"."ID"="OD"."NAD_ID")
  61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
       filter("OH"."SYSTEM_NO">0)


Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601389 is a reply to message #601388] Wed, 20 November 2013 08:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is US.ID indexed?
Re: Please help me my query performance is bad. [message #601390 is a reply to message #601388] Wed, 20 November 2013 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like the pain is all here:
|  60 |     TABLE ACCESS BY INDEX ROWID       | WEDB_MAN_UNIT                  |  10055 |      7 |   8481 |00:04:34.07 |    3113K|  27784 |       |       |          |
|* 61 |      INDEX RANGE SCAN                 | WEDB_GMT_UNIT_N2               |  10055 |      1 |   8481 |00:03:41.59 |    3105K|  22456 |       |       |          |

Oracle thought the query was going to find 7 rows in WEDB_MAN_UNIT, instead it found 8481.
Are statistics on that table up to date and what columns is WEDB_GMT_UNIT_N2 on?
Re: Please help me my query performance is bad. [message #601395 is a reply to message #601388] Wed, 20 November 2013 08:52 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Unique index on US.ID (primary key is created).

WEDB_GMT_UNIT_N2 it is index on SYSTEM_NO column.

Stats are up to date.

Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601396 is a reply to message #601395] Wed, 20 November 2013 09:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramya_162 wrote on Wed, 20 November 2013 20:22
Hi,

Stats are up to date.


Can you gather stats once again and post the execution plan. Because, the estimated rows and actual rows does not support your statement.
Re: Please help me my query performance is bad. [message #601397 is a reply to message #601396] Wed, 20 November 2013 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
what does this give:
SELECT COUNT(*), COUNT(CASE WHEN system_no > 0 THEN 1 END) cnt_sys_no,
COUNT(DISTINCT(CASE WHEN system_no > 0 THEN system_no END)) cnt_distinct
FROM WEDB_MAN_UNIT;
Re: Please help me my query performance is bad. [message #601474 is a reply to message #601396] Thu, 21 November 2013 07:16 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

I have gathered the stats once again still same plan and took more than 5 mins.

Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601479 is a reply to message #601397] Thu, 21 November 2013 07:26 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Please find the output of the query.

SELECT COUNT(*), COUNT(CASE WHEN system_no > 0 THEN 1 END) cnt_sys_no,
COUNT(DISTINCT(CASE WHEN system_no > 0 THEN system_no END)) cnt_distinct
FROM WEDB_MAN_UNIT;

COUNT(*) cnt_sys_no   cnt_distinct

31649514  19765655     19765655


Thanks.
Re: Please help me my query performance is bad. [message #601493 is a reply to message #601479] Thu, 21 November 2013 08:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
That's more than half of the table rows being counted with the filter("OH"."SYSTEM_NO">0) , almost ~ 63%.

[Updated on: Thu, 21 November 2013 08:09]

Report message to a moderator

Re: Please help me my query performance is bad. [message #601541 is a reply to message #601493] Fri, 22 November 2013 00:50 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

What should I do to improve the performance of this query.

Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601553 is a reply to message #601541] Fri, 22 November 2013 02:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
No promises, but you could try hinting a hash join between WEDB_MAN_UNIT OH and NAD_DETAILS_ND_VW OD. Given the apparent cardinality estimation error,that might help.

I think the problem is with the outer join at operation ID 44. That is forcing 10055 look ups into webdb_man_unit at IDs 60 and 61, all of which retrieve 8481 rows. My first question is always "do you rally need the outer join?" Often, in my experience, developers use an outer join when an inner join would do. Why? Either because they do not understand their data, or they do not have adequate constraints. Ask them why they need it. If they do, and that simple hint doesn't help, I think you have to look at major restructuring of the statement


--update: typo, where I say ID 44, I mean ID 43. Sorry about that.

[Updated on: Fri, 22 November 2013 02:07]

Report message to a moderator

Re: Please help me my query performance is bad. [message #601555 is a reply to message #601553] Fri, 22 November 2013 02:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Having had a another quick look (I'm procrastinating: I have to start a job this morning that is going to be really difficult) I think we can say with certainty that the outer joins are not needed. The view you materialize between IDs 41 and 61 consists of 10055 rows, which you then cut down to 20 rows with a row number filter. If you are going to throw away 10035 rows, does it really matter which ones remain? I don't think so. Get rid of the outer joins to NAD_DETAILS_ND_VW OD and WEDB_MAN_UNIT OH and UNIT_SOURCE US, and see what happens.

[Updated on: Fri, 22 November 2013 02:28]

Report message to a moderator

Re: Please help me my query performance is bad. [message #601561 is a reply to message #601555] Fri, 22 November 2013 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I really don't see anyway you can say that just becuase we're cutting down a load of rows in an ordered list to 20, the outer-joins don't matter.
It looks like a pagination query to get data ordered by NAD_LISTER.ID.

So the only thing that determines if those joins need to be inner or outer is if the data being retrieved from those tables is optional or not.
You can't tell that from the query or the plan.
Re: Please help me my query performance is bad. [message #601574 is a reply to message #601561] Fri, 22 November 2013 05:22 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

I have tried using hash join hint WEDB_MAN_UNIT OH and NAD_DETAILS_ND_VW OD
but no improvement.

Please help me .How to proceed.

Thanks.
Re: Please help me my query performance is bad. [message #601575 is a reply to message #601574] Fri, 22 November 2013 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
post the revised explain plan
Re: Please help me my query performance is bad. [message #601668 is a reply to message #601575] Sun, 24 November 2013 01:56 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Please find the explain plan after applying the hint /*+ use_hash(OH OD) */

SELECT  SECOND_LIST.*
  FROM   (SELECT   ROW_NUMBER () OVER (ORDER BY FIRST_LIST.NAD_ID) RN,
                   FIRST_LIST.*
            FROM   (SELECT /*+ use_hash(OH OD) */
                          FH .ID
                                AS NAD_ID,

Plan hash value: 3984024202

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                                |      1 |        |     20 |00:03:49.62 |    2356K|   2341K|       |       |          |
|   1 |  SORT AGGREGATE                       |                                |     14 |      1 |     14 |00:00:00.39 |    2874 |     44 |       |       |          |
|   2 |   VIEW                                | TRANSACTIONS_VW                |     14 |      2 |     54 |00:00:00.39 |    2874 |     44 |       |       |          |
|   3 |    UNION-ALL                          |                                |     14 |        |     54 |00:00:00.39 |    2874 |     44 |       |       |          |
|*  4 |     HASH JOIN OUTER                   |                                |     14 |      1 |     14 |00:00:00.39 |    2704 |     44 |  1035K|  1035K|  430K (0)|
|   5 |      NESTED LOOPS OUTER               |                                |     14 |      1 |     14 |00:00:00.01 |    1597 |      0 |       |       |          |
|   6 |       NESTED LOOPS                    |                                |     14 |      1 |     14 |00:00:00.01 |      57 |      0 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID    | NAD_LISTER                     |     14 |      1 |     14 |00:00:00.01 |      38 |      0 |       |       |          |
|*  8 |         INDEX UNIQUE SCAN             | PK_NAD_LISTER                  |     14 |      1 |     14 |00:00:00.01 |      24 |      0 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN              | PK_MAINUNIT                    |     14 |   1388 |     14 |00:00:00.01 |      19 |      0 |       |       |          |
|* 10 |       TABLE ACCESS FULL               | ROUND_UNIT                     |     14 |      1 |     13 |00:00:00.01 |    1540 |      0 |       |       |          |
|  11 |      VIEW                             |                                |     14 |      1 |     14 |00:00:00.38 |    1107 |     44 |       |       |          |
|* 12 |       FILTER                          |                                |     14 |        |     14 |00:00:00.38 |    1107 |     44 |       |       |          |
|  13 |        SORT GROUP BY                  |                                |     14 |      1 |     40 |00:00:00.38 |    1107 |     44 |  2048 |  2048 | 2048  (0)|
|  14 |         NESTED LOOPS                  |                                |     14 |      1 |    244 |00:00:00.37 |    1107 |     44 |       |       |          |
|  15 |          NESTED LOOPS                 |                                |     14 |      1 |    244 |00:00:00.36 |     861 |     42 |       |       |          |
|  16 |           NESTED LOOPS                |                                |     14 |      1 |    244 |00:00:00.32 |     358 |     39 |       |       |          |
|  17 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     14 |      1 |     40 |00:00:00.12 |      75 |     15 |       |       |          |
|* 18 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     14 |      1 |     40 |00:00:00.02 |      35 |      3 |       |       |          |
|  19 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     40 |      1 |    244 |00:00:00.21 |     283 |     24 |       |       |          |
|* 20 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     40 |      1 |    244 |00:00:00.01 |      38 |      0 |       |       |          |
|  21 |           TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON     |    244 |      1 |    244 |00:00:00.03 |     503 |      3 |       |       |          |
|* 22 |            INDEX UNIQUE SCAN          | PK_MAINUNIT_TRANSITION_REASON  |    244 |      1 |    244 |00:00:00.03 |     259 |      3 |       |       |          |
|* 23 |          INDEX UNIQUE SCAN            | PK_MAINUNIT_TRANSITION         |    244 |      1 |    244 |00:00:00.01 |     246 |      2 |       |       |          |
|  24 |     NESTED LOOPS                      |                                |     14 |        |     40 |00:00:00.01 |     170 |      0 |       |       |          |
|  25 |      NESTED LOOPS                     |                                |     14 |      1 |     40 |00:00:00.01 |     130 |      0 |       |       |          |
|  26 |       TABLE ACCESS BY INDEX ROWID     | TRANSACTION_HISTORY            |     14 |      1 |     40 |00:00:00.01 |      75 |      0 |       |       |          |
|* 27 |        INDEX RANGE SCAN               | TRANSACTION_HISTORY_IDX1       |     14 |      1 |     40 |00:00:00.01 |      35 |      0 |       |       |          |
|* 28 |       INDEX UNIQUE SCAN               | PK_MAINUNIT_TRANSITION_REASON  |     40 |      1 |     40 |00:00:00.01 |      55 |      0 |       |       |          |
|* 29 |      TABLE ACCESS BY INDEX ROWID      | MAINUNIT_TRANSITION_REASON     |     40 |      1 |     40 |00:00:00.01 |      40 |      0 |       |       |          |
|  30 |  SORT AGGREGATE                       |                                |     14 |      1 |     14 |00:00:00.24 |     159 |     27 |       |       |          |
|* 31 |   FILTER                              |                                |     14 |        |     18 |00:00:00.24 |     159 |     27 |       |       |          |
|  32 |    TABLE ACCESS BY INDEX ROWID        | NAD_DETAILS                    |     14 |      1 |     20 |00:00:00.14 |      44 |     14 |       |       |          |
|* 33 |     INDEX RANGE SCAN                  | NAD_DETAILS_IDX1               |     14 |      1 |     20 |00:00:00.01 |      25 |      0 |       |       |          |
|* 34 |    FILTER                             |                                |     20 |        |   2 |00:00:00.10 |     115 |     13 |          |       |          |
|* 35 |     TABLE ACCESS BY INDEX ROWID       | NAD_DETAILS                    |     20 |      1 |   7 |00:00:00.07 |      80 |     10 |          |       |          |
|* 36 |      INDEX UNIQUE SCAN                | PK_NAD_DETAILS                 |     20 |      1 |     20 |00:00:00.07 |      60 |     10 |       |       |          |
|* 37 |     FILTER                            |                                |      7 |        |   2 |00:00:00.03 |      35 | 3 |       |       |          |
|  38 |      HASH GROUP BY                    |                                |      7 |      1 |     12 |00:00:00.03 |      35 |      3 |  1079K|  1079K|  625K (0)|
|  39 |       TABLE ACCESS BY INDEX ROWID     | NAD_DETAILS                    |      7 |      1 |     14 |00:00:00.03 |      35 |      3 |       |       |          |
|* 40 |        INDEX RANGE SCAN               | NAD_DETAILS_IDX1               |      7 |      1 |     14 |00:00:00.01 |      21 |      0 |       |       |          |
|* 41 |  VIEW                                 |                                |      1 |  90355 |     20 |00:03:49.62 |    2356K|   2341K|       |       |          |
|* 42 |   WINDOW SORT PUSHED RANK             |                                |      1 |  90355 |     21 |00:03:48.50 |    2352K|   2341K| 64512 | 64512 |57344  (0)|
|* 43 |    HASH JOIN RIGHT OUTER              |                                |      1 |  90355 |  10055 |00:03:48.46 |    2352K|   2341K|   837K|   837K| 1391K (0)|
|  44 |     TABLE ACCESS FULL                 | UNIT_SOURCE                    |      1 |   2368 |   2368 |00:00:00.21 |      70 |     63 |       |       |          |
|* 45 |     HASH JOIN OUTER                   |                                |      1 |  90355 |  10055 |00:03:48.20 |    2351K|   2341K|  6466K|  1848K| 6992K (0)|
|* 46 |      HASH JOIN OUTER                  |                                |      1 |  12537 |  10055 |00:00:38.65 |   76559 |  66366 |  3267K|   934K| 3854K (0)|
|  47 |       MERGE JOIN CARTESIAN            |                                |      1 |   8632 |   6907 |00:00:01.28 |    4734 |      2 |       |       |          |
|  48 |        NESTED LOOPS                   |                                |      1 |      1 |   1 |00:00:00.01 |       7 | 0 |       |       |          |
|* 49 |         TABLE ACCESS BY INDEX ROWID   | MAINUNIT                       |      1 |      1 |   1 |00:00:00.01 |       5 | 0 |       |       |          |
|* 50 |          INDEX UNIQUE SCAN            | PK_MAINUNIT                    |      1 |      1 |   1 |00:00:00.01 |       2 | 0 |       |       |          |
|  51 |         TABLE ACCESS BY INDEX ROWID   | WEDB_SOURCE                    |      1 |      1 |   1 |00:00:00.01 |       2 | 0 |       |       |          |
|* 52 |          INDEX RANGE SCAN             | WEDB_SOURCE_BUID               |      1 |      1 |   1 |00:00:00.01 |       1 | 0 |       |       |          |
|  53 |        BUFFER SORT                    |                                |      1 |   7673 |   6907 |00:00:01.26 |    4727 |      2 |  2391K|   711K| 2125K (0)|
|  54 |         TABLE ACCESS BY INDEX ROWID   | NAD_LISTER                     |      1 |   7673 |   6907 |00:00:01.22 |    4727 |      2 |       |       |          |
|* 55 |          INDEX RANGE SCAN             | IDX_MAINUNIT_ID2               |      1 |   7673 |   6907 |00:00:00.02 |      29 |      0 |       |       |          |
|  56 |       VIEW                            | NAD_DETAILS_ND_VW              |      1 |   3210K|   3210K|00:00:29.59 |   71825 |  66364 |       |       |          |
|* 57 |        HASH JOIN RIGHT SEMI           |                                |      1 |   3210K|   3210K|00:00:24.41 |   71825 |  66364 |    68M|  7188K|  111M (0)|
|  58 |         INDEX FAST FULL SCAN          | PK_NAD_LISTER                  |      1 |   2423K|   2423K|00:00:02.43 |    5453 |      0 |       |       |          |
|  59 |         TABLE ACCESS FULL             | NAD_DETAILS                    |      1 |   3210K|   3210K|00:00:04.60 |   66372 |  66364 |       |       |          |
|* 60 |      TABLE ACCESS FULL                | WEDB_MAN_UNIT	               |      1 |     19M|     19M|00:02:17.34 |    2275K|   2275K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

    4 - access("FTH"."NAD_ID"="FH"."ID")
   8 - access("FH"."ID"=:B1)
   9 - access("W1"."ID"="FH"."MAINUNIT_ID")
  10 - filter("RR"."ID"="FH"."APPLIED_ROUND_UNIT")
  12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
  18 - access("NAD_ID"=:B1)
  20 - access("FHIS"."NAD_ID"=:B1)
       filter("FHIS"."NAD_ID"="NAD_ID")
  22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
  27 - access("FHIS"."NAD_ID"=:B1)
  28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
  31 - filter( IS NULL)
  33 - access("OD"."NAD_ID"=:B1)
  34 - filter( IS NOT NULL)
  35 - filter("SYSTEM_NUMBER" IS NULL)
  36 - access("ID"=:B1)
  37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
  40 - access("NAD_ID"=:B1)
  41 - filter(("RN">=1 AND "RN"<=20))
  42 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
  43 - access("FS"."ID"="FH"."UNIT_SOURCE")
  45 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
  46 - access("OD"."NAD_ID"="FH"."ID")
  49 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
  50 - access("WG"."ID"=262)
  52 - access("GBM"."BU_ID"="MANAGECONFIG"."BU_ID"())
  55 - access("FH"."MAINUNIT_ID"=262 AND "FH"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "FH"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
  57 - access("FHH"."ID"="OD"."NAD_ID")
  60 - filter("OH"."SYSTEM_NO">0)


Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601671 is a reply to message #601574] Sun, 24 November 2013 02:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ramya_162 wrote on Fri, 22 November 2013 11:22
Hi,

I have tried using hash join hint WEDB_MAN_UNIT OH and NAD_DETAILS_ND_VW OD
but no improvement.

Please help me .How to proceed.

Thanks.
What do you mean, "no improvement"? I've reduced your query time from 5:13.35 to 3:49.62. That's 25% faster already.
You might want to say "thank you". I think I shall say "if you want more help, you can pay for consultancy services"
--
John Watson
Oracle Certified Master DBA
Re: Please help me my query performance is bad. [message #601680 is a reply to message #601671] Sun, 24 November 2013 06:51 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Actually these three are not constant we are sending as variables front frot end.

WHERE   NL.MAINUNIT_ID = 262
AND NL.DATE_REACHED >= TRUNC (TO_DATE('01-MAR-2012','DD-MON-YYYY'))
AND NL.DATE_REACHED < TRUNC (TO_DATE('31-AUG-2012','DD-MON-YYYY'))+1);

WHERE   NL.MAINUNIT_ID = :MAINUNIT_ID 
AND NL.DATE_REACHED >= TRUNC (:DATE_FROM)
AND NL.DATE_REACHED < TRUNC (:DATE_TO)+1);

Based on the values performance is vary with hint and without hint(/*+ use_hash(OH OD) */).
In some cases without hint is fast and in some cases with hint is fast.

Please help me how to improve the performance of the query for any input values.


Without /*+ use_hash(OH OD) */ hint took less time.

Plan hash value: 2350839375

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                                |      1 |        |     10 |00:00:42.51 |   74740 |  72089 |       |       |          |
|   1 |  SORT AGGREGATE                       |                                |     10 |      1 |     10 |00:00:00.42 |    1212 |    115 |       |       |          |
|   2 |   VIEW                                | TRANSACTIONS_VW                |     10 |      2 |     10 |00:00:00.42 |    1212 |    115 |       |       |          |
|   3 |    UNION-ALL                          |                                |     10 |        |     10 |00:00:00.42 |    1212 |    115 |       |       |          |
|*  4 |     HASH JOIN OUTER                   |                                |     10 |      1 |     10 |00:00:00.42 |    1182 |    115 |  1035K|  1035K|  410K (0)|
|   5 |      NESTED LOOPS OUTER               |                                |     10 |      1 |     10 |00:00:00.27 |    1152 |    108 |       |       |          |
|   6 |       NESTED LOOPS                    |                                |     10 |      1 |     10 |00:00:00.01 |      52 |      0 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID    | NAD_LISTER                     |     10 |      1 |     10 |00:00:00.01 |      37 |      0 |       |       |          |
|*  8 |         INDEX UNIQUE SCAN             | PK_NAD_LISTER                  |     10 |      1 |     10 |00:00:00.01 |      27 |      0 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN              | PK_MAINUNIT                    |     10 |   1388 |     10 |00:00:00.01 |      15 |      0 |       |       |          |
|* 10 |       TABLE ACCESS FULL               | ROUND_UNIT                     |     10 |      1 |   0 |00:00:00.27 |    1100 |    108 |          |       |          |
|  11 |      VIEW                             |                                |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|* 12 |       FILTER                          |                                |     10 |        |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|  13 |        SORT GROUP BY                  |                                |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |  1024 |  1024 |          |
|  14 |         NESTED LOOPS                  |                                |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|  15 |          NESTED LOOPS                 |                                |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|  16 |           NESTED LOOPS                |                                |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|  17 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|* 18 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     10 |      1 |   0 |00:00:00.15 |      30 | 7 |       |       |          |
|  19 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 20 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  21 |           TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON     |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 22 |            INDEX UNIQUE SCAN          | PK_MAINUNIT_TRANSITION_REASON  |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 23 |          INDEX UNIQUE SCAN            | PK_MAINUNIT_TRANSITION         |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  24 |     NESTED LOOPS                      |                                |     10 |        |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|  25 |      NESTED LOOPS                     |                                |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|  26 |       TABLE ACCESS BY INDEX ROWID     | TRANSACTION_HISTORY            |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|* 27 |        INDEX RANGE SCAN               | TRANSACTION_HISTORY_IDX1       |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|* 28 |       INDEX UNIQUE SCAN               | PK_MAINUNIT_TRANSITION_REASON  |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 29 |      TABLE ACCESS BY INDEX ROWID      | MAINUNIT_TRANSITION_REASON     |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  30 |  SORT AGGREGATE                       |                                |     10 |      1 |     10 |00:00:00.36 |      71 |     21 |       |       |          |
|* 31 |   FILTER                              |                                |     10 |        |     10 |00:00:00.36 |      71 |     21 |       |       |          |
|  32 |    TABLE ACCESS BY INDEX ROWID        | NAD_DETAILS                    |     10 |      1 |     10 |00:00:00.15 |      31 |     14 |       |       |          |
|* 33 |     INDEX RANGE SCAN                  | NAD_DETAILS_IDX1               |     10 |      1 |     10 |00:00:00.06 |      22 |      6 |       |       |          |
|* 34 |    FILTER                             |                                |     10 |        |   0 |00:00:00.21 |      40 | 7 |       |       |          |
|* 35 |     TABLE ACCESS BY INDEX ROWID       | NAD_DETAILS                    |     10 |      1 |   0 |00:00:00.21 |      40 | 7 |       |       |          |
|* 36 |      INDEX UNIQUE SCAN                | PK_NAD_DETAILS                 |     10 |      1 |     10 |00:00:00.21 |      30 |      7 |       |       |          |
|* 37 |     FILTER                            |                                |      0 |        |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  38 |      HASH GROUP BY                    |                                |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |  1023K|  1023K|          |
|  39 |       TABLE ACCESS BY INDEX ROWID     | NAD_DETAILS                    |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 40 |        INDEX RANGE SCAN               | NAD_DETAILS_IDX1               |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 41 |  VIEW                                 |                                |      1 |     14 |     10 |00:00:42.51 |   74740 |  72089 |       |       |          |
|* 42 |   WINDOW SORT PUSHED RANK             |                                |      1 |     14 |     10 |00:00:39.69 |   72164 |  71862 |  9216 |  9216 | 8192  (0)|
|  43 |    NESTED LOOPS OUTER                 |                                |      1 |     14 |     10 |00:00:39.69 |   72164 |  71862 |       |       |          |
|* 44 |     HASH JOIN OUTER                   |                                |      1 |      5 |     10 |00:00:39.59 |   72132 |  71855 |   707K|   707K| 1169K (0)|
|  45 |      NESTED LOOPS OUTER               |                                |      1 |      3 |     10 |00:00:00.51 |     308 |     48 |       |       |          |
|  46 |       MERGE JOIN CARTESIAN            |                                |      1 |      3 |     10 |00:00:00.49 |     289 |     45 |       |       |          |
|  47 |        NESTED LOOPS                   |                                |      1 |      1 |   1 |00:00:00.37 |     276 |     32 |          |       |          |
|* 48 |         TABLE ACCESS BY INDEX ROWID   | MAINUNIT                       |      1 |      1 |   1 |00:00:00.35 |     274 |     30 |          |       |          |
|* 49 |          INDEX UNIQUE SCAN            | PK_MAINUNIT                    |      1 |      1 |   1 |00:00:00.01 |       2 | 2 |       |       |          |
|  50 |         TABLE ACCESS BY INDEX ROWID   | WEDB_SOURCE                    |      1 |      1 |   1 |00:00:00.02 |       2 | 2 |       |       |          |
|* 51 |          INDEX RANGE SCAN             | WEDB_SOURCE_BUID               |      1 |      1 |   1 |00:00:00.01 |       1 | 1 |       |       |          |
|  52 |        BUFFER SORT                    |                                |      1 |      3 |     10 |00:00:00.12 |      13 |     13 |  2048 |  2048 | 2048  (0)|
|  53 |         TABLE ACCESS BY INDEX ROWID   | NAD_LISTER                     |      1 |      3 |     10 |00:00:00.12 |      13 |     13 |       |       |          |
|* 54 |          INDEX RANGE SCAN             | IDX_MAINUNIT_ID2               |      1 |      3 |     10 |00:00:00.02 |       3 |      3 |       |       |          |
|  55 |       TABLE ACCESS BY INDEX ROWID     | UNIT_SOURCE                    |     10 |      1 |     10 |00:00:00.02 |      19 |      3 |       |       |          |
|* 56 |        INDEX UNIQUE SCAN              | PK_UNIT_SOURCE                 |     10 |      1 |     10 |00:00:00.01 |       9 |      2 |       |       |          |
|  57 |      VIEW                             | NAD_DETAILS_FH_V               |      1 |   3210K|   3210K|00:00:31.26 |   71824 |  71807 |       |       |          |
|* 58 |       HASH JOIN RIGHT SEMI            |                                |      1 |   3210K|   3210K|00:00:26.00 |   71824 |  71807 |    68M|  7188K|  111M (0)|
|  59 |        INDEX FAST FULL SCAN           | PK_NAD_LISTER                  |      1 |   2423K|   2423K|00:00:03.69 |    5453 |   5440 |       |       |          |
|  60 |        TABLE ACCESS FULL              | NAD_DETAILS                    |      1 |   3210K|   3210K|00:00:04.23 |   66371 |  66367 |       |       |          |
|* 61 |     INDEX RANGE SCAN                  | WEDB_MAN_UNIT_N5               |     10 |      3 |     10 |00:00:00.10 |      32 |      7 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("FTH"."NAD_ID"="FH"."ID")
   8 - access("FH"."ID"=:B1)
   9 - access("W1"."ID"="FH"."MAINUNIT_ID")
  10 - filter("RR"."ID"="FH"."APPLIED_ROUND_UNIT")
  12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
  18 - access("NAD_ID"=:B1)
  20 - access("FHIS"."NAD_ID"=:B1)
       filter("FHIS"."NAD_ID"="NAD_ID")
  22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
  27 - access("FHIS"."NAD_ID"=:B1)
  28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
  31 - filter( IS NULL)
  33 - access("OD"."NAD_ID"=:B1)
  34 - filter( IS NOT NULL)
  35 - filter("SYSTEM_NUMBER" IS NULL)
  36 - access("ID"=:B1)
  37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
  40 - access("NAD_ID"=:B1)
  41 - filter(("RN">=1 AND "RN"<=20))
  42 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
  44 - access("OD"."NAD_ID"="FH"."ID")
  48 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
  49 - access("WG"."ID"=262)
  51 - access("GBM"."BU_ID"="MANAGECONFIG"."BU_ID"())
  54 - access("FH"."MAINUNIT_ID"=262 AND "FH"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "FH"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
  56 - access("FS"."ID"="FH"."UNIT_SOURCE")
  58 - access("FHH"."ID"="OD"."NAD_ID")
  61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
       filter("OH"."SYSTEM_NO">0)


With /*+ use_hash(OH OD) */ hint took more time.

Plan hash value: 182466403

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                                |      1 |        |     10 |00:01:36.65 |     175K|    172K|       |       |          |
|   1 |  SORT AGGREGATE                       |                                |     10 |      1 |     10 |00:00:00.28 |    1212 |    115 |       |       |          |
|   2 |   VIEW                                | TRANSACTIONS_VW                |     10 |      2 |     10 |00:00:00.28 |    1212 |    115 |       |       |          |
|   3 |    UNION-ALL                          |                                |     10 |        |     10 |00:00:00.28 |    1212 |    115 |       |       |          |
|*  4 |     HASH JOIN OUTER                   |                                |     10 |      1 |     10 |00:00:00.28 |    1182 |    115 |  1035K|  1035K|  418K (0)|
|   5 |      NESTED LOOPS OUTER               |                                |     10 |      1 |     10 |00:00:00.21 |    1152 |    108 |       |       |          |
|   6 |       NESTED LOOPS                    |                                |     10 |      1 |     10 |00:00:00.01 |      52 |      0 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID    | NAD_LISTER                     |     10 |      1 |     10 |00:00:00.01 |      37 |      0 |       |       |          |
	|*  8 |         INDEX UNIQUE SCAN                                      | PK_NAD_LISTER                  |     10 |      1 |     10 |00:00:00.01 |      27 |      0 |       |       |          |
	|*  9 |        INDEX UNIQUE SCAN                                       | PK_MAINUNIT                   |     10 |   1388 |     10 |00:00:00.01 |      15 |      0 |       |       |          |
|* 10 |       TABLE ACCESS FULL               | ROUND_UNIT                     |     10 |      1 |   0 |00:00:00.21 |    1100 |    108 |          |       |          |
|  11 |      VIEW                             |                                |     10 |      1 |   0 |00:00:00.07 |      30 | 7 |       |       |          |
|* 12 |       FILTER                          |                                |     10 |        |   0 |00:00:00.07 |      30 | 7 |       |       |          |
|  13 |        SORT GROUP BY                  |                                |     10 |      1 |   0 |00:00:00.07 |      30 | 7 |  1024 |  1024 |          |
|  14 |         NESTED LOOPS                  |                                |     10 |      1 |   0 |00:00:00.07 |      30 | 7 |       |       |          |
|  15 |          NESTED LOOPS                 |                                |     10 |      1 |   0 |00:00:00.07 |      30 | 7 |       |       |          |
|  16 |           NESTED LOOPS                |                                |     10 |      1 |   0 |00:00:00.06 |      30 | 7 |       |       |          |
|  17 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |     10 |      1 |   0 |00:00:00.06 |      30 | 7 |       |       |          |
|* 18 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |     10 |      1 |   0 |00:00:00.06 |      30 | 7 |       |       |          |
|  19 |            TABLE ACCESS BY INDEX ROWID| TRANSACTION_HISTORY            |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 20 |             INDEX RANGE SCAN          | TRANSACTION_HISTORY_IDX1       |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  21 |           TABLE ACCESS BY INDEX ROWID | MAINUNIT_TRANSITION_REASON     |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 22 |            INDEX UNIQUE SCAN          | PK_MAINUNIT_TRANSITION_REASON  |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 23 |          INDEX UNIQUE SCAN            | PK_MAINUNIT_TRANSITION         |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  24 |     NESTED LOOPS                      |                                |     10 |        |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|  25 |      NESTED LOOPS                     |                                |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|  26 |       TABLE ACCESS BY INDEX ROWID     | TRANSACTION_HISTORY            |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|* 27 |        INDEX RANGE SCAN               | TRANSACTION_HISTORY_IDX1       |     10 |      1 |   0 |00:00:00.01 |      30 | 0 |       |       |          |
|* 28 |       INDEX UNIQUE SCAN               | PK_MAINUNIT_TRANSITION_REASON  |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 29 |      TABLE ACCESS BY INDEX ROWID      | MAINUNIT_TRANSITION_REASON     |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  30 |  SORT AGGREGATE                       |                                |     10 |      1 |     10 |00:00:00.20 |      71 |     21 |       |       |          |
|* 31 |   FILTER                              |                                |     10 |        |     10 |00:00:00.20 |      71 |     21 |       |       |          |
|  32 |    TABLE ACCESS BY INDEX ROWID        | NAD_DETAILS                    |     10 |      1 |     10 |00:00:00.12 |      31 |     14 |       |       |          |
|* 33 |     INDEX RANGE SCAN                  | NAD_DETAILS_IDX1               |     10 |      1 |     10 |00:00:00.05 |      22 |      6 |       |       |          |
|* 34 |    FILTER                             |                                |     10 |        |   0 |00:00:00.08 |      40 | 7 |       |       |          |
|* 35 |     TABLE ACCESS BY INDEX ROWID       | NAD_DETAILS                    |     10 |      1 |   0 |00:00:00.08 |      40 | 7 |       |       |          |
|* 36 |      INDEX UNIQUE SCAN                | PK_NAD_DETAILS                 |     10 |      1 |     10 |00:00:00.08 |      30 |      7 |       |       |          |
|* 37 |     FILTER                            |                                |      0 |        |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|  38 |      HASH GROUP BY                    |                                |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |  1023K|  1023K|          |
|  39 |       TABLE ACCESS BY INDEX ROWID     | NAD_DETAILS                    |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 40 |        INDEX RANGE SCAN               | NAD_DETAILS_IDX1               |      0 |      1 |   0 |00:00:00.01 |       0 | 0 |       |       |          |
|* 41 |  VIEW                                 |                                |      1 |     14 |     10 |00:01:36.65 |     175K|    172K|       |       |          |
|* 42 |   WINDOW SORT PUSHED RANK             |                                |      1 |     14 |     10 |00:01:34.96 |     172K|    172K|  9216 |  9216 | 8192  (0)|
|* 43 |    HASH JOIN OUTER                    |                                |      1 |     14 |     10 |00:01:34.96 |     172K|    172K|   696K|   696K| 1145K (0)|
|* 44 |     HASH JOIN OUTER                   |                                |      1 |      5 |     10 |00:00:39.14 |   72132 |  71855 |   707K|   707K| 1160K (0)|
|  45 |      NESTED LOOPS OUTER               |                                |      1 |      3 |     10 |00:00:00.57 |     308 |     48 |       |       |          |
|  46 |       MERGE JOIN CARTESIAN            |                                |      1 |      3 |     10 |00:00:00.54 |     289 |     45 |       |       |          |
|  47 |        NESTED LOOPS                   |                                |      1 |      1 |   1 |00:00:00.37 |     276 |     32 |          |       |          |
|* 48 |         TABLE ACCESS BY INDEX ROWID   | MAINUNIT                       |      1 |      1 |   1 |00:00:00.34 |     274 |     30 |          |       |          |
|* 49 |          INDEX UNIQUE SCAN            | PK_MAINUNIT                    |      1 |      1 |   1 |00:00:00.01 |       2 | 2 |       |       |          |
|  50 |         TABLE ACCESS BY INDEX ROWID   | WEDB_SOURCE                    |      1 |      1 |   1 |00:00:00.03 |       2 | 2 |       |       |          |
|* 51 |          INDEX RANGE SCAN             | WEDB_SOURCE_BUID               |      1 |      1 |   1 |00:00:00.02 |       1 | 1 |       |       |          |
|  52 |        BUFFER SORT                    |                                |      1 |      3 |     10 |00:00:00.17 |      13 |     13 |  2048 |  2048 | 2048  (0)|
|  53 |         TABLE ACCESS BY INDEX ROWID   | NAD_LISTER                     |      1 |      3 |     10 |00:00:00.17 |      13 |     13 |       |       |          |
|* 54 |          INDEX RANGE SCAN             | IDX_MAINUNIT_ID2               |      1 |      3 |     10 |00:00:00.02 |       3 |      3 |       |       |          |
|  55 |       TABLE ACCESS BY INDEX ROWID     | UNIT_SOURCE                    |     10 |      1 |     10 |00:00:00.03 |      19 |      3 |       |       |          |
|* 56 |        INDEX UNIQUE SCAN              | PK_UNIT_SOURCE                 |     10 |      1 |     10 |00:00:00.01 |       9 |      2 |       |       |          |
|  57 |      VIEW                             | NAD_DETAILS_FH_V               |      1 |   3210K|   3210K|00:00:30.83 |   71824 |  71807 |       |       |          |
|* 58 |       HASH JOIN RIGHT SEMI            |                                |      1 |   3210K|   3210K|00:00:25.60 |   71824 |  71807 |    68M|  7188K|  111M (0)|
|  59 |        INDEX FAST FULL SCAN           | PK_NAD_LISTER                  |      1 |   2423K|   2423K|00:00:03.63 |    5453 |   5440 |       |       |          |
|  60 |        TABLE ACCESS FULL              | NAD_DETAILS                    |      1 |   3210K|   3210K|00:00:04.07 |   66371 |  66367 |       |       |          |
|* 61 |     INDEX FAST FULL SCAN              | WEDB_MAN_UNIT_N5               |      1 |   8569K|   8507K|00:00:34.79 |     100K|    100K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

      4 - access("FTH"."NAD_ID"="FH"."ID")
   8 - access("FH"."ID"=:B1)
   9 - access("W1"."ID"="FH"."MAINUNIT_ID")
  10 - filter("RR"."ID"="FH"."APPLIED_ROUND_UNIT")
  12 - filter("FHIS"."MODIFIED_DATE"=MIN("MODIFIED_DATE"))
  18 - access("NAD_ID"=:B1)
  20 - access("FHIS"."NAD_ID"=:B1)
       filter("FHIS"."NAD_ID"="NAD_ID")
  22 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  23 - access("WT"."ID"="WTR"."MAINUNIT_TRANSITION_ID")
  27 - access("FHIS"."NAD_ID"=:B1)
  28 - access("WTR"."ID"="FHIS"."TRANSITION_REASON_ID")
  29 - filter("WTR"."MAINUNIT_TRANSITION_ID" IS NOT NULL)
  31 - filter( IS NULL)
  33 - access("OD"."NAD_ID"=:B1)
  34 - filter( IS NOT NULL)
  35 - filter("SYSTEM_NUMBER" IS NULL)
  36 - access("ID"=:B1)
  37 - filter(("RENDER_NO"=:B1 AND COUNT(*)>1))
  40 - access("NAD_ID"=:B1)
  41 - filter(("RN">=1 AND "RN"<=20))
  42 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
  44 - access("OD"."NAD_ID"="FH"."ID")
  48 - filter("WG"."BUID"="MANAGECONFIG"."BU_ID"())
  49 - access("WG"."ID"=262)
  51 - access("GBM"."BU_ID"="MANAGECONFIG"."BU_ID"())
  54 - access("FH"."MAINUNIT_ID"=262 AND "FH"."DATE_REACHED">=TIMESTAMP' 2012-03-01 00:00:00' AND "FH"."DATE_REACHED"<TIMESTAMP' 2012-09-01 00:00:00')
  56 - access("FS"."ID"="FH"."UNIT_SOURCE")
  58 - access("FHH"."ID"="OD"."NAD_ID")
  61 - access("OH"."SYSTEM_NO"="OD"."SYSTEM_NUMBER")
       filter("OH"."SYSTEM_NO">0)


Please help me.

Thanks.

[Updated on: Sun, 24 November 2013 07:03]

Report message to a moderator

Re: Please help me my query performance is bad. [message #601692 is a reply to message #601680] Sun, 24 November 2013 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=Oracle+bind+variable+peeking
Re: Please help me my query performance is bad. [message #601701 is a reply to message #601692] Sun, 24 November 2013 11:42 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Could you please provide the exact information
what should I do to improve the performance of the query.

Thanks.
Re: Please help me my query performance is bad. [message #601703 is a reply to message #601701] Sun, 24 November 2013 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I decline your request to do your job for you.
Re: Please help me my query performance is bad. [message #601706 is a reply to message #601680] Sun, 24 November 2013 12:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ramya,
If you have noticed that the plan hash value has changed for the same SQL you used with the hint. It is due to the bind variables you are using in your query through frontend. So, now you have a different challenge to handle the adaptive cursor sharing technique with bind variables. With the skewness of data you would see the execution plan to vary. So, start thinking all over again in a new direction as pointed out to you.

Are the bind vaiables being used only for date fields or any other fields too?

[Updated on: Sun, 24 November 2013 22:41]

Report message to a moderator

Re: Please help me my query performance is bad. [message #601722 is a reply to message #601706] Sun, 24 November 2013 23:51 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Bind variables are using for these three fields.

WHERE   NL.MAINUNIT_ID = :MAINUNIT_ID 
AND NL.DATE_REACHED >= TRUNC (:DATE_FROM)
AND NL.DATE_REACHED < TRUNC (:DATE_TO)+1);

Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601734 is a reply to message #601722] Mon, 25 November 2013 02:15 Go to previous messageGo to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi,

Please help me.

Thanks.
Re: Please help me my query performance is bad. [message #601759 is a reply to message #601734] Mon, 25 November 2013 06:18 Go to previous message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi All,

I am really struggling lot.
Kindly please help me.

Thanks.
Previous Topic: SQL & PL/SQL
Next Topic: fast query
Goto Forum:
  


Current Time: Fri Mar 29 04:08:57 CDT 2024