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

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

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

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

Monday, November 4, 2013

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.