Thanks for the question, Sara.
Asked: August 12, 2020 - 8:10 pm UTC
Last updated: August 13, 2020 - 6:29 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi Team,
I have a same table in 2 environments Development and SIT. Both have the same index pptcode_1_3.
Environment - DEV
Table name - pptcode
number of rows - 103819
execution time - 9 secs
when i run execution plan on development it does TABLE ACCESS STORAGE FULL for the select statement with the where condition.
Environment - SIT
TABLE name - pptcode
number of rows 57109
execution time - 1 sec
When I run execution plan on SIT it does go with TABLE ACCESS BY INDEX ROWID and INDEX RANGE SCAN
How can i make the query to use index in development and run in 1 second.
and Connor said...
You could add a hint to the code to force the index, but from the data you've provided, the tables obviously have different data in each environment, so I'm not sure there is a justification to force the same plan for both.
This really depends on your requirements:
- If you want the best plans for each environment, make sure your statistics are up to data in each environment
- If you want dev to match SIT for execution plans, then look at exporting/importing the schema level optimizer stats. You'll get similar plans, but since the data is different, you may get different response time characteristics.
- If you want lock in a plan from SIT and force Dev to take it, look at SQL Plan Management to grab the plan from SIT and import it into Dev.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment