Home » RDBMS Server » Performance Tuning » Please help me on tune this query (Oracle 11g)
Please help me on tune this query [message #572358] Tue, 11 December 2012 03:27 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

Please hrlp me to tune this query.
To below query is taking nearly 1 hour time.
The inner query before using ROW_NUMBER is fetching records.
I have gathered STATISTICS for all tables and indexes and I have rebuild the indexes
then also the query is taking one hour time.

The number records in all tables.

SELECT COUNT(*) FROM ORL.FAX_TRANSITION_V OFT;--3190748

SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL OD; --991595

SELECT COUNT(*) FROM ORL.FAX_HEADER FH;--839835

SELECT COUNT(*) FROM ORL.WORKGROUP WG;--392

SELECT COUNT(*) FROM APPS_GLOBAL.GLOBAL_BU_MAPPING GBM;--9

SELECT COUNT(*) FROM APPS_JP.GEDIS_OFFER_HEADER OH;--5185757

SELECT COUNT(*) FROM ORL.FAX_SOURCE FS;--227



I have provided the explain plan.

  2  SELECT FAX_LIST2.*
  3    FROM (SELECT ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
  4            FROM (SELECT FH.ID AS FAX_ID,
  5                         FS.ACCOUNT_TYPE_CODE,
  6                         FS.PRIORITY_CODE,
  7                         FS.FAX_NUMBER AS Fax_Dest_Num,
  8                         FS.DESCRIPTION,
  9                         OD.ORDER_NUMBER AS ORDER_NUMBER,
 10                         OD.PURCHASE_ORDER_NUMBER,
 11                         OD.ID AS OD_ID,
 12                         OD.OFFER_NUMBER,
 13                         OD.VERSION_NO,
 14                         OD.SOURCE_OFFER_NO,
 15                         OD.OMEGA_ORDER_NUMBER,
 16                         OD.VALUE,
 17                         OD.IS_EMC_ORDER,
 18                         OD.CREATION_DATE,
 19                         OD.UPDATED_DATE,
 20                         OD.CREATED_BY,
 21                         OD.UPDATED_BY,
 22                         WG.NAME AS STATUS,
 23                         OH.ORDER_TYPE,
 24                         OH.ORDER_TYPE_ID,
 25                         OH.ORDER_DATE AS DATEORDERENTERED,
 26                         FH.IS_LOCKED,
 27                         FH.CUSTOMER_NUMBER,
 28                         FH.CUSTOMER_OMEGA_NUMBER,
 29                         FH.BU_FILE_LOCATION,
 30                         FH.CUSTOMER_NAME,
 31                         FH.GENIFAX_RECIPIENT_ID,
 32                         FROM_TZ (FH.DATE_RECEIVED, 'UTC')
 33                            AT TIME ZONE GBM.ORACLE_TZ_NAME
 34                            AS DATE_RECEIVED,
 35                         FH.ACCOUNT_TYPE,
 36                         FH.PAYMENT_TYPE,
 37                         FH.WORKGROUP_ID,
 38                         GBM.COUNTRY_NAME,
 39                         FROM_TZ ( (SELECT MAX (CHANGED_DATE)
 40                                      FROM ORL.FAX_TRANSITION_V OFT
 41                                     WHERE OFT.FAX_ID = FH.ID),
 42                                  'UTC')
 43                            AT TIME ZONE GBM.ORACLE_TZ_NAME
 44                            AS CHANGED_DATE,
 45                         NULL AS DATA_VALUE,
 46                         (SELECT SUM (VALUE)
 47                            FROM ORL.FAX_OFFER_DETAIL OD
 48                           WHERE OD.FAX_ID = FH.ID)
 49                            AS ORDER_VALUE
 50                    FROM ORL.FAX_HEADER FH
 51                         INNER JOIN ORL.WORKGROUP WG
 52                            ON (WG.ID = FH.WORKGROUP_ID)
 53                         INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
 54                            ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
 55                         LEFT JOIN
 56                         (SELECT DISTINCT OD.FAX_ID,
 57                                          OD.ORDER_NUMBER AS ORDER_NUMBER,
 58                                          OD.PURCHASE_ORDER_NUMBER,
 59                                          OD.ID,
 60                                          OD.OFFER_NUMBER,
 61                                          OD.VERSION_NO,
 62                                          OD.SOURCE_OFFER_NO,
 63                                          OD.OMEGA_ORDER_NUMBER,
 64                                          OD.VALUE,
 65                                          OD.IS_EMC_ORDER,
 66                                          OD.CREATION_DATE,
 67                                          OD.UPDATED_DATE,
 68                                          OD.CREATED_BY,
 69                                          OD.UPDATED_BY
 70                            FROM ORL.FAX_HEADER FHH
 71                                 INNER JOIN ORL.FAX_OFFER_DETAIL OD
 72                                    ON (OD.FAX_ID = FHH.ID)) OD
 73                            ON (OD.FAX_ID = FH.ID)
 74                         LEFT JOIN
 75                         APPS_JP.GEDIS_OFFER_HEADER OH
 76                            ON (    OH.ORDER_NO = OD.ORDER_NUMBER
 77                                AND OH.ORDER_NO != 0)
 78                         LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
 79                   WHERE     FH.WORKGROUP_ID = 245
 80                         AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
 81                         AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
 82                                                                         ) FAX_LIST) FAX_LIST2
 83   WHERE RN BETWEEN 1 AND 20;

Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |  1504 |  3708K|       | 12475   (1)|
|   1 |  SORT AGGREGATE                     |                             |     1 |    26 |       |         |
|   2 |   VIEW                              | FAX_TRANSITION_V            |     4 |   104 |       |    42  (10)|
|   3 |    SORT UNIQUE                      |                             |     4 |  2190 |       |    42  (53)|
|   4 |     UNION-ALL                       |                             |       |       |       |         |
|   5 |      NESTED LOOPS OUTER             |                             |     1 |  1443 |       |    22  (10)|
|   6 |       NESTED LOOPS OUTER            |                             |     1 |  1403 |       |    21  (10)|

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

|   7 |        NESTED LOOPS                 |                             |     1 |   221 |       |  4   (0)|
|   8 |         TABLE ACCESS BY INDEX ROWID | FAX_HEADER                  |     1 |   168 |       |  3   (0)|
|*  9 |          INDEX UNIQUE SCAN          | PK_FAX_HEADER               |     1 |       |       |  2   (0)|
|* 10 |         INDEX RANGE SCAN            | IDX_WRKGRP_COMP_3           |   392 | 20776 |       |  1   (0)|
|* 11 |        VIEW                         |                             |     1 |  1182 |       |    17  (12)|
|* 12 |         FILTER                      |                             |       |       |       |         |
|  13 |          SORT GROUP BY              |                             |     1 |   146 |       |    17  (12)|
|  14 |           NESTED LOOPS              |                             |     3 |   438 |       |    16   (7)|
|  15 |            NESTED LOOPS             |                             |     3 |   243 |       |    13   (8)|
|  16 |             NESTED LOOPS            |                             |     3 |   189 |       |    10  (10)|
|* 17 |              HASH JOIN              |                             |     3 |   135 |       |  7  (15)|

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

|* 18 |               INDEX RANGE SCAN      | IDX_FAX_TRANS_HIST_COMP_3   |     3 |    45 |       |  3   (0)|
|* 19 |               INDEX RANGE SCAN      | IDX_FAX_TRANS_HIST_COMP_3   |     3 |    90 |       |  3   (0)|
|* 20 |              INDEX RANGE SCAN       | IDX_WRKG_TRNS_RSN_COMP_3    |     1 |    18 |       |  1   (0)|
|* 21 |             INDEX RANGE SCAN        | IDX_WRKGRP_TRANS_COMP_3     |     1 |    18 |       |  1   (0)|
|* 22 |            INDEX RANGE SCAN         | IDX_WRKGRP_COMP_3           |     1 |    65 |       |  1   (0)|
|* 23 |       INDEX RANGE SCAN              | IDX_ROUTING_RULE_COMP_2     |     1 |    40 |       |  1   (0)|
|  24 |      NESTED LOOPS                   |                             |     3 |   747 |       |    18   (0)|
|  25 |       NESTED LOOPS                  |                             |     3 |   588 |       |    15   (0)|
|  26 |        NESTED LOOPS                 |                             |     3 |   429 |       |    12   (0)|
|  27 |         NESTED LOOPS                |                             |     3 |   402 |       |  9   (0)|
|  28 |          TABLE ACCESS BY INDEX ROWID| FAX_TRANSITION_HISTORY      |     3 |   195 |       |  6   (0)|

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

|* 29 |           INDEX RANGE SCAN          | FAX_TRANSITION_HISTORY_IDX1 |     3 |       |       |  3   (0)|
|* 30 |          INDEX RANGE SCAN           | IDX_WRKG_TRNS_RSN_COMP_3    |     1 |    69 |       |  1   (0)|
|* 31 |         INDEX RANGE SCAN            | IDX_WRKGRP_TRANS_COMP_3     |     1 |     9 |       |  1   (0)|
|* 32 |        INDEX RANGE SCAN             | IDX_WRKGRP_COMP_3           |     1 |    53 |       |  1   (0)|
|* 33 |       INDEX RANGE SCAN              | IDX_WRKGRP_COMP_3           |     1 |    53 |       |  1   (0)|
|  34 |  SORT AGGREGATE                     |                             |     1 |    10 |       |         |
|  35 |   TABLE ACCESS BY INDEX ROWID       | FAX_OFFER_DETAIL            |     2 |    20 |       |  5   (0)|
|* 36 |    INDEX RANGE SCAN                 | FAX_OFFER_DETAIL_IDX1       |     2 |       |       |  3   (0)|
|* 37 |  VIEW                               |                             |  1504 |  3708K|       | 12475   (1)|
|* 38 |   WINDOW SORT PUSHED RANK           |                             |  1504 |  1111K|  1216K| 12475   (1)|
|  39 |    NESTED LOOPS OUTER               |                             |  1504 |  1111K|       | 12232   (1)|

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

|  40 |     NESTED LOOPS OUTER              |                             |  1148 |   812K|       |  8919   (1)|
|* 41 |      HASH JOIN                      |                             |   745 |   435K|       |  6286   (2)|
|* 42 |       TABLE ACCESS FULL             | GLOBAL_BU_MAPPING           |     1 |    29 |       |  7   (0)|
|* 43 |       HASH JOIN RIGHT OUTER         |                             |  1583 |   879K|       |  6278   (2)|
|  44 |        TABLE ACCESS FULL            | FAX_SOURCE                  |   227 | 50848 |       |  5   (0)|
|  45 |        NESTED LOOPS                 |                             |  1583 |   533K|       |  6273   (2)|
|* 46 |         TABLE ACCESS BY INDEX ROWID | WORKGROUP                   |     1 |    35 |       |  1   (0)|
|* 47 |          INDEX UNIQUE SCAN          | PK_WORKGROUP                |     1 |       |       |  0   (0)|
|* 48 |         TABLE ACCESS BY INDEX ROWID | FAX_HEADER                  |  1583 |   425K|       |  6272   (2)|
|* 49 |          INDEX FULL SCAN            | IDX_FAX_HEADER_COMP_5       |  2100 |       |       |  5682   (2)|
|  50 |      TABLE ACCESS BY INDEX ROWID    | FAX_OFFER_DETAIL            |     2 |   254 |       |  4   (0)|

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------

|* 51 |       INDEX RANGE SCAN              | FAX_OFFER_DETAIL_IDX1       |     2 |       |       |  2   (0)|
|  52 |     TABLE ACCESS BY INDEX ROWID     | GEDIS_OFFER_HEADER          |     1 |    32 |       |  3   (0)|
|* 53 |      INDEX RANGE SCAN               | GEDIS_ORDER_HEADER_N2       |     1 |       |       |  2   (0)|
----------------------------------------------------------------------------------------------------------------

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

   9 - access("FH"."ID"=:B1)
  10 - access("W1"."ID"="FH"."WORKGROUP_ID")
  11 - filter("FTH"."FAX_ID"(+)="FH"."ID")

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------

  12 - filter("CHANGED_DATE"=MIN("CHANGED_DATE"))
  17 - access("FAX_ID"="FAX_ID")
  18 - access("FAX_ID"=:B1)
  19 - access("FAX_ID"=:B1)
  20 - access("WTR"."ID"="TRANSITION_REASON_ID")
  21 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
  22 - access("W"."ID"="WT"."CURRENT_WORKGROUP_ID")
  23 - access("RR"."ID"(+)="FH"."APPLIED_ROUTING_RULE")
  29 - access("FAX_ID"=:B1)
  30 - access("WTR"."ID"="TRANSITION_REASON_ID")
  31 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

  32 - access("WT"."CURRENT_WORKGROUP_ID"="FROMW"."ID")
  33 - access("WT"."NEXT_WORKGROUP_ID"="TOW"."ID")
  36 - access("OD"."FAX_ID"=:B1)
  37 - filter("RN">=1 AND "RN"<=20)
  38 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
  41 - access("GBM"."BU_ID"="WG"."BUID")
  42 - filter("GBM"."BU_ID"=3535)
  43 - access("FS"."ID"(+)="FH"."FAX_SOURCE")
  46 - filter("WG"."BUID"=3535)
  47 - access("WG"."ID"=245)
  48 - filter("FH"."WORKGROUP_ID"=245)

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

  49 - filter(TRUNC(INTERNAL_FUNCTION("FH"."DATE_RECEIVED"))>=TO_DATE(' 2010-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("FH"."DATE_RECEIVED"))<=TO_DATE(' 2012-12-04
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  51 - access("OD"."FAX_ID"(+)="FH"."ID")
       filter("OD"."FAX_ID"(+) IS NOT NULL)
  53 - access("OH"."ORDER_NO"(+)="OD"."ORDER_NUMBER")
       filter("OH"."ORDER_NO"(+)<>0)


[Updated on: Wed, 30 October 2013 08:02] by Moderator

Report message to a moderator

Re: Please help me on tune this query [message #572361 is a reply to message #572358] Tue, 11 December 2012 03:28 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
The inner query before using ROW_NUMBER is fetching 974804 records.
Re: Please help me on tune this query [message #572366 is a reply to message #572361] Tue, 11 December 2012 04:05 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Could any body please help it's very urgent for me.

Thanks in advance.
Re: Please help me on tune this query [message #572367 is a reply to message #572366] Tue, 11 December 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But it is not for us and maybe we have no time to investigate on your problem.
If you ask in forum you have to be patient.
You pay us nothing, we owe you nothing.

Regards
Michel
Re: Please help me on tune this query [message #572369 is a reply to message #572367] Tue, 11 December 2012 04:17 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Sorry
I am requesting.
Re: Please help me on tune this query [message #572378 is a reply to message #572369] Tue, 11 December 2012 06:21 Go to previous messageGo to next message
burasami
Messages: 20
Registered: April 2010
Junior Member
Hi ,

Oracle would choose not to use an index sometimes, if you're reading a lot of rows, or your index is not selective, or you're using a column other than the leading one in a concatenated index. What about if you want to do a case-insensitive search? Something like:

WHERE UPPER(first_name) = 'JOHN'
This won't use an index on first_name. Why? Because Oracle would have to go and apply the UPPER function on ALL values in the index, so it might as well do the full table scan. This was such a common need that Oracle created the function-based index for this purpose.

remove trunc function or create function based index on
AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'


Thanks
Sami
Re: Please help me on tune this query [message #572381 is a reply to message #572378] Tue, 11 December 2012 06:58 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have create functional based index cost got reduced ,but still the query is taking more time.
Please help me.
Re: Please help me on tune this query [message #572383 is a reply to message #572381] Tue, 11 December 2012 07:04 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have changed this condition from
ON (    OH.ORDER_NO = OD.ORDER_NUMBER
                              AND OH.ORDER_NO != 0)
to

ON (    OH.ORDER_NO = OD.ORDER_NUMBER
                              AND OH.ORDER_NO >0)


Still it's taking time.
Re: Please help me on tune this query [message #572394 is a reply to message #572381] Tue, 11 December 2012 09:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
ajaykumarkona wrote on Tue, 11 December 2012 07:58
I have create functional based index cost got reduced ,but still the query is taking more time.
Please help me.


Now get rid of it. It is not needed. Create index on (FH.WORKGROUP_ID,FH.DATE_RECEIVED) if you don't have it and change

WHERE     FH.WORKGROUP_ID = 245
AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'


to:

WHERE     FH.WORKGROUP_ID = 245
AND FH.DATE_RECEIVED >= DATE '2010-01-01'
AND FH.DATE_RECEIVED <  DATE '2010-12-05'


Also,

SELECT MAX (CHANGED_DATE)
 40                                      FROM ORL.FAX_TRANSITION_V OFT
 41                                     WHERE OFT.FAX_ID = FH.ID


might benefit form having index on FAX_ID,CHANGED_DATE, if it is possible since, I assume, ORL.FAX_TRANSITION_V is a view and we don't know if both FAX_ID,CHANGED_DATE are coming from same table.

SY.
Re: Please help me on tune this query [message #572422 is a reply to message #572394] Tue, 11 December 2012 23:46 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have created index on (FH.WORKGROUP_ID,FH.DATE_RECEIVED) but cost got increased from previous.
And ORL.FAX_TRANSITION_V is a view.
Below is the view script.
Please help me.
CREATE OR REPLACE FORCE VIEW ORL.FAX_TRANSITION_V
(
   FAX_ID,
   FROM_WORKGROUP_ID,
   FROM_WORKGROUP_NAME,
   FROM_WORKGROUP_DESCRIPTION,
   TO_WORKGROUP_ID,
   TO_WORKGROUP_NAME,
   TO_WORKGROUP_DESCRIPTION,
   REASON_ID,
   REASON_DESCRIPTION,
   CHANGED_BY,
   CHANGED_DATE,
   COMMENTS,
   IMPERSONATED_BY
)
AS
   SELECT fh.ID AS fax_id,
          NULL AS from_workgroup_id,
          NULL AS from_workgroup_name,
          CASE
             WHEN fh.genifax_recipient_id IS NULL THEN TO_NCHAR ('N/A')
             ELSE TO_NCHAR ('ORL FEEDER STAGING')
          END
             AS from_workgroup_description,
          NVL (fth.ID, w1.ID) AS to_workgroup_id,
          NVL (fth.NAME, w1.NAME) AS to_workgroup_name,
          NVL (fth.description, w1.description) AS to_workgroup_description,
          NULL AS reason_id,
          CASE
             WHEN NVL (fh.applied_routing_rule, 0) = 0
             THEN
                TO_NCHAR ('Reroute')
             ELSE
                TO_NCHAR (
                   'Reroute By Rule:''' || rr.NAME || '''(#' || rr.ID || ')')
          END
             AS reason_description,
          CASE
             WHEN fh.genifax_recipient_id IS NULL THEN TO_NCHAR ('Admin')
             ELSE TO_NCHAR ('ORL FEEDER')
          END
             AS changed_by,
          fh.date_received AS changed_date,
          TO_NCHAR ('NO COMMENTS') AS comments,
          fh.impersonated_by
     FROM orl.fax_header fh
          INNER JOIN orl.workgroup w1 ON w1.ID = fh.workgroup_id
          LEFT JOIN orl.routing_rule rr ON rr.ID = fh.applied_routing_rule
          LEFT OUTER JOIN
          (SELECT fhis.fax_id AS fax_id,
                  w.ID,
                  w.NAME,
                  w.description
             FROM (SELECT ID
                     FROM orl.fax_transition_history
                    WHERE (changed_date, fax_id) IN
                             (  SELECT MIN (changed_date), fax_id
                                  FROM orl.fax_transition_history
                              GROUP BY fax_id)) gfh
                  INNER JOIN orl.fax_transition_history fhis
                     ON fhis.ID = gfh.ID
                  INNER JOIN orl.workgroup_transition_reason wtr
                     ON wtr.ID = fhis.transition_reason_id
                  INNER JOIN orl.workgroup_transition wt
                     ON wt.ID = wtr.workgroup_transition_id
                  INNER JOIN orl.workgroup w
                     ON w.ID = wt.current_workgroup_id) fth
             ON fth.fax_id = fh.ID
   UNION
   SELECT fhis.fax_id AS fax_id,
          fromw.ID AS from_workgroup_id,
          fromw.NAME AS from_workgroup_name,
          fromw.description AS from_workgroup_description,
          tow.ID AS to_workgroup_id,
          tow.NAME AS to_workgroup_name,
          tow.description AS to_workgroup_description,
          wtr.ID AS reason_id,
          wtr.reason AS reason_description,
          fhis.changed_by,
          fhis.changed_date,
          fhis.comments,
          fhis.impersonated_by
     FROM (SELECT fax_id, ID FROM orl.fax_transition_history) gfh
          INNER JOIN
          orl.fax_transition_history fhis
             INNER JOIN
             orl.workgroup_transition_reason wtr
                INNER JOIN
                orl.workgroup_transition wt
                   INNER JOIN orl.workgroup fromw
                      ON wt.current_workgroup_id = fromw.ID
                   INNER JOIN orl.workgroup tow
                      ON wt.next_workgroup_id = tow.ID
                   ON wt.ID = wtr.workgroup_transition_id
                ON wtr.ID = fhis.transition_reason_id
             ON fhis.ID = gfh.ID
   ORDER BY fax_id, changed_date ASC;
Re: Please help me on tune this query [message #572427 is a reply to message #572422] Wed, 12 December 2012 00:53 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Any body could please help me.
Re: Please help me on tune this query [message #572453 is a reply to message #572427] Wed, 12 December 2012 03:13 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,
I have modified my query as below and I have created functional based inex on

AND TRUNC (FH.DATE_RECEIVED) >=TRUNC (:DATE_FROM)
AND TRUNC (FH.DATE_RECEIVED) <=TRUNC (:DATE_TO)


But still the query is executing for morethan one hour.
Please help me how to improve the performance

SQL> explain plan for
  2   WITH OD AS (SELECT /*+ INLINE */ DISTINCT OD.FAX_ID,
  3                                          OD.ORDER_NUMBER AS ORDER_NUMBER,
  4                                          OD.PURCHASE_ORDER_NUMBER,
  5                                          OD.ID,
  6                                          OD.OFFER_NUMBER,
  7                                          OD.VERSION_NO,
  8                                          OD.SOURCE_OFFER_NO,
  9                                          OD.OMEGA_ORDER_NUMBER,
 10                                          OD.VALUE,
 11                                          OD.IS_EMC_ORDER,
 12                                          OD.CREATION_DATE,
 13                                          OD.UPDATED_DATE,
 14                                          OD.CREATED_BY,
 15                                          OD.UPDATED_BY
 16                            FROM ORL.FAX_HEADER FHH
 17                                 INNER JOIN ORL.FAX_OFFER_DETAIL OD
 18                                    ON (OD.FAX_ID = FHH.ID))
 19  SELECT FAX_LIST2.*
 20    FROM (SELECT /*+ INDEX_ASC(FH PK_FAX_HEADER) NOPARALLEL_INDEX(FH PK_FAX_HEADER) */ ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
 21            FROM (SELECT FH.ID AS FAX_ID,
 22                         FS.ACCOUNT_TYPE_CODE,
 23                         FS.PRIORITY_CODE,
 24                         FS.FAX_NUMBER AS Fax_Dest_Num,
 25                         FS.DESCRIPTION,
 26                         OD.ORDER_NUMBER AS ORDER_NUMBER,
 27                         OD.PURCHASE_ORDER_NUMBER,
 28                         OD.ID AS OD_ID,
 29                         OD.OFFER_NUMBER,
 30                         OD.VERSION_NO,
 31                         OD.SOURCE_OFFER_NO,
 32                         OD.OMEGA_ORDER_NUMBER,
 33                         OD.VALUE,
 34                         OD.IS_EMC_ORDER,
 35                         OD.CREATION_DATE,
 36                         OD.UPDATED_DATE,
 37                         OD.CREATED_BY,
 38                         OD.UPDATED_BY,
 39                         WG.NAME AS STATUS,
 40                         OH.ORDER_TYPE,
 41                         OH.ORDER_TYPE_ID,
 42                         OH.ORDER_DATE AS DATEORDERENTERED,
 43                         FH.IS_LOCKED,
 44                         FH.CUSTOMER_NUMBER,
 45                         FH.CUSTOMER_OMEGA_NUMBER,
 46                         FH.BU_FILE_LOCATION,
 47                         FH.CUSTOMER_NAME,
 48                         FH.GENIFAX_RECIPIENT_ID,
 49                         FROM_TZ (FH.DATE_RECEIVED, 'UTC')
 50                            AT TIME ZONE GBM.ORACLE_TZ_NAME
 51                            AS DATE_RECEIVED,
 52                         FH.ACCOUNT_TYPE,
 53                         FH.PAYMENT_TYPE,
 54                         FH.WORKGROUP_ID,
 55                         GBM.COUNTRY_NAME,
 56                         FROM_TZ ( (SELECT MAX (CHANGED_DATE)
 57                                      FROM ORL.FAX_TRANSITION_V OFT
 58                                     WHERE OFT.FAX_ID = FH.ID),
 59                                  'UTC')
 60                            AT TIME ZONE GBM.ORACLE_TZ_NAME
 61                            AS CHANGED_DATE,
 62                         NULL AS DATA_VALUE,
 63                         (SELECT SUM (VALUE)
 64                            FROM ORL.FAX_OFFER_DETAIL OD
 65                           WHERE OD.FAX_ID = FH.ID)
 66                            AS ORDER_VALUE
 67                    FROM ORL.FAX_HEADER FH
 68                         INNER JOIN ORL.WORKGROUP WG
 69                            ON (WG.ID = FH.WORKGROUP_ID)
 70                         INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
 71                            ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
 72                         LEFT JOIN
 73                          OD
 74                            ON (OD.FAX_ID = FH.ID)
 75                         LEFT JOIN
 76                         APPS_JP.GEDIS_OFFER_HEADER OH
 77                            ON (    OH.ORDER_NO = OD.ORDER_NUMBER
 78                                AND OH.ORDER_NO !=0)
 79                         LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
 80                   WHERE     FH.WORKGROUP_ID =245
 81                         AND TRUNC (FH.DATE_RECEIVED) >=TRUNC (:DATE_FROM)
 82                         AND TRUNC (FH.DATE_RECEIVED) <=TRUNC (:DATE_TO)
 83                                                                         ) FAX_LIST) FAX_LIST2
 84   WHERE RN BETWEEN 1 AND 20;


Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |  1504 |  3708K|       |  6625   (1)|
|   1 |  SORT AGGREGATE                     |                             |     1 |    26 |       |         |
|   2 |   VIEW                              | FAX_TRANSITION_V            |     4 |   104 |       |    42  (10)|
|   3 |    SORT UNIQUE                      |                             |     4 |  2190 |       |    42  (53)|
|   4 |     UNION-ALL                       |                             |       |       |       |         |
|   5 |      NESTED LOOPS OUTER             |                             |     1 |  1443 |       |    22  (10)|
|   6 |       NESTED LOOPS OUTER            |                             |     1 |  1403 |       |    21  (10)|

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

|   7 |        NESTED LOOPS                 |                             |     1 |   221 |       |  4   (0)|
|   8 |         TABLE ACCESS BY INDEX ROWID | FAX_HEADER                  |     1 |   168 |       |  3   (0)|
|*  9 |          INDEX UNIQUE SCAN          | PK_FAX_HEADER               |     1 |       |       |  2   (0)|
|* 10 |         INDEX RANGE SCAN            | IDX_WRKGRP_COMP_3           |   392 | 20776 |       |  1   (0)|
|* 11 |        VIEW                         |                             |     1 |  1182 |       |    17  (12)|
|* 12 |         FILTER                      |                             |       |       |       |         |
|  13 |          SORT GROUP BY              |                             |     1 |   146 |       |    17  (12)|
|  14 |           NESTED LOOPS              |                             |     3 |   438 |       |    16   (7)|
|  15 |            NESTED LOOPS             |                             |     3 |   243 |       |    13   (8)|
|  16 |             NESTED LOOPS            |                             |     3 |   189 |       |    10  (10)|
|* 17 |              HASH JOIN              |                             |     3 |   135 |       |  7  (15)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

|* 18 |               INDEX RANGE SCAN      | IDX_FAX_TRANS_HIST_COMP_3   |     3 |    45 |       |  3   (0)|
|* 19 |               INDEX RANGE SCAN      | IDX_FAX_TRANS_HIST_COMP_3   |     3 |    90 |       |  3   (0)|
|* 20 |              INDEX RANGE SCAN       | IDX_WRKG_TRNS_RSN_COMP_3    |     1 |    18 |       |  1   (0)|
|* 21 |             INDEX RANGE SCAN        | IDX_WRKGRP_TRANS_COMP_3     |     1 |    18 |       |  1   (0)|
|* 22 |            INDEX RANGE SCAN         | IDX_WRKGRP_COMP_3           |     1 |    65 |       |  1   (0)|
|* 23 |       INDEX RANGE SCAN              | IDX_ROUTING_RULE_COMP_2     |     1 |    40 |       |  1   (0)|
|  24 |      NESTED LOOPS                   |                             |     3 |   747 |       |    18   (0)|
|  25 |       NESTED LOOPS                  |                             |     3 |   588 |       |    15   (0)|
|  26 |        NESTED LOOPS                 |                             |     3 |   429 |       |    12   (0)|
|  27 |         NESTED LOOPS                |                             |     3 |   402 |       |  9   (0)|
|  28 |          TABLE ACCESS BY INDEX ROWID| FAX_TRANSITION_HISTORY      |     3 |   195 |       |  6   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

|* 29 |           INDEX RANGE SCAN          | FAX_TRANSITION_HISTORY_IDX1 |     3 |       |       |  3   (0)|
|* 30 |          INDEX RANGE SCAN           | IDX_WRKG_TRNS_RSN_COMP_3    |     1 |    69 |       |  1   (0)|
|* 31 |         INDEX RANGE SCAN            | IDX_WRKGRP_TRANS_COMP_3     |     1 |     9 |       |  1   (0)|
|* 32 |        INDEX RANGE SCAN             | IDX_WRKGRP_COMP_3           |     1 |    53 |       |  1   (0)|
|* 33 |       INDEX RANGE SCAN              | IDX_WRKGRP_COMP_3           |     1 |    53 |       |  1   (0)|
|  34 |  SORT AGGREGATE                     |                             |     1 |    10 |       |         |
|  35 |   TABLE ACCESS BY INDEX ROWID       | FAX_OFFER_DETAIL            |     2 |    20 |       |  5   (0)|
|* 36 |    INDEX RANGE SCAN                 | FAX_OFFER_DETAIL_IDX1       |     2 |       |       |  3   (0)|
|* 37 |  VIEW                               |                             |  1504 |  3708K|       |  6625   (1)|
|* 38 |   WINDOW SORT PUSHED RANK           |                             |  1504 |  1073K|  1216K|  6625   (1)|
|* 39 |    FILTER                           |                             |       |       |       |         |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
|  40 |     NESTED LOOPS OUTER              |                             |  1504 |  1073K|       |  6388   (1)|
|  41 |      NESTED LOOPS OUTER             |                             |  1148 |   783K|       |  3075   (1)|
|* 42 |       HASH JOIN RIGHT OUTER         |                             |   745 |   416K|       |   442   (1)|
|  43 |        TABLE ACCESS FULL            | FAX_SOURCE                  |   227 | 50848 |       |  5   (0)|
|  44 |        MERGE JOIN CARTESIAN         |                             |   745 |   253K|       |   436   (0)|
|  45 |         NESTED LOOPS                |                             |     1 |    64 |       |  8   (0)|
|* 46 |          TABLE ACCESS BY INDEX ROWID| WORKGROUP                   |     1 |    35 |       |  1   (0)|
|* 47 |           INDEX UNIQUE SCAN         | PK_WORKGROUP                |     1 |       |       |  0   (0)|
|* 48 |          TABLE ACCESS FULL          | GLOBAL_BU_MAPPING           |     1 |    29 |       |  7   (0)|
|  49 |         BUFFER SORT                 |                             |  1583 |   439K|       |   429   (0)|
|* 50 |          TABLE ACCESS BY INDEX ROWID| FAX_HEADER                  |  1583 |   439K|       |   428   (0)|

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

|* 51 |           INDEX RANGE SCAN          | IDX_DATE_RECEIVED_RAM       |  3779 |       |       |    12   (0)|
|  52 |       TABLE ACCESS BY INDEX ROWID   | FAX_OFFER_DETAIL            |     2 |   254 |       |  4   (0)|
|* 53 |        INDEX RANGE SCAN             | FAX_OFFER_DETAIL_IDX1       |     2 |       |       |  2   (0)|
|  54 |      TABLE ACCESS BY INDEX ROWID    | GEDIS_OFFER_HEADER          |     1 |    32 |       |  3   (0)|
|* 55 |       INDEX RANGE SCAN              | GEDIS_ORDER_HEADER_N2       |     1 |       |       |  2   (0)|
----------------------------------------------------------------------------------------------------------------

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

   9 - access("FH"."ID"=:B1)

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
  10 - access("W1"."ID"="FH"."WORKGROUP_ID")
  11 - filter("FTH"."FAX_ID"(+)="FH"."ID")
  12 - filter("CHANGED_DATE"=MIN("CHANGED_DATE"))
  17 - access("FAX_ID"="FAX_ID")
  18 - access("FAX_ID"=:B1)
  19 - access("FAX_ID"=:B1)
  20 - access("WTR"."ID"="TRANSITION_REASON_ID")
  21 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
  22 - access("W"."ID"="WT"."CURRENT_WORKGROUP_ID")
  23 - access("RR"."ID"(+)="FH"."APPLIED_ROUTING_RULE")
  29 - access("FAX_ID"=:B1)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

  30 - access("WTR"."ID"="TRANSITION_REASON_ID")
  31 - access("WT"."ID"="WTR"."WORKGROUP_TRANSITION_ID")
  32 - access("WT"."CURRENT_WORKGROUP_ID"="FROMW"."ID")
  33 - access("WT"."NEXT_WORKGROUP_ID"="TOW"."ID")
  36 - access("OD"."FAX_ID"=:B1)
  37 - filter("RN">=1 AND "RN"<=20)
  38 - filter(ROW_NUMBER() OVER ( ORDER BY "FH"."ID")<=20)
  39 - filter(TRUNC(TO_NUMBER(:DATE_FROM))<=TRUNC(TO_NUMBER(:DATE_TO)))
  42 - access("FS"."ID"(+)="FH"."FAX_SOURCE")
  46 - filter("WG"."BUID"=3535)
  47 - access("WG"."ID"=245)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

  48 - filter("GBM"."BU_ID"=3535)
  50 - filter("FH"."WORKGROUP_ID"=245)
  51 - access(TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))>=TRUNC(TO_NUMBER(:DATE_FROM)) AND
              TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))<=TRUNC(TO_NUMBER(:DATE_TO)))
  53 - access("OD"."FAX_ID"(+)="FH"."ID")
       filter("OD"."FAX_ID"(+) IS NOT NULL)
  55 - access("OH"."ORDER_NO"(+)="OD"."ORDER_NUMBER")
       filter("OH"."ORDER_NO"(+)<>0)

[Updated on: Wed, 30 October 2013 08:04] by Moderator

Report message to a moderator

Re: Please help me on tune this query [message #572465 is a reply to message #572453] Wed, 12 December 2012 04:30 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Any body could you please help me.
Re: Please help me on tune this query [message #572489 is a reply to message #572465] Wed, 12 December 2012 06:42 Go to previous messageGo to next message
Aditisinha
Messages: 1
Registered: December 2012
Junior Member
I would suggest to run sql tuning advisor for this query and check the recommendation.
Re: Please help me on tune this query [message #572510 is a reply to message #572489] Wed, 12 December 2012 11:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I believe either your stats are insufficient (not collected, or out of date, or no histogram when there is skew). I say this because given the rowcount estimates of the plan this query should go very fast.

OR

The rowcount estimates in the plan are way off, in which case you should be doing a full table scan and hash joins in most places and not nested loops join and index lookups.

You need to investigate the rowcount estimates of the query plan to see if they are accurate or not.

Decompose your query into its smaller parts to see where your time is going and where rowcounts do not match what the plan says. For exmampe:

set timing on

create table temp1
nologging
as
select
                       FH.IS_LOCKED,
                       FH.CUSTOMER_NUMBER,
                       FH.CUSTOMER_OMEGA_NUMBER,
                       FH.BU_FILE_LOCATION,
                       FH.CUSTOMER_NAME,
                       FH.GENIFAX_RECIPIENT_ID,
                       FH.ACCOUNT_TYPE,
                       FH.PAYMENT_TYPE,
                       FH.WORKGROUP_ID
from FROM ORL.FAX_HEADER FH
WHERE     FH.WORKGROUP_ID = 245
/

create table temp2
nologging
as
select
                       FH.IS_LOCKED,
                       FH.CUSTOMER_NUMBER,
                       FH.CUSTOMER_OMEGA_NUMBER,
                       FH.BU_FILE_LOCATION,
                       FH.CUSTOMER_NAME,
                       FH.GENIFAX_RECIPIENT_ID,
                       FH.ACCOUNT_TYPE,
                       FH.PAYMENT_TYPE,
                       FH.WORKGROUP_ID
           ,FROM_TZ ( (SELECT MAX (CHANGED_DATE)
                                    FROM ORL.FAX_TRANSITION_V OFT
                                   WHERE OFT.FAX_ID = FH.ID),
                                'UTC')
                          AT TIME ZONE GBM.ORACLE_TZ_NAME
                          AS CHANGED_DATE
           ,(SELECT SUM (VALUE)
                          FROM ORL.FAX_OFFER_DETAIL OD
                         WHERE OD.FAX_ID = FH.ID)
                          AS ORDER_VALUE
FROM ORL.FAX_HEADER FH
WHERE     FH.WORKGROUP_ID = 245
/

create table temp3
nologging
as
SELECT
                       FH.ID AS FAX_ID,
                       WG.NAME AS STATUS,
                       FH.IS_LOCKED,
                       FH.CUSTOMER_NUMBER,
                       FH.CUSTOMER_OMEGA_NUMBER,
                       FH.BU_FILE_LOCATION,
                       FH.CUSTOMER_NAME,
                       FH.GENIFAX_RECIPIENT_ID,
                       FROM_TZ (FH.DATE_RECEIVED, 'UTC')
                          AT TIME ZONE GBM.ORACLE_TZ_NAME
                          AS DATE_RECEIVED,
                       FH.ACCOUNT_TYPE,
                       FH.PAYMENT_TYPE,
                       FH.WORKGROUP_ID,
                       FROM_TZ ( (SELECT MAX (CHANGED_DATE)
                                    FROM ORL.FAX_TRANSITION_V OFT
                                   WHERE OFT.FAX_ID = FH.ID),
                                'UTC')
                          AT TIME ZONE GBM.ORACLE_TZ_NAME
                          AS CHANGED_DATE,
                       NULL AS DATA_VALUE,
                       (SELECT SUM (VALUE)
                          FROM ORL.FAX_OFFER_DETAIL OD
                         WHERE OD.FAX_ID = FH.ID)
                          AS ORDER_VALUE
                  FROM ORL.FAX_HEADER FH
                       INNER JOIN ORL.WORKGROUP WG ON (WG.ID = FH.WORKGROUP_ID)
/



And so on. Break down the query to small independent parts. Then run these parts for timings and counts and plans and add parts back together one at a time to see their affect. Done correctly this will show you where rowcounts are off and where time starts adding up.

Good luck, Kevin
Re: Please help me on tune this query [message #572544 is a reply to message #572510] Thu, 13 December 2012 01:37 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
If I comment ROW_NUMBER() and WHERE RN BETWEEN 1 AND 20.
Then I am getting the output in 2 minutes and it is returning 700000 records.
Please help me.

--SELECT FAX_LIST2.*
--  FROM (SELECT  ROW_NUMBER () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
--          FROM (

  -- ) 
--                                                                       
--                                                                       FAX_LIST) FAX_LIST2
-- WHERE RN BETWEEN 1 AND 20;
-- 
Re: Please help me on tune this query [message #572571 is a reply to message #572544] Thu, 13 December 2012 05:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have used FIRST_ROWS hint ,I got the output with in seconds only.
Could you please confirm to me is there any problem in using FIRST_ROWS hint in terms of data.
Please help me.

 WITH OD AS (SELECT /*+ INLINE */ OD.FAX_ID,
                                        OD.ORDER_NUMBER AS ORDER_NUMBER,
                                        OD.PURCHASE_ORDER_NUMBER,
                                        OD.ID,
                                        OD.OFFER_NUMBER,
                                        OD.VERSION_NO,
                                        OD.SOURCE_OFFER_NO,
                                        OD.OMEGA_ORDER_NUMBER,
                                        OD.VALUE,
                                        OD.IS_EMC_ORDER,
                                        OD.CREATION_DATE,
                                        OD.UPDATED_DATE,
                                        OD.CREATED_BY,
                                        OD.UPDATED_BY
                          FROM ORL.FAX_HEADER FHH
                               INNER JOIN ORL.FAX_OFFER_DETAIL OD
                                  ON (OD.FAX_ID = FHH.ID))
SELECT /*+ FIRST_ROWS */ FAX_LIST2.* 
  FROM (SELECT  RANK () OVER (ORDER BY FAX_LIST.FAX_ID) RN, FAX_LIST.*
          FROM (
          SELECT /*+ INDEX_ASC(FH PK_FAX_HEADER) NOPARALLEL_INDEX(FH PK_FAX_HEADER) */ FH.ID AS FAX_ID,
                       FS.ACCOUNT_TYPE_CODE,
                       FS.PRIORITY_CODE,
                       FS.FAX_NUMBER AS Fax_Dest_Num,
                       FS.DESCRIPTION,
                       OD.ORDER_NUMBER AS ORDER_NUMBER,
                       OD.PURCHASE_ORDER_NUMBER,
                       OD.ID AS OD_ID,
                       OD.OFFER_NUMBER,
                       OD.VERSION_NO,
                       OD.SOURCE_OFFER_NO,
                       OD.OMEGA_ORDER_NUMBER,
                       OD.VALUE,
                       OD.IS_EMC_ORDER,
                       OD.CREATION_DATE,
                       OD.UPDATED_DATE,
                       OD.CREATED_BY,
                       OD.UPDATED_BY,
                       WG.NAME AS STATUS,
                       OH.ORDER_TYPE,
                       OH.ORDER_TYPE_ID,
                       OH.ORDER_DATE AS DATEORDERENTERED,
                       FH.IS_LOCKED,
                       FH.CUSTOMER_NUMBER,
                       FH.CUSTOMER_OMEGA_NUMBER,
                       FH.BU_FILE_LOCATION,
                       FH.CUSTOMER_NAME,
                       FH.GENIFAX_RECIPIENT_ID,
                       FROM_TZ (FH.DATE_RECEIVED, 'UTC')
                          AT TIME ZONE GBM.ORACLE_TZ_NAME
                          AS DATE_RECEIVED,
                       FH.ACCOUNT_TYPE,
                       FH.PAYMENT_TYPE,
                       FH.WORKGROUP_ID,
                       GBM.COUNTRY_NAME,
                       FROM_TZ ( (SELECT MAX (CHANGED_DATE)
                                    FROM ORL.FAX_TRANSITION_V OFT
                                   WHERE OFT.FAX_ID = FH.ID),
                                'UTC')
                          AT TIME ZONE GBM.ORACLE_TZ_NAME
                          AS CHANGED_DATE,
                       NULL AS DATA_VALUE,
                       (SELECT SUM (VALUE)
                          FROM ORL.FAX_OFFER_DETAIL OD
                         WHERE OD.FAX_ID = FH.ID)
                          AS ORDER_VALUE
                  FROM ORL.FAX_HEADER FH
                       INNER JOIN ORL.WORKGROUP WG
                          ON (WG.ID = FH.WORKGROUP_ID)
                       INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
                          ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
                       LEFT JOIN
                        OD
                          ON (OD.FAX_ID = FH.ID)
                       LEFT JOIN
                       APPS_JP.GEDIS_OFFER_HEADER OH
                          ON (    OH.ORDER_NO = OD.ORDER_NUMBER
                              AND OH.ORDER_NO !=0)
                       LEFT JOIN ORL.FAX_SOURCE FS ON (FS.ID = FH.FAX_SOURCE)
                 WHERE     FH.WORKGROUP_ID =245
                       AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
                       AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2011'
                                                                       ) FAX_LIST) FAX_LIST2
 WHERE RN BETWEEN 1 AND 20;

Re: Please help me on tune this query [message #572578 is a reply to message #572510] Thu, 13 December 2012 06:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Kevin Meade wrote on Wed, 12 December 2012 17:43

Decompose your query into its smaller parts to see where your time is going and where rowcounts do not match what the plan says


If it is on 11g with the right options you can massively cheat take a significant shortcut here with the sql monitoring features.

Just for mentioning interest as I'm a massive fan of query decomposition, and it saved me a boatload of time.

It works from the cmd line with a fair bit of faffing and config, but there's also a great bit of grid/EM you can use for it if you don't want to script it up.
Re: Please help me on tune this query [message #572590 is a reply to message #572578] Thu, 13 December 2012 07:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Roachcoach, that sounds very promising. Can you provide additional details on what "RIGHT OPTIONS" means and possibily even forward an example session of doin it via email to me?

Kevin
Re: Please help me on tune this query [message #572592 is a reply to message #572590] Thu, 13 December 2012 08:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The "right options" was to do with licencing requirements (I imagine it's the tuning pack, but check!). We've got just about everything and it's dog in here, but I know not all sites do.

I'll get something scooped out and fired off Smile

Edit: All done.

The short version is that it'll show you each step of the execution, what it expected and what it actually got. You can also see where it spends its time, proportions, PX details and so forth.

If you're not picking up the query you want, you can add the hint /*+ MONITOR */ and it'll push it through - although in my experience the ones you're looking for run long enough to be snapped up anyway.

It's not a silver bullet, but with all the information it has condensed into pretty much a single page, it can certainly point you in the right area very quickly.

As mentioned, cmd line is a little more work, but works everywhere Smile

[Updated on: Thu, 13 December 2012 08:19]

Report message to a moderator

Re: Please help me on tune this query [message #572596 is a reply to message #572592] Thu, 13 December 2012 08:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks man.
Re: Please help me on tune this query [message #599936 is a reply to message #572596] Wed, 30 October 2013 05:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Reported message


Reported By: ajaykumarkona On: Wed, 30 October 2013 11:20 In: RDBMS Server » Performance Tuning » Please help me on tune this query
Reason: Please delete this message from this forum. Since i GOT THE ANSWER. Please do it immediately. Thanks.

Just in case someone of fellow moderators sees the request: I sent a private message to the OP, explaining that the whole topic won't be removed (according to OraFAQ Forum Guide (see 13)) and asking him to mark messages and information he finds confidential, so that we could mask it.

He chose to send a report once again, without doing what I asked. Therefore, no action has been performed yet.

[Updated on: Wed, 30 October 2013 05:25]

Report message to a moderator

Re: Please help me on tune this query [message #599939 is a reply to message #599936] Wed, 30 October 2013 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did the same thing too (specifying that table and column names are not seen as confidential).

Re: Please help me on tune this query [message #599960 is a reply to message #599939] Wed, 30 October 2013 08:00 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Another report received, has the reporter responded to either of you with the info that you requested?

[Updated on: Wed, 30 October 2013 08:00]

Report message to a moderator

Re: Please help me on tune this query [message #599961 is a reply to message #599960] Wed, 30 October 2013 08:09 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No.
Re: Please help me on tune this query [message #599963 is a reply to message #599961] Wed, 30 October 2013 08:28 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
OK, I'll just delete the report then.
Re: Please help me on tune this query [message #599964 is a reply to message #599960] Wed, 30 October 2013 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No either (sorry for delay).
I think he will not provide the information and just wants to hide that he "solved" the issue with the help of others.

Re: Please help me on tune this query [message #599965 is a reply to message #599964] Wed, 30 October 2013 08:49 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Cheers Michel, I figured as much.
Previous Topic: XML/XPath rewrites and plan baselines
Next Topic: DB performance problem or "bad" SQL query
Goto Forum:
  


Current Time: Fri Mar 29 08:02:15 CDT 2024