Sample Header Ad - 728x90

Oracle query different performances between prod and staging environment

0 votes
2 answers
166 views
So I have those 2 Oracle 12c databases R2, prod and staging environment, both are perfectly aligned and running on identical hardware. The same select statement will take about 400ms on staging while it will not take less than 4 seconds. Our expert DBA left the company leaving me and my relatively thin skills to investigate on this. I have no idea how to proceed, although I saw some major differences in each explain plans (1st is prod, 2nd is staging) regarding cardinalities. PROD EXPLAIN PLAN PREPROD EXPLAIN PLAN I don't really know where to start to only identify the source of the issue. I rebuilt the index involved in the query with no effect. I later dropped it and created it again with no more improvment. Edit: Following suggestions, I gathered the stats with sample = 100% with no improvment. Though for some reason the only different parameter between prod and staging was sga_max_size value which was much higher in staging. Aligning it in prod helped me regain similar respond time (query runs in 500ms) though the explain plans are still differents. I will mark this as resolved as the main performance issue is now gone
Asked by Marc (101 rep)
Sep 5, 2022, 01:40 PM
Last activity: Jul 16, 2025, 10:04 AM