Skip to Main Content
  • Questions
  • Select statement goes with Table Storage full in Dev environment but uses index range scan in SIT environment

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

on Clustering Factor of Index.

Rajeshwaran, Jeyabal, August 15, 2020 - 3:31 am UTC

Just one more thing to add up.

for this index pptcode_1_3. compare the clustering factor with num of rows to num_of_blocks between SIT and Dev.

That could provide some insights why the optimizer in Dev databse is not picking up the index, thought it got twice the volume compare to SIT.
select i.index_name,i.clustering_factor,i.num_rows,t.blocks
from user_indexes i,
  user_tables t 
where i.index_name = <your_index_name_goes_here>
and i.table_name = t.table_name

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.