Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema

1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.

SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

2. Delete any existing statistics, using the following command from SQLPLUS.

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

3. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:

EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

4. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gather Schema statistics

exec dbms_stats.gather_schema_stats(

       ownname => 'SCOTT',

       cascade => TRUE,

       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

 with 

method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

• ESTIMATE_PERCENT: defaults:
•  9i : 100%
• 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
• 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)

• METHOD_OPT: defaults:
• 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
• 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

Export and Import schema statistics

Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

Create the table to store the statistics- 

EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema stats – will be stored in the ‘STATS_TABLE’

EXEC DBMS_STATS.export_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema.

EXEC DBMS_STATS.import_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats

EXEC DBMS_STATS.drop_stat_table(‘TEST’,’STATS_TABLE’);

Datafiles with highest I/O activity

col name format a40

set linesize 140

select * from (

select name,phyrds, phywrts,readtim,writetim

from v$filestat a, v$datafile b

where a.file#=b.file#

order by readtim desc) where rownum <6;

Sessions/Segments/Datafile with HIGH Physical Reads/Writes,I/O

Sessions with High physical Reads

set linesize 120
col os_user format a10
col username format a15

col pid format 9999999999
PROMPT SESSIONS SORTED BY PHYSICAL READS
PROMPT
select
OSUSER os_user,username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status=’ACTIVE’
order by PHYSICAL_READS;

 Segments with high Physical Reads

set pagesize 200

setlinesize 120

col segment_name format a20

col owner format a10

select segment_name,object_type,total_physical_reads

from ( select owner||'.'||object_name as segment_name,object_type,

value as total_physical_reads

from v$segment_statistics

where statistic_name in ('physical reads')

order by total_physical_reads desc)

where rownum <=10;

Segments with high Physical Writes

set pagesize 200

setlinesize 120

col segment_name format a20

col owner format a10

select segment_name,object_type,total_physical_writes

from ( select owner||'.'||object_name as segment_name,object_type,

value as total_physical_writes

from v$segment_statistics

where statistic_name in ('physical writes')

order by total_physical_writes desc)

where rownum <=10;

Datafiles with highest I/O activity

col name format a40

set linesize 140

select * from (

select name,phyrds, phywrts,readtim,writetim

from v$filestat a, v$datafile b

where a.file#=b.file#

order by readtim desc) where rownum <6;

Tranfer all Roles and Grants to Another Database

Run in source database:

SET pages 0
SPOOL privs.sql
-- create roles
SELECT 'create role ' || ROLE || ';'
  FROM dba_roles;
-- grant roles
SELECT    'grant '
       || granted_role
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_role_privs;
-- grant system privileges
SELECT    'grant '
       || PRIVILEGE
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_sys_privs;
-- grant object privileges
BREAK on myGrantor
COL myCommand newline
SELECT   'prompt ' || grantor || '
connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_tab_privs
ORDER BY grantor;
-- grant column privileges
SELECT   'connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || '('
         || column_name
         || ')'
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_col_privs
ORDER BY grantor;
QUIT

This will generate spool file "privs.sql" in current working directory

Remove references to users from privs.sql that does not exist in target database

connect as user with dba role on target database and run script privs.sql Object privileges can be granted by "object owner" only. Script will ask for password of each user who granted object privilege.

Prevent Truncate/Drop table from own schema

To prevent the truncation of tables in your own schema, write a trigger in your schema.

CREATE OR REPLACE TRIGGER PREVENT_TRUNCATE

BEFORE DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged');
END save_our_db;
/

Now when you try to truncate a table in your own schema, you will receive the following errors:

ORA-00604: error occurred at recursive SQL level 1

ORA-20999:Attempt To Truncate A Production Table Has Been Logged

ORA-06512: At Line 2

Another way to prevent user from dropping objects from own schema is as below:

- Here we are not using any trigger
- This would prevent drop object for SQL*PLUS only
- We could configure it for other IDE tools like TOAD, SQLDEVELOPER etc

SQL> connect system/manager

SQL> insert into product_user_profile (product,userid,attribute,char_value) values('SQL*Plus','SCOTT,'DROP','DISABLED');
SQL> commit;

SQL> select * from product_user_profiles;

PRODUCT    USERID  ATTRIBUTE  CHAR_VALUE
---------  ------- ---------- ----------------
SQL*PLUS   SCOTT   DROP       DISABLED

If for example user SCOTT attempts to Drop an object, it will get this error:

SQL> drop table xyz;
SP2-0544: invalid command: drop.

Alter Database Link

We can alter a DATABASE Link in order modify password (password changed recently for remote schema) without dropping & recreating.

Here is a simple test.

1. Create a DBLINK with wrong password.

CREATE DATABASE LINK TEST_LINK

 CONNECT TO TIGER

 IDENTIFIED BY "tiger1"

 USING 'TESTENV';

SQL> select * from dual@TEST_LINK;

select * from dual@TEST_LINK

                  *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from TEST_LINK

1.    Grant link alter permission to scott.

GRANT ALTER DATABASE LINK TO SCOTT;

Access Granted.

2.    Alter the link with correct password.

ALTER DATABASE LINK TEST_LINK

  CONNECT TO TIGER IDENTIFIED BY "tiger123";

SQL> select * from dual@TEST_LINK;

D

-

X

Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema

1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.

SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

2. Delete any existing statistics, using the following command from SQLPLUS.

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

3. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:

EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

d. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

with

method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

• ESTIMATE_PERCENT: defaults:
•  9i : 100%
• 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
• 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)

• METHOD_OPT: defaults:
• 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
• 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

How to Restore spfile from backup

1.Connect to target Database with RMAN.

i)If you have lost your spfile while your database is running then,

RMAN> CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,

RMAN>CONNECT TARGET /

RMAN>SET DBID 3386862614

2)Start the instance with dummy parameter file.

RMAN>STARTUP FORCE NOMOUNT

3) Restore server parameter file.

i)             To restore in default location,

RMAN>RESTORE SPFILE FROM AUTOBACKUP;

ii)            To restore in another location,

RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;

iii)           If you want to restore to a pfile then use,

RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';

4) Start the instance.

RMAN>STARTUP;

Cleanup Message Queue (MQ) Gateway Agent

Message queue terminated as DB was terminated unexpectedly. Once db is up, MQ won't start with following error.

SQL> exec dbms_mgwadm.shutdown
PL/SQL procedure successfully completed.

SQL> exec dbms_mgwadm.startup
BEGIN dbms_mgwadm.startup; END;
*
ERROR at line 1:
ORA-32846: Messaging Gateway agent cannot be started; status is BROKEN
ORA-06512: at "SYS.DBMS_MGWADM", line 88
ORA-06512: at "SYS.DBMS_MGWADM", line 66
ORA-06512: at line 1

No new LOG is genrating while restarting MQ.

SQL> select agent_status,agent_ping, last_error_date, last_error_msg, max_connections, max_memory, max_threads, agent_status,agent_ping from mgw_gateway;

AGENT_STATUS AGENT_PING LAST_ERROR_MSG MAX_CONNECTIONS AGENT_STATUS AGENT_PING
BROKEN UNREACHABLE ORA-32845: Messaging Gateway agent is already running 100 64 1 BROKEN UNREACHABLE

SOLUTION:

1.    Shutdown Message Queue

exec dbms_mgwadm.shutdown

2.    Check for invalid packages -

select owner, object_name, object_type  from dba_objects where status = 'INVALID'

3.    Recompile any invalid objects.  Either run UTLRP.SQL or recompile individually.

The dbms_mgwadm and mgwi_amdin packages should be valid.

4.    If the Messaging Gateway shutdown still does not work, then issue the following to cleanup the gateway -

Exec DBMS_MGWADM.CLEANUP_GATEWAY(1);

OR

exec dbms_mgwadm.cleanup_gateway(dbms_mgwadm.CLEAN_STARTUP_STATE);

This runs recovery tasks that set the gateway to a known state when the gateway agent has crashed or some other abnormal event occurs so that the gateway cannot be started.

NOTE: This command will only work if a previous shutdown command has been attempted otherwise error messages will be displayed.