Thursday, November 7, 2013

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
Posted by at No comments:
Labels: ,

Wednesday, November 6, 2013

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
Posted by at No comments:
Labels: , ,

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'));
Posted by at No comments:
Labels:

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: find all plans created for a specific SQL ID, including those no longer in the cursor cache: 
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,
        CASE WHEN b.executions > 0 then
        ROUND((b.elapsed_time/b.executions)/1000000,1) 
        ELSE NULL
        END AS "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.

For finding a specific plan name: 
SELECT plan_name,
         sql_handle,
         creator,
         origin,
         TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
         TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
         enabled,
         accepted,
         fixed,
         autopurge,
         module,
         ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
         cpu_time,
         buffer_gets,
         disk_reads
    FROM DBA_SQL_PLAN_BASELINES
WHERE plan_name='SQL_PLAN_asryr8hfgjd5hddf0c98b'
For finding all Plans created today: 
SELECT plan_name,
         sql_handle,
         creator,
         origin,
         TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
         TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
         enabled,
         accepted,
         fixed,
         autopurge,
         module,
         ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
         cpu_time,
         buffer_gets,
         disk_reads
    FROM DBA_SQL_PLAN_BASELINES
WHERE TO_CHAR (created, 'dd.mm.yyyy') = TRUNC (SYSDATE)
Posted by at No comments:
Labels: ,

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
Posted by at No comments:
Labels: ,

Tuesday, November 5, 2013

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
Posted by at No comments:
Labels:

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
Posted by at No comments:
Labels:
Subscribe to: Comments (Atom)