Home » RDBMS Server » Performance Tuning » Please tune this query. (Oracle 11G)
Please tune this query. [message #588455] Wed, 26 June 2013 01:07 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

My below query is taking long time.

SELECT FAX_LIST.*
    FROM (SELECT /*+ use_nl(fax, src)  */
                ROW_NUMBER () OVER (ORDER BY fax.ID ASC NULLS FIRST) RN,
                 fax.ACCOUNT_TYPE,
                 fax.BU_FILE_LOCATION,
                 fax.COUNT_PAGES_RECEIVED,
                 NVL (fax.CUSTOMER_NAME, fax.SENDER_NAME) AS CUSTOMER_NAME,
                 fax.FAX_SOURCE,
                 fax.CUSTOMER_NUMBER,
                 fax.CUSTOMER_OMEGA_NUMBER,
                 fax.GENIFAX_RECIPIENT_ID,
                 fax.ID AS FAX_ID,
                 fax.DATE_RECEIVED AS FAX_RECEIVED,
                 fax.IS_LOCKED,
                 fax.LOCKED_BY,
                 fax.ORIGINATOR_CSI,
                 fax.MARGIN,
                 fax.PAYMENT_TYPE,
                 fax.PRIORITY_CODE,
                 fax.PRIORITY_VALUE,
                 FROM_TZ (fax.DATE_RECEIVED, 'UTC')
                    AT TIME ZONE fax.LOCAL_TZ_NAME
                    AS DATE_RECEIVED,
                 fax.SALES_PERSON,
                 fax.SENDER_EMAIL,
                 fax.SENDER_NAME,
                 fax.SUBJECT,
                 fax.WORKGROUP_ID,
                 fax.LOCKED_TIME,
                 src.DESCRIPTION,
                 src.FAX_NUMBER,
                 src.WORKFLOW_ID,
                 fax.CREATED_BY,
                 fax.CREATED_DATE,
                 fax.UPDATE_DATE,
                 fax.UPDATED_BY,
                 fax.RESERVED_BY,
                 fax.PRICE_TO_ORDER,
                 fax.SKU_COUNT_TO_ORDER,
                 NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_BY,
                    CASE
                       WHEN fax.LAST_TRANSITION_ID IS NULL
                            AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
                       THEN
                          'ORL FEEDER'
                       ELSE
                          'Admin'
                    END)
                    AS LAST_USER,
                 FROM_TZ (
                    NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_DATE,
                         fax.DATE_RECEIVED),
                    'UTC')
                    AT TIME ZONE fax.LOCAL_TZ_NAME
                    AS MOVE_DATETIME,
                 NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).REASON,
                      'Reroute')
                    AS MOVE_REASON,
                 NVL (
                    (SELECT fw.DESCRIPTION
                       FROM ORL.WORKGROUP_TRANSITION wt, ORL.WORKGROUP fw
                      WHERE wt.ID =
                               ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).WORKGROUP_TRANSITION_ID
                            AND fw.ID = wt.CURRENT_WORKGROUP_ID),
                    CASE
                       WHEN fax.LAST_TRANSITION_ID IS NULL
                            AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
                       THEN
                          'ORL FEEDER STAGING'
                       ELSE
                          'N/A'
                    END)
                    AS OLD_STATUS,
                 (SELECT MAX (PURCHASE_ORDER_NUMBER)
                    FROM ORL.FAX_OFFER_DETAIL
                   WHERE FAX_ID = fax.ID)
                    AS MAXPO,
                 (SELECT CASE COUNT (PURCHASE_ORDER_NUMBER)
                            WHEN 0 THEN NULL
                            ELSE COUNT (PURCHASE_ORDER_NUMBER)
                         END
                            AS POCOUNT
                    FROM ORL.FAX_OFFER_DETAIL
                   WHERE FAX_ID = fax.ID)
                    AS POCOUNT,
                 (SELECT SUM (VALUE) AS ORDER_VALUE
                    FROM ORL.FAX_OFFER_DETAIL
                   WHERE FAX_ID = fax.ID)
                    AS ORDER_VALUE,
                 fax.SALESPERSON_NAME,
                 fax.GROUP_NAME,
                 fax.ROLE_NAME,
                 fax.EMAIL,
                 fax.SALES_CHANNEL
            FROM (SELECT fax.*,
                         map.ORACLE_TZ_NAME AS LOCAL_TZ_NAME,
                         ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME
                            AS SALESPERSON_NAME,
                         ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).GROUP_NAME
                            AS GROUP_NAME,
                         ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).ROLE_NAME
                            AS ROLE_NAME,
                         ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).EMAIL
                            AS EMAIL,
                         ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALES_CHANNEL
                            AS SALES_CHANNEL
                    FROM APPS_GLOBAL.GLOBAL_BU_MAPPING map,
                         (SELECT *
                            FROM ORL.FAX_HEADER FH
                           WHERE FH.WORKGROUP_ID = 262) fax
                   WHERE map.GEDIS = 'Y'
                         AND map.BU_ID = (SELECT BUID
                                            FROM ORL.WORKGROUP
                                           WHERE ID = fax.WORKGROUP_ID)
                         AND ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME =
                                'A_CARRICK') fax,
                 (SELECT *
                    FROM ORL.FAX_SOURCE FS
                   WHERE FS.WORKFLOW_ID IN
                            (SELECT  /*+ CARDINALITY(t, 1) */
                                   TO_NUMBER (
                                       COLUMN_VALUE)
                                       AS COLUMN_VALUE
                               FROM TABLE (
                                       SplitClob ('1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 
				       16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 
				       32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
				       48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
				       64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
				       80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95,
				       96, 97, 98', ',')) t)) src
           WHERE src.ID(+) = fax.FAX_SOURCE) FAX_LIST
   WHERE RN BETWEEN 1 AND 100
ORDER BY RN ASC;


In my query the following three functions are using.
ORL.GET_FTH_WTR
ORL.GET_SALESPERSON
APPS_GLOBAL.SplitClob

CREATE OR REPLACE FUNCTION ORL.GET_FTH_WTR(p_LAST_TRANSITION_ID NUMBER)
        RETURN ORL.FTH_WTR_T
        DETERMINISTIC
    IS
        v_REASON nvarchar2(1024 char);
        v_WORKGROUP_TRANSITION_ID number;
        v_CHANGED_BY nvarchar2(64 char);
        v_CHANGED_DATE timestamp(6);

    BEGIN
    SELECT
        WTR.REASON,
        wtr.WORKGROUP_TRANSITION_ID,
        fth.CHANGED_BY,
        fth.CHANGED_DATE
    INTO
        v_REASON,
        v_WORKGROUP_TRANSITION_ID,
        v_CHANGED_BY,
        v_CHANGED_DATE
    FROM
        ORL.FAX_TRANSITION_HISTORY fth,
        ORL.WORKGROUP_TRANSITION_REASON wtr
    WHERE
         fth.ID = p_LAST_TRANSITION_ID
        AND wtr.ID(+) = fth.TRANSITION_REASON_ID;

    RETURN ORL.FTH_WTR_T(v_CHANGED_BY, v_CHANGED_DATE, v_REASON, v_WORKGROUP_TRANSITION_ID);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        RETURN ORL.FTH_WTR_T(NULL, NULL, NULL, NULL);
    END GET_FTH_WTR;
/

CREATE OR REPLACE function ORL.GET_SALESPERSON(ORG_ID number, SALESPERSON_ID number)
    return ORL.SALESPERSON_T
    deterministic
is
    apps_bu             varchar2(30);
    salesperson_name    varchar2(150 char);
    email               varchar2(70 char);
    sales_channel       varchar2(60 char);
    role_name           varchar2(60 char);
    group_name          varchar2(60 char);
begin
    select OWNER_NAME into apps_bu
      from APPS_GLOBAL.GLOBAL_BU_MAPPING
     where ORG_ID = GET_SALESPERSON.ORG_ID;
    execute immediate
    replace('
        select SALESPERSON_NAME
             , EMAIL
             , SALES_CHANNEL
             , ROLE_NAME
             , GROUP_NAME
          from APPS_**.ORL_ACTIVE_SALESPERSON
         where SALESPERSON_ID = :1
		 and  rownum = 1
            ', 'APPS_**', apps_bu)
    into salesperson_name, email, sales_channel, role_name, group_name
    using SALESPERSON_ID;
    return SALESPERSON_T(ORG_ID, SALESPERSON_ID, salesperson_name, email, sales_channel, role_name, group_name);
exception
    when no_data_found then
        return SALESPERSON_T(null, null, null, null, null, null, null);
end GET_SALESPERSON;
/

CREATE OR REPLACE function APPS_GLOBAL.SplitClob
(
    p_clob          clob
  , p_delimiter     varchar2 := ','
)
return StringTable
deterministic
pipelined
as
    v_current_pos   pls_integer := 1;
    v_delimiter_pos pls_integer;
begin
    if (p_clob is not NULL) and (p_delimiter is not NULL) then
        while v_current_pos <= length(p_clob) loop
            v_delimiter_pos := instr(p_clob, p_delimiter, v_current_pos);
            if  v_delimiter_pos < 1  then   -- no more delimiters
                v_delimiter_pos := length(p_clob) + 1;
            end if;
            pipe row( to_char( substr(p_clob,
                                      v_current_pos,
                                      v_delimiter_pos - v_current_pos) ) );
            v_current_pos := v_delimiter_pos + length(p_delimiter);
        end loop;
    end if;
end SplitClob;
/

CREATE OR REPLACE TYPE SALESPERSON_T  AS OBJECT
(
  SALESPERSON_ID        number(15)
, SALESPERSON_NUMBER    varchar2(150 char)
, FIRST_NAME            varchar2(20 char)
, LAST_NAME             varchar2(40 char)
, SALES_CHANNEL         varchar2(60 char)
, ORG_ID                number(15)
, USER_NAME             varchar2(61 char)
, EFFECTIVE_START_DATE  date
, EFFECTIVE_END_DATE    date
, STATUS_FLAG           varchar2(1 char)
, EMAIL                 varchar2(70 char)
, WORK_TELEPHONE        varchar2(60 char)
, MANAGERS_NAME         varchar2(50 char)
, FAX_NO                varchar2(60 char)
, SALESPERSON_NAME      varchar2(30 char)
, TERRITORY             varchar2(40 char)
, FO_LOGON              varchar2(150 char)
, BO_LOGON              varchar2(150 char)
, SUB_CHANNEL           varchar2(25 char)
, BUSINESS_SEGMENT      varchar2(3 char)
, DISCOUNT_NAME         varchar2(30 char)
, RESPONSIBILITY_ID     number
, RESPONSIBILITY_KEY    varchar2(30 char)
);
/

CREATE OR REPLACE TYPE STRINGTABLE as table of varchar2(4000);


The total number of records in each table.

SELECT COUNT(*) FROM ORL.FAX_HEADER FH --4397829

SELECT COUNT(*) FROM APPS_GLOBAL.GLOBAL_BU_MAPPING map --31

SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION  --6735

SELECT COUNT(*) FROM ORL.WORKGROUP fw --1495
 
SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL --5904039

SELECT COUNT(*) FROM  ORL.FAX_SOURCE--2368 --2457


Indexes on the columns.

ORL.WORKGROUP_TRANSITION(ID), 
ORL.WORKGROUP(ID),
ORL.FAX_OFFER_DETAIL(FAX_ID),
ORL.FAX_HEADER(WORKGROUP_ID).


Please help me to tune this query.

Thanks in advance.
Re: Please tune this query. [message #588458 is a reply to message #588455] Wed, 26 June 2013 01:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
ajaykumarkona wrote on Wed, 26 June 2013 11:37
Please help me to tune this query.



It is impossible to do anything here just by looking at your query. You must go in a proper direction if you want the resolution. Get the execution plans. If possible generate the trace file and run tkprof. Where are the table definitions? What is data you are dealing with?

Nobody can guess/do anything haphazardly.

http://www.orafaq.com/forum/t/84315/178722/

[Updated on: Wed, 26 June 2013 01:57]

Report message to a moderator

Re: Please tune this query. [message #588466 is a reply to message #588458] Wed, 26 June 2013 02:07 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am extremely sorry.

I am sending execution plan.

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                         |  6927 |    39M|       |   374K  (1)|
|   1 |  NESTED LOOPS                           |                         |     1 |    38 |       |  3   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP_TRANSITION    |     1 |     9 |       |  2   (0)|
|   3 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP_TRANSITION |     1 |       |       |  1   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP               |  1482 | 42978 |       |  1   (0)|
|   5 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP            |     1 |       |       |  0   (0)|
|   6 |  SORT AGGREGATE                         |                         |     1 |    16 |       |         |
|   7 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    16 |       |  5   (0)|
|   8 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |       |  3   (0)|
|   9 |  SORT AGGREGATE                         |                         |     1 |    16 |       |         |
|  10 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    16 |       |  5   (0)|
|  11 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |       |  3   (0)|
|  12 |  SORT AGGREGATE                         |                         |     1 |    11 |       |         |
|  13 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    11 |       |  5   (0)|
|  14 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |       |  3   (0)|
|  15 |  SORT ORDER BY                          |                         |  6927 |    39M|    54M|   374K  (1)|
|  16 |   VIEW                                  |                         |  6927 |    39M|       |   365K  (1)|
|  17 |    WINDOW SORT PUSHED RANK              |                         |  6927 |  4660K|  5048K|   365K  (1)|
|  18 |     NESTED LOOPS OUTER                  |                         |  6927 |  4660K|       |   364K  (1)|
|  19 |      NESTED LOOPS                       |                         |  6927 |  4302K|       |   364K  (1)|
|  20 |       TABLE ACCESS FULL                 | GLOBAL_BU_MAPPING       |    29 |   696 |       |    18   (0)|
|  21 |       TABLE ACCESS BY INDEX ROWID       | FAX_HEADER              |   235 |   140K|       | 12552   (1)|
|  22 |        INDEX RANGE SCAN                 | FAX_HEADER_IDX1         | 23482 |       |       |    30  (30)|
|  23 |         TABLE ACCESS BY INDEX ROWID     | WORKGROUP               |     1 |     8 |       |  2   (0)|
|  24 |          INDEX UNIQUE SCAN              | PK_WORKGROUP            |     1 |       |       |  1   (0)|
|  25 |      VIEW PUSHED PREDICATE              |                         |     1 |    53 |       |  1   (0)|
|  26 |       NESTED LOOPS SEMI                 |                         |     1 |    53 |       |    31   (0)|
|  27 |        TABLE ACCESS BY INDEX ROWID      | FAX_SOURCE              |     1 |    51 |       |  2   (0)|
|  28 |         INDEX UNIQUE SCAN               | PK_FAX_SOURCE           |     1 |       |       |  1   (0)|
|  29 |        COLLECTION ITERATOR PICKLER FETCH| SPLITCLOB               |     1 |     2 |       |    29   (0)|
----------------------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
     864231  recursive calls
     262330  db block gets
    7031278  consistent gets
        909  physical reads
      48248  redo size
      46309  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
     818169  sorts (memory)
          0  sorts (disk)
        100  rows processed


Please help me.

Thanks.
Re: Please tune this query. [message #588471 is a reply to message #588466] Wed, 26 June 2013 02:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
DDLs please.

Do this:-
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
    
TRUNCATE TABLE PLAN_TABLE;

EXPLAIN PLAN FOR <your slow SQL statement>;

SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
FROM   plan_table
WHERE  object_type IN ('TABLE','VIEW');

SELECT DBMS_METADATA.GET_DDL ('INDEX', index_name, index_owner)
FROM   all_indexes
WHERE  table_owner, table_name IN (
    SELECT object_owner, object_name
    FROM   plan_table p
    WHERE  object_type IN ('TABLE')
);

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION 
FROM ALL_IND_COLUMNS 
WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM PLAN_TABLE p WHERE  object_type = 'TABLE') ORDER BY 1,2,4;

COMMIT;


>cut the SQL from code frame above & PASTE into a terminal/command window running sqlplus
>cut SQL & output from above & PASTE formatted results into your post.

Please refer to help yourself :- http://www.orafaq.com/forum/t/84315/178722/

[Updated on: Wed, 26 June 2013 02:32]

Report message to a moderator

Re: Please tune this query. [message #588487 is a reply to message #588471] Wed, 26 June 2013 03:26 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Thanks for your reply.

I have executed the required statements from SQL*PLUS and sending the result.

SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);

PL/SQL procedure successfully completed.

SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.


SQL> TRUNCATE TABLE PLAN_TABLE;

Table truncated.


SQL> TRUNCATE TABLE PLAN_TABLE;

Table truncated.

SQL> EXPLAIN PLAN FOR
  2  SELECT FAX_LIST.*
  3      FROM (SELECT /*+ use_nl(fax, src)  */
  4                  ROW_NUMBER () OVER (ORDER BY fax.ID ASC NULLS FIRST) RN,
  5                   fax.ACCOUNT_TYPE,
  6                   fax.BU_FILE_LOCATION,
  7                   fax.COUNT_PAGES_RECEIVED,
  8                   NVL (fax.CUSTOMER_NAME, fax.SENDER_NAME) AS CUSTOMER_NAME,
  9                   fax.FAX_SOURCE,
 10                   fax.CUSTOMER_NUMBER,
 11                   fax.CUSTOMER_OMEGA_NUMBER,
 12                   fax.GENIFAX_RECIPIENT_ID,
 13                   fax.ID AS FAX_ID,
 14                   fax.DATE_RECEIVED AS FAX_RECEIVED,
 15                   fax.IS_LOCKED,
 16                   fax.LOCKED_BY,
 17                   fax.ORIGINATOR_CSI,
 18                   fax.MARGIN,
 19                   fax.PAYMENT_TYPE,
 20                   fax.PRIORITY_CODE,
 21                   fax.PRIORITY_VALUE,
 22                   FROM_TZ (fax.DATE_RECEIVED, 'UTC')AT TIME ZONE fax.LOCAL_TZ_NAME AS DATE_RECEIVED,
 23                   fax.SALES_PERSON,
 24                   fax.SENDER_EMAIL,
 25                   fax.SENDER_NAME,
 26                   fax.SUBJECT,
 27                   fax.WORKGROUP_ID,
 28                   fax.LOCKED_TIME,
 29                   src.DESCRIPTION,
 30                   src.FAX_NUMBER,
 31                   src.WORKFLOW_ID,
 32                   fax.CREATED_BY,
 33                   fax.CREATED_DATE,
 34                   fax.UPDATE_DATE,
 35                   fax.UPDATED_BY,
 36                   fax.RESERVED_BY,
 37                   fax.PRICE_TO_ORDER,
 38                   fax.SKU_COUNT_TO_ORDER,
 39                   NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_BY, CASE WHEN fax.LAST_TRANSITION_ID IS NULL
 40                              AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
 41                         THEN
 42                            'ORL FEEDER'
 43                         ELSE
 44                            'Admin'
 45                      END)
 46                      AS LAST_USER,
 47                   FROM_TZ (NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_DATE,fax.DATE_RECEIVED),'UTC') AT TIME ZONE fax.LOCAL_TZ_NAME AS MOVE_DATETIME,
 48                   NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).REASON,'Reroute')AS MOVE_REASON,
 49                   NVL (
 50                      (SELECT fw.DESCRIPTION
 51                         FROM ORL.WORKGROUP_TRANSITION wt, ORL.WORKGROUP fw
 52                        WHERE wt.ID =
 53                                 ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).WORKGROUP_TRANSITION_ID
 54                              AND fw.ID = wt.CURRENT_WORKGROUP_ID),
 55                      CASE
 56                         WHEN fax.LAST_TRANSITION_ID IS NULL
 57                              AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
 58                         THEN
 59                            'ORL FEEDER STAGING'
 60                         ELSE
 61                            'N/A'
 62                      END)
 63                      AS OLD_STATUS,
 64                   (SELECT MAX (PURCHASE_ORDER_NUMBER)
 65                      FROM ORL.FAX_OFFER_DETAIL
 66                     WHERE FAX_ID = fax.ID)
 67                      AS MAXPO,
 68                   (SELECT CASE COUNT (PURCHASE_ORDER_NUMBER)
 69                              WHEN 0 THEN NULL
 70                              ELSE COUNT (PURCHASE_ORDER_NUMBER)
 71                           END
 72                              AS POCOUNT
 73                      FROM ORL.FAX_OFFER_DETAIL
 74                     WHERE FAX_ID = fax.ID)
 75                      AS POCOUNT,
 76                   (SELECT SUM (VALUE) AS ORDER_VALUE
 77                      FROM ORL.FAX_OFFER_DETAIL
 78                     WHERE FAX_ID = fax.ID)
 79                      AS ORDER_VALUE,
 80                   fax.SALESPERSON_NAME,
 81                   fax.GROUP_NAME,
 82                   fax.ROLE_NAME,
 83                   fax.EMAIL,
 84                   fax.SALES_CHANNEL
 85              FROM (SELECT fax.*,
 86                           map.ORACLE_TZ_NAME AS LOCAL_TZ_NAME,
 87                           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME AS SALESPERSON_NAME,
 88                           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).GROUP_NAME AS GROUP_NAME,
 89                           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).ROLE_NAME AS ROLE_NAME,
 90                           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).EMAIL AS EMAIL,
 91                           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALES_CHANNEL AS SALES_CHANNEL
 92                      FROM APPS_GLOBAL.GLOBAL_BU_MAPPING map,
 93                           (SELECT *
 94                              FROM ORL.FAX_HEADER FH
 95                             WHERE FH.WORKGROUP_ID = 262) fax
 96                     WHERE map.GEDIS = 'Y'
 97                           AND map.BU_ID = (SELECT BUID
 98                                              FROM ORL.WORKGROUP
 99                                             WHERE ID = fax.WORKGROUP_ID)
100                           AND ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME =
101                                  'A_CARRICK') fax,
102                   (SELECT *
103                      FROM ORL.FAX_SOURCE FS
104                     WHERE FS.WORKFLOW_ID IN
105                              (SELECT  /*+ CARDINALITY(t, 1) */
106                                     TO_NUMBER (
107                                         COLUMN_VALUE)
108                                         AS COLUMN_VALUE
109                                 FROM TABLE (
110                                         SplitClob ('1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
111                         16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
112                         32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
113                         48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
114                         64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
115                         80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95,
116                         96, 97, 98', ',')) t)) src
117             WHERE src.ID(+) = fax.FAX_SOURCE) FAX_LIST
118     WHERE RN BETWEEN 1 AND 100
119  ORDER BY RN ASC;

Explained.


SQL> SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
  2  FROM   plan_table
  3  WHERE  object_type IN ('TABLE','VIEW');

9 rows selected.

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  2698 | 64752 |    68   (0)|
|   1 |  TABLE ACCESS FULL| PLAN_TABLE |  2698 | 64752 |    68   (0)|
---------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
       1164  recursive calls
        914  db block gets
       4705  consistent gets
          0  physical reads
       6560  redo size
       4999  bytes sent via SQL*Net to client
       2137  bytes received via SQL*Net from client
         29  SQL*Net roundtrips to/from client
         47  sorts (memory)
          0  sorts (disk)
          9  rows processed


SQL> SELECT DBMS_METADATA.GET_DDL ('INDEX', index_name,owner)
  2  FROM   all_indexes
  3  WHERE  (table_owner, table_name) IN (
  4      SELECT object_owner, object_name
  5      FROM   plan_table p
  6      WHERE  object_type IN ('TABLE')
  7  );

35 rows selected.

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |     2 |   378 |   357   (1)|
|   1 |  FILTER                          |                |       |       |            |
|   2 |   HASH JOIN OUTER                |                |    18 |  3402 |   357   (1)|
|   3 |    HASH JOIN                     |                |    18 |  3348 |   356   (1)|
|   4 |     NESTED LOOPS OUTER           |                |    18 |  3060 |   352   (1)|
|   5 |      NESTED LOOPS                |                |    18 |  2916 |   334   (1)|
|   6 |       NESTED LOOPS OUTER         |                |    18 |  2268 |   298   (1)|
|   7 |        HASH JOIN OUTER           |                |    18 |  2034 |   280   (1)|
|   8 |         NESTED LOOPS             |                |    18 |  1980 |   275   (1)|
|   9 |          HASH JOIN               |                |    10 |   770 |   274   (1)|
|  10 |           HASH JOIN              |                |   516 | 20640 |    72   (2)|
|  11 |            JOIN FILTER CREATE    | :BF0000        |    90 |  1440 |     3   (0)|
|  12 |             TABLE ACCESS FULL    | USER$          |    90 |  1440 |     3   (0)|
|  13 |            SORT UNIQUE           |                |   516 | 12384 |    68   (0)|
|  14 |             JOIN FILTER USE      | :BF0000        |   516 | 12384 |    68   (0)|
|  15 |              TABLE ACCESS FULL   | PLAN_TABLE     |   516 | 12384 |    68   (0)|
|  16 |           INDEX FAST FULL SCAN   | I_OBJ5         |  3224 |   116K|   201   (0)|
|  17 |          TABLE ACCESS CLUSTER    | IND$           |     2 |    66 |     1   (0)|
|  18 |           INDEX UNIQUE SCAN      | I_OBJ#         |     1 |       |     0   (0)|
|  19 |         TABLE ACCESS FULL        | TS$            |    12 |    36 |     5   (0)|
|  20 |        TABLE ACCESS CLUSTER      | SEG$           |     1 |    13 |     1   (0)|
|  21 |         INDEX UNIQUE SCAN        | I_FILE#_BLOCK# |     1 |       |     0   (0)|
|  22 |       TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    36 |     2   (0)|
|  23 |        INDEX RANGE SCAN          | I_OBJ1         |     1 |       |     1   (0)|
|  24 |      INDEX RANGE SCAN            | I_OBJ1         |     1 |     8 |     1   (0)|
|  25 |     TABLE ACCESS FULL            | USER$          |    90 |  1440 |     3   (0)|
|  26 |    INDEX FULL SCAN               | I_USER2        |    90 |   270 |     1   (0)|
|  27 |   NESTED LOOPS                   |                |     1 |    21 |     2   (0)|
|  28 |    INDEX RANGE SCAN              | I_OBJAUTH1     |     1 |     8 |     2   (0)|
|  29 |    FIXED TABLE FULL              | X$KZSRO        |     1 |    13 |     0   (0)|
|  30 |   FIXED TABLE FULL               | X$KZSPR        |     1 |    26 |     0   (0)|
----------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
       3908  recursive calls
       6194  db block gets
      10532  consistent gets
          0  physical reads
      25596  redo size
      18285  bytes sent via SQL*Net to client
       7259  bytes received via SQL*Net from client
        107  SQL*Net roundtrips to/from client
         36  sorts (memory)
          0  sorts (disk)
         35  rows processed


SQL> SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
  2  FROM ALL_IND_COLUMNS
  3  WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM PLAN_TABLE p WHERE  object_type = 'TABLE') ORDER BY 1,2,4;

36 rows selected.

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |   187 |   429   (1)|
|   1 |  NESTED LOOPS OUTER                 |            |     1 |    67 |     3   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID       | COL$       |     1 |    26 |     2   (0)|
|   3 |    INDEX UNIQUE SCAN                | I_COL3     |     1 |       |     1   (0)|
|   4 |   TABLE ACCESS CLUSTER              | ATTRCOL$   |     1 |    41 |     1   (0)|
|   5 |  SORT ORDER BY                      |            |     1 |   187 |   429   (1)|
|   6 |   FILTER                            |            |       |       |            |
|   7 |    NESTED LOOPS OUTER               |            |     1 |   187 |   428   (1)|
|   8 |     NESTED LOOPS                    |            |     1 |   146 |   427   (1)|
|   9 |      NESTED LOOPS                   |            |     1 |   143 |   426   (1)|
|  10 |       NESTED LOOPS                  |            |     1 |   140 |   425   (1)|
|  11 |        NESTED LOOPS                 |            |     4 |   456 |   421   (1)|
|  12 |         NESTED LOOPS                |            |    68 |  7004 |   416   (1)|
|  13 |          NESTED LOOPS               |            |    68 |  4760 |   280   (1)|
|  14 |           HASH JOIN RIGHT SEMI      |            |   268 | 13668 |   271   (1)|
|  15 |            TABLE ACCESS FULL        | PLAN_TABLE |   829 | 14922 |    68   (0)|
|  16 |            INDEX FAST FULL SCAN     | I_OBJ5     | 69454 |  2238K|   201   (0)|
|  17 |           TABLE ACCESS CLUSTER      | ICOL$      |     1 |    19 |     1   (0)|
|  18 |            INDEX UNIQUE SCAN        | I_OBJ#     |     1 |       |     0   (0)|
|  19 |          TABLE ACCESS BY INDEX ROWID| OBJ$       |     1 |    33 |     2   (0)|
|  20 |           INDEX RANGE SCAN          | I_OBJ1     |     1 |       |     1   (0)|
|  21 |         TABLE ACCESS BY INDEX ROWID | IND$       |     1 |    11 |     1   (0)|
|  22 |          INDEX UNIQUE SCAN          | I_IND1     |     1 |       |     0   (0)|
|  23 |        TABLE ACCESS CLUSTER         | COL$       |     1 |    26 |     1   (0)|
|  24 |       INDEX RANGE SCAN              | I_USER2    |     1 |     3 |     1   (0)|
|  25 |      INDEX RANGE SCAN               | I_USER2    |     1 |     3 |     1   (0)|
|  26 |     TABLE ACCESS CLUSTER            | ATTRCOL$   |     1 |    41 |     1   (0)|
|  27 |    NESTED LOOPS                     |            |     1 |    21 |     2   (0)|
|  28 |     INDEX RANGE SCAN                | I_OBJAUTH1 |     1 |     8 |     2   (0)|
|  29 |     FIXED TABLE FULL                | X$KZSRO    |     1 |    13 |     0   (0)|
|  30 |    FIXED TABLE FULL                 | X$KZSPR    |     1 |    26 |     0   (0)|
---------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1083  consistent gets
          0  physical reads
          0  redo size
       2114  bytes sent via SQL*Net to client
        386  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         36  rows processed

SQL> COMMIT;
Commit complete.


Please help me.

Thanks in advance.
Re: Please tune this query. [message #588532 is a reply to message #588487] Wed, 26 June 2013 06:44 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi ,

I am sending the code after some changes along with explain plan.

SELECT   FAX_LIST.*
  FROM   (SELECT   ROW_NUMBER () OVER (ORDER BY fax.ID ASC) RN,
                   fax.ACCOUNT_TYPE,
                   fax.BU_FILE_LOCATION,
                   fax.COUNT_PAGES_RECEIVED,
                   NVL (fax.CUSTOMER_NAME, fax.SENDER_NAME) AS CUSTOMER_NAME,
                   fax.FAX_SOURCE,
                   fax.CUSTOMER_NUMBER,
                   fax.CUSTOMER_OMEGA_NUMBER,
                   fax.GENIFAX_RECIPIENT_ID,
                   fax.ID AS FAX_ID,
                   fax.DATE_RECEIVED AS FAX_RECEIVED,
                   fax.IS_LOCKED,
                   fax.LOCKED_BY,
                   fax.ORIGINATOR_CSI,
                   fax.MARGIN,
                   fax.PAYMENT_TYPE,
                   fax.PRIORITY_CODE,
                   fax.PRIORITY_VALUE,
                   FROM_TZ (fax.DATE_RECEIVED, 'UTC') AT TIME ZONE map.ORACLE_TZ_NAME AS DATE_RECEIVED,
                   fax.SALES_PERSON,
                   fax.SENDER_EMAIL,
                   fax.SENDER_NAME,
                   fax.SUBJECT,
                   fax.WORKGROUP_ID,
                   fax.LOCKED_TIME,
                   src.DESCRIPTION,
                   src.FAX_NUMBER,
                   src.WORKFLOW_ID,
                   fax.CREATED_BY,
                   fax.CREATED_DATE,
                   fax.UPDATE_DATE,
                   fax.UPDATED_BY,
                   fax.RESERVED_BY,
                   fax.PRICE_TO_ORDER,
                   fax.SKU_COUNT_TO_ORDER,
                   NVL (
                      ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_BY,
                      CASE
                         WHEN fax.LAST_TRANSITION_ID IS NULL
                              AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
                         THEN
                            'ORL FEEDER'
                         ELSE
                            'Admin'
                      END
                   )
                      AS LAST_USER,
                   FROM_TZ (NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_DATE,fax.DATE_RECEIVED),'UTC')
                   AT TIME ZONE map.ORACLE_TZ_NAME AS MOVE_DATETIME,
                   NVL (ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).REASON,'Reroute')AS MOVE_REASON,
                   NVL ((SELECT   fw.DESCRIPTION
                         FROM   ORL.WORKGROUP_TRANSITION wt, ORL.WORKGROUP fw
                        WHERE   wt.ID =
                                   ORL.GET_FTH_WTR (
                                      fax.LAST_TRANSITION_ID
                                   ).WORKGROUP_TRANSITION_ID
                                AND fw.ID = wt.CURRENT_WORKGROUP_ID),
                      CASE
                         WHEN fax.LAST_TRANSITION_ID IS NULL
                              AND fax.GENIFAX_RECIPIENT_ID IS NOT NULL
                         THEN
                            'ORL FEEDER STAGING'
                         ELSE
                            'N/A'
                      END
                   )
                      AS OLD_STATUS,
                   (SELECT   MAX (PURCHASE_ORDER_NUMBER)
                      FROM   ORL.FAX_OFFER_DETAIL
                     WHERE   FAX_ID = fax.ID)
                      AS MAXPO,
                   (SELECT   CASE COUNT (PURCHASE_ORDER_NUMBER)
                                WHEN 0 THEN NULL
                                ELSE COUNT (PURCHASE_ORDER_NUMBER)
                             END
                                AS POCOUNT
                      FROM   ORL.FAX_OFFER_DETAIL
                     WHERE   FAX_ID = fax.ID)
                      AS POCOUNT,
                   (SELECT   SUM (VALUE) AS ORDER_VALUE
                      FROM   ORL.FAX_OFFER_DETAIL
                     WHERE   FAX_ID = fax.ID) AS ORDER_VALUE,
                   ORL.GET_SALESPERSON (map.ORG_ID,fax.SALES_PERSON).SALESPERSON_NAME AS SALESPERSON_NAME,
                   ORL.GET_SALESPERSON (map.ORG_ID,fax.SALES_PERSON).GROUP_NAME AS GROUP_NAME,
                   ORL.GET_SALESPERSON (map.ORG_ID,fax.SALES_PERSON).ROLE_NAME AS ROLE_NAME,
                   ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).EMAIL AS EMAIL,
                   ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALES_CHANNEL AS SALES_CHANNEL
            FROM   ORL.FAX_HEADER fax,
                   APPS_GLOBAL.GLOBAL_BU_MAPPING map,
                   ORL.FAX_SOURCE src
           WHERE       fax.WORKGROUP_ID = :WORKGROUP_ID
                   AND map.GEDIS = 'Y'
                   AND map.BU_ID = (SELECT   BUID
                                      FROM   ORL.WORKGROUP
                                     WHERE   ID = fax.WORKGROUP_ID)
                   AND ORL.GET_SALESPERSON (map.ORG_ID,fax.SALES_PERSON).GROUP_NAME =:SALES_TEAM
                   AND ORL.GET_SALESPERSON (map.ORG_ID,fax.SALES_PERSON
                      ).SALESPERSON_NAME = :SALESPERSON_NAME
                   AND src.WORKFLOW_ID IN
                            (SELECT TO_NUMBER (COLUMN_VALUE) AS COLUMN_VALUE
                               FROM table(SplitClob (:WORKFLOW_ID,:WORKFLOW_ID_delim)) t)
                   AND src.ID(+) = fax.FAX_SOURCE) FAX_LIST
WHERE   RN BETWEEN 1 AND 100;
 
Execution Plan
----------------------------------------------------------
 
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                    | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                         |     2 | 12092 | 88923   (1)|
|   1 |  NESTED LOOPS                           |                         |     1 |    38 |     3   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP_TRANSITION    |     1 |     9 |     2   (0)|
|   3 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP_TRANSITION |     1 |       |     1   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP               |  1482 | 42978 |     1   (0)|
|   5 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP            |     1 |       |     0   (0)|
|   6 |  SORT AGGREGATE                         |                         |     1 |    16 |            |
|   7 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    16 |     5   (0)|
|   8 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |     3   (0)|
|   9 |  SORT AGGREGATE                         |                         |     1 |    16 |            |
|  10 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    16 |     5   (0)|
|  11 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |     3   (0)|
|  12 |  SORT AGGREGATE                         |                         |     1 |    11 |            |
|  13 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL        |     1 |    11 |     5   (0)|
|  14 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1   |     1 |       |     3   (0)|
|  15 |  VIEW                                   |                         |     2 | 12092 | 88923   (1)|
|  16 |   WINDOW SORT PUSHED RANK               |                         |     2 |  1378 | 88923   (1)|
|  17 |    NESTED LOOPS                         |                         |       |       |            |
|  18 |     NESTED LOOPS                        |                         |     2 |  1378 | 88920   (1)|
|  19 |      NESTED LOOPS                       |                         | 18420 |    11M| 70490   (1)|
|  20 |       HASH JOIN RIGHT SEMI              |                         |    69 |  3657 |    49   (3)|
|  21 |        COLLECTION ITERATOR PICKLER FETCH| SPLITCLOB               |  8168 | 16336 |    29   (0)|
|  22 |        TABLE ACCESS FULL                | FAX_SOURCE              |  2409 |   119K|    19   (0)|
|  23 |       TABLE ACCESS BY INDEX ROWID       | FAX_HEADER              |   268 |   160K|  2854   (1)|
|  24 |        INDEX RANGE SCAN                 | FAX_HEADER_FS_IDX       |  4345 |       |     7   (0)|
|  25 |      INDEX RANGE SCAN                   | GLOBAL_BU_MAPPING_BUID  |     1 |       |     0   (0)|
|  26 |       TABLE ACCESS BY INDEX ROWID       | WORKGROUP               |     1 |     8 |     2   (0)|
|  27 |        INDEX UNIQUE SCAN                | PK_WORKGROUP            |     1 |       |     1   (0)|
|  28 |     TABLE ACCESS BY INDEX ROWID         | GLOBAL_BU_MAPPING       |     1 |    24 |     1   (0)|
--------------------------------------------------------------------------------------------------------
 
 
Note
-----
   - 'PLAN_TABLE' is old version
 
 
 
 
Statistics
----------------------------------------------------------
     894400  recursive calls
         60  db block gets
    7402741  consistent gets
          0  physical reads
          0  redo size
      46309  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
     848304  sorts (memory)
          0  sorts (disk)
        100  rows processed



Please help me.
Thanks in advance.
Re: Please tune this query. [message #588612 is a reply to message #588532] Thu, 27 June 2013 01:06 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Any body please help me on this.

Thanks.
Re: Please tune this query. [message #588683 is a reply to message #588612] Thu, 27 June 2013 06:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
You have not yet posted the DDLs. Where is the output for the following:-
1. DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
2. DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)

Also, post the following:-
1. Total rows returned by the query.
2. FTS on FAX_SOURCE returns 2409 rows -- are all the rows really needed?
3. Now see the ITERATOR in the execution plan, it must loop each of it's child 8168 times. So the total rows actually processed become 8168 times the rows returned in the child steps.
4. Assuming the number of rows per your previous post:-
SELECT COUNT(*) FROM ORL.FAX_HEADER FH --4397829
SELECT COUNT(*) FROM APPS_GLOBAL.GLOBAL_BU_MAPPING map --31
SELECT COUNT(*) FROM  ORL.FAX_SOURCE--2368 --2457


Post the execution plan with following hint:-
/*+ leading(GLOBAL_BU_MAPPING) use_nl(FAX_SOURCE) index(FAX_SOURCE required_index) use_nl(FAX_HEADER) index(FAX_HEADER required_index) */

[Updated on: Thu, 27 June 2013 06:56]

Report message to a moderator

Re: Please tune this query. [message #589115 is a reply to message #588683] Tue, 02 July 2013 07:27 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi ,


I have created bitmap index on ORL.FAX_HEADER(WORKGROUP_ID).
Some what the cost got reduced.
I have created bitmap index on ORL.FAX_SOURCE(WORKFLOW_ID)
After creating this index I didn't see any difference in the cost.
I have used the hints as per your suggestion but the cost got increased from 36115 to 131558.

I am sending execution plan for query with hint and without hint.
And also I am sending output for

 DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER) 
 DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)

SQL> SELECT
  2  FAX_LIST.*
  3  FROM  (SELECT
  4   row_number() over ( order by fax.ID ASC ) RN,
  5                       fax.ACCOUNT_TYPE,
  6                       fax.BU_FILE_LOCATION,
  7                       fax.COUNT_PAGES_RECEIVED,
  8                       nvl(fax.CUSTOMER_NAME, fax.SENDER_NAME) AS CUSTOMER_NAME,
  9                       fax.FAX_SOURCE,
 10                       fax.CUSTOMER_NUMBER,
 11                       fax.CUSTOMER_OMEGA_NUMBER,
 12                       fax.GENIFAX_RECIPIENT_ID,
 13                       fax.ID AS FAX_ID,
 14                       fax.DATE_RECEIVED AS FAX_RECEIVED,
 15                       fax.IS_LOCKED,
 16                       fax.LOCKED_BY,
 17                       fax.ORIGINATOR_CSI,
 18                       fax.MARGIN,
 19                       fax.PAYMENT_TYPE,
 20                       fax.PRIORITY_CODE,
 21                       fax.PRIORITY_VALUE,
 22                       FROM_TZ (fax.DATE_RECEIVED, 'UTC') AT TIME ZONE map.ORACLE_TZ_NAME AS DATE_RECEIVED,
 23                       fax.SALES_PERSON,
 24                       fax.SENDER_EMAIL,
 25                       fax.SENDER_NAME,
 26                       fax.SUBJECT,
 27                       fax.WORKGROUP_ID,
 28                       fax.LOCKED_TIME,
 29                       src.DESCRIPTION,
 30                       src.FAX_NUMBER,
 31                       src.WORKFLOW_ID,
 32                       fax.CREATED_BY,
 33                       fax.CREATED_DATE,
 34                       fax.UPDATE_DATE,
 35                       fax.UPDATED_BY,
 36                       fax.RESERVED_BY,
 37                       fax.PRICE_TO_ORDER,
 38                       fax.SKU_COUNT_TO_ORDER
 39                   ,   nvl(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_BY,
 40                              case
 41                                  when fax.LAST_TRANSITION_ID is null
 42                                      and fax.GENIFAX_RECIPIENT_ID is not null
 43                                  then 'ORL FEEDER'
 44                                  else 'Admin'
 45                              end
 46                           ) AS LAST_USER
 47                   ,   from_tz(nvl(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_DATE, fax.DATE_RECEIVED), 'UTC') at time zone map.ORACLE_TZ_NAME AS MOVE_DATETIME
 48                   ,   NVL(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).REASON, 'Reroute') AS MOVE_REASON
 49                   ,   NVL((select fw.DESCRIPTION
 50                                  from ORL.WORKGROUP_TRANSITION wt
 51                                      , ORL.WORKGROUP fw
 52                                  where wt.ID = ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).WORKGROUP_TRANSITION_ID
 53                                  and fw.ID = wt.CURRENT_WORKGROUP_ID
 54                              ),
 55                              case
 56                                  when fax.LAST_TRANSITION_ID is null
 57                                      and fax.GENIFAX_RECIPIENT_ID is not null
 58                                  then 'ORL FEEDER STAGING'
 59                                  else 'N/A'
 60                              end
 61                          ) as OLD_STATUS
 62                   ,  (select max(PURCHASE_ORDER_NUMBER)
 63                          from ORL.FAX_OFFER_DETAIL
 64                          where FAX_ID = fax.ID)
 65                       as MAXPO
 66                   ,  (select case count(PURCHASE_ORDER_NUMBER)
 67                          when 0 then null
 68                          else count(PURCHASE_ORDER_NUMBER)
 69                          end as POCOUNT
 70                            from ORL.FAX_OFFER_DETAIL
 71                           where FAX_ID = fax.ID)
 72                       as POCOUNT
 73                   ,  (select sum(VALUE) as ORDER_VALUE
 74                        from ORL.FAX_OFFER_DETAIL
 75                        where FAX_ID = fax.ID)
 76                      as ORDER_VALUE,
 77           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME AS SALESPERSON_NAME,
 78           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).GROUP_NAME AS GROUP_NAME,
 79           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).ROLE_NAME AS ROLE_NAME,
 80           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).EMAIL AS EMAIL,
 81           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALES_CHANNEL AS SALES_CHANNEL
 82  FROM ORL.FAX_HEADER fax,
 83  APPS_GLOBAL.GLOBAL_BU_MAPPING map,
 84  ORL.FAX_SOURCE src
 85  WHERE fax.WORKGROUP_ID = 262
 86  AND map.GEDIS = 'Y'
 87  AND map.BU_ID = (SELECT BUID from ORL.WORKGROUP where ID = fax.WORKGROUP_ID)
 88  AND ORL.GET_SALESPERSON(map.ORG_ID, fax.SALES_PERSON).GROUP_NAME = 'ORL_GB_Channel_PD3'
 89  AND ORL.GET_SALESPERSON(map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME = 'A_CARRICK'
 90  and src.WORKFLOW_ID IN (select to_number(COLUMN_VALUE) as COLUMN_VALUE
 91  FROM table(SplitClob('1,2,3', ',')) t) and src.ID (+)= fax.FAX_SOURCE)  FAX_LIST
 92  WHERE RN between 1 and 100;

Elapsed: 00:00:00.32


EXECUTION PLAN WITHOUT THE SUGGESTED HINT.

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                        | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                             |     2 | 12092 | 36115   (1)|
|   1 |  NESTED LOOPS                           |                             |     1 |    38 |     3   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP_TRANSITION        |     1 |     9 |     2   (0)|
|   3 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP_TRANSITION     |     1 |       |     1   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP                   |  1482 | 42978 |     1   (0)|
|   5 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP                |     1 |       |     0   (0)|
|   6 |  SORT AGGREGATE                         |                             |     1 |    16 |         |
|   7 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)|
|   8 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|   9 |  SORT AGGREGATE                         |                             |     1 |    16 |         |
|  10 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)|
|  11 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|  12 |  SORT AGGREGATE                         |                             |     1 |    11 |         |
|  13 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    11 |     5   (0)|
|  14 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|  15 |  VIEW                                   |                             |     2 | 12092 | 36115   (1)|
|  16 |   WINDOW SORT PUSHED RANK               |                             |     2 |  1378 | 36115   (1)|
|  17 |    NESTED LOOPS                         |                             |       |       |         |
|  18 |     NESTED LOOPS                        |                             |     2 |  1378 | 36112   (1)|
|  19 |      NESTED LOOPS                       |                             | 20127 |    12M| 15974   (1)|
|  20 |       HASH JOIN RIGHT SEMI              |                             |    68 |  3604 |    52   (2)|
|  21 |        COLLECTION ITERATOR PICKLER FETCH| SPLITCLOB                   |  8168 | 16336 |    29   (0)|
|  22 |        TABLE ACCESS FULL                | FAX_SOURCE                  |  2368 |   117K|    22   (0)|
|  23 |       TABLE ACCESS BY INDEX ROWID       | FAX_HEADER                  |   297 |   177K| 15974   (1)|
|  24 |        BITMAP CONVERSION TO ROWIDS      |                             |       |       |         |
|  25 |         BITMAP AND                      |                             |       |       |         |
|  26 |          BITMAP CONVERSION FROM ROWIDS  |                             |       |       |         |
|  27 |           INDEX RANGE SCAN              | FAX_HEADER_FS_IDX           |  4345 |       |     7   (0)|
|  28 |          BITMAP INDEX SINGLE VALUE      | BITMAPINDX_WORKGROUP_ID_RAM |       |       |         |
|  29 |      INDEX RANGE SCAN                   | GLOBAL_BU_MAPPING_BUID      |     1 |       |     0   (0)|
|  30 |       TABLE ACCESS BY INDEX ROWID       | WORKGROUP                   |     1 |     8 |     2   (0)|
|  31 |        INDEX UNIQUE SCAN                | PK_WORKGROUP                |     1 |       |     1   (0)|
|  32 |     TABLE ACCESS BY INDEX ROWID         | GLOBAL_BU_MAPPING           |     1 |    24 |     1   (0)|
------------------------------------------------------------------------------------------------------------


EXECUTION PLAN WITH THE SUGGESTED HINT.

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                        | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                             |     2 | 12092 |   131K  (1)|
|   1 |  NESTED LOOPS                           |                             |     1 |    38 |     3   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP_TRANSITION        |     1 |     9 |     2   (0)|
|   3 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP_TRANSITION     |     1 |       |     1   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP                   |  1482 | 42978 |     1   (0)|
|   5 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP                |     1 |       |     0   (0)|
|   6 |  SORT AGGREGATE                         |                             |     1 |    16 |         |
|   7 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)|
|   8 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|   9 |  SORT AGGREGATE                         |                             |     1 |    16 |         |
|  10 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)|
|  11 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|  12 |  SORT AGGREGATE                         |                             |     1 |    11 |         |
|  13 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    11 |     5   (0)|
|  14 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)|
|  15 |  VIEW                                   |                             |     2 | 12092 |   131K  (1)|
|  16 |   WINDOW SORT PUSHED RANK               |                             |     2 |  1378 |   131K  (1)|
|  17 |    FILTER                               |                             |       |       |         |
|  18 |     NESTED LOOPS                        |                             |       |       |         |
|  19 |      NESTED LOOPS                       |                             |    16 | 11024 |   131K  (1)|
|  20 |       HASH JOIN RIGHT SEMI              |                             |   532 | 40964 |  2599   (1)|
|  21 |        COLLECTION ITERATOR PICKLER FETCH| SPLITCLOB                   |  8168 | 16336 |    29   (0)|
|  22 |        NESTED LOOPS                     |                             |       |       |         |
|  23 |         NESTED LOOPS                    |                             | 18628 |  1364K|  2569   (1)|
|  24 |          TABLE ACCESS FULL              | GLOBAL_BU_MAPPING           |     8 |   192 |     7   (0)|
|  25 |          INDEX FULL SCAN                | BITMAPINDX_WORKFLOW_ID_RAM  |  2368 |       |     6   (0)|
|  26 |         TABLE ACCESS BY INDEX ROWID     | FAX_SOURCE                  |  2368 |   117K|   320   (0)|
|  27 |       BITMAP CONVERSION TO ROWIDS       |                             |       |       |         |
|  28 |        BITMAP AND                       |                             |       |       |         |
|  29 |         BITMAP CONVERSION FROM ROWIDS   |                             |       |       |         |
|  30 |          INDEX RANGE SCAN               | FAX_HEADER_FS_IDX           |       |       |     7   (0)|
|  31 |         BITMAP INDEX SINGLE VALUE       | BITMAPINDX_WORKGROUP_ID_RAM |       |       |         |
|  32 |      TABLE ACCESS BY INDEX ROWID        | FAX_HEADER                  |     1 |   612 |   131K  (1)|
|  33 |     TABLE ACCESS BY INDEX ROWID         | WORKGROUP                   |     1 |     8 |     2   (0)|
|  34 |      INDEX UNIQUE SCAN                  | PK_WORKGROUP                |     1 |       |     1   (0)|
------------------------------------------------------------------------------------------------------------


  CREATE TABLE "ORL"."WORKGROUP_TRANSITION" 
   (	"ID" NUMBER, 
	"CURRENT_WORKGROUP_ID" NUMBER NOT NULL ENABLE, 
	"NEXT_WORKGROUP_ID" NUMBER NOT NULL ENABLE, 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_DELETED" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	 CONSTRAINT "PK_WORKGROUP_TRANSITION" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "FK_WORKGROUP_TRANSITION_CURR" FOREIGN KEY ("CURRENT_WORKGROUP_ID")
	  REFERENCES "ORL"."WORKGROUP" ("ID") ENABLE, 
	 CONSTRAINT "FK_WORKGROUP_TRANSITION_NEXT" FOREIGN KEY ("NEXT_WORKGROUP_ID")
	  REFERENCES "ORL"."WORKGROUP" ("ID") ENABLE
   ) 


   
  CREATE TABLE "ORL"."WORKGROUP" 
   (	"ID" NUMBER, 
	"BUID" NUMBER, 
	"NAME" NVARCHAR2(128), 
	"MAX_RECORD_NUMBER" NUMBER, 
	"IS_ACTIVE" CHAR(1 CHAR) DEFAULT 'Y' NOT NULL ENABLE, 
	"SLA1" NUMBER, 
	"SLA2" NUMBER, 
	"IS_FILTER" NVARCHAR2(1), 
	"IS_BACKLOG" NVARCHAR2(1), 
	"LOCATION" NVARCHAR2(64), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"IS_EMC" CHAR(1 CHAR) DEFAULT 'N', 
	"IS_SYSTEM_WORKGROUP" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_DELETED" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"DESCRIPTION" NVARCHAR2(1024), 
	 CONSTRAINT "PK_WORKGROUP" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "CH_IS_FILTER" CHECK (IS_FILTER IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_BACKLOG" CHECK (IS_BACKLOG IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_EMC" CHECK (IS_EMC IN ('Y', 'N')) ENABLE
   ) 


   
  CREATE TABLE "ORL"."FAX_OFFER_DETAIL" 
   (	"ID" NUMBER, 
	"FAX_ID" NUMBER, 
	"PURCHASE_ORDER_NUMBER" VARCHAR2(100 CHAR), 
	"OFFER_NUMBER" NUMBER(25,0), 
	"ORDER_NUMBER" NUMBER, 
	"OMEGA_ORDER_NUMBER" NUMBER(22,0), 
	"VALUE" NUMBER, 
	"IS_EMC_ORDER" CHAR(1 CHAR), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_SEC_SUBMITTED" CHAR(1 CHAR) DEFAULT 'N', 
	"SOURCE_OFFER_NO" NUMBER, 
	"VERSION_NO" NUMBER, 
	"SKU_COUNT" NUMBER, 
	 CONSTRAINT "PK_FAX_OFFER_DETAIL" PRIMARY KEY ("ID") ENABLE
   ) 


   
  CREATE TABLE "ORL"."FAX_OFFER_DETAIL" 
   (	"ID" NUMBER, 
	"FAX_ID" NUMBER, 
	"PURCHASE_ORDER_NUMBER" VARCHAR2(100 CHAR), 
	"OFFER_NUMBER" NUMBER(25,0), 
	"ORDER_NUMBER" NUMBER, 
	"OMEGA_ORDER_NUMBER" NUMBER(22,0), 
	"VALUE" NUMBER, 
	"IS_EMC_ORDER" CHAR(1 CHAR), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_SEC_SUBMITTED" CHAR(1 CHAR) DEFAULT 'N', 
	"SOURCE_OFFER_NO" NUMBER, 
	"VERSION_NO" NUMBER, 
	"SKU_COUNT" NUMBER, 
	 CONSTRAINT "PK_FAX_OFFER_DETAIL" PRIMARY KEY ("ID") ENABLE
   ) 


   
  CREATE TABLE "ORL"."FAX_OFFER_DETAIL" 
   (	"ID" NUMBER, 
	"FAX_ID" NUMBER, 
	"PURCHASE_ORDER_NUMBER" VARCHAR2(100 CHAR), 
	"OFFER_NUMBER" NUMBER(25,0), 
	"ORDER_NUMBER" NUMBER, 
	"OMEGA_ORDER_NUMBER" NUMBER(22,0), 
	"VALUE" NUMBER, 
	"IS_EMC_ORDER" CHAR(1 CHAR), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_SEC_SUBMITTED" CHAR(1 CHAR) DEFAULT 'N', 
	"SOURCE_OFFER_NO" NUMBER, 
	"VERSION_NO" NUMBER, 
	"SKU_COUNT" NUMBER, 
	 CONSTRAINT "PK_FAX_OFFER_DETAIL" PRIMARY KEY ("ID") ENABLE
   ) 

   
  CREATE TABLE "ORL"."FAX_SOURCE" 
   (	"ID" NUMBER, 
	"FAX_NUMBER" NVARCHAR2(64), 
	"DESCRIPTION" NVARCHAR2(256), 
	"BUID" NUMBER, 
	"ATTRIBUTES" NVARCHAR2(32), 
	"IS_SEGMENT_REQUIRED" NCHAR(1), 
	"IS_GEDIS_BASENUMBER_REQUIRED" NCHAR(1), 
	"IS_LOB_REQUIRED" NCHAR(1), 
	"IS_SALES_REP_REQUIRED" NCHAR(1), 
	"IS_ORDER_VALUE_REQUIRED" NCHAR(1), 
	"IS_REF_NO_REQUIRED" NCHAR(1), 
	"IS_CUSTOMER_DESCR_REQUIRED" NCHAR(1), 
	"IS_ACCOUNT_TYPE_REQUIRED" NCHAR(1), 
	"IS_PRIORITY_REQUIRED" NCHAR(1), 
	"IS_PAYMENT_TYPE_REQUIRED" NCHAR(1), 
	"IS_ORDER_TYPE_REQUIRED" NCHAR(1), 
	"IS_IR_NUMBER_REQUIRED" NCHAR(1), 
	"IS_MARGIN_REQUIRED" NCHAR(1), 
	"IS_GEDIS_QUOTE_REQUIRED" NCHAR(1), 
	"SALES_REP_ID" NUMBER, 
	"LOB_CODE" VARCHAR2(32 CHAR), 
	"SEGMENT_CODE" VARCHAR2(32 CHAR), 
	"ORDER_VALUE" NVARCHAR2(32), 
	"PAYMENT_METHOD_CODE" VARCHAR2(32 CHAR), 
	"ORDER_TYPE_CODE" VARCHAR2(32 CHAR), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_DELETED" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"ACCOUNT_TYPE_CODE" VARCHAR2(128 CHAR), 
	"PRIORITY_CODE" VARCHAR2(128 CHAR), 
	"IS_VISIBLE_FOR_ALL_WORKGROUPS" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"WORKFLOW_ID" NUMBER, 
	 CONSTRAINT "PK_FAX_SOURCE" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "UK_BU_FAX_SOURCE_NAME" UNIQUE ("BUID", "FAX_NUMBER") ENABLE
   ) 


   
  CREATE TABLE "ORL"."FAX_HEADER" 
   (	"ID" NUMBER, 
	"GENIFAX_RECIPIENT_ID" NVARCHAR2(64), 
	"FAX_SOURCE" NUMBER, 
	"CUSTOMER_NAME" NVARCHAR2(128), 
	"SENDER_EMAIL" NVARCHAR2(256), 
	"SENDER_NAME" NVARCHAR2(256), 
	"SUBJECT" NVARCHAR2(256), 
	"BU_FILE_LOCATION" NVARCHAR2(512), 
	"DATE_RECEIVED" TIMESTAMP (6), 
	"PRIORITY_CODE" VARCHAR2(100 CHAR), 
	"PRIORITY_VALUE" NUMBER DEFAULT 0, 
	"RECIPIENT_BUSINESS_FAX_PHONE" NVARCHAR2(48), 
	"SALES_PERSON" NUMBER, 
	"ACCOUNT_TYPE" NVARCHAR2(32), 
	"PAYMENT_TYPE" NVARCHAR2(32), 
	"ORIGINATOR_CSI" NVARCHAR2(64), 
	"COUNT_PAGES_RECEIVED" NUMBER, 
	"IS_DUPLICATE" NCHAR(1), 
	"IS_TLC_CHECK" NCHAR(1), 
	"ORL_CHECKLIST" NVARCHAR2(256), 
	"WORKGROUP_ID" NUMBER NOT NULL ENABLE, 
	"IS_LOCKED" NCHAR(1), 
	"LOCKED_BY" NVARCHAR2(64), 
	"LOCKED_TIME" TIMESTAMP (6), 
	"UPDATED_BY" NVARCHAR2(128), 
	"CREATED_BY" NVARCHAR2(128) NOT NULL ENABLE, 
	"CREATED_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"UPDATE_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP), 
	"MARGIN" NUMBER, 
	"IS_SPECIAL_RULE_APPLIED" CHAR(1 CHAR) DEFAULT 'N', 
	"CUSTOMER_OMEGA_NUMBER" NVARCHAR2(256), 
	"PRICE_TO_ORDER" NUMBER, 
	"SKU_COUNT_TO_ORDER" NUMBER, 
	"LAST_TRANSITION_ID" NUMBER, 
	"CUSTOMER_NUMBER" NUMBER, 
	"RESERVED_BY" VARCHAR2(64 CHAR), 
	"APPLIED_ROUTING_RULE" NUMBER DEFAULT 0, 
	"IMPERSONATED_BY" NVARCHAR2(128), 
	 CONSTRAINT "PK_FAX_HEADER" PRIMARY KEY ("ID") ENABLE
   ) 

   
  CREATE TABLE "ORL"."WORKGROUP" 
   (	"ID" NUMBER, 
	"BUID" NUMBER, 
	"NAME" NVARCHAR2(128), 
	"MAX_RECORD_NUMBER" NUMBER, 
	"IS_ACTIVE" CHAR(1 CHAR) DEFAULT 'Y' NOT NULL ENABLE, 
	"SLA1" NUMBER, 
	"SLA2" NUMBER, 
	"IS_FILTER" NVARCHAR2(1), 
	"IS_BACKLOG" NVARCHAR2(1), 
	"LOCATION" NVARCHAR2(64), 
	"CREATED_BY" NVARCHAR2(64) NOT NULL ENABLE, 
	"CREATION_DATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE, 
	"IS_EMC" CHAR(1 CHAR) DEFAULT 'N', 
	"IS_SYSTEM_WORKGROUP" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"UPDATED_BY" NVARCHAR2(64), 
	"UPDATED_DATE" TIMESTAMP (6), 
	"IS_DELETED" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE, 
	"DESCRIPTION" NVARCHAR2(1024), 
	 CONSTRAINT "PK_WORKGROUP" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "CH_IS_FILTER" CHECK (IS_FILTER IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_BACKLOG" CHECK (IS_BACKLOG IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N')) ENABLE, 
	 CONSTRAINT "CH_IS_EMC" CHECK (IS_EMC IN ('Y', 'N')) ENABLE
   ) 

   
  CREATE TABLE "APPS_GLOBAL"."GLOBAL_BU_MAPPING" 
   (	"COUNTRY_NAME" VARCHAR2(50 CHAR), 
	"ORG_ID" NUMBER(15,0), 
	"OWNER_NAME" VARCHAR2(50 CHAR), 
	"BU_CODE" VARCHAR2(3 CHAR), 
	"TRACKER_SYSTEM" VARCHAR2(11 CHAR), 
	"PREFERRED_SERVER" VARCHAR2(100 CHAR), 
	"INSTANCE_ID" NUMBER, 
	"DOWNSTREAM_OMEGA" VARCHAR2(30 CHAR), 
	"GEDIS" VARCHAR2(1 CHAR), 
	"BU_ID" NUMBER, 
	"SMARTS_IBU" NUMBER, 
	"DEFAULT_COUNTRY_CODE" VARCHAR2(3 CHAR), 
	"CUSTOMER_PREFIX" VARCHAR2(3 CHAR), 
	"MICROSOFT_TZ_NAME" VARCHAR2(64 CHAR), 
	"ORACLE_TZ_NAME" VARCHAR2(64 CHAR)
   ) 


   
  CREATE INDEX "ORL"."WORKGROUP_TRANSITION_IDX2" ON "ORL"."WORKGROUP_TRANSITION" ("NEXT_WORKGROUP_ID") 
  
  CREATE INDEX "ORL"."WORKGROUP_TRANSITION_IDX1" ON "ORL"."WORKGROUP_TRANSITION" ("CURRENT_WORKGROUP_ID") 
  
  CREATE INDEX "ORL"."WORKGROUP_TRANSITION_IDX3" ON "ORL"."WORKGROUP_TRANSITION" ("IS_DELETED") 
  
  CREATE UNIQUE INDEX "ORL"."PK_WORKGROUP_TRANSITION" ON "ORL"."WORKGROUP_TRANSITION" ("ID") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX6" ON "ORL"."WORKGROUP" ("DESCRIPTION") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX5" ON "ORL"."WORKGROUP" ("IS_BACKLOG") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX4" ON "ORL"."WORKGROUP" ("IS_EMC") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX3" ON "ORL"."WORKGROUP" ("IS_DELETED") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX2" ON "ORL"."WORKGROUP" ("IS_ACTIVE") 
  
  CREATE INDEX "ORL"."WORKGROUP_IDX1" ON "ORL"."WORKGROUP" ("BUID") 
  
  CREATE UNIQUE INDEX "ORL"."PK_WORKGROUP" ON "ORL"."WORKGROUP" ("ID") 
  
  CREATE INDEX "ORL"."IDX_BUID_RAM" ON "ORL"."FAX_SOURCE" ("BUID") 
  
  CREATE BITMAP INDEX "ORL"."INDX_IS_DELETED_RAM" ON "ORL"."FAX_SOURCE" ("IS_DELETED") 
  
  CREATE INDEX "ORL"."INDX_FAX_NUMBER_RAM" ON "ORL"."FAX_SOURCE" (UPPER("FAX_NUMBER")) 
  
  CREATE UNIQUE INDEX "ORL"."UK_BU_FAX_SOURCE_NAME" ON "ORL"."FAX_SOURCE" ("BUID", "FAX_NUMBER") 
  
  CREATE UNIQUE INDEX "ORL"."PK_FAX_SOURCE" ON "ORL"."FAX_SOURCE" ("ID") 
  
  CREATE UNIQUE INDEX "ORL"."PK_FAX_OFFER_DETAIL" ON "ORL"."FAX_OFFER_DETAIL" ("ID") 
  
  CREATE INDEX "ORL"."FAX_OFFER_DETAIL_IDX3" ON "ORL"."FAX_OFFER_DETAIL" ("VERSION_NO") 
  
  CREATE INDEX "ORL"."FAX_OFFER_DETAIL_IDX2" ON "ORL"."FAX_OFFER_DETAIL" ("SOURCE_OFFER_NO") 
  
  CREATE INDEX "ORL"."FAX_OFFER_DETAIL_IDX1" ON "ORL"."FAX_OFFER_DETAIL" ("FAX_ID") 
  
  CREATE INDEX "ORL"."IDX_PURCHASE_ORDER_NUMBER" ON "ORL"."FAX_OFFER_DETAIL" ("PURCHASE_ORDER_NUMBER") 
  
  CREATE INDEX "ORL"."IDX_OMEGA_ORDER_NUMBER" ON "ORL"."FAX_OFFER_DETAIL" ("OMEGA_ORDER_NUMBER") 
  
  CREATE INDEX "ORL"."IDX_ORDER_NUMBER" ON "ORL"."FAX_OFFER_DETAIL" ("ORDER_NUMBER") 
  
  CREATE INDEX "ORL"."IDX_OFFER_NUMBER" ON "ORL"."FAX_OFFER_DETAIL" ("OFFER_NUMBER") 
  
  CREATE BITMAP INDEX "ORL"."IDX_FAX_HEADER_SPECIAL_RULE" ON "ORL"."FAX_HEADER" ("IS_SPECIAL_RULE_APPLIED") 
  
  CREATE INDEX "ORL"."IDX_FAX_HEADER_UPPER_SUBJECT" ON "ORL"."FAX_HEADER" (UPPER("SUBJECT")) 
  
  CREATE INDEX "ORL"."FAX_HEADER_SUBJ_IDX2" ON "ORL"."FAX_HEADER" ( REGEXP_SUBSTR (UPPER("SUBJECT"),U'(QUOTE|DEVIS)005CD{0,}005Cd{3,}',1,1,'i',1)) 
  
  CREATE INDEX "ORL"."FAX_HEADER_IDX2" ON "ORL"."FAX_HEADER" (UPPER("CUSTOMER_NAME")) 
  
  CREATE UNIQUE INDEX "ORL"."PK_FAX_HEADER" ON "ORL"."FAX_HEADER" ("ID") 
  
  CREATE INDEX "ORL"."IDX_FAX_HEADER_SUBJECT" ON "ORL"."FAX_HEADER" ("SUBJECT") 
  
  CREATE INDEX "ORL"."IDX_FAX_HEADER_LAST_TRANSITION" ON "ORL"."FAX_HEADER" ("LAST_TRANSITION_ID") 
  
  CREATE INDEX "ORL"."FAX_HEADER_IDX6" ON "ORL"."FAX_HEADER" ("CUSTOMER_OMEGA_NUMBER") 
  
  CREATE INDEX "ORL"."FAX_HEADER_IDX5" ON "ORL"."FAX_HEADER" ("SALES_PERSON") 
  
  CREATE INDEX "ORL"."FAX_HEADER_IDX4" ON "ORL"."FAX_HEADER" ("DATE_RECEIVED") 
  
  CREATE INDEX "ORL"."FAX_HEADER_FS_IDX" ON "ORL"."FAX_HEADER" ("FAX_SOURCE") 
  
  CREATE INDEX "APPS_GLOBAL"."BITMAPINDX_WORKFLOW_ID_RAM" ON "ORL"."FAX_SOURCE" ("WORKFLOW_ID") 
  
  CREATE BITMAP INDEX "APPS_GLOBAL"."BITMAPINDX_WORKGROUP_ID_RAM" ON "ORL"."FAX_HEADER" ("WORKGROUP_ID") 
  
  CREATE INDEX "APPS_GLOBAL"."IDX_GBM_ORG_ID_TST" ON "APPS_GLOBAL"."GLOBAL_BU_MAPPING" ("ORG_ID") 
  
  CREATE INDEX "APPS_GLOBAL"."GLOBAL_BU_MAPPING_BUID" ON "APPS_GLOBAL"."GLOBAL_BU_MAPPING" ("BU_ID") 


Please help me.

Thanks in advance.
Re: Please tune this query. [message #589193 is a reply to message #589115] Wed, 03 July 2013 04:41 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Any body please help me on this.

Thanks in advance.
Re: Please tune this query. [message #589291 is a reply to message #589193] Thu, 04 July 2013 02:05 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, ajay
Could you fix your plan table issue and provide predicate info ?

[Updated on: Thu, 04 July 2013 02:08]

Report message to a moderator

Re: Please tune this query. [message #589380 is a reply to message #589291] Fri, 05 July 2013 00:58 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Thanks for your response.

What Should I do to fix plan table issue.

Thanks.
Re: Please tune this query. [message #589390 is a reply to message #589380] Fri, 05 July 2013 03:06 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
In your previous topic, I gave you the steps to re-create your plan_table.
Re: Please tune this query. [message #589430 is a reply to message #589380] Fri, 05 July 2013 09:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
ajaykumarkona wrote on Fri, 05 July 2013 11:28
What Should I do to fix plan table issue.


Ajay -

1. Your 'PLAN_TABLE' is old version. From $ORACLE_HOME/rdbms/admin/ please run "utlxplan".

2. Also, please mention the numer of rows the query returns. This is to check if rows in explain plan matches the number of actual rows returned.

3. Could you please include Access pridicates and time in your explain plan.
Re: Please tune this query. [message #589525 is a reply to message #589430] Mon, 08 July 2013 04:29 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Thanks for your response.

I have resolved the plan table issue.
The inner query is returning 4,372 records.
Finally it will return 100 records because we have filtering the records ROWNUM 1 and 100.
I am included access predicates and time in your explain plan.
I am sending latest query and execution plan .

SQL> explain plan for
  2  select FAX_LIST.*
  3   from  (select row_number() over ( order by fax.ID ASC ) RN,
  4  fax.ACCOUNT_TYPE,
  5                       fax.BU_FILE_LOCATION,
  6                       fax.COUNT_PAGES_RECEIVED,
  7                       nvl(fax.CUSTOMER_NAME, fax.SENDER_NAME) AS CUSTOMER_NAME,
  8                       fax.FAX_SOURCE,
  9                       fax.CUSTOMER_NUMBER,
 10                       fax.CUSTOMER_OMEGA_NUMBER,
 11                       fax.GENIFAX_RECIPIENT_ID,
 12                       fax.ID AS FAX_ID,
 13                       fax.DATE_RECEIVED AS FAX_RECEIVED,
 14                       fax.IS_LOCKED,
 15                       fax.LOCKED_BY,
 16                       fax.ORIGINATOR_CSI,
 17                       fax.MARGIN,
 18                       fax.PAYMENT_TYPE,
 19                       fax.PRIORITY_CODE,
 20                       fax.PRIORITY_VALUE,
 21                       FROM_TZ (fax.DATE_RECEIVED, 'UTC') AT TIME ZONE map.ORACLE_TZ_NAME AS DATE_RECEIVED,
 22                       fax.SALES_PERSON,
 23                       fax.SENDER_EMAIL,
 24                       fax.SENDER_NAME,
 25                       fax.SUBJECT,
 26                       fax.WORKGROUP_ID,
 27                       fax.LOCKED_TIME,
 28                       src.DESCRIPTION,
 29                       src.FAX_NUMBER,
 30                       src.WORKFLOW_ID,
 31                       fax.CREATED_BY,
 32                       fax.CREATED_DATE,
 33                       fax.UPDATE_DATE,
 34                       fax.UPDATED_BY,
 35                       fax.RESERVED_BY,
 36                       fax.PRICE_TO_ORDER,
 37                       fax.SKU_COUNT_TO_ORDER
 38                   ,   nvl(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_BY,
 39                              case
 40                                  when fax.LAST_TRANSITION_ID is null
 41                                      and fax.GENIFAX_RECIPIENT_ID is not null
 42                                  then 'ORL FEEDER'
 43                                  else 'Admin'
 44                              end
 45                           ) AS LAST_USER
 46                   ,   from_tz(nvl(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).CHANGED_DATE, fax.DATE_RECEIVED), 'UTC')
 47                   at time zone map.ORACLE_TZ_NAME AS MOVE_DATETIME
 48                   ,   NVL(ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).REASON, 'Reroute') AS MOVE_REASON
 49                   ,   NVL((select fw.DESCRIPTION
 50                                  from ORL.WORKGROUP_TRANSITION wt
 51                                      , ORL.WORKGROUP fw
 52                                  where wt.ID = ORL.GET_FTH_WTR (fax.LAST_TRANSITION_ID).WORKGROUP_TRANSITION_ID
 53                                  and fw.ID = wt.CURRENT_WORKGROUP_ID
 54                              ),
 55                              case
 56                                  when fax.LAST_TRANSITION_ID is null
 57                                      and fax.GENIFAX_RECIPIENT_ID is not null
 58                                  then 'ORL FEEDER STAGING'
 59                                  else 'N/A'
 60                              end
 61                          ) as OLD_STATUS
 62                   ,  (select max(PURCHASE_ORDER_NUMBER)
 63                          from ORL.FAX_OFFER_DETAIL
 64                          where FAX_ID = fax.ID)
 65                       as MAXPO
 66                   ,  (select case count(PURCHASE_ORDER_NUMBER)
 67                          when 0 then null
 68                          else count(PURCHASE_ORDER_NUMBER)
 69                          end as POCOUNT
 70                            from ORL.FAX_OFFER_DETAIL
 71                           where FAX_ID = fax.ID)
 72                       as POCOUNT
 73                   ,  (select sum(VALUE) as ORDER_VALUE
 74                        from ORL.FAX_OFFER_DETAIL
 75                        where FAX_ID = fax.ID)
 76                      as ORDER_VALUE,
 77           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME AS SALESPERSON_NAME,
 78           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).GROUP_NAME AS GROUP_NAME,
 79           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).ROLE_NAME AS ROLE_NAME,
 80           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).EMAIL AS EMAIL,
 81           ORL.GET_SALESPERSON (map.ORG_ID, fax.SALES_PERSON).SALES_CHANNEL AS SALES_CHANNEL
 82  from ORL.FAX_HEADER fax,
 83  APPS_GLOBAL.GLOBAL_BU_MAPPING map,
 84  ORL.FAX_SOURCE src
 85  where
 86  fax.WORKGROUP_ID = 262
 87  and map.GEDIS = 'Y'
 88  and map.BU_ID = (select BUID from ORL.WORKGROUP where ID = fax.WORKGROUP_ID)
 89  and ORL.GET_SALESPERSON(map.ORG_ID, fax.SALES_PERSON).GROUP_NAME = 'ORL_GB_Channel_PD3'
 90  and ORL.GET_SALESPERSON(map.ORG_ID, fax.SALES_PERSON).SALESPERSON_NAME = 'A_CARRICK'
 91  and src.WORKFLOW_ID IN (
 92  select to_number(COLUMN_VALUE) as COLUMN_VALUE
 93  from table(SplitClob('1,2,3',',')) t) and src.ID (+)= fax.FAX_SOURCE
 94  )  FAX_LIST
 95  WHERE
 96  RN between 1 and 100;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 1377120664

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                             |     2 | 12092 | 36115   (1)| 00:07:14 |
|   1 |  NESTED LOOPS                           |                             |     1 |    38 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP_TRANSITION        |     1 |     9 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP_TRANSITION     |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID           | WORKGROUP                   |  1482 | 42978 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                    | PK_WORKGROUP                |     1 |       |     0   (0)| 00:00:01 |

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

|   6 |  SORT AGGREGATE                         |                             |     1 |    16 |         |             |
|   7 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)| 00:00:01 |
|   9 |  SORT AGGREGATE                         |                             |     1 |    16 |         |             |
|  10 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    16 |     5   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)| 00:00:01 |
|  12 |  SORT AGGREGATE                         |                             |     1 |    11 |         |             |
|  13 |   TABLE ACCESS BY INDEX ROWID           | FAX_OFFER_DETAIL            |     1 |    11 |     5   (0)| 00:00:01 |
|* 14 |    INDEX RANGE SCAN                     | FAX_OFFER_DETAIL_IDX1       |     1 |       |     3   (0)| 00:00:01 |
|* 15 |  VIEW                                   |                             |     2 | 12092 | 36115   (1)| 00:07:14 |
|* 16 |   WINDOW SORT PUSHED RANK               |                             |     2 |  1378 | 36115   (1)| 00:07:14 |

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

|  17 |    NESTED LOOPS                         |                             |       |       |         |             |
|  18 |     NESTED LOOPS                        |                             |     2 |  1378 | 36112   (1)| 00:07:14 |
|  19 |      NESTED LOOPS                       |                             | 20127 |    12M| 15974   (1)| 00:03:12 |
|* 20 |       HASH JOIN RIGHT SEMI              |                             |    68 |  3604 |    52   (2)| 00:00:01 |
|  21 |        COLLECTION ITERATOR PICKLER FETCH| SPLITCLOB                   |  8168 | 16336 |    29   (0)| 00:00:01 |
|  22 |        TABLE ACCESS FULL                | FAX_SOURCE                  |  2368 |   117K|    22   (0)| 00:00:01 |
|  23 |       TABLE ACCESS BY INDEX ROWID       | FAX_HEADER                  |   297 |   177K| 15974   (1)| 00:03:12 |
|  24 |        BITMAP CONVERSION TO ROWIDS      |                             |       |       |         |             |
|  25 |         BITMAP AND                      |                             |       |       |         |             |
|  26 |          BITMAP CONVERSION FROM ROWIDS  |                             |       |       |         |             |
|* 27 |           INDEX RANGE SCAN              | FAX_HEADER_FS_IDX           |  4345 |       |     7   (0)| 00:00:01 |

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

|* 28 |          BITMAP INDEX SINGLE VALUE      | BITMAPINDX_WORKGROUP_ID_RAM |       |       |         |             |
|* 29 |      INDEX RANGE SCAN                   | GLOBAL_BU_MAPPING_BUID      |     1 |       |     0   (0)| 00:00:01 |
|  30 |       TABLE ACCESS BY INDEX ROWID       | WORKGROUP                   |     1 |     8 |     2   (0)| 00:00:01 |
|* 31 |        INDEX UNIQUE SCAN                | PK_WORKGROUP                |     1 |       |     1   (0)| 00:00:01 |
|* 32 |     TABLE ACCESS BY INDEX ROWID         | GLOBAL_BU_MAPPING           |     1 |    24 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

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

   3 - access("WT"."ID"=SYS_OP_ATG("ORL"."GET_FTH_WTR"(:B1),4,5,2))

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

   5 - access("FW"."ID"="WT"."CURRENT_WORKGROUP_ID")
   8 - access("FAX_ID"=:B1)
  11 - access("FAX_ID"=:B1)
  14 - access("FAX_ID"=:B1)
  15 - filter("RN">=1 AND "RN"<=100)
  16 - filter(ROW_NUMBER() OVER ( ORDER BY "FAX"."ID")<=100)
  20 - access("SRC"."WORKFLOW_ID"=TO_NUMBER(VALUE(KOKBF$)))
  27 - access("SRC"."ID"="FAX"."FAX_SOURCE")
  28 - access("FAX"."WORKGROUP_ID"=262)
  29 - access("MAP"."BU_ID"= (SELECT "BUID" FROM "ORL"."WORKGROUP" "WORKGROUP" WHERE "ID"=:B1))
  31 - access("ID"=:B1)

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

  32 - filter("MAP"."GEDIS"='Y' AND SYS_OP_ATG("ORL"."GET_SALESPERSON"("MAP"."ORG_ID","FAX"."SALES_PERSON"),7,8
              ,2)='ORL_GB_Channel_PD3' AND SYS_OP_ATG("ORL"."GET_SALESPERSON"("MAP"."ORG_ID","FAX"."SALES_PERSON"),3,4,2)='A_
              CARRICK')

58 rows selected.


Please help me.
Thanks in advance.
Re: Please tune this query. [message #589587 is a reply to message #589525] Tue, 09 July 2013 00:27 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Lalith,

Could you please help me .

Thanks.
Re: Please tune this query. [message #589618 is a reply to message #589587] Tue, 09 July 2013 03:14 Go to previous messageGo to next message
michael_bialik
Messages: 605
Registered: July 2006
Senior Member
After posting over 300 messages - post TKPROF.
Re: Please tune this query. [message #590260 is a reply to message #589618] Tue, 16 July 2013 07:25 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I am sending TKPROF file.

Please help me.

Thanks.
Re: Please tune this query. [message #590261 is a reply to message #590260] Tue, 16 July 2013 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like your real problem is these:
SELECT OWNER_NAME 
FROM
 APPS_GLOBAL.GLOBAL_BU_MAPPING WHERE ORG_ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 661661     11.54      11.73          0          0          0           0
Fetch   661661     10.70      10.92          0    1323322          0      661661
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1323323     22.24      22.66          0    1323322          0      661661

        select SALESPERSON_NAME
             , EMAIL
             , SALES_CHANNEL
             , ROLE_NAME
             , GROUP_NAME
          from APPS_UK.ORL_ACTIVE_SALESPERSON
         where SALESPERSON_ID = :1
		 and  rownum = 1
            

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute 661661     30.88      31.04          0          0          0           0
Fetch   661661    111.87     112.94         42    9059169          0      587745
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1323324    142.76     143.99         42    9059169          0      587745


Are the above called by ORL.GET_SALESPERSON by any chance?
Re: Please tune this query. [message #590330 is a reply to message #590261] Wed, 17 July 2013 04:45 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Yes,

I am posting the code for ORL.GET_SALESPERSON.

CREATE OR REPLACE function ORL.GET_SALESPERSON(ORG_ID number, SALESPERSON_ID number)
    return ORL.SALESPERSON_T
    deterministic
is
    apps_bu             varchar2(30);
    salesperson_name    varchar2(150 char);
    email               varchar2(70 char);
    sales_channel       varchar2(60 char);
    role_name           varchar2(60 char);
    group_name          varchar2(60 char);
begin
    select OWNER_NAME into apps_bu
      from APPS_GLOBAL.GLOBAL_BU_MAPPING
     where ORG_ID = GET_SALESPERSON.ORG_ID;
    execute immediate
    replace('
        select SALESPERSON_NAME
             , EMAIL
             , SALES_CHANNEL
             , ROLE_NAME
             , GROUP_NAME
          from APPS_**.ORL_ACTIVE_SALESPERSON
         where SALESPERSON_ID = :1
		 and  rownum = 1
            ', 'APPS_**', apps_bu)
    into salesperson_name, email, sales_channel, role_name, group_name
    using SALESPERSON_ID;
    return SALESPERSON_T(ORG_ID, SALESPERSON_ID, salesperson_name, email, sales_channel, role_name, group_name);
exception
    when no_data_found then
        return SALESPERSON_T(null, null, null, null, null, null, null);
end GET_SALESPERSON;


Please help me.

Thanks.
Re: Please tune this query. [message #590334 is a reply to message #590330] Wed, 17 July 2013 05:09 Go to previous messageGo to next message
cookiemonster
Messages: 11203
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really need to rewrite the query so you don't have to call the function.
Re: Please tune this query. [message #590458 is a reply to message #590334] Thu, 18 July 2013 06:00 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
What is the alternative for this.

Thanks.
Re: Please tune this query. [message #591589 is a reply to message #590458] Mon, 29 July 2013 23:54 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Any body please help me.


Thanks.
Re: Please tune this query. [message #591600 is a reply to message #591589] Tue, 30 July 2013 02:38 Go to previous messageGo to next message
michael_bialik
Messages: 605
Registered: July 2006
Senior Member
1. What is the result of

SELECT COUNT(*) FROM ORL.FAX_HEADER FH  WHERE FH.WORKGROUP_ID = 262
query?

2. Why do you need to use the pipe-lined function SplitClob instead of using simple condition

AND src.WORKFLOW_ID BETWEEN 1 AND 3
?

3. The function ORL.GET_SALESPERSON is called 661661 times. Each time it performs

SELECT OWNER_NAME FROM APPS_GLOBAL.GLOBAL_BU_MAPPING WHERE ORG_ID = :B1


However the table APPS_GLOBAL.GLOBAL_BU_MAPPING is already accessed in the main query.

Pass the OWNER_NAME instead of ORG_ID and to eliminate that SQL statement.

Rewrite that function as PIPE-lined ( It's possible that Oracle disregards DETERMINISTIC option).

4. Try rewriting the statement (AFTER you remove unnecessary SELECT from function) as

...
  from ( SELECT * FROM ORL.FAX_SOURCE 
         WHERE  WORKFLOW_ID BETWEEN 1 AND 3 ) src,
       ORL.FAX_HEADER                fax,
       ORL.WORKGROUP                 GRP,
       APPS_GLOBAL.GLOBAL_BU_MAPPING map
 where fax.WORKGROUP_ID = 262
   AND GRP.ID = fax.WORKGROUP_ID
   and map.GEDIS = 'Y'
   and map.BU_ID = GRP.BUID
   and ORL.GET_SALESPERSON(map.OWNER_NAME, fax.SALES_PERSON).GROUP_NAME = 'ORL_GB_Channel_PD3'
   and ORL.GET_SALESPERSON(map.OWNER_NAME, fax.SALES_PERSON).SALESPERSON_NAME = 'A_CARRICK'
   and src.ID(+) = fax.FAX_SOURCE


HTH.
Re: Please tune this query. [message #591611 is a reply to message #591589] Tue, 30 July 2013 04:17 Go to previous message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ajay -

The reason the hints did not prove helpful is that the hint was just to force the nested loops in an order per the table sizes, however, the culprit for poor performance is the function call. Very clear in the tkprof output.

If you also see, the ITERATOR in the execution plan, it must loop each of it's child 8168 times. So the total rows actually processed become 8168 times the rows returned in the child steps. To know results of each child steps, you need to do query decomposition and reconstruction.

Few more observations on the DDLs you posted. Not necessarily would impact the performance, though good to be aware of.

1. Few tables use column data type as NVARCHAR2, while other tables use VARCHAR2. What's the reason behind doing this?
2. 1 character long columns have been declared as CHAR datatype. Though immaterial of saying that it has any difference as compared to VARCHAR2(1). It's a bad practice.
See - CHAR is a VARCHAR2 in disguise
Myths about CHAR data type performance
3. Hope you are aware of declaring size as char or bytes-
varchar2(20 char) --> stores 20 characters, whereas varchar2(20) stores 20 bytes. So, a varchar2(x char) will end up when it becomes 4000 bytes(maximum in Oracle).
Previous Topic: Need Help In Performance Tuning
Next Topic: How to determine memory usage for a function
Goto Forum:
  


Current Time: Thu Nov 27 06:04:29 CST 2014

Total time taken to generate the page: 0.11667 seconds