0

We have evening package that is scheduled to run during evening and usually completes by midnight. We recently migrated our Oracle ODA's to Exadata cloud and did containerization and oracle patching. After our database migration it stopped the next day. Then as a solution I asked DBA team to gather stale stats and it worked the next day. After that it worked fine for rest of the days. Last week we did Oracle upgrade to latest version available, it again stopped the next day then I asked DBA team to gather stale stats, but it didn't effect, now it is running inconsistently, one day it errored out stating temp tablespace issue, our DBA team increased the space, after that it completed next day but taking 12 hours to complete instead of 5. For two days it ran but struck at one point without erroring out, therefore summary is sometimes it is completing and other times it gets struck without erroring out. Then we have to kill the struck session before next one starts in the evening.

I checked with DBA team to look into it, they don't see any issues from their end. Please let me know what else can be the reason for that slowness and inconsistent behavior.

Note: There was no change made in definition of the package.

Any feedback or recommendations are greatly appreciated.

Thanks, Khush11

Khush11
  • 1
  • 1
    What do you mean the session was "stuck". Either a session is blocked (i.e. waiting for a lock), it is running and consuming resources, or it has been killed. Knowing which it is would be important. My guess from your description is that one or more statements in the package have seen their plans change either because of changes to the optimizer, to statistics, or to the process that gathers statistics. I don't suppose you happened to gather baseline plans from before you started all these changes and upgrades? – Justin Cave Mar 02 '22 at 16:01
  • If not, I'd run an AWR from the period in question and take a look at the top resource consuming queries and see what plans the optimizer is coming up with. – Justin Cave Mar 02 '22 at 16:01
  • Session struck means it is struck at a level of building table or views. – Khush11 Mar 02 '22 at 16:21
  • I have AWR report but I am not sure how should I optimize plans now from that AWR report. – Khush11 Mar 02 '22 at 16:29
  • Top ADDM Findings by Average Active Sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Finding Name Avg act Percen Task Name ---------------------------------------- ------- ------ ------------------------ Top SQL Statements 2.37 62.63 ADDM:4178876543_1_2134 Top SQL Statements 2.58 51.23 ADDM:4178876543_1_2133 Hard Parse Due to Parse Errors 2.52 38.61 ADDM:4178876543_1_2139 Top SQL Statements 2.52 25.00 ADDM:4178876543_1_2139 – Khush11 Mar 02 '22 at 16:34
  • Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 1.0 0.8 0.00 0.00 DB CPU(s): 0.8 0.6 0.00 0.00 Background CPU(s): 0.1 0.1 0.00 0.00 Redo size (bytes): 409,325.5 305,758.7 Logical read (blocks): 221,477.9 165,439.9 – Khush11 Mar 02 '22 at 16:35
  • 2
    @Khush11 - please add that kind of information as an edit to your question, not as comments, so you can format it to be readable (and so it isn't deleted). But you need to look at the statements and plans the report highlights. Did your DBAs do any investigation at that level? – Alex Poole Mar 02 '22 at 16:43
  • This looks like a SQL tuning issue, in which case the "PL/SQL Developer" and "PL/SQL Package" tags are not related to the issue. – William Robertson Mar 02 '22 at 20:06
  • 2
    Most likely there is a particular cursor whose plan is varying for some reason. You need to identify that cursor and its plan history and investigate what is making it change and why. Your DBA team should be able to help with this. – William Robertson Mar 02 '22 at 20:10

0 Answers0