Monitor real time active session statistics by ORAPUB tool

Posted on May 3, 2018May 7, 2018 by debasis maity

The tool was devised by Craig from ORAPUB which is excellent to monitor what happens in session real time.

1.First I need to down OSM tool kit from ORAPUB. First we need to run the following scripts to create some pre-requisite tables under sys/system schema.

a)osmprep.sql

-- ******************************************************
-- * Copyright Notice : (c)1998-2014,2015 OraPub, Inc.
-- * Filename : osmprep.sql 
-- * Author : Craig A. Shallahamer
-- * Original : 17-AUG-98
-- * Last Modified : 23-Dec-2014
-- * Description : OSM preperation script
-- * Usage : start osmprep.sql 
-- ******************************************************

prompt 
prompt OraPub System Monitor - Interactive (OSM-I) installation script.
prompt 
prompt (c)1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008 by OraPub, Inc.
prompt (c)2009,2010,2011,2012,2013,2014,2015 by OraPub, Inc.
prompt 
prompt There is absolutely no guarantee with this software. You may
prompt use this software at your own risk, not OraPub's risk. 
prompt No value is implied or stated.
prompt
prompt You may need to run $ORACLE_HOME/rdbms/admin/catblock.sql
prompt
prompt Connect as the user who will be using the OSM.
prompt
prompt Press ENTER to continue.
accept x

prompt
prompt Creating interim tables for delta calculations
prompt
-- Just for rtsysx8.sql
drop table o$sysstat;
create table o$sysstat as select * from v$sysstat where 1=0;
--
drop table system_event_snap;
create table system_event_snap as select * from v$system_event where 1=0;
drop table event_histogram_snap;
create table event_histogram_snap as select * from v$event_histogram where 1=0;
drop table op_sys_time_model_snap;
create table op_sys_time_model_snap as select * from v$sys_time_model where 1=0;
drop table op_os_cpu_snap;
create table op_os_cpu_snap as select stat_name,value from v$osstat where 1=0;

drop table o$system_event_snap;
create table o$system_event_snap as select * from v$system_event where 1=0;

drop table o$sys_time_model;
create table o$sys_time_model as select * from v$sys_time_model where 1=0;

drop table o$rtsysx_sql;
create table o$rtsysx_sql ( 
 sql_id varchar2(13),sql_address raw(8),cpu_time number, elapsed_time number,
 sorts number, executions number, parse_calls number, disk_reads number,
 buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql on o$rtsysx_sql (sql_id);

-- rtsysx8.sql
drop table o$rtsysx_sql8;
create table o$rtsysx_sql8 ( 
 sql_address raw(8),cpu_time number, elapsed_time number,
 sorts number, executions number, parse_calls number, disk_reads number,
 buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql8 on o$rtsysx_sql8 (sql_address);

-- rtcx.sql
drop table o$rtcx_snap;
create table o$rtcx_snap (category varchar2(100),time_cs number, waits number);

-- collection scripts (e.g., rss.sql, ash scripts,) need these two functions

create or replace function get_interval_s_fnc(i_intrvl interval day to second) return number is
begin
 return extract(day from i_intrvl) * 86400 +
 extract(hour from i_intrvl) * 3600 +
 extract(minute from i_intrvl) * 60 +
 extract(second from i_intrvl);
 exception
 when others then begin return null;
 end;
end;
/


prompt To categorize wait events for OSM reports, run:
prompt
prompt For pre-10g systems, run event_type_nc.sql
prompt For 10g and beyond, run event_type.sql

prompt
prompt Once you cateogrize the wait events, the installation is complete.
prompt
prompt Menu is osmi.sql 
prompt
prompt ENJOY!!
prompt

b)Then I will run another script

-- ********************************************************************
-- * Copyright Notice : (c)2001-2014 OraPub, Inc.
-- * Filename : event_type.sql - For 10g+ databases!!!!!
-- * Author : Craig A. Shallahamer
-- * Original : 11-may-01
-- * Last Update : 19-May-2015 (dealing with I/O events)
-- * Description : event_type.sql - Loads "event type" table
-- * which is used by other scripts.
-- * Usage : start event_type.sql
-- * This is usually run (manually) after osmprep.sql
-- ********************************************************************

prompt
prompt file: event_type.sql for Oracle 10g and beyond...
prompt
prompt About to categorize wait events for OSM reports.
prompt
prompt Press ENTER to re-create the o$event_type table.
accept x

-- Event TYPES are defined as follows:

-- ior - IO read related wait
-- iow - IO write related wait
-- other - "real" but not explicitly categorized
-- idle/bogus - idle events, usually not useful

drop table o$event_type
/
create table o$event_type (
event varchar2(64),
type varchar2(64)
)
/

insert into o$event_type
 select name,'other'
 from v$event_name
 where wait_class in ('Administrative','Application','Cluster','Concurrency',
 'Configuration','Other','Scheduler','Queuing','Scheduler')
/
insert into o$event_type
 select name,'bogus'
 from v$event_name
 where wait_class in ('Idle','Network')
/
insert into o$event_type
 select name,'ior'
 from v$event_name
 where wait_class in ('Commit','System I/O','User I/O')
 and name like '%read%'
/
insert into o$event_type
 select name,'iow'
 from v$event_name
 where wait_class in ('Commit','System I/O','User I/O')
 and name like '%write%'
/
insert into o$event_type
select name, 'other' from v$event_name
minus
select event, 'other' from o$event_type
/

-----------------------------------------------------------------------------------------
----- DO NOT REMOVE THE BELOW LINES as they make adjustments to the broad inserts above.
-----------------------------------------------------------------------------------------

-- select 'update o$event_type set type=''iow'' where event = '''||name||''';' from v$event_name where name like '%I/O%';

-- The below is not perfect cleary, but probably good enough... you still must think...

update o$event_type set type='iow' where event = 'Parameter File I/O';
update o$event_type set type='iow' where event = 'Disk file operations I/O';
update o$event_type set type='iow' where event = 'Disk file I/O Calibration';
update o$event_type set type='iow' where event = 'Datapump dump file I/O';
update o$event_type set type='iow' where event = 'dbms_file_transfer I/O';
update o$event_type set type='iow' where event = 'DG Broker configuration file I/O';
update o$event_type set type='iow' where event = 'Log archive I/O';
--update o$event_type set type='iow' where event = 'RMAN backup & recovery I/O';
update o$event_type set type='iow' where event like 'RMAN backup % recovery I/O';
update o$event_type set type='iow' where event = 'Standby redo I/O';
update o$event_type set type='iow' where event = 'RMAN Disk slave I/O';
update o$event_type set type='iow' where event = 'RMAN Tape slave I/O';
update o$event_type set type='iow' where event = 'DBWR slave I/O';
update o$event_type set type='iow' where event = 'LGWR slave I/O';
update o$event_type set type='iow' where event = 'Archiver slave I/O';
update o$event_type set type='iow' where event = 'db file async I/O submit';
update o$event_type set type='iow' where event = 'concurrent I/O completion';
update o$event_type set type='iow' where event = 'Archive Manager file transfer I/O';
--update o$event_type set type='iow' where event = 'resmgr:large I/O queued';
--update o$event_type set type='iow' where event = 'resmgr:small I/O queued';
update o$event_type set type='iow' where event = 'utl_file I/O';
update o$event_type set type='iow' where event = 'TEXT: File System I/O';
update o$event_type set type='iow' where event = 'ASM Fixed Package I/O';
update o$event_type set type='iow' where event = 'ASM Staleness File I/O';
update o$event_type set type='iow' where event = 'Data Guard: RFS disk I/O';


update o$event_type set type='iow' where event = 'free buffer waits';
update o$event_type set type='other' where event like 'latch%';
update o$event_type set type='other' where event like 'enq%';
update o$event_type set type='other' where event like 'cursor%';
update o$event_type set type='iow' where event like 'log%sync%';
update o$event_type set type='iow' where event like 'log%write%';
update o$event_type set type='iow' where event like 'db%write%';
update o$event_type set type='iow' where event like 'log%file%switch%';
update o$event_type set type='iow' where event like 'LGWR%';
update o$event_type set type='ior' where event like 'log%read%';
update o$event_type set type='ior' where event like 'db%read%';
update o$event_type set type='iow' where event like 'i/o%slave%wait';
update o$event_type set type='iow' where event = 'write complete waits';
update o$event_type set type='bogus' where event = 'wait for unread message on broadcast channel';
update o$event_type set type='bogus' where event = 'wait for unread message on multiple broadcast channels';
--update o$event_type set type='bogus' where event like 'db%file%async%submit%';
-- update o$event_type set type='bogus' where event like 'DLM%cross%inst%call%completion';
update o$event_type set type='bogus' where event like 'rdbms ipc reply';

-- for 12c
update o$event_type set type='bogus' where event like 'LGWR worker group idle';

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------


commit;

col name format a50 trunc
col wait_class format a20
col type format a20

prompt
prompt OraPub Categorization Summary
prompt ------------------------------------------------

select distinct type,
count(*)
from o$event_type
group by type
order by type
/
select count(*) from o$event_type
/

prompt
prompt Oracle Categorization Summary
prompt ------------------------------------------------

select distinct wait_class, count(*) from v$event_name group by wait_class order by wait_class
/
select count(*) from v$event_name
/

2.Now the following script can be saved under /home/oracle/rss.sql

-- ******************************************************
-- * Copyright Notice : (c)2014 OraPub, Inc.
-- * Filename : rss.sql - Realtime Session Sampler
-- * Author : Craig A. Shallahamer
-- * Original : 07-Jun-2014
-- * Last Modified : 15-Jul-2014
-- * Description : Collect and display session activity in realtime
-- * Usage : @rss sid_low sid_high serial#_low serial#_high cpu|wait|% event%|% delay_s
-- * @rss 0 999999 0 999999 % % 1
-- * @rss 0 999999 0 999999 cpu % 1
-- * @rss 0 999999 0 999999 wait % 1
-- * @rss 0 999999 0 999999 wait db%file%seq% 1
-- * @rss 328 328 1 1 % % 0.25
-- ******************************************************

set echo off verify off heading off
--set echo on verify on heading on

def sidLow=&1
def sidHigh=&2
def serLow=&3
def serHigh=&4
def state=&5
def wePartial=&6
def delay=&7

prompt OraPub Realtime Session Sampler - collection and display
prompt
prompt Every &delay second(s), session ID from &sidLow to &sidHigh and serial# from &serLow to &serHigh
prompt in a &state state will be sampled and displayed.
prompt When waiting, only display when wait event is like &wePartial
prompt 
prompt Output will be written to the /tmp/rss_sql.txt file.
prompt To stream output in realtime, in another window do: tail -f /tmp/rss_sql.txt
prompt
accept x prompt "To begin sampling press ENTER. Otherwise break out now."

create or replace directory temp_dir as '/tmp';

prompt To stop sampling, break out (e.g., CNTRL-C)
prompt
prompt Sampling started...

set serveroutput on

begin
declare
 type cv_typ is ref cursor;
 cv cv_typ;
 sql_to_run_v varchar2(999);

fHandle UTL_FILE.FILE_TYPE;

cntr_v number;
 next_sample_time_ts_var timestamp;

current_timestamp_v timestamp;
 sid_v number;
 serial#_v number;
 username_v varchar2(30);
 state_v varchar2(19);
 event_v varchar2(64);
 wecat_v varchar2(64);
 sql_id_v varchar2(13);
 p1_v number;
 p2_v number;
 p3_v number;

begin
 dbms_session.set_identifier('osm:i rss.sql');

fHandle := UTL_FILE.FOPEN('TEMP_DIR', 'rss_sql.txt', 'W');

UTL_FILE.PUT_LINE(fHandle, 'Starting sampling...');
 utl_file.fflush(fHandle);

cntr_v := 0;

while 1 = 1
 loop

cntr_v := cntr_v + 1;

select current_timestamp + interval '0 00:00:&delay' day to second
 into next_sample_time_ts_var
 from dual;

begin

open cv for
 select
 current_timestamp,
 sid,
 serial#,
 decode(username,'','-',username),
 decode(state,'WAITING','WAIT','CPU '),
 nvl(sql_id,'-'),
 decode(state,'WAITING',decode(o.type,'bogus','idle',o.type),'-'),
 decode(state,'WAITING',vs.event,'-') ,
 decode(state,'WAITING',p1,''),
 decode(state,'WAITING',p2,''),
 decode(state,'WAITING',p3,'')
 from v$session vs,
 o$event_type o
 where sid between &sidLow and &sidHigh
 and serial# between &serLow and &serHigh
 and decode(state,'WAITING','WAIT','CPU') like upper('&state%')
 and vs.event like '&wePartial%'
 and vs.event = o.event(+)
 and o.type != 'bogus';

loop
 fetch cv into
 current_timestamp_v, 
 sid_v,
 serial#_v,
 username_v,
 state_v,
 sql_id_v,
 wecat_v,
 event_v,
 p1_v, p2_v, p3_v
 ;
 exit when cv%NOTFOUND;

UTL_FILE.PUT_LINE(fHandle, lpad(trim(cntr_v),3,' ')||' '|| to_char(current_timestamp_v,'HH24:MI:SS:FF3')||' '||lpad(trim( sid_v ),5,' ')||lpad(trim(serial#_v),6,' ')||' '||lpad(trim(username_v),10,' ')||' '||state_v||' '||rpad(trim(sql_id_v),20,' ')||' '||rpad(trim(wecat_v),5,' ')||' '||rpad(trim(event_v),30,' ')||' ['||p1_v||','||p2_v||','||p3_v||']');
 utl_file.fflush(fHandle);

end loop;

EXCEPTION when others then
 cntr_v := cntr_v + 1;
 end;

dbms_lock.sleep( greatest( get_interval_s_fnc( next_sample_time_ts_var - current_timestamp ),0));

end loop;
 UTL_FILE.PUT_LINE(fHandle, 'Ending sampling...');
 UTL_FILE.FCLOSE(fHandle);
end;
end;
/

3.Now execute the script to take sames of either individual user or many users

Situation: I want to sample a single session (sid:10 serial:50) once every five seconds.

SQL>@rss.sql 10 10 50 50 % % 5.0

Situation: I want to essentially stream a single session's (sid:10 serial:50) activity.

SQL>@rss.sql 10 10 50 50 % % 0.125

Situation: I want to see what sessions are waiting for an row level lock while sampling once every second.

SQL>@rss.sql 0 99999 0 99999 wait enq%tx%row% 1.0

Situation: I want to see which sessions are consuming CPU, while sampling once every half second.

SQL>@rss.sql 0 99999 0 99999 cpu % 0.50

Situation:I want to same all sessions of my database

SQL> @rss.sql 0 99999 0 99999 % % 0.125

Reference

http://blog.orapub.com/20140811/watch-oracle-db-session-activity-with-the-real-time-session-sampler.html

Cache buffer chain CBC latch simple query to investigate

Posted on April 26, 2018April 26, 2018 by debasis maity

For deep understanding on CBC,please refer to below links:-

http://www.proligence.com/pres/ioug14/2014_431_Nanda_ppr.pdf

and

Video tutorial from ORAPUB.

I am just going to mention some useful queries to investigate or identify CBC latch

Step 1:-You as user fire a sql query from client.

select * from cbctest;

Step 2:-Now oracle will search metadata and determine what is your file_id and block_id to locate the block to be fetched from buffer cache or physical disk.

select
col,
dbms_rowid.rowid_relative_fno(rowid) rfile#,
dbms_rowid.rowid_block_number(rowid) block#
from cbc;

Step 3:-Let me for example choose file_id = 6 and block_id=2288143.Now oracle will determine DBA value from it.

SQL> select dbms_utility.make_data_block_address(6,2288143) from dual;

Step 4:-If there are 3 chains,oracle will use module function that returns the reminder from an input after dividing it by 3:

SQL> select mod(27453967,3) from dual;

MOD(27340036,3)
—————
1

So this block will be put into chain #1.

Simulation of CBC test:-

From one session,run following:-

declare
 aa varchar2(1000);
 begin
 for i in 1..1000000 loop
 select count(1) into aa from cbc ;
 end loop;
 end;
 /

From another session ,run following:-

declare
 begin
 for i in 1..1000000 loop
 update CBC set c1='MAITY';
 end loop;
 commit;
 end;
 /

From oratop,I can see “Latch: cache buffers chains” now.

Mechanism:-

1.Oracle once determine file_id and block_id,create a hash value first.

2.After determining hash value,oracle will acquire latch to hash into hash bucket and move across to chain to ask block header whether the block is already in memory or it needs to be fetched from disk.

When the block comes to the buffer cache, Oracle applies a hash function to determine the buffer chain number and places the block in a buffer in that chain alone. Similarly, while looking up a specific buffer, Oracle applies the same hash function to the DBA, instantly knows the chain the buffer will be found and walks that specific buffer only.This makes accessing a buffer much easier compared to searching the entire cache.

Why CBC cache buffer chain occurs

Problem:-

a)When different session try to acquire latch (Your buffer is very popular) chain #1.It needs to spin and sleep several times.

b)When different session try to acquire latch chain #1 and chain #2.It needs to spin and sleep several times.

Remember,latch is exclusive and if one session is trying to acquire latch,other session will be in queue.

No problem:-

When different session try to acquire latch chain #1 and chain #4.There will be no contention as they are in different latch .

Now how we can identify CBC problem

Step 5:-Let us find the latch number of “cache buffers chains”

SQL> select latch# from v$latch where name = 'cache buffers chains';

LATCH#
———-
228

If you check the values of the two hidden parameters explained earlier, you will see:
_db_block_hash_buckets 524288
_db_block_hash_latches 16384

Step 7:-Now check longest sleep and gets by executing following query 1 minutes gap and check whether sleep and gets are increasing.

select * from v$latch_children where latch#=228 order by sleeps desc;

Step 8:-You can also identify the buffer causing cache buffer chain wait for session you are experiencing CBC problem.Let us say session# 208 is experiencing CBC contention.

select p1, p1raw, p1text
 from v$session where sid = 208;

Here please take a note of p1raw which is hash address.

Step 8:-Please verify gets,misses and sleeps from v$latch_children

select gets, misses, sleeps, name
 from v$latch where addr = '000000014AFC7A70';

Step 9:-Also determine touch count of the corresponding block .

select dbarfil, dbablk, tch
 from x$bh
 where hladdr = '000000014AFC7A70';

Identify overall problem on CBC

Step 10:-You can also check from v$session_wait or v$active_session_history

select p1raw,count(*) from v$session_wait where event like '%cache%buffers%' group by event, p1raw order by 2 desc

select p1, count(*)
 from v$active_session_history
 where sample_time < sysdate – 1/24
 and event = 'latch: cache buffers chain'
 group by event, p1
 order by 3 desc

Step 11:-The easiest way is to dump the block and get the object ID from the dump file. Here is how you dump the above mentioned block.

alter system dump datafile 6 block min 220 block max 220;

or we can get block_id of corresponding address.

select hladdr, file#, dbablk, decode(state,1,'cur ',3,'CR',state) ST, tch
 from x$bh where hladdr in
 (select addr from (select addr from v$latch_children where addr='000000B9CA3336A0'
 order by sleeps, misses,immediate_misses desc )where rownum <2)

Step 12:-Now we can identify the segment_name which may be table or index using below query.

select segment_name
 from dba_extents
 where file_id = 48
 and 94182 between block_id and block_id + blocks - 1
 and rownum = 1

Solving problem:-

a)CBC latch waits are caused by popularity of the blocks by different processes. If you reduce the popularity, you reduce the chances that two processes will wait for the same
buffer. Note: you can’t completely eliminate the waits; you can only reduce it. To reduce is, reduce logical I/O. For instance,Nested Loops revisit the same object several times causing the buffers to be accessed multiple times. If you rewrite the query to avoid NLs, you will significantly reduce the chance that one process will wait for the CBC latch.

b)You can minimize less no of rows per block.The less the number of rows in a block, the less popular the block will be. You can reduce the number of rows in a block by increasing PCTFREE or using ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK.

c) If that does not help, you can partition a table. That forces the data block address to be recomputed for each partition, making it more likely that the buffers will end up in different buffer chains and hence the competition for the same chain will be less.

Performance monitoring or tuning tool for SQL query taking long time using various tools like explain plan,sql monitor,awrsqrpt,sqlhc

Posted on April 4, 2018May 13, 2018 by debasis maity

Performance monitoring or tuning tool for SQL query taking long time using various tools

In that post,I will explain using various method or tool to investigate performance problem of particular sql query.

Let me create a test case first

create table test_c(id number,name char(100),address char(100),create_date date);
create table test_p(id number,name char(100));

declare
 v_m number;
 begin
 for i in 1..10000000 loop
 select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
 insert into test_c values (i, 'My _name'||dbms_random.value(),'My _name'||dbms_random.value(),sysdate);
 end loop;
 commit;
 end;

declare
 v_m number;
 begin
 for i in 1..10000000 loop
 select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
 insert into test_p values (i, 'My _name'||dbms_random.value());
 commit;
 end loop;
 end;

SQL> create index i1_test_c on test_c(id);

SQL> create index i1_test_p on test_p(id);

1.Run time analysis

1.1. Analysis by different options of explain plan and execution plan.

Here I used GATHER_PLAN_STATISTICS hint to capture E-rows (Estimated rows) and A-rows (Actual Rows).

Please use the below query for simulating the test case when bind variable can cause change of explain plan hence execution time.user will complain why one query is fast yesterday but slow today.one of cause may be bind variable change causing your query to take different execution plan in run time.this is sometimes default behavior or sometimes abnormal.my intention is to show how to use different method to check what may causes changes in BEHAVIOR.

The following query may pick adaptive plan.But this depend on your environment.

1.1.1 First I will test with id between 1 and 1000 .We will note the execution plan.

SQL> variable var1 number;
SQL> variable var2 number;

SQL> exec :var1:=1;

PL/SQL procedure successfully completed.

SQL> exec :var2:=1000;

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select /*+ gather_plan_statistics */ a.name,a.address,b.id from test_c a,test_p b where a.id=b.id and b.id between :var1 and :var2 order by a.name;

1000 rows selected.

Please note that if adaptive plan is being used, then it means SQL plan is being changed during execution because of cardinality mismatch on the fly.

Now we can use below method to identify what steps have been removed during run time execution by statistics collector.The steps marked in RED are deleted during on the fly execution plan generation.

SQL> SET LINESIZE 200 PAGESIZE 100
 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

1.1.2 Now I am gathering statistics.After that I will run same statement.You may notice difference in cost calculation.

exec :var2:=1000;

SQL>set autotrace traceonly;

SQL> select /*+ gather_plan_statistics */ a.name,a.address,b.id from test_c a,test_p b where a.id=b.id and b.id between :var1 and :var2 order by a.name;
 1000 rows selected.

We can check A-rows and E-rows which can show indication in cardinality mismatches.

SQL> set linesize 200 pagesize 100;
SQL> SELECT * FROM table(dbms_xplan.display_cursor('fwhp6ukwmb1fj', 0, 'iostats last'));

1.1.3 Now let me run the same query again.We will check different advanced options shown from explain plan.

exec :var2:=1000;

Elapsed: 00:02:02.74
 SQL> SELECT *  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED')); 

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SQL_ID fwhp6ukwmb1fj, child number 2
 -------------------------------------
 select /*+ gather_plan_statistics */ a.name,a.address,b.id from test_c a,test_p b where a.id=b.id and b.id between :var1 and :var2 order by a.name

Plan hash value: 3508152348

Query Block Name / Object Alias (identified by operation id):
————————————————————-

1 – SEL$1
4 – SEL$1 / B@SEL$1
5 – SEL$1 / A@SEL$1
6 – SEL$1 / A@SEL$1

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1” (“TEST_C”.”ID”))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$1″ “A”@”SEL$1″)
INDEX(@”SEL$1” “B”@”SEL$1” (“TEST_P”.”ID”))
LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1″)
USE_HASH(@”SEL$1” “B”@”SEL$1″)
SWAP_JOIN_INPUTS(@”SEL$1” “B”@”SEL$1”)
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
————————————–

1 – :VAR1 (NUMBER): 1
2 – :VAR2 (NUMBER): 1000

Predicate Information (identified by operation id):
—————————————————

2 – filter(:VAR2>=:VAR1)
3 – access(“A”.”ID”=”B”.”ID”)
4 – access(“B”.”ID”>=:VAR1 AND “B”.”ID”<=:VAR2)
6 – access(“A”.”ID”>=:VAR1 AND “A”.”ID”<=:VAR2)

Column Projection Information (identified by operation id):
———————————————————–

1 – (#keys=1) “A”.”NAME”[CHARACTER,100], “B”.”ID”[NUMBER,22],
“A”.”ADDRESS”[CHARACTER,100]
2 – “B”.”ID”[NUMBER,22], “A”.”ADDRESS”[CHARACTER,100], “A”.”NAME”[CHARACTER,100]
3 – (#keys=1) “B”.”ID”[NUMBER,22], “A”.”ADDRESS”[CHARACTER,100],
“A”.”NAME”[CHARACTER,100]
4 – “B”.”ID”[NUMBER,22]
5 – “A”.”ID”[NUMBER,22], “A”.”NAME”[CHARACTER,100], “A”.”ADDRESS”[CHARACTER,100]
6 – “A”.ROWID[ROWID,10], “A”.”ID”[NUMBER,22]

73 rows selected.

1.2 You can view run time performance of a query by writing SQL query which takes data from v$sql

select sql_text,sql_id,executions,users_executing,invalidations,disk_reads,buffer_gets,optimizer_cost,cpu_time,elapsed_time,rows_processed,is_bind_sensitive from v$sql where sql_id='fwhp6ukwmb1fj'

Please note disk_reads,buffer_gets.Sudden rise of these values may indicate problem.It also evident that query has 2 child cursors with different optimizer_cost.Users_executing shows 1 when the query is being executed.The rows_processed column shows how much rows the query has already fetched.

1.3 Checking run time performance using SQL monitor.

http://www.oracle.com/technetwork/database/manageability/owp-sql-monitoring-128746.pdf

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => ‘fwhp6ukwmb1fj’, type => ‘HTML’, report_level => ‘ALL’) AS report FROM dual;

After the execution finises,it will show as below:-

2.historical analysis

2.1 Analysis using AWR history to check plan flip

Now there is another option to check what plans were used during execution of particular SQL_ID from AWR. This is good option for historical analysis OF PLAN FLIP.

SQL> select * from table(dbms_xplan.display_awr('fwhp6ukwmb1fj'));

2.2 Analysis using SQLHC report (Light wight version of SQLT report)

For more details:-

SQL Tuning Health-Check Script (SQLHC) Video (Doc ID 1455583.1)

sqlhc_20180403_233101_fwhp6ukwmb1fj\sqlhc_20180403_233101_fwhp6ukwmb1fj_3_execution_plans.html

This html is important to understand possible plan flip

A)

B)

C:\Project\sql_tuning\sqlhc_20180403_233101_fwhp6ukwmb1fj\sqlhc_20180403_233101_fwhp6ukwmb1fj_2_diagnostics.html

This html is very important to compare different metrics to pin point the issue.

2.3 Analysis using awrsqlrpt report

How to generate AWR SQL report:-

sqlplus / as sysdba

SQL> @?/rdbms/admin/awrsqrpt.sql

Type Specified: html

Enter value for num_days: 1

Enter value for begin_snap: 11949
 Begin Snapshot Id specified: 11949

Enter value for end_snap: 11957
 End Snapshot Id specified: 11957

Specify the SQL Id
 ~~~~~~~~~~~~~~~~~~
 Enter value for sql_id:Enter SQL_ID to check

The following section is showing possible plan change

The below sections shows plan change and effects on execution details:-

AWR setup and configuration

Posted on April 2, 2018 by debasis maity

1.General Information

Memory versions of statistics regularly transferred to disk by MMON.

AWR stores base statistics:-counters and value statistics.

ASH data is firstly captured first to memory in 1 second interval for active sessions only.ASH data is reduced by 10 times sample in memory data.ASH data is used by ADDM.

The advisor report produced by ADDM reports are used by segment advisior and other advisiors.

AWR stores in SYSAUX.

By default 60 min interval.

On typical case,for 10 Active sessions,200MB to 300MB space is occupied for 7 days.

2.How to estimate size of SYSAUX in case of retention is changed

system. A sizing script, utlsyxsz.sql, includes factors such as the size of the current occupants of the SYSAUX tablespace, number of active sessions, frequency of snapshots, and retention time.

SQL> @?/rdbms/admin/utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
23:53:17 on Apr 01, 2018 ( Sunday ) in Timezone -05:00

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
———– —————————————- —– —————– —
* ORCL localhost01 – Linux x86 64-bit 1 12:37:35 (02/21) YES
ORCL localhost02 – Linux x86 64-bit 2 12:37:35 (02/21) YES

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 4,700.1 MB
|
| Total size of SM/AWR 3,360.6 MB ( 71.5% of SYSAUX )
| Total size of SM/OPTSTAT 269.1 MB ( 5.7% of SYSAUX )
| Total size of SM/OTHER 368.5 MB ( 7.8% of SYSAUX )
| Total size of SDO 77.8 MB ( 1.7% of SYSAUX )
| Total size of XDB 68.6 MB ( 1.5% of SYSAUX )
| Total size of SM/ADVISOR 45.6 MB ( 1.0% of SYSAUX )
| Total size of XSOQHIST 38.8 MB ( 0.8% of SYSAUX )
| Total size of AO 38.8 MB ( 0.8% of SYSAUX )
| Total size of JOB_SCHEDULER 16.6 MB ( 0.4% of SYSAUX )
| Total size of ORDIM/ORDDATA 16.1 MB ( 0.3% of SYSAUX )
| Total size of LOGMNR 13.9 MB ( 0.3% of SYSAUX )
| Total size of WM 7.2 MB ( 0.2% of SYSAUX )
| Total size of TEXT 3.8 MB ( 0.1% of SYSAUX )
| Total size of AUDSYS 3.6 MB ( 0.1% of SYSAUX )
| Total size of SMON_SCN_TIME 3.3 MB ( 0.1% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 2.4 MB ( 0.1% of SYSAUX )
| Total size of PL/SCOPE 1.6 MB ( 0.0% of SYSAUX )
| Total size of LOGSTDBY 1.5 MB ( 0.0% of SYSAUX )
| Total size of EM_MONITORING_USER 1.4 MB ( 0.0% of SYSAUX )
| Total size of STREAMS 1.0 MB ( 0.0% of SYSAUX )
| Total size of ORDIM 0.4 MB ( 0.0% of SYSAUX )
| Total size of AUTO_TASK 0.4 MB ( 0.0% of SYSAUX )
| Total size of Others 359.4 MB ( 7.6% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

** Value for ‘Interval Setting’: 60

** Value for ‘Retention Setting’: 90

** Value for ‘Number of Instances’: 2

** Value for ‘Average Number of Active Sessions’: 20

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

** Value for ‘Number of Tables’: 137

** Value for ‘Number of Partitions’: 37

** Value for ‘Statistics Retention’: 31

** Value for ‘DML Activity’: 2

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| Est size of SM/AWR 9,779.7 MB
| Est size of SM/OPTSTAT 51.5 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 10,901.6 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

3.The awrinfo.sql script produces a report of the estimated growth rates of various occupants of the SYSAUX tablespace.

SQL> @?/rdbms/admin/awrinfo.sql

AWR handles space management for the snapshots. Every night the MMON process purges snapshots that are older than the retention period. If AWR detects that SYSAUX is out of space, it automatically reuses the space occupied by the oldest set of snapshots by deleting them. An alert is then sent to the DBA to indicate that SYSAUX is under space pressure.

4.Modify snapshot setting

SQL>DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(retention IN NUMBER DEFAULT null,

Interval IN NUMBER DEFAULT null,

Topnsql IN NUMBER DEFAULT null);

Retention:-default 8 days. Minimum 1 day.0 to disable automatic purging.

Interval:-Minimum 10 min.Maximum 100 years.Default 60 min.

Topnsql:-top 30 for typical.Top 100 for ALL.

5.Create manual snapshot:-

SQL>DBMS_WORKLOAD_REPOSITORY.create_snapshot.

6.Generating AWR reports:-

SQL>@?/rdbms/admin/awrrpt.sql

The user must have SELECT_CATALOG_ROLE privilege.

DBA_HIST_SNAPSHOT will have range of SNAP_ID.

Script prompts for the following:
– Report format: HTML or text
– Number of days from which snapshots are to be chosen
– Beginning and ending snapshot IDs
– File name for the report File name for the report

STATISTICS_LEVEL parameter Oracle 12c

Posted on April 2, 2018 by debasis maity

You determine the level of statistics collection on the database by setting the value of the STATISTICS_LEVEL parameter. The values for this parameter are:

• BASIC: No advisory or other statistical data is collected. You can manually set other statistic collection parameters such as TIMED_STATISTICS and DB_CACHE_ADVICE.Many of the statistics required for a performance baseline are not collected. Oracle strongly recommends that you do not disable statistic gathering.
• TYPICAL: This is the default value. Data is collected for segment-level statistics, timed statistics, and all advisories. The value of other statistic collection parameters is overridden.
• ALL: Collection is made of all the TYPICAL level data, the timed operating system statistics, and the row source execution statistics. The value of other statistic collection parameters is overridden.

SQL>select statistics_name,activation_level from v$statistics_level order by 2;

The following statistic parameters can also be set individually TIMED_STATISTICS: Set to TRUE to collect statistics related to time.

• DB_CACHE_ADVICE: Accepts the following values:
– OFF: No statistics collected and no memory used : No statistics collected and no memory used
– READY: No statistics collected, but memory is allocated. Setting
DB_CACHE_ADVICE to READY before setting it to ON prevents memory errors when collecting statistics on buffer cache utilization.
– ON: Statistics collected and memory allocated. Changing the status of
DB_CACHE_ADVICE from OFF to ON can raise an error if the required memory is not available.

• TIMED OS STATISTICS TIMED_OS_STATISTICS: Specifies the interval (in seconds) at which an Oracle : Specifies the interval (in seconds) at which an Oracle
instance collects operating system statistics when a request is made from the client to the server or when a request completes
When STATISTICS_LEVEL is modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system wide state is not changed.
• Timed statistics
• Timed OS statistics
• Plan execution statistics

Real time performance tuning using Oracle SQL Developer

Posted on March 31, 2018March 31, 2018 by debasis maity

Real time performance tuning using Oracle SQL Developer

SQL developer is free tool can be downloaded from Oracle .I use it frequently as my performance tuning day to day arsenal.It is great tool to analyze both overall system performance as well as particular SQL tuning.

Let me go thorugh how we can use it to leverage it’s utility to dig down performance bottleneck.

1.Let us simulate the same test case which I explained in following link

https://clouddba.co/how-to-investigate-real-time-over-all-performance-bottleneck-enq-tx-row-lock-contention-using-os-tools-like-top-sar-vmstatashawroratop-and-oem/

2.SQL developer with system/power user.Let me navigate to Tools-> Monitor sessions.The session monitor console shows you all real time active and in-active sessions details

3.Now I will filter to only ‘ACTIVE’ sessions IN SQL DEVELOPER.

4.All active sessions are filtered.We can get various information IN SQL DEVELOPER.

.

INST_ID:-Instance id in case of RAC

SID=Session identifier

Username:-Schema name who is executing the query.

Seconds in wait:-Very important column to monitor how long the sessions are waiting.If it is small,means very short time it is executed which is good.Long time sometimes indicate problems.

Machine:-From which terminal the query is being executed.

Status:-Active means the session is actively executing the query.

Module:-From which module the query is being executed.

5.Now select the sessions individually and check Active SQL tab to check full SQL and SQL_ID IN SQL DEVELOPER.

.

6.You can check the execution plan as well from “Explain plan” tab.

7.This tab “Waits” is very important to check real time wait.We can see Event as “Enq:TX – row lock contention” IN SQL DEVELOPER.

8.This section “Server” shows various details like “Logon_time”,SPID details InSQL developer.

9.This tab “Client” informs from which machine and terminal user is connected.

10.”Application” tab shows what is command type and program responsible for the query execution.

11.The “Contention” session shows for what reason and who is blocking the transactions.This is very important section to identify waiting sessions IN SQL DEVELOPER.

12.This “Long Opsq” session in sql developer is also important to monitor and estimate long running sessions.There is a dynamic performance view v$session_longops that is populated for many long running operations in Oracle. The primary criterion for any operation to appear in v$session_longops is to run more than 6 seconds.

For more details on session long ops refer below:-

http://www.gplivna.eu/papers/v$session_longops.htm

How to investigate real time over all performance bottleneck (enq: TX – row lock contention) using OS tools like top/sar/vmstat,ASH,AWR,ORATOP and OEM

Posted on March 30, 2018April 24, 2018 by debasis maity

How to investigate real time over all performance bottleneck (enq: TX – row lock contention) using OS tools like top/sar/vmstat,ASH,AWR,ORATOP and OEM

I will simulate “row lock contention” using following procedure( I will resue my test case mentioned in Test case 4:-Now I will run update in parallel 10 sessions of https://clouddba.co/test-case-when-index-size-can-grow-than-column-size-and-effect-of-rebuild-index/)

CREATE OR REPLACE procedure TEST.manyupdates as
v_m number;
begin
for i in 1..100000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
update t set id=v_m/15*i where rownum<10;
commit;
end loop;
end;
/

CREATE OR REPLACE procedure TEST.manysessions_update as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyupdates;’, sysdate);
END LOOP;
commit;
end;
/

Exec TEST.manysessions_update;

As we are running this procedure parallel with 10 sessions,it will definitely create row lock contention even in very short time.

Now application team will raise concern that system is very slow and you as DBA start to investigate.

A.real time performance tuning using OS tools

Tool1:-top utility

You should check %CPU section to identify top CPU consuming processes

You will notice load average is almost 5.It means at least 5-6 processes are running at a time.You can check in TOP with column S where it shows ‘R’ means running and ‘S’ means sleeping.

Also note 72% idle and 22% CPU busy.It is just a indication that your system is bit busy with CPU load.

%Cpu(s): 22.6 us, 4.6 sy, 0.0 ni, 72.3 id, 0.1 wa, 0.0 hi, 0.5 si, 0.0 st

If you want to investigate which process takes more memory,you need to sort the output on basis of “RES”

Tool 2:-SAR utility with -U option to check CPU and IO bottleneck

sar -u 10 8

%user: This shows the total time that the processor is spending on different process .

%iowait: the name iowait itself suggests that its the time spend by processor waiting for devices(input and output)

%nice: Most of you guys must be knowing that a user can change the priority of a linux process. This table shows the time spend by CPU for process whose nice value has been changed.

%steal: This column shows the amount to time spend by a CPU (which is virtualized), for resources from the physical CPU

%idle: This suggests the idle time spend by the processor.

A low %idle time could point to which processes are using the most CPU . Use the ps or top command to find a CPU-intensive job. A poorly written query requiring a large amount of disk access can also cause a large amount of CPU usage as well.
In the above sar output, the cause for concern is the large values being returned for %user means application is using more cpu.%iowait (waiting for block I/O) is low here so we are not having disk contention.

We need to make below consideration while checking sar report of CPU

Low CPU idle times.
High percentage of time spent waiting on I/O or %iowait> 10.
Bottlenecks with %system> 15, which could indicate that swapping, paging, or backups are causing a bottleneck.
Abnormally high %user, which could be due to applications not being tuned properly or CPU over utilization.This is our case.

Tool 3:vmstat report

vmstat 1 10

r column is runnable processes.So 7 processes are currently running.IDLE percent is overall 70%.

Any non-zero value of “SO” column leads to probable memory leak in SWAP which needs to be analyzed immediately.

For more info about vmstat:-

Linux vmstat Command – Tool to Report Virtual Memory Statistics

Tool 4:To identify disk bottleneck

sar -d 5 2

tps: tps stands for transfer per second, so it shows the transfer per second to that particular device

rd_sec/s: this shows you the total number of sectors on that device which is being read

wr_sec/s: if rd_sec/s is sectors being read per second then obviously wr_sec is sectors being written per second.

await: this shows the total number of time that the processor waited for requests regarding IO

%util on a device that is greater than 50 percent denotes disk bottleneck.In our case it is normal for highly utilized disk.
If await is greater than svctm due to Unbalanced disk I/O load.

Please note disk dev129-160 is most busiest disk.The tps is 100 almost.

Tool 5:-sar -b to report disk usage

tps – Transactions per second (this includes both read and write)
rtps – Read transactions per second
wtps – Write transactions per second
bread/s – Bytes read per second
bwrtn/s – Bytes written per second

The sar –b command reports on the system’s buffer cache activities (not the Oracle buffer cache). It gives you the number of transfers per second between system buffers and block devices.

Tool 6:-sar -q to find processes under queue.We need to look blocked section

Tool 7:-To identify memory usage using sar -r

in the above output most of the columns are self explanatory(and most of the outputs are in KB).

kbmemfree: this shows the amount of free memory

Kbmemused: memory used

%memused: percentage of memory used

kbbuffers: buffer memory used by the kernel.

kbcached: cached memory used by the kernel

all other entries for memory are swap(free,used,percentage etc)

B.real time performance tuning using ORATOP report

./oratop -f -d -i 10 / as sysdba

ASW:-Active session waiting

Event:-Enq:TX – row lock contention is top wait event.AVG_MS is almost 0.It will be big concern if AVG_MS is very large which means your session is being blocked long time.

SQLID/BLOCKER:-You will get which session is blocking and which sessions are blocked.

Other statistics are also will be useful for more investigation.

Please refer for more info on oratop :-

AWR report analysis in depth-part 1

C.real time performance tuning using OEM monitoring:-

Form OEM console you can check how many active sessions are waiting and RED means concurrency wait.You can also find SQL_ID responsible.

D.real time performance tuning using AWR report:-

You may look below section for AWR.The most important top 10 foreground events .42% DB time has been contributed by enqTX – row lock contention.

This section segments by row lock waits show which objects are getting locked.

E.Real time performance tuning using ASH report:-

ASH report is very handy for generating which SQL are responsible for locking.

Please check top sessions with SID and SERIAL for deeper details.Please check %event column to check what are most eventful wait events.

F.Real time performance tuning using SQL Developer Please refer below:-

Real time performance tuning using Oracle SQL Developer

Test case when index size can grow than table size and effect of rebuild index

Posted on March 30, 2018April 16, 2018 by debasis maity

Test case preparation

I am going to create test case to simulate different index test cases

Test case 1:-First we create procedure to parallel insert in 10 sessions.The insert query will insert data based on random values generated from select query.Please note I will run gather stats after every test case.

SQL>create table t(id number,sometext varchar2(50),mydate date);

SQL>CREATE OR REPLACE procedure TEST.manyinserts as
v_m number;
begin
for i in 1..1000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/

SQL>CREATE OR REPLACE procedure TEST.manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

SQL> create index I1_t1 on t(ID) pctfree 0;

SQL> create index I2_ti on t(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,'T');

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='T';

SUM(BYTES)/1024/1024/1024

————————-

.7734375

Index statistics

Col INDEX_NAME format a10;

Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

Size of index and table

select bytes/1024/1024/1024,segment_name from user_segments where segment_name in ('I1_T','I2_T','T');

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.265625 I1_T

.8671875 I2_T

.7890625 T

Test case 2:-Second we create procedure to insert serially.

SQL>create table t1(id number,sometext varchar2(50),mydate date);

SQL>CREATE OR REPLACE procedure TEST.manyinserts1 as

v_m number;

begin

for i in 1..10000000 loop

select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;

insert /*+ new2 */ into t1 values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);

end loop;

commit;

end;

SQL> create index I1_t1 on t1(ID) pctfree 0;

SQL> create index I2_ti on t1(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,’T’);

SQL> Set linesize 300;

INDEX STATISTICS

SQL> Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

INDEX_NAME PCT_INCREASE PCT_FREE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS

———- ———— ———- ———- ———– —————– ———-

I1_T 0 2 20296 9402633 9589796

I2_T 10 2 85153 10138784 10138858

INDEX AND TABLE SIZE

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NAME

——————– ——————————————————————————————————————————–

.6484375 I2_T

.7890625 T

.1640625 I1_T

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=’T’;

SUM(BYTES)/1024/1024/1024

————————-

.7734375

Test case3:-Now I will run some select statement in parallel 10 sessions.I forcefully making index on column ID.THE TABLE I SELECTED WHERE I INSERTED DATA PARALLELY.

CREATE OR REPLACE procedure TEST.manyselect as

v_m number;

begin

for i in 1..100 loop

select count(id) into v_m from t;

end loop;

end;

CREATE OR REPLACE procedure TEST.manysessions_select as

v_jobno number:=0;

begin

FOR i in 1..10 LOOP

dbms_job.submit(v_jobno,’manyselect;’, sysdate);

END LOOP;

commit;

end;

Exec TEST.manysessions_select;

Now let me check SQL statistics and buffer read from AWR

Test case 4:-Now I will run update in parallel 10 sessions

CREATE OR REPLACE procedure TEST.manyupdates as

v_m number;

begin

for i in 1..100000 loop

select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;

update t set id=v_m/15*i where rownum<10;

commit;

end loop;

end;

CREATE OR REPLACE procedure TEST.manysessions_update as

v_jobno number:=0;

begin

FOR i in 1..10 LOOP

dbms_job.submit(v_jobno,’manyupdates;’, sysdate);

END LOOP;

commit;

end;

Exec TEST.manysessions_update;

Table statistics

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.265625 I1_T

.8671875 I2_T

.7890625 T

Index statistics:-

Test case 5:-Now I will run SELECT AGAIN

Exec TEST.manysessions_select;

TEST case 6:-Let me rebuild index on column ID

SQL> alter index I1_t rebuild;

Index altered.

TABLE and INDEX size

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.1875 I1_T

.8671875 I2_T

.7890625 T

Index statistics

Test case 7:-Let me run the select again in parallel

Exec TEST.manysessions_select;

Observations:-

Please note that select query was taking more time (3.07 s) after bulk update which I think was due to temp area usage (see segments by logical read section in awr) .AFter index reuild the query response time (0.30 s)improved.

select query which traverses index I1_T	Elapsed time/exec	Buffer gets
Before bulk update	0.35	35809
After bulk update	3.07	41832
after rebuild index	0.30	23704

Also please note index size I2_T is more in size than table T.Index rebuild reduced the index size though cluster factor remains same.

	TABLE_SIZE IN GB	INDEX SIZE I1_T IN GB	INDEX SIZE I2_T in GB	CLUSTER FACTOR I1_T	CLUSTER FACTOR I2_T
PARALLEL INSERT	.7890625	.265625	.8671875	9676232	10117165
SERIAL INSERT	.7890625	.1640625	.6484375	9402633	10138858
PARALLEL UPDATE ON COLUMN ID	.7890625	.265625	.8671875	10262755	9399026
AFTER REBUILD I1_T	.7890625	.1875	.8671875	9999904	9399026

AWR report analysis 12c in depth-part 2

Posted on March 20, 2018May 29, 2020 by debasis maity

AWR report analysis 12c in depth-part 2

This document is continuation of AWR report analysis in depth step by step which will provide you an idea to understand AWR report more quickly.

Please refer to first part:-

AWR report analysis in 12c depth-part 1

H.SQL Ordered by Elapsed Time in AWR REPORT

For A:-

For B:-

For A:-Total Elapsed Time = CPU Time + Wait Time. If a SQL statement appears in the total elapsed time area of the report this means its CPU time plus any other wait times made it. Excessive Elapsed Time could be due to excessive CPU usage or excessive wait times.

Please note that first 3 rows have highest elapsed time.Hence we will provide our attention to analyze the queries.

First row is clearly indicates the PL/SQL block we ran in first part of AWR analysis report.It’s elapsed time is cumulative sum of insert statements and select statements executed.

PL/SQL block executed 10 times,per execution it took 4,868 seconds which is 58% of total DB time spent (83385 sec total DB TIME).It took 33% CPU time of total CPU time(16250 sec total CPU time).

I.SQL Ordered by CPU time in AWR REPORT

For A:-

For B:-

For A:-When a statement appears in the Total CPU Time area this indicates it used excessive CPU cycles during its processing. Excessive CPU processing time can be caused by sorting, excessive function usage or long parse times. Indicators that you should be looking at this section for SQL tuning candidates include high CPU percentages in the service section for the service associated with this SQL.

In that section,again the 3 queries appeared as most CPU consuming queries.We need to take close look how to reduce CPU time.The first row is PL/SQL block which is cumulative CPU time of the other 2 expensive insert and select query.

J.SQL ordered by user I/O wait time in AWR REPORT

for A:-

For B:-

For A:-In that section,We need to identify which query is making most I/O .The gather_stats job needs to scan the tables hence it is accounted for large IO time.But still I can say,here we need to worry much on IO time as it is very less(123 sec) compared to DB Time(83348 sec). Please check “Segments by table scans” to understand what may have been contributed in your IO time .

K.SQL Ordered by Gets in AWR REPORT

For A:-

For B:-

For A:-Total buffer gets mean a SQL statement is reading a lot of data from the db block buffers. Buffer gets (Logical IO) are OK, except when they become excessive. LIO may have incurred a PIO in order to get the block into the buffer in the first place. Reducing buffer gets is very important and should not be ignored.

To get a block from db block buffers, we have to latch it (i.e. in order to prevent someone from modifying the data structures we are currently reading from the buffer). Although latches are less persistent than locks, a latch is still a serialization device.

Also note that by lowering buffer gets you will require less CPU usage and less latching. Thus to reduce excessive buffer gets, optimize SQL to use appropriate indexes and reduce full table scans.

Please check “Segments by Logical Reads” section of AWR REPORT.

For A:-

For B:-

L.SQL ordered by Reads in AWR REPORT.

For A:-

For B:-

For A:-High total disk reads mean a SQL statement is reading a lot of data from disks rather than being able to access that data from the db block buffers. Excessive disk reads do cause performance issues. The usual norm is to increase the db buffer cache to allow more buffers and reduce ageing . Total disk reads are caused by high physical reads, a low buffer cache hit ratio, with high IO wait times. Higher wait times for Disk IO can be associated with a variety of reasons (busy or over saturated SAN, slower underlying storage, low capacity in HBC and other hardware causes). Statistics on IO section in AWR, plus the Operating System diagnostic tools as simple as iostat can help in identifying these issues. To reduce excessive disk reads, consider partitioning, use indexes and look at optimizing SQL to avoid excessive full table scans.

In our case first query is doing physical reads o 879299 blocks;

Please check “Segments by Physical Reads” in AWR report.

M.SQL ordered by Execution in AWR REPORT.

For A:-

For B:-

High total executions need to be reviewed to see if they are genuine executions or loops in SQL code.Here we see Top 2 queries have most executions typically due to loop in PL/SQL.

In general statements with high numbers of executions usually are being properly reused. However, there is always a chance of unnecessary loop in PL/SQL, Java or C#. Statements with high number of executions, high number of logical and or physical reads are candidates for review to be sure they are not being executed multiple times when a single execution would serve.

N.SQL ordered by Parse Calls IN AWR REPORT.

For A:-

For B:-

Whenever a statement is issued by a user or process, regardless of whether it is in the SQL pool it undergoes a parse. As explained under Parsing, the parse can be a hard parse or a soft parse. Excessive parse calls usually go with excessive executions. If the statement is using what are known as unsafe bind variables then the statement will be reparsed each time. If the header parse ratios are low look here and in the version count areas.

O.SQL ordered by shareable Memory in AWR REPORT.

For A:-

For B:-

For A:-Shareable Memory refers to Shared Pool memory area in SGA , hence this particular section in AWR Report states about the SQL STATEMENT CURSORS which consumed the maximum amount of the Shared Pool for their execution.

We need to be concerned if some query takes large chunk of shareable memory.

P.Key Instance Activity stats in AWR REPORT.

For A:-

For B:-

DB Block changes:-Denotes you have frequent change in DB block due to huge DML.Please check “Segments by physical writes” for more details in AWR.

which supports following statistics:-

1.Execute count :-very high number of executions

2.Physical writes :-Huge physical writes.Please check “Segments by physical writes” for more details in AWR.

3.Redo size:-58 GB total redo generated during snapshot time.Please search “log switches” in AWR to check how many log switches happened during snapshot capture time.We can see 43 per hour is very bad.Ideally it should be around 3-4 log switches per hour.

4.User commits:-Number of user commits very high (Case A) .But low in case B (As commit was done outside loop)

For A:-

For B:-

Q.Tablespace IO stat in AWR REPORT

Functions:-

LGWR generates 113 GB Data total(Redo data).Data/sec is 23 MB.IOPS is 1047 for redo.

DBWR reads 28 MB total and writes 21 GB data.IOPS for read is 1 and IOPS for write is 301.

Direct reads read 7GB data( Full table scan of T table which was direct path read )

In Total:-

8.7GB data in read.

141 GB data in write.

1358 IOPS derived from reqs per second.

For A:-

For B:-

It is evident that DATA and UNDOTBS1 have undergone most reads/write during snapshot period.

If the I/O activity is not properly distributed among your files, you may encounter performance bottlenecks during periods of high activity. As a rule of thumb, you don’t want more than 100 I/Os per second per 10,000 RPM disk (even with a RAID array). If the Av Rd(ms) column (Average Reads per millisecond) is higher than 14 ms (given a fair amount of reading is being done), you may want to investigate, since most disks should provide at least this much performance. If rows in this column show 1,000 ms or more in the Av Rd(ms) column or in the Av Buf Wt(ms) column (Average Buffer Writes per millisecond), you probably have some type of I/O problem, and if it shows ###### (meaning it couldn’t fit the value in the size of the field), then you have a serious I/O problem of some kind (this can also be a formatting problem, but anything greater than 1,000 is a problem when a fair number of reads are being done).

AWR report analysis in 12c depth-part 1

Posted on March 15, 2018May 29, 2020 by debasis maity

1.Let me create a test case : I will take different OS statistics and run time statistics to compare with AWR report generated after execution.

Case A.About test case in lower CPU:-

I will create a table first

create table t (id number, sometext varchar2(50),my_date date) tablespace data;

Now I will create a simple procedure to load bulk data

create or replace procedure manyinserts as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);

commit;
end loop;
end;

This insert will be executed in 10 parallel sessions using dbms_job.This will fictitiously increase load on database.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/

Now I will execute manysessions which will fork 10 parallel sessions

exec manysessions;

Case B.About test case in higer CPU:-

I will create a table first

create table t (id number, sometext varchar2(50),my_date date) tablespace data;

Now I will create a simple procedure to load bulk data.I will make commit out of loop to decrease frequent commit.

–commit;
end loop;

commit;
end;

This insert will be executed in 10 parallel sessions using dbms_job.This will fictitiously increase load on database.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/

Now I will execute manysessions which will fork 10 parallel sessions.

exec manysessions;

I will execute the test case on server with low CPU (A) as well as high CPU (B).

2.I will use oratop tool to monitor the sessions

oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

./oratop -f -d -i 10 / as sysdba

optionally you press x

Then you can put sql_id to see the execution plan as well.

For A.

For B

Section 1 - database
        Global Database information
   Version        : Oracle major version
   role           : database_role
   db name        : db_unique_name
   time        [s]: time as of the most recent stats (hh24:mi:ss)
   up          [T]: database uptime
   ins         [N]: total number of instance(s)
   sn        [c,N]: total user sessions (active/inactive)
   us        [c,N]: number of distinct users
   mt        [s,N]: global database memory total (sga+pga)
   fra         [N]: flashback recovery area %used, (red &gt; 90%)
   er          [N]: diag active problem count (faults)
   % db      [s,N]: database time as %(dbtime/cpu) (red if &gt; 99%)

Section 2 - instance
        Top 5 Instance(s) Activity
        o Ordered by Database time desc
   ID        [c,N]: inst_id (instance id)
   %CPU      [m,N]: host cpu busy %(busy/busy+idle). (red if &gt; 90%)
   LOAD      [m,N]: current os load. (red if &gt; 2*#cpu &amp; high cpu)
   %DCU      [m,N]: db cpu otusef as %host cpu. (red if &gt; 99% &amp; high AAS)
   AAS       [s,N]: Average Active Sessions. (red if &gt; #cpu)
   ASC       [c,N]: active Sessions on CPU
   ASI       [c,N]: active Sessions waiting on user I/O
   ASW       [c,N]: active Sessions Waiting, non-ASI (red if &gt; ASC+ASI)
   ASP       [m,N]: active parallel sessions (F/G)
   AST       [c,N]: Active user Sessions Total (ASC+ASI+ASW)
   UST       [c,N]: user Sessions Total (ACT/INA)
   MBPS      [m,N]: i/o megabytes per second (throughput)
   IOPS      [m,N]: i/o requests per second
   IORL      [m,T]: avg synchronous single-block read latency. (red &gt; 20ms)
   LOGR      [s,N]: logical reads per sec
   PHYR      [s,N]: physical reads per sec)
   PHYW      [s,N]: physical writes per sec
   %FR       [s,N]: shared pool free %
   PGA       [s,N]: total pga allocated
   TEMP      [s,N]: temp space used
   UTPS      [s,N]: user transactions per sec
   UCPS    [c,m,N]: user calls per sec
   SSRT    [c,m,T]: sql service response time (T/call)
   DCTR      [m,N]: database cpu time ratio
   DWTR      [m,N]: database wait time ratio. (red if &gt; 50 &amp; high ASW)
   %DBT      [s,N]: instance %Database Time (e.g. non-rac shows 100%)

Section 3 - db wait events
        Top 5 Timed Events
        o Cluster-wide, non-idle
        o Ordered by wait time desc
  EVENT      : wait event name. (red if active)
        (RT) : Real-Time mode
  WAITS      : total waits
  TIME(s)    : total wait time in seconds)
  AVG_MS     : average wait time in milliseconds
  PCT        : percent of wait time (all events)
  WAIT_CLASS : name of the wait class

Section 4 - process
        o Non-Idle processes
        o Ordered by event wait time desc
   ID          [N]: inst_id. (red if blocking)
   SID         [N]: session identifier. (red if blocking)
   SPID        [N]: server process os id
   USERNAME       : Oracle user name
   PROGRAM        : process program name
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA         [N]: pga_used_mem. (red if continuously growing)
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid, in red)
   OPN            : operation name, e.g. select
   E/T         [T]: session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name.
                    (red if process is hung/spin)
   W/T         [T]: event wait time. (red if &gt; 1s)

3.TOP output during activity

For Case A:-

We had 4 CPU,so our 6 jobs (status :-S means Sleeping) had to wait for CPU.Other 4 jobs are running (Status :-R means Running). Also note 86% CPU used because we are consuming 4 CPU out of 4 CPU)There is almost no idle CPU.

For Case B:-

We had 20 CPU,so our all 10 jobs are running (Status :-R means Running).Also note 44.4 % CPU is used because out of 20 CPU we are using 10 CPU.Hence there is 50% idle CPU.

4.VMSTAT report

For Case A:-

For Case B:-

5.The table size after 10 jobs completed.

SQL> select bytes/1024/1024/1024  from dba_segments where segment_name='T';

BYTES/1024/1024/1024

-------------------

7.375

6.AWR report 360 degree analysis

A.Header Section IN AWR REPORT

For Case A:-

For Case B:-

Same load completed 5 times faster when we have more CPU.

Sessions:=No of active sessions

Cursors/Session:=No of open cursor per session.If this value is increasing or high indicates potential problem of cursor leaking.

Elapsed :=The elapsed time indicates the duration of the report between the 2 selected snapshots. Any other duration figures can be compared back to this. A 30-60 minute reporting period is usually recommended.

DB time:=db time is the time spent in the database.Ideally DB time=CPU time+Non-idle wait time.For example,If we have 10 parallel session each taking 20 minutes to execute,our DB time will be 200 minutes+other query execution time in 20 minutes snapshot period.The DB Time is the time spent in the database for the period of the report. If this is significantly higher than the Elapsed time then this is a good indicator of a heavily loaded system.

B.Load Profile IN AWR REPORT

For Case A:-

For Case B:-

DB Time:-No of active sessions average during snapshot period.This is derived by DB time/Elapsed time in previous section.If the number his high,then probably many active session are there in database on particular point which may indicate bottleneck or opportunity to deep analysis.

DB Cpu:-DB CPU is quite less than DB time ,so sessions are waiting not much working on cpu.This may indicate a problem if your DB cpu percent is quite less than DB time.

Redo size:-11 MB per second.An increase in Redo size and Block changes indicate increased DML(INSERT/UPDATE/DELETE) activity.

Logical reads:-Logical reads is simply the number of blocks read by the database, including physical (i.e. disk) reads, and block changes is fairly self-descriptive.If you find those number higher than expected (based on usual numbers for this database, current application workload etc.), then you can drill down to the “SQL by logical reads” and “SQL by physical reads” to see if you can identify specific SQL responsible.

Block Changes:-This indicates high block change means many transactions going on.

User Calls:-In particular, when the database is executing many times per a user call, this could be an indication of excessive context switching (e.g. a PL/SQL function in a SQL statement called too often because of a bad plan). In such cases looking into “SQL ordered by executions” will be the logical next step.

Hard parse:-A hard parse occurs when a SQL statement is executed and is not currently in the shared pool.A hard parse rate greater than 100 per second could indicate that bind variables are not being used effectively; the CURSOR_SHARING initialization parameter should be used; or you have a shared pool–sizing problem.

Parse:-This is soft parse+hard parse.A soft parse occurs when a SQL statement is executed and it is currently in the shared pool. A very high soft parse rate could lead to problem of programmed application.

Logons:-Establishing a new database connection is also expensive (and even more expensive in case of audit or triggers). “Logon storms” are known to create very serious problem.In fact log off is more expensive.This indicates problem of not using proper connection pooling.

Executes:-High no of executes always indicates potential load to database.

Transactions:-High number of transactions indicates it is OLTP system.

C.Time Model Statistics IN AWR REPORT

for Case A:

For Case B:

For Case A:-

sql execute elapsed time is 60%.That means DB spent 60% time of DB_TIME is executing SQL query which is OK for now .
DB CPU is used 20% of DB_TIME.
Now We can see PL/SQL execution elapsed time is 11% means many PL/SQL procedure is being executed.
You can see which PL/SQL executed in SQL ordered by Elapsed Time.
Parse time elapsed and hard parse elapsed time is less which is good sign.If these are bigger,we need to check why query is taking more time to parse.

D.Instance Efficiency Percentages IN AWR REPORT

For Case A:-

For Case B:-

Buffer Nowait %:-Less than 99 percent may indicate a problem . This value is the ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then there are (hot) blocks being contented for that should be found in the Buffer Wait section.

Buffer Hit %:- Less than 95 percent may indicate a problem. This value is the ratio of hits on a request for a specific buffer when the buffer was in memory and no physical I/O was needed.A hit ratio that is steadily at 95 percent and then drops to 45 percent should be checked for bad SQL or a dropped index (check the top physical reads SQL) causing a surge in physical reads that are not using an index or an index that has been dropped

Library Hit % :-Less than 95 percent indicates problem. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small). A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case, a smaller shared pool may only be a bandage that potentially fixes a resulting library latch problem)

In-memory Sort % :- less than 95 percent in OLTP indicates problem. In an OLTP system, you really don’t want to do disk sorts. Setting the MEMORY_TARGET or PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE in previous versions) initialization parameter effectively eliminates this Problem.

Soft Parse %:- Less than 95 percent indicates problem. A soft parse ratio that is less than 80 percent indicates that SQL is not being reused and needs to be investigated.

Latch Hit %:- Less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue.

E.Foreground Events by total wait time IN AWR REPORT

For Case A:-

For Case B:-

This is one of the most important section.You must be concerned if any wait event take abnormally high %DB time apart for DB CPU.If DB CPU is high like 80%,then your application is CPU bound.

For case A,

DB CPU :-It is showing 19.5% means it is may be CPU bound but because we have only 4 CPU and 10 parallel sessions,CPU is not able to do much work and waiting for IO to be completed.Please check OS statistics for reviewing CPU related bottlenecks (% cpu utilization)

Resmgr:cpu quantum:-Indicates it may be problem of allocating sufficient number of CPU cycles.

F.Background wait events IN AWR REPORT

For Case A:-

For Case B:-

For case A:-

The log file parallel write shows LGWR is waiting for blocks to be written to all online redo log members in one group. LGWR will wait until all blocks have been written to all members. So here we had 80% of background total time spent on log file parallel write. The parameter of interest here is Avg wait (ms). In our case it is 1ms which is a perfectly respectable figure. Obviously larger average wait times point to slower I/O subsystem or poor I/O configurations.As a rule of thumb, an average time for ‘log file parallel write’ over 20 milliseconds suggests a problem with IO subsystem.
Another thing is log file parallel write is background wait event of log file sync.You need to check if large commit is happening in “user commits” section of “Instance Activity Stats”

G.Host CPU IN AWR REPORT

For Case A:-

For Case B:-

For Case A,

Host CPU

Here you can notice %user is 84%.This really gives you sign that your CPU are highly used.Load average 2.77 (after execution completed) means you are having average 3 sessions running at one time and other 7 sessions are waiting for CPU.But most of the time load average was around 10 (refer oratop and vmstat) as there was 10 sessions running all the time during snapshot period.

Instance CPU

%Total cpu indicates that Database is using 82.6 % cpu of total CPU of the database server. This indicates there are not many other database’s process running currently.If you see less value here it may indicates the database server total cpu may be used by other application or database instances.

H.IO Profile

For case A:-

For case B:-

Here Total requests:=Database requests + Redo requests=1046+322=1381(around) which is actually IOPS.

Total MB=Database (MB) + Redo (MB)=5.9+22.9=30.4 MB which denotes database is generating 30 MB data per second.

I.Memory Statistics

For case A:-

For case B:-

Host Mem(MB):-Total memory in the system

SGA use (MB):-Total SGA used

PGA use(MB):-Total PGA used.

Cache size mainly distributed Buffer cache(where data buffer stored in memory)+Shared pool size.

A low value for the % SQL with exec>1 statistic indicates databasee is not re-using shared SQL statements, usually because the SQL is not using bind variables.

J.OS Statistics

For A:-

For B:-

This is like vmstat report.Almost 83% cpu was used during execution which is quite high.%busy high is good means cpu are not using simultaneous multi threading.

The tool was devised by Craig from ORAPUB which is excellent to monitor what happens in session real time.

1.First I need to down OSM tool kit from ORAPUB. First we need to run the following scripts to create some pre-requisite tables under sys/system schema.

b)Then I will run another script

2.Now the following script can be saved under /home/oracle/rss.sql

3.Now execute the script to take sames of either individual user or many users

For deep understanding on CBC,please refer to below links:-

I am just going to mention some useful queries to investigate or identify CBC latch

Step 1:-You as user fire a sql query from client.

Step 2:-Now oracle will search metadata and determine what is your file_id and block_id to locate the block to be fetched from buffer cache or physical disk.

Step 3:-Let me for example choose file_id = 6 and block_id=2288143.Now oracle will determine DBA value from it.

Step 4:-If there are 3 chains,oracle will use module function that returns the reminder from an input after dividing it by 3:

So this block will be put into chain #1.

Simulation of CBC test:-

From one session,run following:-

From another session ,run following:-

From oratop,I can see “Latch: cache buffers chains” now.

Mechanism:-

1.Oracle once determine file_id and block_id,create a hash value first.

2.After determining hash value,oracle will acquire latch to hash into hash bucket and move across to chain to ask block header whether the block is already in memory or it needs to be fetched from disk.

Why CBC cache buffer chain occurs

Problem:-

a)When different session try to acquire latch (Your buffer is very popular) chain #1.It needs to spin and sleep several times.

b)When different session try to acquire latch chain #1 and chain #2.It needs to spin and sleep several times.

Remember,latch is exclusive and if one session is trying to acquire latch,other session will be in queue.

No problem:-

When different session try to acquire latch chain #1 and chain #4.There will be no contention as they are in different latch .

Now how we can identify CBC problem

Step 5:-Let us find the latch number of “cache buffers chains”

If you check the values of the two hidden parameters explained earlier, you will see: _db_block_hash_buckets 524288 _db_block_hash_latches 16384

Step 7:-Now check longest sleep and gets by executing following query 1 minutes gap and check whether sleep and gets are increasing.

Step 8:-You can also identify the buffer causing cache buffer chain wait for session you are experiencing CBC problem.Let us say session# 208 is experiencing CBC contention.

Here please take a note of p1raw which is hash address.

Step 8:-Please verify gets,misses and sleeps from v$latch_children

Step 9:-Also determine touch count of the corresponding block .

Identify overall problem on CBC

Step 10:-You can also check from v$session_wait or v$active_session_history

Step 11:-The easiest way is to dump the block and get the object ID from the dump file. Here is how you dump the above mentioned block.

or we can get block_id of corresponding address.

Step 12:-Now we can identify the segment_name which may be table or index using below query.

Solving problem:-

b)You can minimize less no of rows per block.The less the number of rows in a block, the less popular the block will be. You can reduce the number of rows in a block by increasing PCTFREE or using ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK.

c) If that does not help, you can partition a table. That forces the data block address to be recomputed for each partition, making it more likely that the buffers will end up in different buffer chains and hence the competition for the same chain will be less.

Performance monitoring or tuning tool for SQL query taking long time using various tools

In that post,I will explain using various method or tool to investigate performance problem of particular sql query.

Let me create a test case first

1.Run time analysis

1.1. Analysis by different options of explain plan and execution plan.

Here I used GATHER_PLAN_STATISTICS hint to capture E-rows (Estimated rows) and A-rows (Actual Rows).

1.1.1 First I will test with id between 1 and 1000 .We will note the execution plan.

Please note that if adaptive plan is being used, then it means SQL plan is being changed during execution because of cardinality mismatch on the fly.

Now we can use below method to identify what steps have been removed during run time execution by statistics collector.The steps marked in RED are deleted during on the fly execution plan generation.

1.1.2 Now I am gathering statistics.After that I will run same statement.You may notice difference in cost calculation.

We can check A-rows and E-rows which can show indication in cardinality mismatches.

1.1.3 Now let me run the same query again.We will check different advanced options shown from explain plan.

1.2 You can view run time performance of a query by writing SQL query which takes data from v$sql

Please note disk_reads,buffer_gets.Sudden rise of these values may indicate problem.It also evident that query has 2 child cursors with different optimizer_cost.Users_executing shows 1 when the query is being executed.The rows_processed column shows how much rows the query has already fetched.

1.3 Checking run time performance using SQL monitor.

After the execution finises,it will show as below:-

2.historical analysis

2.1 Analysis using AWR history to check plan flip

Now there is another option to check what plans were used during execution of particular SQL_ID from AWR. This is good option for historical analysis OF PLAN FLIP.

2.2 Analysis using SQLHC report (Light wight version of SQLT report)

For more details:-

sqlhc_20180403_233101_fwhp6ukwmb1fj\sqlhc_20180403_233101_fwhp6ukwmb1fj_3_execution_plans.html

This html is important to understand possible plan flip

A)

B)

C:\Project\sql_tuning\sqlhc_20180403_233101_fwhp6ukwmb1fj\sqlhc_20180403_233101_fwhp6ukwmb1fj_2_diagnostics.html

This html is very important to compare different metrics to pin point the issue.

2.3 Analysis using awrsqlrpt report

How to generate AWR SQL report:-

The following section is showing possible plan change

The below sections shows plan change and effects on execution details:-

1.General Information

Memory versions of statistics regularly transferred to disk by MMON.

AWR stores base statistics:-counters and value statistics.

ASH data is firstly captured first to memory in 1 second interval for active sessions only.ASH data is reduced by 10 times sample in memory data.ASH data is used by ADDM.

The advisor report produced by ADDM reports are used by segment advisior and other advisiors.

AWR stores in SYSAUX.

By default 60 min interval.

If you check the values of the two hidden parameters explained earlier, you will see:
_db_block_hash_buckets 524288
_db_block_hash_latches 16384