sql tuning

Posted by Roshan on Jul 30, 2018; 6:03am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/sql-tuning-tp6504.html

Hi Erman,

I have a few questions regarding sql tuning.

Before executing
select * from table(dbms_xplan.display_cursor('<SQL ID>',NULL,'allstats typical last'));

I set

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;

or alter session set statistics=all;

For testing purpose, I ran xplain plan on the sql in the screenshot below.
pastedImage_0.png
The E-rows shows is it looping 4 rows from dba_data_files.

The second time I ran the xplain plan, the value is different for E-rows and FIXED TABLE INDEX
test2.PNG

Why is it so?