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

SHUTDOWN ABORT, IMMEDIATE, NORMAL, TRANSACTIONAL

SHUTDOWN ABORT;

Fastest.

Uncommitted transactions are not rolled back.

All users currently connected to the database are implicitly disconnected.

The next database startup will require instance recovery.

SHUTDOWN IMMEDIATE;

Does not wait for current calls to complete or users to disconnect from the database.

Further connects are prohibited. 

The database is closed and dismounted. 

The instance is shutdown and no instance recovery is required on the next database startup.

SHUTDOWN NORMAL;

NORMAL is the default option which waits for users to disconnect from the database.

Further connects are prohibited. 

The database is closed and dismounted. 

The instance is shutdown and no instance recovery is required on the next database startup.

SHUTDOWN TRANSACTIONAL;

Performs a planned shutdown of an instance while allowing active transactions to complete first. 

It prevents clients from losing work without requiring all users to log off.

No client can start a new transaction on this instance.

After completion of all transactions, any client still connected to the instance is disconnected.

Now the instance shuts down just as it would if a SHUTDOWN IMMEDIATE

The next startup of the database will not require any instance recovery procedures.

$ lsnrctl start 

You must be connected to a database as SYSDBA, SYSOPER, SYSBACKUP, or SYSDG.

SYSDBA - database management commands

SYSOPER - Limited to database shutdown, startup, open, recover, create spfile.

SYSBACKUP - similar to SYSDBA. with few limitations.

SYSDG - Data Guards Related