Home » SQL & PL/SQL » SQL & PL/SQL » Query needs to be tune (12.2.0.2)
Query needs to be tune [message #680317] |
Wed, 06 May 2020 09:37 |
|
bha_96
Messages: 11 Registered: April 2020
|
Junior Member |
|
|
HI Experts,
Could you please help me to tune this query and also needs to order by event_minute.
select /*ALL_ROWS*/
TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,
PROCESS_NAME,
substr(
XMLCast(
XMLAgg(
XMLElement(e, ','||CONTRACT)
order by CONTRACT
)
as clob
), 2
) CONTRACT_LIST,
APPLICATION AS APPLICATION,
SUM(TOTAL_COUNT) AS TOTAL_COUNT,
SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,
ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,
ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE
FROM STATS_TRACK_SUMMARY_BY_MIN
WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)
AND contract <> 'NULL'
AND APPLICATION IN ('cap','CAP')
and process_name='ws_router'
GROUP BY
TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),
APPLICATION,
PROCESS_NAME
union all
select
TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,
PROCESS_NAME,
to_clob( contract),
APPLICATION AS APPLICATION,
SUM(TOTAL_COUNT) AS TOTAL_COUNT,
SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,
ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,
ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE
FROM STATS_TRACK_SUMMARY_BY_MIN
WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)
AND contract <> 'NULL'
AND APPLICATION IN ('cap','CAP')
and process_name!='ws_router'
GROUP BY
TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),
APPLICATION,
PROCESS_NAME,
contract
Also explain plan:
==================
Plan hash value: 350428443
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 556K| 30M| | 58143 (1)| 00:00:03 | | |
| 1 | UNION-ALL | | | | | | | | |
| 2 | SORT GROUP BY | | 7008 | 396K| | 25213 (1)| 00:00:01 | | |
| 3 | PARTITION RANGE ITERATOR| | 11387 | 644K| | 25212 (1)| 00:00:01 | KEY |1048575|
|* 4 | TABLE ACCESS FULL | STATS_TRACK_SUMMARY_BY_MIN | 11387 | 644K| | 25212 (1)| 00:00:01 | KEY |1048575|
| 5 | HASH GROUP BY | | 549K| 30M| 40M| 32930 (1)| 00:00:02 | | |
| 6 | PARTITION RANGE ITERATOR| | 549K| 30M| | 25234 (1)| 00:00:01 | KEY |1048575|
|* 7 | TABLE ACCESS FULL | STATS_TRACK_SUMMARY_BY_MIN | 549K| 30M| | 25234 (1)| 00:00:01 | KEY |1048575|
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("PROCESS_NAME"='ws_router' AND ("APPLICATION"='CAP' OR "APPLICATION"='cap') AND "CONTRACT"<>'NULL' AND
"EVENT_MINUTE">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-3))
7 - filter(("APPLICATION"='CAP' OR "APPLICATION"='cap') AND "PROCESS_NAME"<>'ws_router' AND "CONTRACT"<>'NULL' AND
"EVENT_MINUTE">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-3))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
|
|
|
Re: Query needs to be tune [message #680320 is a reply to message #680317] |
Wed, 06 May 2020 10:09 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
1) tune
If only a sql can be formatted so it would be readable..
2) order by
SQL> select *
from bla;
ID
----------
1
2
3
4
SQL> select id from bla where id>2
union all
select id from bla where id<3;
ID
----------
3
4
1
2
SQL> select id from bla where id>2
union all
select id from bla where id<3
order by id;
ID
----------
1
2
3
4
SQL>
Maybe the above example may shine some light.
|
|
|
|
|
Re: Query needs to be tune [message #680326 is a reply to message #680317] |
Wed, 06 May 2020 12:21 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Sat, 25 April 2020 15:16Welcome to this forum
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Michel Cadot wrote on Sat, 25 April 2020 16:44Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
...
Michel Cadot wrote on Mon, 27 April 2020 07:17Michel Cadot wrote on Sun, 26 April 2020 12:06Michel Cadot wrote on Sun, 26 April 2020 07:45
Michel Cadot wrote on Sat, 25 April 2020 20:25
Can you provide what is asked in the way it is asked.
John Watson wrote on Sun, 26 April 2020 15:38If you persist in ignoring Forum Guidelines, you are unlikely to get any assistance. Please format your code (there is a code formatting tool here, http://www.dpriver.com/pp/sqlformat.htm ) and enclose the code you post in [code] tags, as described here, http://www.orafaq.com/forum/m/673006/#msg_673006
John Watson wrote on Mon, 27 April 2020 10:14BHA, is your obnoxious behaviour is deliberate? You are repeatedly making posts that are not properly formatted. This looks very like trolling: being rude in an attempt to make people angry. Please stop flooding the forum with rubbish.
I shall lock this topic.
Michel Cadot wrote on Mon, 27 April 2020 10:16
In the end, it seems you are just a troll trying to make people angry.
This topic is locked.
Come back with a proper request in another topic.
Dito.
[Updated on: Wed, 06 May 2020 12:24] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:43:13 CDT 2024
|