DBMS_STATS Starhome Stuff

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_IMSI_PREFIXES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_COUNTRIES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_NETWORKS_T', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_MERGE_NETWORKS', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_MERGE_NW_MAPPING', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_NETWORKS_SEG_T', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_LANGUAGES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_SERVICE_MERGED_NETWORKS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_NETWORKS_EXCEPTIONS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_ACCOUNTS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

=========================

See execution plan of an SQL

=========================

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT sql_id, child_number 

  FROM V$SQL 

WHERE sql_id = 'fw4tmpkt79r4r';

SELECT sql_id, child_number 

  FROM V$SQL 

WHERE sql_text LIKE 'MERGE into SGA_W_IPN_SUBSCRIBER o using%';

SET LINESIZE 120

SET PAGESIZE 0

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('fw4tmpkt79r4r',3,'OUTLINE'));