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;
/

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS( ownname => NULL);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS 

   (ownname=> 'SCHEMA_NAME',

    degree=>2, 

    cascade=>TRUE, 

    no_invalidate=>FALSE);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(

    ownname          => 'YOUR_SCHEMA_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 

    degree           => 2,      

    granularity      => 'AUTO',

    cascade          => TRUE,

    no_invalidate    => TRUE,   

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',

    options          => 'GATHER STALE' -- Only touches changed tables

  );

END;

/

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname          => 'YOUR_SCHEMA_NAME',

    tabname          => 'TABLE_A',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 

    degree           => 2,      

    granularity      => 'AUTO',

    cascade          => TRUE,

    no_invalidate    => TRUE,   

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'

  );

END;

/

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

Gather stats for few schemas

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

CREATE OR REPLACE PROCEDURE schema_stats_a2b IS 

  v_stats varchar2(2000);

BEGIN

  for i in (SELECT username FROM DBA_USERS WHERE username like 'A%' OR username like 'B%' AND account_status='OPEN') LOOP

    v_stats := 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS (ownname=> '''||i.username||''',degree=>2,cascade=>TRUE,no_invalidate=>FALSE, force=>TRUE);  end;';

    EXECUTE IMMEDIATE v_stats;

  END LOOP;

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.

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

See that there are no invalid objects

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

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 

  FROM DBA_OBJECTS WHERE status <> 'VALID';

SELECT 'ALTER '||OBJECT_TYPE||' '||owner||'.'||object_name||' COMPILE;' 

FROM  DBA_OBJECTS WHERE status <> 'VALID';