SQL Performance (MOSC)

MOSC Banner

index full scan and no parallel degree when running stats collection on application table

edited Sep 8, 2016 5:00AM in SQL Performance (MOSC) 3 commentsAnswered

Hi Friends,

I am facing a wierd problem.

We are running stats collection by using below command:

BEGIN dbms_stats.gather_table_stats(ownname => 'SA', tabname => 'MTM_SITE_PART76_X_NEED3', degree => 10, cascade => TRUE,ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1') ; END;

It is calling below sql , which you can see is doing index full scan on K15_76_2000_3 . Problem is it is doing

SQL_ID  c9j2kcmpd6jtt, child number 0

-------------------------------------

select /*+  parallel(t,10) parallel_index(t,10) dbms_stats

cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)

no_monitoring no_substrb_pad  */count(*), count(distinct

Tagged:

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

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center