Category Archives: Uncategorized
Oracle ASH use cases
Overview
Oracle takes a snapshot of active sessions once a second. We can analyze ASH data to solve real world problems, such as sql monitoring, sql stats, sql plan management, and find queries that are not using bind variables.
SQL monitoring
With ASH data, you can monitor the queries that are currently running.
SQL stats
Sometimes, you may want to check the snapshots of the past. You can get the similar data from ASH history table.
SQL plan management
In some cases, the execution plan changes and causes performance issue. Using ASH, we can see when plan changed, which plan is better,which plan is causing issue.
Find queries that are not using bind variables
Some applications use literals instead of bind variables. When the literal values are different, each sql_id is unique, even though they are the same query from the perspective of the application. With the ASH data, you can find a group of queries that share the same signature.
Example Queries
The following query can be used for sql monitoring
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
col sample_time for a30
col machine for a40
col sid for 9999999
col force_matching_signature for 999999999999999999999999
set lines 1000
select v.inst_id,
v.sid,
v.blocking_session,
v.blocking_inst_id,
v.machine,
v.osuser,
v.schemaname,
v.state,
v.status,
v.sql_id,
v.phv,
v.force_matching_signature,
v.sql_exec_id,
v.sql_exec_start,
v.sample_time,
round((cast(v.sample_time as date) - v.sql_exec_start)*86400) seconds,
v.session_state,
v.event,
v.program,
v.sql_text
from (
select
h.inst_id,
h.session_id sid,
h.blocking_session,
h.blocking_inst_id,
h.machine,
s.osuser,
s.schemaname,
s.state,
s.status,
h.sql_id,
h.sql_plan_hash_value phv,
h.force_matching_signature,
h.sql_exec_id,
h.sql_exec_start,
h.sample_time,
h.session_state,
h.event,
h.program,
nvl2(t.sql_text,
replace(replace(t.sql_text, chr(10), ''), chr(13), ''), '') sql_text,
row_number() over (partition by
h.inst_id, h.session_id, h.session_serial#,
h.sql_id, h.sql_exec_id order by h.sample_time desc) r
from gv$active_session_history h
left join gv$session s on s.sid = h.session_id
and s.inst_id = h.inst_id and s.serial# = h.session_serial#
left join gv$sqltext t on t.inst_id = h.inst_id
and h.sql_id = t.sql_id and t.piece = 0
where h.sample_time > sysdate - 15/1440
and h.session_type = 'FOREGROUND'
) v
where v.r = 1
and (cast(v.sample_time as date) - v.sql_exec_start)*86400 > 5
order by v.inst_id, v.sample_time desc
;
Oracle resource manager for CDB
Overview
You can create a resouce plan in CDB level or PDB level, or both. This post summarizes how to use resource manager in CDB. The CDB level resource plan controls resouce allocation between individual PDBs, and the PDB level resouce plan controls resouce allocation between consumer groups.
You have two options when creating resouce plan directives, plan directive for individual PDBs or plan directive for performance profiles.
There are three plan directive attributes you can set, shares, utilization_limit, and parallel_server_limit.
When you create resouce plan in PDB level, the following restrictions apply,
A PDB resource plan cannot have subplans. A PDB resource plan can have a maximum of eight consumer groups. A PDB resource plan cannot have a multiple-level scheduling policy.
1. memory limits for PDBs
The following parameters control the memory limits for PDBs,
Prerequistes: NONCDB_COMPATIBLE=false, MEMORY_TARGET=0
DB_CACHE_SIZE SHARED_POOL_SIZE PGA_AGGREGATE_LIMIT PGA_AGGREGATE_TARGET SGA_MIN_SIZE SGA_TARGET
2. I/O limits for PDBs
The following parameters cannot be set in non-CDB. They can be set in PDBs or root. The PDB level setting overrides setting in root.
MAX_IOPS MAX_MBPS
3. the common procedures when create/update/delete a plan or plan directive
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
4. create/update/delete CDB plan
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'newcdb_plan',
comment => 'CDB resource plan for newcdb');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN(
plan => 'newcdb_plan',
new_comment => 'CDB plan for PDBs in newcdb');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN(
plan => 'newcdb_plan');
END;
/
5. create/update/delete resource plan directive for individual PDBs
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'newcdb_plan',
pluggable_database => 'salespdb',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN_DIRECTIVE(
plan => 'newcdb_plan',
pluggable_database => 'operpdb',
new_shares => 1,
new_utilization_limit => 10,
new_parallel_server_limit => 20);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(
plan => 'newcdb_plan',
pluggable_database => 'operpdb');
END;
/
6. create/update/delete resource plan directive for performance profiles
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'gold',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'silver',
shares => 2,
utilization_limit => 40,
parallel_server_limit => 40);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'copper',
new_shares => 1,
new_utilization_limit => 10,
new_parallel_server_limit => 20);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(
plan => 'newcdb_plan',
profile => 'operpdb');
END;
/
7. update the default directive for PDBs
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
plan => 'newcdb_plan',
new_shares => 1,
new_utilization_limit => 50,
new_parallel_server_limit => 50);
END;
/
8. update the autotask directive
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
plan => 'newcdb_plan',
new_shares => 1,
new_utilization_limit => 75,
new_parallel_server_limit => 75);
END;
/
9. enable/disable resource manager
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'newcdb_plan'; ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'salespdb_plan'; ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
10. monitoring resource manager
DBA_CDB_RSRC_PLAN_DIRECTIVES V$RSRCPDBMETRIC V$RSRCPDBMETRIC_HISTORY DBA_HIST_RSRC_PDB_METRIC
create physical standby in oracle 12
prepare standby
prepare primary
rman duplicate
-- backup primary
run
{
sql "alter system switch logfile";
sql "alter system checkpoint";
backup database;
backup archivelog all delete input;
backup current controlfile for standby;
}
-- duplicate to auxiliary
run
{
duplicate target database for standby nofilenamecheck dorecover;
}
Notes 
