After the Upgradation
from 11.1.0.7 to 11.2.0.3 we started facing performance issue in execution of
the custom concurrent program. The program which was running within 5 minutes
was not completing even after 24 hours.
from 11.1.0.7 to 11.2.0.3 we started facing performance issue in execution of
the custom concurrent program. The program which was running within 5 minutes
was not completing even after 24 hours.
We enabled the ORADEBUG
on the custom concurrent program and found the below wait Events continuously
appearing in the trace file.
on the custom concurrent program and found the below wait Events continuously
appearing in the trace file.
*** 2013-05-10 19:33:50.434
WAIT #4786366736: nam=’db
file sequential read’ ela= 123 file#=37 block#=483362 blocks=1 obj#=78688
tim=2862165438070
file sequential read’ ela= 123 file#=37 block#=483362 blocks=1 obj#=78688
tim=2862165438070
WAIT #4786366736: nam=’db
file sequential read’ ela= 13734 file#=23 block#=530786 blocks=1 obj#=294940
tim=2862165452342
file sequential read’ ela= 13734 file#=23 block#=530786 blocks=1 obj#=294940
tim=2862165452342
WAIT #4786366736: nam=’db
file sequential read’ ela= 6370 file#=23 block#=530811 blocks=1 obj#=295006
tim=2862165460960
file sequential read’ ela= 6370 file#=23 block#=530811 blocks=1 obj#=295006
tim=2862165460960
WAIT #4786366736: nam=’db
file sequential read’ ela= 3017 file#=28 block#=348952 blocks=1 obj#=296770
tim=2862165595961
file sequential read’ ela= 3017 file#=28 block#=348952 blocks=1 obj#=296770
tim=2862165595961
WAIT #4786366736: nam=’db
file sequential read’ ela= 217 file#=23 block#=531149 blocks=1 obj#=295006
tim=2862165869723
file sequential read’ ela= 217 file#=23 block#=531149 blocks=1 obj#=295006
tim=2862165869723
WAIT #4786366736: nam=’db
file sequential read’ ela= 91 file#=37 block#=483363 blocks=1 obj#=78688
tim=2862166021779
file sequential read’ ela= 91 file#=37 block#=483363 blocks=1 obj#=78688
tim=2862166021779
We identified a particular insert
query which was running for a long time.
query which was running for a long time.
We also observed that
Execution plan has been changed for the insert query from 11.1.0.7 to 11.2.0.3
after the db upgrade to 11.2.0.3
Execution plan has been changed for the insert query from 11.1.0.7 to 11.2.0.3
after the db upgrade to 11.2.0.3
We performed the below
action plan to revert back the performance before the db upgrade to 11.2.0.3.
action plan to revert back the performance before the db upgrade to 11.2.0.3.
We have set the parameter
“optimizer_features_enable = 11.1.0.7” in session level for the
particular package which has been utilized in the custom program, in the
beginning of the package. Again at the end of the package we reset the
optimizer back to 11.2.0.3 by setting “optimizer_features_enable =
11.2.0.3”
“optimizer_features_enable = 11.1.0.7” in session level for the
particular package which has been utilized in the custom program, in the
beginning of the package. Again at the end of the package we reset the
optimizer back to 11.2.0.3 by setting “optimizer_features_enable =
11.2.0.3”
The above change considers the
execution plan back to 11.1.0.7 as it was before the db upgrade.
execution plan back to 11.1.0.7 as it was before the db upgrade.
After that custom program
started to complete normally within 5 minutes, like how it worked before db upgradation.
started to complete normally within 5 minutes, like how it worked before db upgradation.
Recent Posts