Home » RDBMS Server » Performance Tuning » Performance Tuning (11g)
Performance Tuning [message #618319] Thu, 10 July 2014 11:47 Go to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
we have a query with large union all with a lot of tables. If that query gets hung up, it will hold upalter statements on those tables
we should break the query up into individual ones to minimize that effect in the future. Does anyone have any thoughts/ideas on it ?
Re: Performance Tuning [message #618321 is a reply to message #618319] Thu, 10 July 2014 11:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
I tried to find the query(which I can't see) in your post, I get no clues how to help you.

Please read the sticky on top of this forum. If it doesn't help, moderators would point you to further links to make a sensible post Smile

Edit : typo

[Updated on: Thu, 10 July 2014 11:57]

Report message to a moderator

Re: Performance Tuning [message #618322 is a reply to message #618319] Thu, 10 July 2014 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
> If that query gets hung up, it will hold up alter statements on those tables
I don't believe you.
in Oracle readers do not block writers & writers do not block readers.
only poorly designed application does ALTER during normal processing.

>Does anyone have any thoughts/ideas on it ?
correct the design so no ALTER is required.

No DDL should exist as part of typical application execution.
DDL done only as part of software version upgrade when the DB has been quiesced.
Re: Performance Tuning [message #618323 is a reply to message #618322] Thu, 10 July 2014 12:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, BS gave a good clue, so is your question about tuning/rewrite the query or get an alternative to the absurd ALTER method?
Re: Performance Tuning [message #618324 is a reply to message #618323] Thu, 10 July 2014 12:28 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
the query is to large ,yes its about tuning/rewrite the query
Re: Performance Tuning [message #618325 is a reply to message #618324] Thu, 10 July 2014 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Performance Tuning [message #618326 is a reply to message #618324] Thu, 10 July 2014 12:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2520
Registered: May 2013
Location: World Wide on the Web
Senior Member
andrewscharles89 wrote on Thu, 10 July 2014 22:58
the query is to large


No problem, use SPOILER tags. Interested folks will definitely help you. Make sure you prepare a good teat case to easily replicate Smile

Quote:
yes its about tuning/rewrite the query

Then you need to post more details as suggested in the sticky. I already told you.
Re: Performance Tuning [message #618327 is a reply to message #618326] Thu, 10 July 2014 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
I can't tune SQL, that I can not see.
Re: Performance Tuning [message #618409 is a reply to message #618327] Fri, 11 July 2014 09:31 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
[code]SELECT MODEL_ID, COUNT(*) FROM MOD_IN_MLTLVL_AA WHERE SCENARIOS_KEY = (SELECT MEMBER_KEY FROM HRY_SCN_CURR WHERE SCENARIOS = '1415.C.JUL.BF') AND ACCOUNT_KEY IN (SELECT MEMBER_KEY FROM OVW_ALL_ACCOUNTS WHERE (MEMBER_ID LIKE '%84240000%' OR MEMBER_ID LIKE '%84330000%' OR MEMBER_ID LIKE '%84280000%' OR MEMBER_ID LIKE '%84370000%' OR MEMBER_ID LIKE '%84250000%' OR MEMBER_ID LIKE '%84340000%' OR MEMBER_ID LIKE '%84290000%' OR MEMBER_ID LIKE '%84380000%' OR MEMBER_ID LIKE '%84260000%' OR MEMBER_ID LIKE '%84350000%' OR MEMBER_ID LIKE '%84300000%' OR MEMBER_ID LIKE '%84390000%' OR MEMBER_ID LIKE '%81013112%' OR MEMBER_ID LIKE '%81013113%' OR MEMBER_ID LIKE '%81013341%' OR MEMBER_ID LIKE '%81013346%' OR MEMBER_ID LIKE '%81013335%' OR MEMBER_ID LIKE '%81013336%' OR MEMBER_ID LIKE '%81013340%' OR MEMBER_ID LIKE '%81013345%') )union all	


SELECT MODEL_ID, COUNT(*) FROM MOD_IN_MLTLVL_AB WHERE SCENARIOS_KEY = (SELECT MEMBER_KEY FROM HRY_SCN_CURR WHERE SCENARIOS = '1415.C.JUL.BF') AND ACCOUNT_KEY IN (SELECT MEMBER_KEY FROM OVW_ALL_ACCOUNTS WHERE (MEMBER_ID LIKE '%84240000%' OR MEMBER_ID LIKE '%84330000%' OR MEMBER_ID LIKE '%84280000%' OR MEMBER_ID LIKE '%84370000%' OR MEMBER_ID LIKE '%84250000%' OR MEMBER_ID LIKE '%84340000%' OR MEMBER_ID LIKE '%84290000%' OR MEMBER_ID LIKE '%84380000%' OR MEMBER_ID LIKE '%84260000%' OR MEMBER_ID LIKE '%84350000%' OR MEMBER_ID LIKE '%84300000%' OR MEMBER_ID LIKE '%84390000%' OR MEMBER_ID LIKE '%81013112%' OR MEMBER_ID LIKE '%81013113%' OR MEMBER_ID LIKE '%81013341%' OR MEMBER_ID LIKE '%81013346%' OR MEMBER_ID LIKE '%81013335%' OR MEMBER_ID LIKE '%81013336%' OR MEMBER_ID LIKE '%81013340%' OR MEMBER_ID LIKE '%81013345%') )union all[/code]	
...
..

..

the query goes on like this for next 180 lines with union all
the only change is the table name in outer query, the sub query and the values in the sub query are all same

[Updated on: Fri, 11 July 2014 09:31]

Report message to a moderator

Re: Performance Tuning [message #618412 is a reply to message #618409] Fri, 11 July 2014 09:37 Go to previous messageGo to next message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a with clause for the sub-queries.
Real question is, should all the MOD_IN_MLTLVL... tables exist, or would you be a lot better off if they were combined into a single table.
Partitioning data across separate tables is fine up until the point where you need to combine them.
Re: Performance Tuning [message #618463 is a reply to message #618412] Sat, 12 July 2014 01:54 Go to previous messageGo to next message
Kevin Meade
Messages: 1955
Registered: December 1999
Location: Connecticut USA
Senior Member
As always "it depends". But if you are on 11g (or 10g even), I would not expect this union all query to be a problem. In fact, I would expect the large query to run faster than the equivalent solution of many smaller queries doing the same thing. Breaking the query up would only result in you having to manually do stuff that the database is already doing for you, and might even increase the workload. Here is what I would expect.

1. the sub-query if it is a nested select (I believe it is), and not a correlated sub-query, should automatically be extracted out my Oracle during its query rewrite phase as if you had written it using the with clause yourself, with the effect that it should be executed only once and the results reused in the query as needed.

2. Oracle is putting results of multiple lookups together for you. If you break the query up then you have to figure out how to do that and I can't think of any way that would be more efficient that letting oracle do it via union all.

So if your main concern is performance, I say go with the big union all query (subject to assumptions noted above).

Kevin
Re: Performance Tuning [message #619437 is a reply to message #618463] Tue, 22 July 2014 08:20 Go to previous message
michael_bialik
Messages: 605
Registered: July 2006
Senior Member
1. As cookiemonster already wrote - Use WITH clause to reduce the number of accesses from HRY_SCN_CURR and OVW_ALL_ACCOUNTS tables/views
WITH MEMBER_LIST AS (SELECT S.MEMBER_KEY AS SCENARIOS_KEY, A.MEMBER_KEY AS ACCOUNT_KEY
                     FROM OVW_ALL_ACCOUNTS A, HRY_SCN_CURR S
                     WHERE S.SCENARIOS = '1415.C.JUL.BF' AND
                         (A.MEMBER_ID LIKE '%84240000%' OR A.MEMBER_ID LIKE '%84330000%' OR A.MEMBER_ID LIKE '%84280000%' OR 
                          A.MEMBER_ID LIKE '%84370000%' OR A.MEMBER_ID LIKE '%84250000%' OR A.MEMBER_ID LIKE '%84340000%' OR 
                          A.MEMBER_ID LIKE '%84290000%' OR A.MEMBER_ID LIKE '%84380000%' OR A.MEMBER_ID LIKE '%84260000%' OR 
                          A.MEMBER_ID LIKE '%84350000%' OR A.MEMBER_ID LIKE '%84300000%' OR A.MEMBER_ID LIKE '%84390000%' OR 
                          A.MEMBER_ID LIKE '%81013112%' OR A.MEMBER_ID LIKE '%81013113%' OR A.MEMBER_ID LIKE '%81013341%' OR 
                          A.MEMBER_ID LIKE '%81013346%' OR A.MEMBER_ID LIKE '%81013335%' OR A.MEMBER_ID LIKE '%81013336%' OR 
                          A.MEMBER_ID LIKE '%81013340%' OR A.MEMBER_ID LIKE '%81013345%') )
SELECT AA.MODEL_ID, COUNT(*) 
FROM  MEMBER_LIST M
  JOIN MOD_IN_MLTLVL_AA AA ON AA.SCENARIOS_KEY = M.SCENARIOS_KEY AND AA.ACCOUNT_KEY = M.ACCOUNT_KEY
union all	
SELECT AB.MODEL_ID, COUNT(*) 
FROM  MEMBER_LIST M
  JOIN MOD_IN_MLTLVL_AB  AB ON AB.SCENARIOS_KEY = M.SCENARIOS_KEY AND AB.ACCOUNT_KEY = M.ACCOUNT_KEY
...

2. How many rows are selected from OVW_ALL_ACCOUNTS table (with all these ORs)?
3. Do you have indexes on all MOD_IN... tables on ( SCENARIOS_KEY, ACCOUNT_KEY ) pair?
4. Post EXPLAIN/ TKPROF.
HTH
Previous Topic: How to revert the work done by accept_sql_profile procedure & Issues to watch out with SPM (merged)
Next Topic: performance lag of the quey
Goto Forum:
  


Current Time: Wed Nov 26 16:07:12 CST 2014

Total time taken to generate the page: 0.14727 seconds