Home » RDBMS Server » Performance Tuning » Help in tuning a value set query
| Help in tuning a value set query [message #506912] |
Wed, 11 May 2011 15:42  |
 |
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
|
Junior Member |
|
|
Hi All,
Can anyone please help me in tuning a value set query, this query has to fetch eligible data for last 6 months.
Below is the query:
select /*+ index(a OE_ORDER_HEADERS_U2) */ ORDER_NUMBER
from OE_ORDER_HEADERS_ALL a
where to_char(order_number) in(
SELECT to_char(ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :ORDER_cancelled_flag = 'N'
AND :ORDER_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :ORDER_SOLD_TO_ORG_ID
AND ( ooha.attribute16 = to_char(ooha.order_number)
)
AND :SYSTEM_CURRENT_BLOCK='ORDER'
union all
SELECT to_char(ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :ORDER_cancelled_flag = 'N'
AND :ORDER_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :ORDER_SOLD_TO_ORG_ID
AND ( ooha.order_number = :ORDER_ORDER_NUMBER
)
AND :SYSTEM_CURRENT_BLOCK='ORDER'
union all
select decode(:SYSTEM_CURRENT_BLOCK,'ORDER',(select NVL(to_char(:ORDER_order_number),'123') from dual where :ORDER_cancelled_flag = 'Y'
) )
from dual
union all
select decode(:SYSTEM_CURRENT_BLOCK,'ORDER',(select NVL(to_char(:ORDER_order_number),'123') from dual where :ORDER_open_flag = 'N') )
from dual
union all
select /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
to_char(ooh.order_number) from oe_order_headers_all ooh where :SYSTEM_CURRENT_BLOCK <> 'ORDER'
and creation_date > sysdate - 90
)
Also find the explain plan below:
SELECT STATEMENT ALL_ROWSCost: 16,983,189 Bytes: 18,789,118 Cardinality: 507,814
26 HASH JOIN Cost: 16,983,189 Bytes: 18,789,118 Cardinality: 507,814
24 VIEW VIEW SYS.VW_NSO_1 Cost: 400,631 Bytes: 11,171,446 Cardinality: 507,793
23 HASH UNIQUE Cost: 400,631 Bytes: 8,632,497 Cardinality: 507,793
22 UNION-ALL
6 FILTER
5 NESTED LOOPS ANTI Cost: 97 Bytes: 40 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 93 Bytes: 27 Cardinality: 1
1 INDEX RANGE SCAN INDEX IKNCU.IKN_OE_ORDER_HEADERS_PRF_N3 Cost: 3 Cardinality: 93
4 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 404,500,590 Cardinality: 31,115,430
3 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
12 FILTER
11 NESTED LOOPS ANTI Cost: 8 Bytes: 44 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 4 Bytes: 31 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 3 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 404,500,590 Cardinality: 31,115,430
9 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
14 FILTER
13 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
17 FILTER
16 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
21 FILTER
20 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 400,522 Bytes: 8,632,413 Cardinality: 507,789
19 INDEX RANGE SCAN INDEX ONT.IKN_OE_HEADERS_ALL_PRF_N1 Cost: 2,295 Cardinality: 507,789
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 105,323 Bytes: 310,485,750 Cardinality: 20,699,050
|
|
|
|
|
|
| Re: Help in tuning a value set query [message #506930 is a reply to message #506917] |
Wed, 11 May 2011 21:35  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Focus on these:
24 VIEW VIEW SYS.VW_NSO_1 Cost: 400,631 Bytes: 11,171,446 Cardinality: 507,793
and
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 105,323 Bytes: 310,485,750 Cardinality: 20,699,050
[Updated on: Thu, 12 May 2011 03:30] by Moderator Report message to a moderator |
|
|
|
| Re: Help in tuning a value set query [message #506992 is a reply to message #506912] |
Thu, 12 May 2011 03:20 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
kavitayetakuri wrote on Wed, 11 May 2011 21:42this query has to fetch eligible data for last 6 months.
Really? It doesn't appear to be doing that at moment. You've got one section with a date restriction of 90 days and the rest has no date restriction at all as far as I can see.
|
|
|
|
|
|
|
|
| Re: Help in tuning a value set query [message #507113 is a reply to message #506917] |
Thu, 12 May 2011 09:58 |
|
kavitayetakuri
Messages: 7
Registered: May 2011
Location: United States
|
Junior Member |
|
|
Hi Blackswan,
1. Please find the formatted code and requested data below:
SELECT /*+ index(a OE_ORDER_HEADERS_U2) */
order_number
FROM oe_order_headers_all a
WHERE TO_CHAR (order_number) IN (
SELECT TO_CHAR (ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :order_cancelled_flag = 'N'
AND :order_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :order_sold_to_org_id
AND (ooha.attribute16 = TO_CHAR (ooha.order_number))
AND :system_current_block = 'ORDER'
UNION ALL
SELECT TO_CHAR (ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :order_cancelled_flag = 'N'
AND :order_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :order_sold_to_org_id
AND (ooha.order_number = :order_order_number)
AND :system_current_block = 'ORDER'
UNION ALL
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_cancelled_flag = 'Y')
)
FROM DUAL
UNION ALL
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_open_flag = 'N')
)
FROM DUAL
UNION ALL
SELECT /*+ index(ooh IKN_OE_HEADERS_ALL_PRF_N1) */
TO_CHAR (ooh.order_number)
FROM oe_order_headers_all ooh
WHERE :system_current_block <> 'ORDER'
AND creation_date > SYSDATE - 180)
2. Attached the explain plan below:
Plan
SELECT STATEMENT ALL_ROWSCost: 16,470,758 Bytes: 31,470,905 Cardinality: 850,565
26 HASH JOIN Cost: 16,470,758 Bytes: 31,470,905 Cardinality: 850,565
24 VIEW VIEW SYS.VW_NSO_1 Cost: 665,147 Bytes: 18,711,880 Cardinality: 850,540
23 HASH UNIQUE Cost: 665,147 Bytes: 14,459,196 Cardinality: 850,540
22 UNION-ALL
6 FILTER
5 NESTED LOOPS ANTI Cost: 96 Bytes: 40 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 92 Bytes: 27 Cardinality: 1
1 INDEX RANGE SCAN INDEX ONT.OE_ORDER_HEADERS_N2 Cost: 3 Cardinality: 92
4 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 394,319,250 Cardinality: 30,332,250
3 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
12 FILTER
11 NESTED LOOPS ANTI Cost: 8 Bytes: 44 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 4 Bytes: 31 Cardinality: 1
7 INDEX RANGE SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 3 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_LINES_ALL Cost: 4 Bytes: 394,319,250 Cardinality: 30,332,250
9 INDEX RANGE SCAN INDEX ONT.OE_ORDER_LINES_N1 Cost: 3 Cardinality: 2
14 FILTER
13 FAST DUAL Cost: 2 Cardinality: 1
15 FAST DUAL Cost: 2 Cardinality: 1
17 FILTER
16 FAST DUAL Cost: 2 Cardinality: 1
18 FAST DUAL Cost: 2 Cardinality: 1
21 FILTER
20 TABLE ACCESS BY INDEX ROWID TABLE ONT.OE_ORDER_HEADERS_ALL Cost: 665,039 Bytes: 14,459,112 Cardinality: 850,536
19 INDEX RANGE SCAN INDEX ONT.IKN_OE_HEADERS_ALL_PRF_N1 Cost: 3,839 Cardinality: 850,536
25 INDEX FULL SCAN INDEX (UNIQUE) ONT.OE_ORDER_HEADERS_U2 Cost: 101,120 Bytes: 304,005,150 Cardinality: 20,267,010
3. Also find the DDL and indexes for the table as an attachment
4. Since it is a value set query attached to DFF I'm not sure how to provide tkprof and tracefile for this.
Thanks,
Kavita
|
|
|
|
|
|
| Re: Help in tuning a value set query [message #507115 is a reply to message #507113] |
Thu, 12 May 2011 10:13 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_cancelled_flag = 'Y')
)
FROM DUAL
UNION ALL
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_open_flag = 'N')
)
FROM DUAL
Can be as
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_cancelled_flag in ('Y','N') |
|
|
|
| Re: Help in tuning a value set query [message #507118 is a reply to message #507115] |
Thu, 12 May 2011 10:14 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
SELECT TO_CHAR (ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :order_cancelled_flag = 'N'
AND :order_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :order_sold_to_org_id
[color=red]AND (ooha.attribute16 = TO_CHAR (ooha.order_number))[/color]------------see here
AND :system_current_block = 'ORDER'
UNION ALL
SELECT TO_CHAR (ooha.order_number)
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :order_cancelled_flag = 'N'
AND :order_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :order_sold_to_org_id
[color=red] AND (ooha.order_number = :order_order_number)[/color]
AND :system_current_block = 'ORDER'
What the importance of this ...repeted?
Sriram
[Updated on: Thu, 12 May 2011 10:16] Report message to a moderator |
|
|
|
| Re: Help in tuning a value set query [message #507121 is a reply to message #507114] |
Thu, 12 May 2011 10:37 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
kavitayetakuri wrote on Thu, 12 May 2011 16:09To RoachCoach,
If I don't give To_char() its giving me datatype mismatch error for the union queries.
Regards,
Kavita
Try removing the to_chars and wrapping the decode in a to_number.
|
|
|
|
|
|
| Re: Help in tuning a value set query [message #507124 is a reply to message #507122] |
Thu, 12 May 2011 10:55 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd remove alll the hints and see what happens.
And I'd change:
SELECT DECODE (:system_current_block,
'ORDER', (SELECT NVL (TO_CHAR (:order_order_number),
'123'
)
FROM DUAL
WHERE :order_cancelled_flag IN ('Y','N'))
)
FROM DUAL
to:
SELECT DECODE (:system_current_block,
'ORDER',
NVL (:order_order_number, 123),
NULL
)
FROM DUAL
WHERE :order_cancelled_flag IN ('Y','N')
Or if you are on oracle 9i or higher (and you really should be) then case is easier to follow:
SELECT CASE WHEN :order_cancelled_flag IN ('Y','N')
AND :system_current_block = 'ORDER'
THEN NVL (:order_order_number, 123)
ELSE NULL
END
FROM dual
|
|
|
|
| Re: Help in tuning a value set query [message #507125 is a reply to message #507122] |
Thu, 12 May 2011 10:57 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
"oe_order_lines_all" DDL does not exist in your file.
ORA-01471: cannot create a synonym with same name as object.
Quote:Or if you are on oracle 9i or higher (and you really should be) then case is easier to follow:
But if Op using this in his Report/Forms of version <9i then case wont work.
@ OP
this why Blackswan said Quote:"It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/ "
Post your Oracle version with 4 digits
Quote:I'd remove alll the hints and see what happens.
Ok
SELECT
order_number
FROM oe_order_headers_all a
WHERE order_number IN
(
SELECT ooha.order_number
FROM oe_order_headers_all ooha
WHERE ooha.cancelled_flag = 'N'
AND ooha.open_flag = 'Y'
AND :order_cancelled_flag = 'N'
AND :order_open_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = ooha.header_id
AND oola.invoice_interface_status_code = 'YES')
AND ooha.sold_to_org_id = :order_sold_to_org_id
And((to_number(ooha.attribute16) = ooha.order_number) or (ooha.order_number = :order_order_number))
AND :system_current_block = 'ORDER'
UNION ALL
SELECT CASE WHEN :order_cancelled_flag IN ('Y','N')
AND :system_current_block = 'ORDER'
THEN NVL (:order_order_number, 123)
ELSE NULL
END
FROM dual
UNION ALL
SELECT
ooh.order_number
FROM oe_order_headers_all ooh
WHERE :system_current_block <> 'ORDER'
AND creation_date > SYSDATE - 180)
Sriram
[Updated on: Thu, 12 May 2011 11:41] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help in tuning a value set query [message #507215 is a reply to message #507201] |
Fri, 13 May 2011 03:42 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Just noticed:
SELECT .....
......
AND :system_current_block = 'ORDER'
UNION ALL
SELECT ...........
.............
WHERE :system_current_block <> 'ORDER'
Your first and last select in the union all are mutually exclusive because of the above conditions.
Split the sql into two seperate ones. One where each of the above conditions is true. Then run the appropriate sql depending on what system_current_block is set to. That should speed things up.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 14:59:50 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
