Home » RDBMS Server » Performance Tuning » Performance degrade after 10.2.0.5 patch upgrade and downgrade (10.2.0.4)
Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582357] Wed, 17 April 2013 07:23 Go to next message
neetesh.jathan
Messages: 2
Registered: April 2013
Location: India
Junior Member
We had a issue with the data insertion in prod server because of which the Oracle support team had suggested to apply the 10.2.0.5 patch after checking the logs. We tested the patch on uat server and found that the eod and other processess were performing poorly so we decided to downgrade. After downgrade we still face the performance issue. The process that took around 2 hours now takes more than 14 hours. We had also changed the optimizer parameter but did not help. Can any body please help.
Especially the below query type of query take a lot of time. We have tried with various parameter for optmizer_mode but in vain.

SELECT 'TABLE', DECODE(TEMPORARY,'N',1,0) FROM ALL_ALL_TABLES T WHERE T.TABLE_NAME = 'SD_TRADING_GROUP' AND T.OWNER = 'TIQOWNER'
AND T.NESTED = 'NO' AND T.DROPPED = 'NO' UNION
SELECT 'PKIDX', COUNT(*) FROM ALL_CONSTRAINTS AC, ALL_INDEXES IDX , ALL_ALL_TABLES T WHERE CASE WHEN AC.INDEX_OWNER IS NOT NULL
THEN AC.INDEX_OWNER ELSE AC.OWNER END = IDX.OWNER AND AC.INDEX_NAME = IDX.INDEX_NAME AND AC.CONSTRAINT_TYPE = 'P'
AND AC.VALIDATED = 'VALIDATED' AND AC.STATUS = 'ENABLED' AND IDX.STATUS = 'VALID' AND AC.TABLE_NAME = 'Table Name'
AND AC.OWNER = 'TIQOWNER' AND CASE WHEN AC.INDEX_OWNER IS NOT NULL THEN AC.INDEX_OWNER ELSE AC.OWNER END = T.OWNER
AND AC.TABLE_NAME = T.TABLE_NAME AND T.DROPPED = 'NO' UNION
SELECT 'PKCOL', COUNT(*) FROM ALL_CONSTRAINTS AC, ALL_TAB_COLUMNS TC, ALL_CONS_COLUMNS CC WHERE AC.OWNER = TC.OWNER
AND AC.TABLE_NAME = TC.TABLE_NAME AND AC.OWNER = CC.OWNER AND AC.TABLE_NAME = CC.TABLE_NAME AND CC.COLUMN_NAME = TC.COLUMN_NAME;

[Updated on: Wed, 17 April 2013 07:24]

Report message to a moderator

Re: Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582360 is a reply to message #582357] Wed, 17 April 2013 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Note that no one will ever try to read your query as it is.
You have to make some effort to format it.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582397 is a reply to message #582360] Thu, 18 April 2013 02:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If that is a typical query - you're hitting dictionary views - check no-one altered the stats on the sys tables as part of the upgrade.

Finally, and I'd highly recommend getting a better solution, but I have had some limited successes adding the rule hint to the dictionary queries. Old structures, old optimizer worked very well in some instances. Not a recommended approach though...
Re: Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582461 is a reply to message #582357] Thu, 18 April 2013 09:41 Go to previous messageGo to next message
neetesh.jathan
Messages: 2
Registered: April 2013
Location: India
Junior Member
SELECT 'TABLE', 
       Decode(temporary, 'N', 1, 
                         0) 
FROM   all_all_tables T 
WHERE  T.table_name = 'TABLE_NAME' 
       AND T.owner = 'TIQOWNER' 
       AND T.nested = 'NO' 
       AND T.dropped = 'NO' 
UNION 
SELECT 'PKIDX', 
       Count(*) 
FROM   all_constraints AC, 
       all_indexes IDX, 
       all_all_tables T 
WHERE  CASE 
         WHEN AC.index_owner IS NOT NULL THEN AC.index_owner 
         ELSE AC.owner 
       END = IDX.owner 
       AND AC.index_name = IDX.index_name 
       AND AC.constraint_type = 'P' 
       AND AC.validated = 'VALIDATED' 
       AND AC.status = 'ENABLED' 
       AND IDX.status = 'VALID' 
       AND AC.table_name = 'SD_TRADING_GROUP' 
       AND AC.owner = 'TIQOWNER' 
       AND CASE 
             WHEN AC.index_owner IS NOT NULL THEN AC.index_owner 
             ELSE AC.owner 
           END = T.owner 
       AND AC.table_name = T.table_name 
       AND T.dropped = 'NO' 
UNION 
SELECT 'PKCOL', 
       Count(*) 
FROM   all_constraints AC, 
       all_tab_columns TC, 
       all_cons_columns CC 
WHERE  AC.owner = TC.owner 
       AND AC.table_name = TC.table_name 
       AND AC.owner = CC.owner 
       AND AC.table_name = CC.table_name 
       AND CC.column_name = TC.column_name;  

[Updated on: Thu, 18 April 2013 09:43]

Report message to a moderator

Re: Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582468 is a reply to message #582461] Thu, 18 April 2013 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 17 April 2013 14:35
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
...
Regards
Michel

Re: Performance degrade after 10.2.0.5 patch upgrade and downgrade [message #582469 is a reply to message #582468] Thu, 18 April 2013 10:39 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are the first two selects restricted by owner and table_name and the last one isn't?
Previous Topic: Tune function
Next Topic: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc.
Goto Forum:
  


Current Time: Thu Mar 28 10:53:27 CDT 2024