PL/SQL (MOSC)

MOSC Banner

SQL profile suggestion

edited Mar 9, 2021 10:41PM in PL/SQL (MOSC) 3 commentsAnswered ✓

Database 12.2.0.1 Apr 2020 CPu patch

We have a sql query which ran fine until 3PM yesterday and all of a sudden it started spinning and it consumes lot of temporary tablspace. i can constantly see the wait event as "direct path write temp"

Seems query plan is changed somehow and its accumulating large number of rows whcich is getting spilled over to from PGA to Temp.

We refreshed the the test DB from prod and there query works well. So i ran the tuning advisor in Production and it gives me better Explain plan is available and it asks me to accept the sql profile which i can do . But we have lot many dynamic variable so i am afraid it may not be the right approach to accept the sql plan and when the query runs from application it will still have an issue.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center