Skip to Main Content
  • Questions

  • Select statement goes with Table Storage full in Dev environment but uses index range scan in SIT environment

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 10K+ times! This question is

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)

We're not taking comments currently, so please try again later if you want to add 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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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