Home » RDBMS Server » Performance Tuning » Please tune this query. (Oracle 11G)
| Please tune this query. [message #588455] |
Wed, 26 June 2013 01:07  |
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 #588466 is a reply to message #588458] |
Wed, 26 June 2013 02:07  |
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 |
Lalit Kumar B
Messages: 3174
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 |
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 |
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 #588683 is a reply to message #588612] |
Thu, 27 June 2013 06:54 |
Lalit Kumar B
Messages: 3174
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 |
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 #589430 is a reply to message #589380] |
Fri, 05 July 2013 09:16 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ajaykumarkona wrote on Fri, 05 July 2013 11:28What 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 |
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 #590261 is a reply to message #590260] |
Tue, 16 July 2013 07:37 |
cookiemonster
Messages: 13962
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 |
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 #591600 is a reply to message #591589] |
Tue, 30 July 2013 02:38 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. What is the result of
SELECT COUNT(*) FROM ORL.FAX_HEADER FH WHERE FH.WORKGROUP_ID = 262
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 |
Lalit Kumar B
Messages: 3174
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).
|
|
|
|
Goto Forum:
Current Time: Thu Jan 23 14:50:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
