How to manually load an execution plan for an SQL statement into the SMB

Facts:
* Your customer has a query against a couple of tables with very volatile contents
* The problem is simply solved by analyzing the tables involved using dbms_stats.gather_table_stats. After that, the query runs with acceptable performance

Solution:
* Add the SQLs execution plan to the SMB as a FIXED statement, so the optimizer will use it next time

Here is how:

1. Make sure the parameter optimizer_use_sql_plan_baselines is set to TRUE.
This is default for 11g.

2. Find the sql_id for the query you want to mark as fixed.
Easiest is to use Enterprise Manager.

3. When found, make a note of the sql_id. Then create an STS as follows:

BEGIN
   DBMS_SQLTUNE.create_sqlset
   (sqlset_name  => 'ACCEPTED_NESTED_LOOP_QUERY',
    description  => 'Freeze of execution plan requested by customer.',
    sqlset_owner => 'DB_ADMIN'
    );
END;
/

4. Add the sql_id found in step 1 to your STS:

DECLARE
   sqlset_cur dbms_sqltune.sqlset_cursor;
   bf VARCHAR2(82);
BEGIN 
  bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'SCOTT' AND UPPER(SQL_ID) = 'BHAANST01X9YB' #';
  OPEN sqlset_cur FOR 
  SELECT VALUE(P) FROM TABLE( 
   dbms_sqltune.select_cursor_cache(bf, NULL, NULL, NULL, NULL, 1, NULL, 'TYPICAL')) P;
   dbms_sqltune.load_sqlset( 
     sqlset_name     => 'ACCEPTED_NESTED_LOOP_QUERY',
     populate_cursor => sqlset_cur, 
     load_option     => 'MERGE', 
     update_option   => 'ACCUMULATE', 
     sqlset_owner    => 'DB_ADMIN');
END;
/

5. Load the plan into the SQL Management Base (SMB). Put the following into a file called manual_load.sql:

declare
   l_num_plans PLS_INTEGER;
begin
 l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET
  (
  'ACCEPTED_NESTED_LOOP_QUERY',
  'DB_ADMIN'
  ,NULL,
  'YES',
  'YES',
  1000
  );
 DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/

Result:

$ sqlplus /nolog

SQL> connect DB_ADMIN
Enter password:
Connected.
SQL> set serveroutput on
SQL> @manual_load.sql
Number of plans loaded: 1

PL/SQL procedure successfully completed.

6. Verify that the plan is indeed saved:

SELECT sql_text,
       plan_name,
       sql_handle,
       creator,
       origin,
       created, 
       last_executed,
       enabled,
       accepted,
       fixed,
       autopurge,
       module
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT like '%your sql text%';

SQL_TEXT	PLAN_NAME	SQL_HANDLE	CREATOR	ORIGIN	CREATED	LAST_EXECUTED	ENABLED	ACCEPTED	FIXED	AUTOPURGE	MODULE
your statement will be visible here	SYS_SQL_PLAN_50701d3df0871b58	SYS_SQL_c95f410d50701d3d	DBA_ADMIN	MANUAL-LOAD	06.11.2013 15:36:20.000000		YES	YES	YES	YES	TOAD background query session

To view the execution plan that is now fixed in the SMB, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:

sqlplus / as sysdba
SQL>
set long 10000
set lines 200
set pages 300
SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_50701d3df0871b58'));


Further reading on the topic:
Oracle-base.com: SQL Plan Management in Oracle Database 11g Release 1
Oracle Database Performing tuning guide
Adaptive cursors and SQL Plan Managment by Arup Nanda

Components and Granules in the SGA

The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests.
Examples of memory components include

* the shared pool (used to allocate memory for SQL and PL/SQL execution)
* the java pool (used for java objects and other java execution memory)
* the buffer cache (used for caching disk blocks)

All SGA components allocate and deallocate space in units of granules.
Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size.
Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB.
For SGAs larger than 1 GB, granule size is 16 MB.

You can query the V$SGAINFO view to see the granule size that is being used by an instance.
The same granule size is used for alldynamic components in the SGA.

Source: Oracle Documentation

How to find the shared memory kernel parameter setting for a Sun Solaris Server

Use the utility prctl:

prctl -n project.max-shm-memory -i project 110

project: 110: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION   RECIPIENT
project.max-shm-memory
        privileged      17.0GB      -   deny     -
        system          16.0EB    max   deny      

Here, 17 GB is the shared memory configuration ("privileged").

This corresponds to the setting in /etc/project:
cat /etc/project

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
user.oracle:110:Oracle default project:::project.max-shm-memory=(privileged,18253611008,deny)

18253611008/1024/1024 = 17 GB

What is standard (or traditional) auditing?

From the Oracle Documentation:

"In standard auditing, you enable auditing of SQL statements, privileges, schema objects, and network or multitier activities. You can audit a specific schema table if you want."

You need to set AUDIT_TRAILto "DB" or "DB,EXTENDED", in order to start using standard auditing.
After this is done, you may execute the AUDIT statement to start auditing of different actions according to your need.
You can choose to write the audit-trail to the database itself, or to operating system files.

Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users. (It performs this audit even if you have not set audit options for the AUD$ and FGA_LOGS$ tables.)

Typically, non-SYS users do not have access to these tables, except if they have been explicitly granted access. If a non-SYS user tampers with the data in the SYS.FGA_LOG$ and SYS.AUD$ tables, then Oracle Database writes an audit record for each action.

To view the standard audit trail, query the views DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL.

Source: Oracle 11g Documentation


Note that in later releases, Oracle is often refering to standard auditing as "Traditional Auditing", since Oracle 12c introduced the concept of Unfied Auditing.

What is mandatory auditing?

Oracle Database always audits certain database-related operations and writes them to the operating system audit files.

It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing.

Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.

Mandatory auditing includes the following operations:

* Database startup
* SYSDBA and SYSOPER logins
* Database shutdown

Source: Oracle Documentation

What is Row Movement and how is it used?

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWIDs.

Features such as flashback table depends on row movement being enabled on the table.

To enable row movement on an existing table:

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMEMENT;

Using enable row movement can corrupt any Oracle features that rely on ROWID, such as nested tables.
Check for such objects before you enable row movement:

SELECT COUNT(*)
FROM dba_nested_tables
WHERE owner = 'SCOTT';


Sources: Oracle Documentation and Don Burleson

How to audit ALTER TABLE statements on a specific table

Enable auditing on specific table:

AUDIT ALTER ON SCOTT.T1;

Verify that the object is being audited:

SELECT OBJECT_NAME,OBJECT_TYPE,ALT
FROM  DBA_OBJ_AUDIT_OPTS
WHERE  OWNER ='SCOTT'
ORDER BY 1 ASC; 

Result:

|------------------------------------|
|object_name  | object_type  | alt   |
|-------------|--------------|-------|
|T1           | TABLE        | S/S   |
|------------------------------------|

Object is indeed being audited.

Make a change to the table:

ALTER TABLE SCOTT.T1
 ADD (age  number(2));

Check that the change is captured in the audit-trail:

SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE
FROM   DBA_AUDIT_TRAIL
WHERE  OBJ_NAME = 'T1'
AND    OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

|-----------------------------------------------------------------------------------------------------------|
|TIMESTAMP     | OS_USERNAME | OBJ_NAME  | USERHOST| SESSIONID | USERNAME | ACTION_NAME  | RETURNCODE |
|-----------------------------------------------------------------------------------------------------------|
|03.11.2013 08:32:28 | SCOTT | T1    | scottpc  | 172154    | SCOTT   | ALTER TABLE  | 0         |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 1430      |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 911       |
|01.11.2013 16:14:17 | SCOTT | T1    | scottpc  | 234766 | SCOTT   | ALTER TABLE  | 0         |
|-----------------------------------------------------------------------------------------------------------|

The return codes can be checked by using the oerr utility that comes with the oracle installed binaries, for example:

oerr ora 1430
01430, 00000, "column being added already exists in table"
// *Cause:
// *Action:

Note that other DDL statements, too, are captured in the audit trail for the table, such as DROP and TRUNCATE.
If you drop the table without purging the recyclebin, the auditing will still be in place, and if you query the DBA_OBJ_AUDIT_OPTS after a DROP without purge, it will show you the recyclebase name in the view.