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'));

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

How to Flush a single SQL

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

--DBA_HIST_ACTIVE_SESS_HISTORY 
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY 

 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs') 

   AND SNAP_ID = (SELECT MAX(snap_id) from DBA_HIST_SNAPSHOT);

--V$SESSION

SELECT * FROM V$SESSION 

 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--V$SQLAREA 

SELECT sql_text, sql_id, last_active_time, executions, 

       disk_reads, buffer_gets, user_io_wait_time

FROM V$SQLAREA 

WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--Flush Shared Pool for SQLs in question

SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='ccgg2ap67ptbp';

SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='0gn14c20ay0qs';

SQL> EXEC DBMS_SHARED_POOL.PURGE ('00000000FD2BC510,1283122549','C');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SHARED_POOL.PURGE ('000000009A0252B8,2158953176','C');

PL/SQL procedure successfully completed.