Script for checking the database flashback settings

I normally use the following script to gather the most important facts about my flashback settings and readyness for a potential flashback database operation:

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300
set trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999
set pagesize 200
spool chk_flashb.log
prompt =================================================
Prompt give me the
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
       retention_target/60                "Hours of flback logs",
       flashback_size/1024/1024           "Current Flbacklog Size mb"
from v$flashback_database_log
/

prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn  "oldest flback SCN",
       oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/

prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
       SCN,
       TIME,
       DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE "guaranteed",
       STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;
Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;

Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
        end_time "End time",
        round(flashback_data/1024/1024) "MB of flbk data written",
        round(redo_data/1024/1024)      "MB of redo data"
from   v$flashback_database_stat
order by begin_time asc;
exit

How to use the dbms_explan.display_awr function to find the execution plan of a query in the AWR

set linesize 200
set pagesize 0
select * from table 
(dbms_xplan.display_awr( '44snxh96pfuxb',1084138060,null, 'TYPICAL'));

Where
- the first argument is the SQL id
- the second argument is plan hash value (optional)
- the third argument the DB_ID. If not specified, the value from V$DATABASE is used
- the fourth argument is the format string. TYPICAL is the default value.

Source: Oracle Documentation

How to use dbms_xplan.display_sql_plan_baseline to reveal execution plan for a SQL in the SMB

1. Find the plan_name:

SELECT sql_text,plan_name
FROM DBA_SQL_PLAN_BASELINES
WHERE sql_text LIKE '%

2. Use the dbms_xplan package to generate information + execution plan about this specific SQL plan:

SET LONG 10000
SET LINES 200
SET PAGES 300
SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_2f5ea4968dd47f8f'));

How to find the plan for a particular SQL in the SMB

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

Joined with v$sql, to reveal the SQL ID, and with some formatting:

select  b.SQL_handle,
        b.plan_name,
        b.origin,
        to_char(b.created, 'dd.mm.rrrr hh24:mi') created,
        to_char(b.last_modified, 'dd.mm.rrrr hh24:mi')last_mod, 
        to_char(b.last_executed, 'dd.mm.rrrr hh24:mi') last_exe,
        b.enabled,
        b.accepted, 
        b.fixed,
        b.optimizer_cost,
        b.executions,
        b.cpu_time,
        b.buffer_Gets,
        b.disk_Reads,
        round((b.elapsed_time/b.executions)/1000000,1) "seconds per execution",
        a.sql_id 
FROM DBA_SQL_PLAN_BASELINES b left outer join v$sql a
ON (b.plan_name = a.sql_plan_baseline)
where b.sql_text LIKE 'SELECT * FROM( SELECT KLM.VIEWTYPE KLMTYPE , TS%';

Result may look like the following:

SQL_HANDLE	PLAN_NAME	ORIGIN	CREATED	LAST_MOD	LAST_EXE	ENABLED	ACCEPTED	FIXED	OPTIMIZER_COST	EXECUTIONS	CPU_TIME	BUFFER_GETS	DISK_READS	seconds pr execution	SQL_ID
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p2a8f06e05	MANUAL-LOAD	11.08.2016 14:42	12.08.2016 07:36	12.08.2016 07:36	YES	YES	YES	10948	27	35862546	7299186	297	1,4	5h0syvrgsjf0j
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p23e45ab6f	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 14:47		NO	YES	NO	30537	1	3549461	434057	5371	10,8
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p28dd60076	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 15:02		NO	YES	NO	1324	2	18519310636	1131813	0	9253,6

From the output above, we can see that the fixed plan is the most efficient, that's why it was marked as FIXED in the SMB.

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