Home » RDBMS Server » Performance Tuning » Expensive Query (Oracle 11g)
| Expensive Query [message #485105] |
Mon, 06 December 2010 04:56  |
|
|
Hi,
I have the following Query.
SELECT trim(oh.enterprise_key) AS "Enterprise",
trim(o.organization_code) AS "Warehouse",
trim(pi.country) AS "Country",
trim(pi.city) AS "City",
TO_CHAR(oh.createts,'DD-Mon-YYYY HH:MI:SS') AS "CDate",
TO_CHAR(SUM (DECODE (document_type, '0009.ex', 1, 0))) AS "SRs",
TO_CHAR(SUM (DECODE (document_type, '0001', 1, 0))) AS "SOs",
TO_CHAR(SUM (DECODE (document_type, '0001', ol.cnt, 0))) AS "SO Lines",
TO_CHAR(SUM (DECODE (document_type, '0005', 1, 0))) AS "POs",
TO_CHAR(SUM (DECODE (document_type, '0005', ol.cnt, 0))) AS "PO Lines",
TO_CHAR(SUM (DECODE (document_type, '0006', 1, 0))) AS "TOs",
TO_CHAR(SUM (DECODE (document_type, '0006', ol.cnt, 0))) AS "TO Lines",
TO_CHAR(SUM (DECODE (document_type, '0001', ol.cnt,'0006', ol.cnt, 0))) AS "Total Outbound",
TO_CHAR(SUM (DECODE (document_type, '0005', ol.cnt,'0006', ol.cnt, 0))) AS "Total Inbound"
FROM yfs_order_header oh,
yfs_organization o,
yfs_org_enterprise oe,
yfs_person_info pi,
(SELECT order_header_key,
COUNT(*) AS cnt
FROM yfs_order_line
GROUP BY order_header_key
)
WHERE oh.order_header_key =ol.order_header_key
AND o.organization_key = oe.organization_key
AND pi.person_info_key = o.contact_address_key
AND oe.enterprise_organization_key <> 'DEFAULT'
AND o.is_node = 'Y'
--and oh.order_header_key <to_char(sysdate,'yyyymm')
AND TO_CHAR(oh.createts,'DD-Mon-YYYY')=to_date(sysdate,'DD-Mon-YYYY')
AND oh.enterprise_key NOT IN ('DUMMY1','DUMMY2')
GROUP BY trim(oh.enterprise_key),
trim(o.organization_code),
trim(pi.country),
trim(pi.city),
TO_CHAR(oh.createts,'DD-Mon-YYYY HH:MI:SS')
Please note that I cannot share the table DDLs and DMLs  as its a production data.
Please find the Explain Plan Result.
"PLAN_TABLE_OUTPUT"
"Plan hash value: 1952294846"
" "
"-------------------------------------------------------------------------------------------------------------"
"| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |"
"-------------------------------------------------------------------------------------------------------------"
"| 0 | SELECT STATEMENT | | 201M| 49G| | 105M (1)|352:26:08 |"
"| 1 | HASH GROUP BY | | 201M| 49G| 54G| 105M (1)|352:26:08 |"
"| 2 | VIEW | VM_NWVW_0 | 201M| 49G| | 94M (1)|314:11:54 |"
"| 3 | HASH GROUP BY | | 201M| 264G| 307G| 94M (1)|314:11:54 |"
"|* 4 | HASH JOIN | | 201M| 264G| 34M| 14M (1)| 46:44:49 |"
"| 5 | TABLE ACCESS FULL | YFS_PERSON_INFO | 284K| 31M| | 6011 (1)| 00:01:13 |"
"|* 6 | HASH JOIN | | 201M| 242G| | 1578K (3)| 05:15:46 |"
"|* 7 | TABLE ACCESS FULL | YFS_ORGANIZATION | 5257 | 1001K| | 437 (1)| 00:00:06 |"
"| 8 | MERGE JOIN CARTESIAN | | 792M| 811G| | 1572K (2)| 05:14:27 |"
"|* 9 | HASH JOIN | | 36995 | 35M| 27M| 133K (1)| 00:26:46 |"
"|* 10 | TABLE ACCESS FULL | YFS_ORDER_HEADER | 31145 | 27M| | 108K (1)| 00:21:47 |"
"| 11 | INDEX FAST FULL SCAN| YFS_ORDER_LINE_I1 | 3682K| 340M| | 4507 (1)| 00:00:55 |"
"| 12 | BUFFER SORT | | 21426 | 1820K| | 1567K (2)| 05:13:33 |"
"|* 13 | INDEX FAST FULL SCAN| YFS_ORG_ENTERPRISE_I1 | 21426 | 1820K| | 39 (3)| 00:00:01 |"
"-------------------------------------------------------------------------------------------------------------"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
" 4 - access(""PI"".""PERSON_INFO_KEY""=""O"".""CONTACT_ADDRESS_KEY"")"
" 6 - access(""O"".""ORGANIZATION_KEY""=""OE"".""ORGANIZATION_KEY"")"
" 7 - filter(""O"".""IS_NODE""='Y')"
" 9 - access(""OH"".""ORDER_HEADER_KEY""=""ORDER_HEADER_KEY"")"
" 10 - filter(TO_CHAR(INTERNAL_FUNCTION(""OH"".""CREATETS""),'DD-Mon-YYYY')=TO_DATE(TO_CHAR(SYSDATE@!),'D"
" D-Mon-YYYY') AND ""OH"".""ENTERPRISE_KEY""<>'DUMMY1' AND ""OH"".""ENTERPRISE_KEY""<>'DUMMY2')"
" 13 - filter(""OE"".""ENTERPRISE_ORGANIZATION_KEY""<>'DEFAULT')"
Please let me know whether I can tune the query...
Regards,
Ashoka
|
|
|
|
| Re: Expensive Query [message #485112 is a reply to message #485105] |
Mon, 06 December 2010 05:03  |
|
|
Can we rewrite the following code in a different way. All I am doing here is getting the count and displaying it.
(SELECT order_header_key,
COUNT(*) AS cnt
FROM yfs_order_line
GROUP BY order_header_key
)
|
|
|
|
| Re: Expensive Query [message #485127 is a reply to message #485112] |
Mon, 06 December 2010 05:46 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You've go a cartesian join.
Also, your query references a table aliased as "ol", yet there is no "ol" in the FROM clause. I can only assume it is the inline-view.
Lets assume that the inline-view is "ol". You join it to "oh", and you join "o", "oe" and "pi", but you don't join these two groups.
So, if the join of "oh" and "ol" produces 1M rows, and the join of the other 3 tables produces 1M rows, then your query will return 1 BILLION rows.
Ross Leishman
|
|
|
|
| Re: Expensive Query [message #485128 is a reply to message #485105] |
Mon, 06 December 2010 05:48 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. IMHO it supposed to be:
(SELECT order_header_key,
COUNT(*) AS cnt
FROM yfs_order_line
GROUP BY order_header_key
) OL
2. Aren't you missing a JOIN condition? You defined JOINs
OH <--> OL
O <--> OE
O <--> PI
I don't see join OH <--> O, so that's the possible reason for
CARTESIAN join ( step id #8) in explain plan.
3. Using function
AND TO_CHAR(oh.createts,'DD-Mon-YYYY') =to_date(sysdate,'DD-Mon-YYYY')
prevents index usage (assuming you have an index on oh.createts column.
Change it to:
AND oh.createts BETWEEN TRUNC(sysdate) AND (TRUNC(SYSDATE) + 0.99999)
4. If your statement selects over 10% of yfs_order_header table - consider using NO_MERGE hint (you still must take care of missing join):
SELECT /*+ NO_MERGE(OL) USE_HASH(OL) */ trim(oh.enterprise_key) AS "Enterprise",
trim(o.organization_code) AS "Warehouse",
trim(pi.country) AS "Country",
trim(pi.city) AS "City",
TO_CHAR(oh.createts,'DD-Mon-YYYY HH:MI:SS') AS "CDate",
TO_CHAR(SUM (DECODE (document_type, '0009.ex', 1, 0))) AS "SRs",
TO_CHAR(SUM (DECODE (document_type, '0001', 1, 0))) AS "SOs",
TO_CHAR(SUM (DECODE (document_type, '0001', ol.cnt, 0))) AS "SO Lines",
TO_CHAR(SUM (DECODE (document_type, '0005', 1, 0))) AS "POs",
TO_CHAR(SUM (DECODE (document_type, '0005', ol.cnt, 0))) AS "PO Lines",
TO_CHAR(SUM (DECODE (document_type, '0006', 1, 0))) AS "TOs",
TO_CHAR(SUM (DECODE (document_type, '0006', ol.cnt, 0))) AS "TO Lines",
TO_CHAR(SUM (DECODE (document_type, '0001', ol.cnt,'0006', ol.cnt, 0))) AS "Total Outbound",
TO_CHAR(SUM (DECODE (document_type, '0005', ol.cnt,'0006', ol.cnt, 0))) AS "Total Inbound"
FROM yfs_order_header oh,
yfs_organization o,
yfs_org_enterprise oe,
yfs_person_info pi,
(SELECT order_header_key,
COUNT(*) AS cnt
FROM yfs_order_line
GROUP BY order_header_key
) OL
WHERE oh.order_header_key =ol.order_header_key
AND o.organization_key = oe.organization_key
AND pi.person_info_key = o.contact_address_key
AND oe.enterprise_organization_key <> 'DEFAULT'
AND o.is_node = 'Y'
AND oh.createts BETWEEN TRUNC(sysdate) AND (TRUNC(SYSDATE) + 0.99999)
AND oh.enterprise_key NOT IN ('DUMMY1','DUMMY2')
GROUP BY trim(oh.enterprise_key),
trim(o.organization_code),
trim(pi.country),
trim(pi.city),
TO_CHAR(oh.createts,'DD-Mon-YYYY HH:MI:SS')
HTH.
[Updated on: Mon, 06 December 2010 05:48] Report message to a moderator |
|
|
|
| Re: Expensive Query [message #485130 is a reply to message #485127] |
Mon, 06 December 2010 05:50 |
|
|
(SELECT order_header_key,
COUNT(*) AS cnt
FROM yfs_order_line
GROUP BY order_header_key
) OL
Yeah.My bad.....
But All I can say is that YFS_ORDER_HEADER and YFS_ORDER_LINE has common column as ORDER_HEADER_KEY only....
So can we change the Query ( the in-line view....)
|
|
|
|
| Re: Expensive Query [message #485131 is a reply to message #485112] |
Mon, 06 December 2010 05:53 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
ashoka_bl wrote on Mon, 06 December 2010 12:03Can we rewrite the following code in a different way. All I am doing here is getting the count and displaying it.
Probably by using Oracle analytics, but I doubt it will lead to better performance as the table would have to be accessed anyway.
The main reason is probably on line 9 of the explain plan - MERGE JOIN for tables with 36995 and 21426 rows after filter. This will lead to 201 million rows in the result set. Do you really want to get so many rows? Because, after the glance into query, there is no join condition between (ORDER_HEADER with ORDER_LINE) and (ORGANIZATION, ORG_ENTERPRISE and PERSON_INFO). Is there really no relationship between those two groups. If no, do you really want to show its cartesian product (all combinations of rows from the first and second group)?
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 15:37:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
