Resmgr:Cpu Quantum | Oracle wait event

 

SCOPE

Application job are running slow and DBA finds out the wait event as "Resmgr:Cpu Quantum" for multiple sessions running in database.

Is it possible to view database performance information without DBA privilege in Oracle 11g?

 

The DBA can grant you SELECT privileges to the V$ performance views. It's good for instance tuning, but not as effective as SQL Trace or ADVISOR privilege for query tuning.

Oracle : Move indexes to another tablespace

 

Wait Events : checkpoint busy waits or archiver busy waits

While such wait events occur  in AlertLog file, one must consider to proceed with ARCHIVER TUNING

1. Check the number of Online Redolog Members and size of the online redo logs. 

Excessive size and the number of online redo log groups will give archiver more time to catch up. Hence Adding more online logs does not help a situation where the archiver cannot keep up with LGWR process.

It can help if there are bursts of redo generation since it gives ARCH more time to average its processing rate over time.

2. In such cases you can add multiple archiver (ARCh) processes

Create 'alter system archive log all'. This will spawn archive processes at some fixed interval may be required. These processes once spawned will assist archiver in archiving any un-archived log in that thread of redo. Once it has been completed, the temporary processes will go away.

3. Evaluate checkpoint interval and frequency

There are several possible actions include adding DBWR processes,  increasing db_block_checkpoint_batch, reducing db_block_buffers. Turning on or allowing async IO capabilities definitely helps alleviate most DBWR inefficiencies.

4. Check OS supportability of asynchronous I/Os

Async reads should help tremendously. Async writes may help if OS supports asynchronous I/Os on file systems.

You can check with your vendor if the current version of your operating system supports async IO to file systems (ufs).

5. Check for system or IO contention.

Check CPU waits and usage, disk  level bottlenecks. Also check operating system manuals for the appropriate commands to monitor system performance.

For example, you can use UNIX  commands such as "sar  5 5 5"  “sar –d ”or "iostat" to identify disk bottlenecks.

Find sessions performing sort operations : TEMP Tablespace occupants

Most of the time, user will ask you to check long running sessions in database and you will figure out the query which the session is performing sort/join operation – reading the query you can say the query is related to sorting. But  you will also see the high usage of Temporary segments or will see wait events such as “Direct Path Temp Read” Or “Direct Path Temp Write”.

To rectify this you can use views such as  V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort operation

Oracle provides two basic dynamic performance views for online monitoring of temporary tablespace usage.

These are v$sort_usage to query online sessions using temporary segments and v$sort_segment to query the size of the temporary segments in the database.

V$SORT_USAGE has below main columns  which can be useful here:

USERNAME       –              Session User name.

SQL_ID                –              unique identifier of the SQL that requires the sort.

EXTENTS           –              number of extents in the temporary segment being used by particular sessions.

BLOCKS              –              number of blocks in the temporary segment being used by particular sessions.

SESSION_ADDR – address of the session, can be used to identify the session in V$SESSION according to the SADDR column.

--Use below query to find the sessions consuming temp segments

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
 

This will give the sid and serial# which be used to find current query that is taking temp segment using view v$sqltext and passing sql_id of the sid and serial#

using  V$SORT_SEGMENT

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

How to generate ADDM task and generate its report for Tuning Purpose

In this post, I will explain How to create ADDM task  and check its report

We are using begin snapshot as 500 And end snapshots as 550

BEGIN

--------At First, we need to Create an ADDM task using the below procedure---------.

DBMS_ADVISOR.create_task (
advisor_name      => ‘ADDM’,
task_name         => ‘500_550_AWR_SNAPSHOT’,
task_desc         => ‘ADDM for snapshots 500 to 550.’);

--------— Now, Set the start and end snapshots as parameters to display. ------------.

DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘START_SNAPSHOT’,
value     => 500);
DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘END_SNAPSHOT’,
value     => 550);

 

—------ Now, Execute the task to display the report as parsing the task name. --------.

DBMS_ADVISOR.execute_task(task_name => ‘500_550_AWR_SNAPSHOT’,);
END;
/

 

—-------Finally, execute the following proc to display the report on console. -----------.

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report(‘500_550_AWR_SNAPSHOT’) AS report
FROM   dual;
SET PAGESIZE 24

ADDM Related Views for DBA

DBA_ADVISOR_TASKS – Basic information about existing tasks.
DBA_ADVISOR_LOG – Status information about existing tasks.
DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.

See Also :   Performace Tuning Scripts

20 ASM Real-time Interview Questions

 

Database Performance Tuning Scripts -Keep Handy

****************************************************************************

Database Performance Tuning Scripts

****************************************************************************

---------------------------------Measure the Buffer Cache Hit Ratio------------------------------------

Increase DB_BLOCK_BUFFER if cache hit ratio < 90% -------------------------------------------------------------------------------select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",

round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"

from v$sysstat cur, v$sysstat con, v$sysstat phy

where cur.name = 'db block gets' and

con.name = 'consistent gets' and

phy.name = 'physical reads'

/

select (d.value+c.value-p.value)/(d.value+c.value)*100 "hit ratio"

from v$sysstat d,v$sysstat c,v$sysstat p

where d.name='db block gets' and

c.name='consistent gets' and

p.name ='physical reads'

/

col name format a20 heading "Buffer Pool Name

select name,free_buffer_wait,write_complete_wait,

buffer_busy_wait,db_block_gets,

consistent_gets,physical_reads,

physical_writes

from v$buffer_pool_statistics;

----------------Sessions with bad Buffer Cache Hit Ratio in %---------------

 select substr(a.sid,1,5) "Sid",

substr(a.username,1,10) "User",

substr(a.osuser,1,23) USER_NM,

--substr(a.machine,1,23) MACHINE_NM,

substr(a.program,1,17) program,

b.consistent_gets "ConsGets",

b.block_gets "BlockGets",

b.physical_reads "PhysReads",

100 * round((b.consistent_gets + b.block_gets - b.physical_reads) /

(b.consistent_gets + b.block_gets),3) HitRatio

from v$session a, v$sess_io b

where a.sid = b.sid

and (b.consistent_gets + b.block_gets) > 0

and a.username is not null

and a.username not in ('SYS','SYSTEM')

order by HitRatio asc

/

-------------response time per transaction---------------------------

select ((WAIT + val) / TRXN_CNT) as TRXN_SPEED

from (select sum(TOTAL_WAITS) as WAIT from v$system_event) ,

(select value as VAL from v$sysstat where name='CPU used by this session'),

(select sum(value) as TRXN_CNT from v$sysstat where name in ('user rollbacks','user commits'))

/

------------sql text executing morethan 25 times(SQL Hard Parsing )--------------

 select substr(sql_text,1,50), count(*) from v$sql

group by substr(sql_text,1,50) having count(*) > 25

/

**---------------------Latch Contention----------------**

col name format a40 heading "Latch Name"

SELECT name, gets, misses,

round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,

sleeps,

round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses"

from v$latch

where gets != 0

order by name;

select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses) Hit_Ratio

from v$latch where immediate_gets != 0;

**---------------Shared Pool Statistics-------------------------------**

col namespace format a22

select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2)

pinhitratio,reloads,invalidations from v$librarycache;

----------------------------------------------------------------------Display database SGA statistics

----------------------------------------------------------------------DECLARE

libcac number(10,2);

rowcac number(10,2);

bufcac number(10,2);

redlog number(10,2);

spsize number;

blkbuf number;

logbuf number;

BEGIN

select value into redlog from v$sysstat

where name = 'redo log space requests';

select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;

select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;

select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac

from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph

 where cur.statistic# = ncu.statistic#

and ncu.name = 'db block gets'

and con.statistic# = nco.statistic#

and nco.name = 'consistent gets'

and phys.statistic# = nph.statistic#

and nph.name = 'physical reads';

select value into spsize from v$parameter where name = 'shared_pool_size';

select value into blkbuf from v$parameter where name = 'db_block_buffers';

select value into logbuf from v$parameter where name = 'log_buffer';

dbms_output.put_line('>

SGA CACHE STATISTICS');

dbms_output.put_line('>

********************');

dbms_output.put_line('>

SQL Cache Hit rate = '||libcac);

dbms_output.put_line('>

Dict Cache Hit rate = '||rowcac);

dbms_output.put_line('>

Buffer Cache Hit rate = '||bufcac);

dbms_output.put_line('>

Redo Log space requests = '||redlog);

dbms_output.put_line('> ');

dbms_output.put_line('>

INIT.ORA SETTING');

dbms_output.put_line('>

****************');

dbms_output.put_line('>

Shared Pool Size = '||spsize||' Bytes');

dbms_output.put_line('>

DB Block Buffer = '||blkbuf||' Blocks');

dbms_output.put_line('>

Log Buffer = '||logbuf||' Bytes');

dbms_output.put_line('> ');

if

libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');

END IF;

if

rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');

END IF;

if

bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');

END IF;

if

redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');

END IF;

END;

/

---------------List all supported INIT.ORA parameters----------------------------

 select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault,

decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file',

a.ksppity) type,

a.ksppdesc description

from sys.x$ksppi a, sys.x$ksppcv b

where a.indx = b.indx

and a.ksppinm not like '\_%' escape '\'

order by name

/

--------------List all un-supported INIT.ORA parameters-------------------------

 select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault,

decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file',

a.ksppity) type,

a.ksppdesc description

from sys.x$ksppi a, sys.x$ksppcv b

where a.indx = b.indx

and a.ksppinm like '\_%' escape '\'

order by name

/

---------------active (in progress) transactionsROLLBACK SEG-----------------

 col name format a8

col username format a8

col osuser format a8

col start_time format a17

col status format a12

tti 'Active transactions'

select username, terminal, osuser,

t.start_time, r.name, t.used_ublk "ROLLB BLKS",

decode(t.space, 'YES', 'SPACE TX',

decode(t.recursive, 'YES', 'RECURSIVE TX',

decode(t.noundo, 'YES', 'NO UNDO TX', t.status)

)) status

from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s

where t.xidusn = r.usn

and t.ses_addr = s.saddr

/

-------------------- Display rollback segment statistics---------------------------

column "Rollback Segment"

format a16

column "Size (Kb)"

format 9,999,999

column "Gets"

format 999,999,990

column "Waits"

format 9,999,990

column "% Waits"

format 90.00

column "# Shrinks"

format 999,990

column "# Extends"

format 999,990

Prompt

Prompt Rollback Segment Statistics...

Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",

rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",

rs.Shrinks "# Shrinks", rs.Extends "# Extends"

from sys.v_$RollName rn, sys.v_$RollStat rs

where rn.usn = rs.usn

/

show sga

show parameters area_size

set pages 999;

column pga_size format 999,999,999

select

2048576+a.value+b.value pga_size

from v$parameter a,v$parameter b

where a.name = 'sort_area_size'

and b.name = 'hash_area_size';

column pga_size format 999,999,999

accept hwm number prompt 'Enter the high-water mark of connected users: '

select

150*(2048576+a.value+b.value) pga_size

from v$parameter a, v$parameter b

where a.name = 'sort_area_size'

and b.name = 'hash_area_size';

****************************************************************************************************

================================

 Database Health monitoring script.

================================

REM Segments Max extents & Current extent comparison

set line 180

set pagesize 10000

set feedback off

prompt

col Time format a50 heading "System Time"

select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;

prompt

prompt

prompt

Rem**-------------Objects Reaching Max extents-----------**

col segment_name format a40 heading "Object Name"

col max_extents format 9999999999 heading "Max Extents"

col curr_extents format 99999999999 heading "Curent Extents"

select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select

segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name

and (a.max_extents - b.curr_extents) <= 10;

Rem**--------------User Session Information-----------------**

col sid format 9999 heading "SID"

col serial# format 9999999 heading "SERIAL NO"

col logon_time format 9999999 heading "Login Time"

col username format a12

col osuser format a24

col program format a38

select s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS') "Log on

Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait in Days" from v$session s,v$process p where s.paddr = p.addr and

s.username is not null order by 8 desc;

Rem**----------------------File Information-------------------------**

col file_name format a55 heading "File Name"

col SizeInMB format 99999999 heading "Total Size (MB)"

col MAXSIZE format 99999999 heading "Maximum Size (MB)"

compute sum of SizeInMB on report

select

substr(file_name,1,60) FIL_NAME,

BYTES/(1024*1024) SizeInMB,

AUTOEXTENSIBLE,

MAXBYTES/(1024*1024) MAXSIZE

from dba_data_files

union all

select

substr(file_name,1,60) FIL_NAME,

BYTES/(1024*1024) SizeInMB,

AUTOEXTENSIBLE,

MAXBYTES/(1024*1024) MAXSIZE

from dba_temp_files;

Rem**-----------------Tablespace Information--------------------------*

col tablespace_name format a25 heading "Tablespace Name"

col logging format a10

col status format a12

col extent_management format a30 heading "Local/Dict"

select tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO') Logging,extent_management from dba_tablespaces;

SELECT Total.name "Tablespace Name",

Free_space, (total_space-Free_space) Used_space, total_space

FROM

(select tablespace_name, sum(bytes/1024/1024) Free_Space

from sys.dba_free_space

group by tablespace_name

) Free,

(select b.name, sum(bytes/1024/1024) TOTAL_SPACE

from sys.v_$datafile a, sys.v_$tablespace B

where a.ts# = b.ts#

group by b.name

) Total

WHERE Free.Tablespace_name = Total.name;

Rem**-----------Tablespace Fragmentation Status---------------------**

col TABLESPACE_NAME format a25 heading "Tablespace Name"

select

substr(TABLESPACE_NAME,1,12) Name,

TOTAL_EXTENTS "Total Extents",

EXTENTS_COALESCED,

decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES') "Frag"

from

dba_free_space_coalesced;

Rem**---------------Top 20 Events and System Statistics-------------------------------**

col event format a40 heading "Event Name"

col Stat format a40 heading "Stat Name"

select * from ( select name "Stat",value from v$sysstat order by value desc ) where rownum <= 20 ;

select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=20;

Rem**---------------Buffer Cache statistics-------------------------------**

select (1-(a.value/(b.value+c.value)))*100 "Buffer Cache Hit ratio"

from v$sysstat a,v$sysstat b,v$sysstat c

where a.name= 'physical reads'

and b.name = 'consistent gets'

and c.name = 'db block gets';

   
Rem**---------------File I/O statistics-------------------------------**

col file# format 99 heading "File No"

select file#,PHYRDS "Physical Reads",

PHYWRTS "Physical Writes",

READTIM "Read Time",

WRITETIM "Write Time",

AVGIOTIM "Avg Time" from v$filestat;

select f.file#,d.file_name,f.PHYRDS "Physical Reads",

f.PHYWRTS "Physical Writes",

f.READTIM "Read Time",

f.WRITETIM "Write Time",

f.AVGIOTIM "Avg Time"

from v$filestat f,dba_data_files d

where d.file_id=f.file#

/

--------------------------------------getting sql statement -------------------------------------

 select sql_text

from v$sql

where address in ( select address

from v$open_cursor

where sid = (select sid

from v$mystat

where rownum=1) )

and upper(sql_text) like '%

------------------------------------idle connections-------------------------------------------------

select s.sid||','||s.serial# session,

s.USERNAME,

s.last_call_et seconds_since_active,

s.status,

s.sql_address,

s.program

from v$session s

where s.sid = nvl(to_number('&sid'),s.sid);