PERFORMANCE ISSUE WHEN RUNNING GATHER TABLE STATS WITH INCREMENTAL STATS
On one of our Cloud Environment we encountered Performance deteriorating .
Initial checks stated High CPU utilization oracle process consuming 100 % of one CPU out of 4 . Underlying sql is the stats gathering which is being run on an continual basis . As we looked at the partitions of that segment (WRI$_OPTSTAT_SYNOPSIS$) , which has total partitions of 8384 .
From longops the queery which was being run was :
SELECT DISTINCT BO#, GROUP# FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$
H WHERE GROUP# <> 0 AND GROUP# NOT IN (SELECT T.OBJ# * 2 FROM S
YS.TABPART$ T UNION ALL SELECT T.OBJ# * 2 FROM SYS.TABCOMPART$ T
)
On Engaging oracle we found the Bug which existed in 12.1.0.2 was
Bug 19450139 - Slow gather table stats with incremental stats enabled ( Doc ID 19450139.8 )
Since we had Proactive BP applied 12.1.0.2.171017 , we then downloaded the patch for release "DATABASE BP 12.1.0.2.171017" -- p19450139_12102171017ProactiveBP_Linux-x86-64.zip
Few steps to reach to the final goal post.
1. /home/oracle/staging=> unzip p19450139_12102171017ProactiveBP_Linux-x86-64.zip
2. /home/oracle/staging=> cd 19450139 - set the enviornemnt so that opatch can be invoked correctly.
3. opatch prereq CheckConflictAgainstOHWithDetail -ph ./ - if all good then step 4.
4. opatch apply
5. Verify whether the patch has been successfully installed by running the following command:
$ opatch lsinventory
6. Invoke datapatch after starting all the services . Please Note the database should be started in upgrade mode which after exection of the datapatch script succesfully bring the database down and startup normally.
Initial checks stated High CPU utilization oracle process consuming 100 % of one CPU out of 4 . Underlying sql is the stats gathering which is being run on an continual basis . As we looked at the partitions of that segment (WRI$_OPTSTAT_SYNOPSIS$) , which has total partitions of 8384 .
From longops the queery which was being run was :
SELECT DISTINCT BO#, GROUP# FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$
H WHERE GROUP# <> 0 AND GROUP# NOT IN (SELECT T.OBJ# * 2 FROM S
YS.TABPART$ T UNION ALL SELECT T.OBJ# * 2 FROM SYS.TABCOMPART$ T
)
On Engaging oracle we found the Bug which existed in 12.1.0.2 was
Bug 19450139 - Slow gather table stats with incremental stats enabled ( Doc ID 19450139.8 )
Since we had Proactive BP applied 12.1.0.2.171017 , we then downloaded the patch for release "DATABASE BP 12.1.0.2.171017" -- p19450139_12102171017ProactiveBP_Linux-x86-64.zip
Few steps to reach to the final goal post.
1. /home/oracle/staging=> unzip p19450139_12102171017ProactiveBP_Linux-x86-64.zip
2. /home/oracle/staging=> cd 19450139 - set the enviornemnt so that opatch can be invoked correctly.
3. opatch prereq CheckConflictAgainstOHWithDetail -ph ./ - if all good then step 4.
4. opatch apply
5. Verify whether the patch has been successfully installed by running the following command:
$ opatch lsinventory
6. Invoke datapatch after starting all the services . Please Note the database should be started in upgrade mode which after exection of the datapatch script succesfully bring the database down and startup normally.
Comments