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 Go to next message
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 Go to previous messageGo to next message
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 #680321 is a reply to message #680320] Wed, 06 May 2020 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/message/15611941?tstart=0#15611941
Re: Query needs to be tune [message #680323 is a reply to message #680320] Wed, 06 May 2020 10:57 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
How is this any different from your two previous threads on OraFAQ?
http://www.orafaq.com/forum/m/680157/#msg_680157
http://www.orafaq.com/forum/m/680156/#msg_680156

Or your multiple threads on OTN?
https://community.oracle.com/thread/4328465
https://community.oracle.com/thread/4328068
Re: Query needs to be tune [message #680326 is a reply to message #680317] Wed, 06 May 2020 12:21 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:


BlackSwan wrote on Sat, 25 April 2020 15:16
Welcome 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:44
Welcome 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:17
Michel Cadot wrote on Sun, 26 April 2020 12:06
Michel 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:38
If 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:14
BHA, 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

Previous Topic: Limit error on function
Next Topic: Looping Chain of Synonyms
Goto Forum:
  


Current Time: Thu Mar 28 07:07:09 CDT 2024