Aug-13

29-Aug

You can ge the BIND values for an SQL execution from V$SQL_MONITOR


select xmltype(binds_xml) from v$sql_monitor where sid =<n> and status = 'EXECUTING';

http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql-monitoring-feature-v-sql_monitor
27-Aug

Buffer Cache working sets

Database data cache divided into multiple lists known as ‘working sets’ and can be viewed in x$kcbwds.  The number of lists will be always multiples of 8 and can be viewed as


SQL>  select SET_ID,DBWR_NUM,CNUM_SET,POOL_ID from x$kcbwds

SET_ID   DBWR_NUM   CNUM_SET    POOL_ID

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

1          0          0          1

2          0          0          1

3          0          0          2

4          0          0          2

5          0      67014          3

6          0      67014          3

7          0          0          4

8          0          0          4

9          0          0          5

10          0          0          5

11          0          0          6

12          0          0          6

13          0          0          7

14          0          0          7

15          0          0          8

16          0          0          8

16 rows selected.

Where

SET_ID      => Working set ID

DBWR_NUM    => DBWR ID associated with the working set

CNUM_SET    => Number of buffers associated with the set

POOL_ID     => Buffer pool ID the set belongs to.

Even though there are 16 sets, the number of active sets are 2 and all others are empty or not active.  I have seen, the working set is directly proportional to the number of DB_WRITER_PROCESSES, but not confirmed.

You can join x$bh.set_ds = x$kcbwds.addr to associate the buffer cache blocks to the working set.


SQL> select b.SET_ID,DBWR_NUM,count(*)

from x$bh a ,x$kcbwds b

where a.set_ds=b.addr

group by b.SET_ID,DBWR_NUM ;

2    3    4

SET_ID   DBWR_NUM   COUNT(*)

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

5          0      22365

6          0      22269

You can join x$kcbwds.pool_id = v$buffer_pool.id


SQL> select name, b.SET_ID,DBWR_NUM

from v$buffer_pool a ,x$kcbwds b

where b.pool_id=a.id;  2    3

NAME                     SET_ID   DBWR_NUM

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

DEFAULT                       5          0

DEFAULT                       6          0

You can view high set number, low set number and set count belongs to a specific cache


SQL> select NAME,LO_SETID,HI_SETID,SET_COUNT from  v$buffer_pool;

NAME                   LO_SETID   HI_SETID  SET_COUNT

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

DEFAULT                       5          6          2

The column COLD_HD in x$kcbwds represents the mid-point location of the working set where the new blocks are inserted using mid-point algorithm.

26-Aug

Bits and pices from JL.

http://jonathanlewis.wordpress.com/bits-and-pieces/

Rolling cursor invalidation

http://prutser.wordpress.com/2009/07/16/rolling-cursor-invalidation/

adrci notes

http://uhesse.com/2011/06/01/adrci-a-survival-guide-for-the-dba/

CBC lacthes

http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/

https://blogs.oracle.com/glennf/entry/decoding_latch_cache_buffers_chains

x$ tables

http://www.adp-gmbh.ch/ora/misc/x.html

Finding VALID parameter options

col PVALID_VALUE for a25
set linesize 200
SELECT
-- INST_ID,
 PARNO_KSPVLD_VALUES pvalid_par#,
 NAME_KSPVLD_VALUES pvalid_name,
 ORDINAL_KSPVLD_VALUES ORD,
 VALUE_KSPVLD_VALUES pvalid_value,
 DECODE(ISDEFAULT_KSPVLD_VALUES, 'FALSE', '', 'DEFAULT' ) pvalid_default
FROM
 X$KSPVLD_VALUES
WHERE
 LOWER(NAME_KSPVLD_VALUES) LIKE LOWER('%&1%')
ORDER BY
 pvalid_par#,
 pvalid_default,
 ord,
 pvalid_Value

22-Aug

In linux, starce -p <pid> produces follwoing trace.  io_submit is the function call used to request for asychornus I/O and io_getevents is checking the response or completion queue for an asychornus I/O.  While pwrite is DIRECTIO.


pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\330\317\0\0\0\0\0\0\0\3 \vu\332\361a"..., 16384, 16384) = 16384
pread(256, "\25\302\0\0\20\0\0\0z\24\0\0\377\377\1\4z\356\0\0\200\3\0\0\0\0\0\0\0\0\0C"..., 16384, 262144) = 16384
pread(256, "\25\302\0\0\22\0\0\0z\24\0\0\377\377\1\4#\267\0\0\0\0\0\0\0\0\0\0u\310\3370"..., 16384, 294912) = 16384
pread(256, "\25\302\0\0\24\0\0\0\v\21\0\0\377\377\1\4G0\0\0\17\0\0\0Q\371\r\0\0\0$\n"..., 16384, 327680) = 16384
io_submit(140064184463360, 2, {{0x7f633a65c210, 0, 1, 0, 256}, {0x7f633a65c460, 0, 1, 0, 257}}) = 2
io_getevents(140064184463360, 2, 128, {{0x7f633a65c210, 0x7f633a65c210, 16384, 0}, {0x7f633a65c460, 0x7f633a65c460, 16384, 0}}, {600, 0}) = 2
pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\330\317\0\0\0\0\0\0\0\3 \vu\332\361a"..., 16384, 16384) = 16384
pread(256, "\25\302\0\0\20\0\0\0z\24\0\0\377\377\1\4z\356\0\0\200\3\0\0\0\0\0\0\0\0\0C"..., 16384, 262144) = 16384
pread(256, "\25\302\0\0\22\0\0\0z\24\0\0\377\377\1\4#\267\0\0\0\0\0\0\0\0\0\0u\310\3370"..., 16384, 294912) = 16384
pread(256, "\25\302\0\0\31\1\0\0z\24\0\0\377\377\1\4\322\355\0\0\5\0\7\0\335\7\0\0\0\0\0\0"..., 16384, 4603904) = 16384
times({tms_utime=3958, tms_stime=12676, tms_cutime=0, tms_cstime=0}) = 514031299

Good stuff on buffer cache.

http://blog.yavor.info/presentations/Yavor_Ivanov_-_Inside_the_buffer_cache.pdf

Some I/O discussion and corresponding waits

http://fritshoogland.wordpress.com/2013/07/14/extra-huge-database-ios-part-3/

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: