Snapshot copy PDB – new stats

There are 5 new stats related to snapshot clone databases (PDB).

select s.statistic#, name from v$statname s where name like ‘% snap %’;

108 physical read snap IO requests base
109 physical read snap IO requests copy
110 physical read snap IO requests no data
111 physical read snap bytes base
112 physical read snap bytes copy
113 physical write snap IO requests new allocations

let’s start investigating when and why that stats can be updated

Using method described here :
A new method of understanding I set up my debug session as

Remark: 0x000000006AFD7830 is my session address.

command	description
(gdb)break pread64 command p $rdi p $rdx end	break point on OS level physical IO
(gdb) watch *(*(0x000000006AFD7830+0x820) +8*108) Hardware watchpoint 2: *(*(0x000000006AFD7830+0x820) +8*108)	hardware level breakpoint on change of stats 108 physical read snap IO requests base
(gdb) watch *(*(0x000000006AFD7830+0x820) +8*109) Hardware watchpoint 3: *(*(0x000000006AFD7830+0x820) +8*109)	hardware level breakpoint on change of stats 109 physical read snap IO requests copy
(gdb) watch *(*(0x000000006AFD7830+0x820) +8*110) Hardware watchpoint 3: *(*(0x000000006AFD7830+0x820) +8*110)	hardware level breakpoint on change of stats 110 physical read snap IO requests no data

Breakpoint 1, 0x00007f256076bf40 in pread64 () from /lib64/libpthread.so.0
$61 = 259  -- ../cln1/users01.dbf
$62 = 8192
(gdb) c
Continuing.
Hardware watchpoint 3: *(*(0x000000006AFD7830+0x820) +8*109)
-- 109  physical read snap IO requests copy
Old value = 47
New value = 48
0x000000000d661ed1 in ksfdss_update_stats ()
(gdb) c
Continuing.

Case 1 (above): we have all data in cloned datafile

Breakpoint 1, 0x00007f256076bf40 in pread64 () from /lib64/libpthread.so.0
$63 = 259 -- ../cln1/users01.dbf
$64 = 57344
(gdb) c
Continuing.

Breakpoint 1, 0x00007f256076bf40 in pread64 () from /lib64/libpthread.so.0
$65 = 260  -- ../orcl/users01.dbf
$66 = 57344
(gdb) c
Continuing.
Hardware watchpoint 2: *(*(0x000000006AFD7830+0x820) +8*108)
-- 108  physical read snap IO requests base
Old value = 263
New value = 264
0x000000000d661f5c in ksfdss_update_stats ()
(gdb) c
Continuing.
Hardware watchpoint 4: *(*(0x000000006AFD7830+0x820) +8*110)
-- 110  physical read snap IO requests no data
Old value = 263
New value = 264
0x000000000d65e02d in ksfdss_checkdata ()

Case 2 (above): we don’t have physical data on “cloned” datafile and have to go to “base” datafile.

Breakpoint 1, 0x00007f256076bf40 in pread64 () from /lib64/libpthread.so.0
$91 = 259  -- ../cln1/users01.dbf
$92 = 524288
(gdb) c
Continuing.
Hardware watchpoint 3: *(*(0x000000006AFD7830+0x820) +8*109)
-- 109  physical read snap IO requests copy
Old value = 48
New value = 49
0x000000000d661ed1 in ksfdss_update_stats ()

Case 1 (above): we have all data in cloned datafile

and my second debug session

command	description
(gdb)break pread64 command p $rdi p $rdx end	break point on OS level physical IO
(gdb) watch *(*(0x000000006AFD9DE8+0x820) +8*111) (gdb) command >print “physical read snap bytes base” >end	hardware level breakpoint on change of stats 111 physical read snap bytes base
(gdb) watch *(*(0x000000006AFD9DE8+0x820) +8*112) (gdb) command >print “physical read snap bytes copy” >end	hardware level breakpoint on change of stats 112 physical read snap bytes copy

Breakpoint 1, 0x00007f1abe686f40 in pread64 () from /lib64/libpthread.so.0
$370 = 261  -- ../cln1/users01.dbf
$371 = 450560
(gdb) 
Continuing.

Breakpoint 1, 0x00007f1abe686f40 in pread64 () from /lib64/libpthread.so.0
$372 = 262 -- ../orcl/users01.dbf
$373 = 450560
(gdb) 
Continuing.
Hardware watchpoint 7: *(*(0x000000006AFD9DE8+0x820) +8*111)

Old value = 2490368
New value = 2940928
0x000000000d661f7a in ksfdss_update_stats ()
$374 = "physical read snap bytes base"
(gdb) 
Continuing.

Breakpoint 1, 0x00007f1abe686f40 in pread64 () from /lib64/libpthread.so.0
$375 = 261
$376 = 524288
(gdb) 
Continuing.
Hardware watchpoint 8: *(*(0x000000006AFD9DE8+0x820) +8*112)

Old value = 614400
New value = 1138688
0x000000000d661eef in ksfdss_update_stats ()
$377 = "physical read snap bytes copy"

Advertisements

PDB snapshot copy – filesystem level details

In my previous “snapshot cloning” series I was focusing on what is going on inside Oracle database itself.
Part 1
Part 2
Part 3
Part 4

It’s time to look deeper on filesystem level to understand what is the miracle under the hood.
Test details:

Database: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (clonedb=true)
OS: OEL 7 (4.1.12-94.3.6.el7uek.x86_64 )
Filesystem: xfs


I have my “source database” already opened in read-only mode as

ALTER PLUGGABLE DATABASE ORCL CLOSE;
ALTER PLUGGABLE DATABASE ORCL OPEN READ ONLY;

First of all, let’s create a directory for my new database

[oracle@localhost orcl12c]$ cd /u01/app/oracle/oradata/orcl12c

[oracle@localhost orcl12c]$ mkdir cln3

[oracle@localhost orcl12c]$ ll
total 2355116
drwxr-x—. 2 oracle oinstall 4096 Oct 27 09:56 cln1
drwxr-x—. 2 oracle oinstall 4096 Oct 27 09:57 cln2
drwxr-xr-x. 2 oracle oinstall 6 Nov 11 03:09 cln3
-rw-r—–. 1 oracle oinstall 18726912 Nov 11 03:10 control01.ctl
drwxr-x—. 2 oracle oinstall 4096 Jun 12 2017 orcl
drwxr-x—. 2 oracle oinstall 4096 Oct 11 01:36 pdb1
drwxr-x—. 2 oracle oinstall 4096 Oct 11 01:43 pdb2
drwxr-x—. 2 oracle oinstall 4096 Jun 12 2017 pdbseed
-rw-r—–. 1 oracle oinstall 209715712 Nov 11 01:59 redo01.log
-rw-r—–. 1 oracle oinstall 209715712 Nov 11 03:10 redo02.log
-rw-r—–. 1 oracle oinstall 209715712 Nov 11 01:31 redo03.log
-rw-r—–. 1 oracle oinstall 786440192 Nov 11 03:08 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 859840512 Nov 11 03:08 system01.dbf
-rw-r—–. 1 oracle oinstall 34611200 Nov 9 00:44 temp01.dbf
-rw-r—–. 1 oracle oinstall 104865792 Nov 11 03:08 undotbs2.dbf
-rw-r—–. 1 oracle oinstall 5251072 Nov 11 02:04 users01.dbf

Now let’s check our “source” database filesystem layout (we will need it for further comparison):

[oracle@localhost orcl12c]$ ll orcl
total 2072252
-r–r—–. 1 oracle oinstall 7938048 Oct 27 09:54 APEX_1941389856444596.dbf
-r–r—–. 1 oracle oinstall 1237327872 Oct 27 09:54 sysaux01.dbf
-r–r—–. 1 oracle oinstall 367009792 Oct 27 09:54 system01.dbf
-rw-r—–. 1 oracle oinstall 67117056 Oct 27 09:31 temp01.dbf
-r–r—–. 1 oracle oinstall 398467072 Oct 27 09:54 undotbs01.dbf
-r–r—–. 1 oracle oinstall 99622912 Oct 27 09:54 users01.dbf

Let’s create an additional snapshot copy PDB


CREATE PLUGGABLE DATABASE CLN3 FROM ORCL SNAPSHOT COPY
PATH_PREFIX = '/u01/app/oracle/oradata/orcl12c/cln3'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl12c/orcl',
'/u01/app/oracle/oradata/orcl12c/cln3')
NOLOGGING;


Let’s have a look at our new datafiles:

[oracle@localhost orcl12c]$ cd cln3
[oracle@localhost cln3]$ ll
total 160
-rw-r—–. 1 oracle oinstall 7938048 Nov 11 03:17 APEX_1941389856444596.dbf
-rw-r—–. 1 oracle oinstall 1237327872 Nov 11 03:17 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 367009792 Nov 11 03:17 system01.dbf
-rw-r—–. 1 oracle oinstall 67117056 Nov 11 03:17 temp01.dbf
-rw-r—–. 1 oracle oinstall 398467072 Nov 11 03:17 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 99622912 Nov 11 03:17 users01.dbf

At 1st glance they look like the same as in our “source database”.
However du command shows the difference

du -h `ls`
16K APEX_1941389856444596.dbf
16K sysaux01.dbf
40K system01.dbf
56K temp01.dbf
16K undotbs01.dbf
16K users01.dbf

So what is the miracle inside? xfs_bitmap command can help to explain it. The command prints the map of real disk blocks used by files in a XFS filesystem. (There are also similar commands for other filesystems, like ext2,3,4, etc. ).

xfs_bmap /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

xfs_bmap /u01/app/oracle/oradata/orcl12c/cln3/users01.dbf

/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf: 0: [0..15]: 33829848..33829863 1: [16..10255]: 36450240..36460479 2: [10256..12815]: 39722952..39725511 3: [12816..30735]: 39725928..39743847 4: [30736..51215]: 39743872..39764351 5: [51216..71695]: 39764616..39785095 6: [71696..74255]: 39785160..39787719 7: [74256..97295]: 39787728..39810767 8: [97296..120335]: 39813864..39836903 9: [120336..145935]: 39859168..39884767 10: [145936..156175]: 39884784..39895023 11: [156176..161295]: 39510400..39515519 12: [161296..168975]: 39493656..39501335 13: [168976..194575]: 36901952..36927551

/u01/app/oracle/oradata/orcl12c/cln3/users01.dbf: 0: [0..15]: 36847536..36847551 1: [16..31]: 36847624..36847639 2: [32..194575]: hole

You can see above that only first 2 blocks of the file(user01.dbf) have mappings to real disk blocks. Other blocks of the file are kind of virtual.

This lead us to an idea that
Linux sparse file technology is used behind the scene. So no real disk space is ocсupied by the files, only inode contains information about the file blocks “holes”.

That 2 blocks allocation can be confirmed on the database side.

SELECT v.snapshotfilename, v.clonefilename, v.blocks_allocated FROM V$CLONEDFILE v WHERE clonefilename='/u01/app/oracle/oradata/orcl12c/cln3/users01.dbf';

snapshotfilename	clonefilename	2
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf	/u01/app/oracle/oradata/orcl12c/cln3/users01.dbf	2

Let’s try to update a single record


UPDATE hr.departments set department_name= department_name || ' department' WHERE department_id=60;


commit;
— we also probably need to flush buffer cache to not wait forever for checkpoint sync buffer cache with data files.
ALTER SYSTEM FLUSH BUFFER_CACHE;

and look at the filesystem again.

xfs_bmap /u01/app/oracle/oradata/orcl12c/cln3/users01.dbf
/u01/app/oracle/oradata/orcl12c/cln3/users01.dbf:
0: [0..15]: 36847536..36847551
1: [16..31]: 36847624..36847639
2: [32..147663]: hole
3: [147664..147679]: 36872464..36872479
4: [147680..194575]: hole

du -h users01.dbf
24K users01.dbf

You can see that one more real 8K disk block has appeared somewhere in the middle of the file.

and finally, let’s create a completely new big segment

CREATE TABLE HR.H_BIG AS SELECT * FROM dba_objects;

ALTER SYSTEM FLUSH BUFFER_CACHE;

after that exercise the filesystem level picture will be:

xfs_bmap /u01/app/oracle/oradata/orcl12c/cln3/users01.dbf
/u01/app/oracle/oradata/orcl12c/cln3/users01.dbf:
0: [0..15]: 36847536..36847551
1: [16..31]: 36847624..36847639
2: [32..63]: 36880496..36880527
3: [64..147663]: hole
4: [147664..147679]: 36872464..36872479
5: [147680..150175]: hole
6: [150176..150191]: 36872976..36872991
7: [150192..154239]: hole
8: [154240..154287]: 36880224..36880271
9: [154288..154495]: 36872768..36872975
10: [154496..154511]: 36880416..36880431
11: [154512..154751]: 36872992..36873231
12: [154752..154767]: 36881152..36881167
13: [154768..155007]: 36873248..36873487
14: [155008..155023]: 36881424..36881439
15: [155024..155263]: 36873504..36873743
16: [155264..155279]: 36874000..36874015
17: [155280..155519]: 36873760..36873999
18: [155520..155535]: 36880992..36881007
19: [155536..155647]: 36874016..36874127
20: [155648..178815]: hole
21: [178816..178943]: 36874128..36874255
22: [178944..178959]: 36881312..36881327
23: [178960..179199]: 36874272..36874511
24: [179200..179215]: 36881536..36881551
25: [179216..179455]: 36874528..36874767
26: [179456..186367]: hole
27: [186368..186399]: 36880592..36880623
28: [186400..204815]: 36881712..36900127
29: [204816..207375]: 36874768..36877327
30: [207376..215055]: 36946144..36953823

du -h users01.dbf
16M users01.dbf

You can see that many ranges of real disk blocks have appeared in the datafile and it even has grown beyond its initial size.

However really occupied disk size are still much less than “declared” size.

ls -lh users01.dbf
-rw-r—–. 1 oracle oinstall 108M Nov 11 16:40 users01.dbf

Where session stats live, 12.2c

In this post I’m going to continue investigation of session stats array location started here Where session stats live.

This post helped me a lot again dynamic-tracing-tools-easier-access-to-sessionprocess-address-ksupga. According to the post you can find your session address at some offset to your process TLS (Thread Local Storage).

Fixed offset “-0x6ea0” mentioned in Mahmood’s blog seems no longer correct for Oracle 12.2c. So I decided to lookup up somewhere around

(gdb) i threads
Id Target Id Frame
* 1 Thread 0x7fed01b63f40 (LWP 30458) “oracle_30458_or” 0x000000000d661ecd in ksfdss_update_stats ()

(gdb) find /g (0x7fed01b63f40-0xffff), +65000, 0x000000006A8FEBF8
0x7fed01b55e88
0x7fed01b55e90

Actually I found 2 candidates located very close, some disass exercise (which I omit here) gave me 0x7fed01b55e90.

So 12.2c sess_addr offset is 0xe0b0, see my calcs below: TLS address – “found address”.

(gdb) p/x 0x7fed01b63f40-0x7fed01b55e90
$59 = 0xe0b0

 
Finally you can get your session address as:

(gdb) x/a (0x7fed01b63f40 – 0xe0b0)
0x7fed01b55e90: 0x6a8febf8

 
It is easy to confirm by

SELECT ‘0x’ || saddr from v$session where sid = 376;
0x000000006A8FEBF8

Repeating my “find exercise” from 0x000000006A8FEBF8 (session address) I found that nowadays not a session stats array itself but session stats array pointer located with fixed offset to session address – 0x820

x/a 0x000000006A8FEBF8 + 0x820
0x6a8ff418: 0x77244de8

And after examining a content of that array, I see;
(gdb) x/10dg 0x77244de8

0x77244de8:	0	17
0x77244df8:	1	1
0x77244e08:	18	2
0x77244e18:	0	0
0x77244e28:	27	93

This is perfectly fit with my session stats.

SELECT s.statistic#, value FROM v$sesstat s WHERE sid = 376;

0	0
1	17
2	1
3	1
4	18
5	2
6	0
7	0
8	27
9	93

Summary

Session address located at fixed offset – 0xe0b0 to thread pointer(“fs:0x0” – 0xe0b0)

Pointer to session stats array located at fixed offset +0x820 to session address(“sess_addr” + 0x820)

PS
Actually session offset exercise was not necessary in this case and I could start with known session address but I found that info during my investigation and decided to publish it.

Snapshot cloning and physical IO. Part 4 algorithm (how does it work).

Finally the algo underneath of “snapshot” physical IO became clear.
You can have a look at the diagram below:

Oracle tries to read dedicated for pluggable database datafile (in my case “../cln1/users01.dbf”) which is Linux sparse file then it checks obtained data buffer in “ksfdss_checkdata” function and if it found data incomplete (I suspect check for zeros ) Oracle issues one more IO on “parent” datafile (“../orcl/users01.dbf”).

clone db with “mixed” segment, missing blocks read
Here is a setup for my gdb session: break pread64 commands p $rdi p $rdx bt 11 end break io_submit
Breakpoint 3, 0x00007f243ed45f40 in pread64 () from /lib64/libpthread.so.0 $17 = 263 0x7116a000: 0x0000a206 $18 = 8192 (gdb) Continuing. Breakpoint 5, 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 $171 = 261 — that is file descriptor (../cln1/users01.dbf) $172 = 65536 — read bytes #0 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 #1 0x0000000010f35410 in skgfqio () #2 0x0000000010e08b74 in ksfd_skgfqio () #3 0x0000000010df59c2 in ksfd_io () #4 0x000000000d65a3c3 in ksfdss_read () #5 0x0000000010df4f8f in ksfdread () #6 0x0000000010dbc5f9 in kcfrbd1 () #7 0x00000000015e0899 in kcbz_table_scan_read () (gdb) c Continuing. Breakpoint 5, 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 $173 = 262 — that is file descriptor (../orcl/users01.dbf) $174 = 65536 read bytes #0 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 #1 0x0000000010f35410 in skgfqio () #2 0x0000000010e08b74 in ksfd_skgfqio () #3 0x0000000010df59c2 in ksfd_io () #4 0x0000000010df4f46 in ksfdread () #5 0x000000000d65dea7 in ksfdss_checkdata () #6 0x000000000d65a44f in ksfdss_read () #7 0x0000000010df4f8f in ksfdread () #8 0x0000000010dbc5f9 in kcfrbd1 () #9 0x00000000015e0899 in kcbz_table_scan_read () (gdb) c Continuing. Breakpoint 5, 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 $175 = 261 $176 = 57344 #0 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 #1 0x0000000010f35410 in skgfqio () #2 0x0000000010e08b74 in ksfd_skgfqio () #3 0x0000000010df59c2 in ksfd_io () #4 0x000000000d65a3c3 in ksfdss_read () #5 0x0000000010df4f8f in ksfdread () #6 0x0000000010dbc5f9 in kcfrbd1 () #7 0x00000000015e0899 in kcbz_table_scan_read () (gdb) c Continuing. Breakpoint 5, 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 $177 = 262 $178 = 57344 #0 0x00007fb781ddbf40 in pread64 () from /lib64/libpthread.so.0 #1 0x0000000010f35410 in skgfqio () #2 0x0000000010e08b74 in ksfd_skgfqio () #3 0x0000000010df59c2 in ksfd_io () #4 0x0000000010df4f46 in ksfdread () #5 0x000000000d65dea7 in ksfdss_checkdata () #6 0x000000000d65a44f in ksfdss_read () #7 0x0000000010df4f8f in ksfdread () #8 0x0000000010dbc5f9 in kcfrbd1 () #9 0x00000000015e0899 in kcbz_table_scan_read () (gdb)

Snapshot cloning and physical IO. Part 3 (mixed case)

Finally I decided to investigate IO for segment partially resided in “cloned PDB” and partially in parent database.
There should be filesystem blocks resided both in “parent” and “cloned” datafiles.

“Reference clone db” untouched	snapshot clone db with “mixed” segment
Here is a setup for my 2 gdb sessions: break pread64 commands p $rdi x/4s $rsi p $rdx end break io_submit
Breakpoint 3, 0x00007f243ed45f40 in pread64 () from /lib64/libpthread.so.0 $17 = 263 — that is file descriptor (../cln2/users01.dbf) 0x7116a000: 0x0000a206 $18 = 8192 (gdb) Continuing. Breakpoint 3, 0x00007f243ed45f40 in pread64 () from /lib64/libpthread.so.0 $19 = 264 –../orcl/users01.dbf 0x7116a000: 0x00000000 $20 = 8192 (gdb) Continuing. Breakpoint 3, 0x00007f243ed45f40 in pread64 () from /lib64/libpthread.so.0 $21 = 263 — (../cln2/users01.dbf) 0x66c3b000: 0x0000a206 $22 = 40960 (gdb) Continuing. Breakpoint 3, 0x00007f243ed45f40 in pread64 () from /lib64/libpthread.so.0 $23 = 264 –../orcl/users01.dbf 0x787f5000: 0x0000a206 $24 = 40960	Breakpoint 3, 0x00007f487b31df40 in pread64 () from /lib64/libpthread.so.0 $9 = 261 cln1/users01.dbf 0x6cdb4000: 0x0000a206 $10 = 8192 (gdb) c Continuing. Breakpoint 3, 0x00007f487b31df40 in pread64 () from /lib64/libpthread.so.0 $11 = 262 orcl/users01.dbf 0x6cdb4000: 0x00000000 $12 = 8192 (gdb) c Continuing. Breakpoint 3, 0x00007f487b31df40 in pread64 () from /lib64/libpthread.so.0 $13 = 261 cln1/users01.dbf 0x66d29000: 0x0000a206 $14 = 40960 (gdb) c Continuing. Breakpoint 3, 0x00007f487b31df40 in pread64 () from /lib64/libpthread.so.0 $15 = 262 orcl/users01.dbf 0x787f7000: 0x0000a206 $16 = 32768 (gdb)
Here is a decypher of file descriptor for processes above, from /proc/>/fd
lrwx——. 1 oracle oinstall 64 Oct 26 01:46 256 -> /u01/app/oracle/oradata/orcl12c/cln2/system01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:46 257 -> /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:46 258 -> /u01/app/oracle/oradata/orcl12c/cln2/undotbs01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:46 259 -> /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:46 260 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:46 261 -> /u01/app/oracle/oradata/orcl12c/cln2/sysaux01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:46 262 -> /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:46 263 -> /u01/app/oracle/oradata/orcl12c/cln2/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:46 264 -> /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf	lrwx——. 1 oracle oinstall 64 Oct 26 01:29 256 -> /u01/app/oracle/oradata/orcl12c/cln1/system01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:29 257 -> /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:29 258 -> /u01/app/oracle/oradata/orcl12c/cln1/undotbs01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:29 259 -> /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:29 260 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 26 01:29 261 -> /u01/app/oracle/oradata/orcl12c/cln1/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 26 01:29 262 -> /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

you can see in this case 2nd IO 1st read 40960 bytes from cloned db datafile/s and then read missing 32768 bytes from “parent” db datafile/s.

Snapshot cloning and physical IO. Part 2.

Following up my previous post I decided to have a look on physical IO for segment created in “cloned PDB” after separation with parent database. It should not have any filesystem blocks resided in parent.
After some iterations to avoid parsing effects I’ve got the following:

snapshot clone db shared segment	snapshot clone db dedicated segment
Here is a setup for my 2 gdb sessions: break pread64 commands p $rdi x/4s $rsi p $rdx end break io_submit
Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $107 = 263 — that is file descriptor (../cln1/users01.dbf) 0x6f972000: “\006\242” 0x6f972003: “” 0x6f972004: “G\230@” 0x6f972008: “\257\352\025” $108 = 8192 amount of byte to read (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $109 = 264 –../orcl/users01.dbf 0x6f972000: “” 0x6f972001: “” 0x6f972002: “” 0x6f972003: “” $110 = 8192 (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $111 = 263 ../cln1/users01.dbf 0x66c35000: “\006\242” 0x66c35003: “” 0x66c35004: “;.\300” 0x66c35008: “\341’\006” $112 = 40960 (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $113 = 264 –../orcl/users01.dbf 0x66c35000: “” 0x66c35001: “” 0x66c35002: “” 0x66c35003: “” $114 = 40960 (gdb) c Continuing.	Breakpoint 1, 0x00007f8f65435f40 in pread64 () from /lib64/libpthread.so.0 $259 = 263— ../cln1/users01.dbf 0x8af70000: “\006\242” 0x8af70003: “” 0x8af70004: “nb” 0x8af70007: “\003\351RI” $260 = 8192 (gdb) Continuing. Breakpoint 1, 0x00007f8f65435f40 in pread64 () from /lib64/libpthread.so.0 $261 = 263— ../cln1/users01.dbf 0x6fb0e000: “\006\242” 0x6fb0e003: “” 0x6fb0e004: “\217b” 0x6fb0e007: “\003\351RI” $262 = 8192 (gdb)
Here is a decypher of file descriptor for processes above, from /proc/>/fd
lrwx——. 1 oracle oinstall 64 Oct 19 01:04 256 -> /u01/app/oracle/oradata/orcl12c/cln1/system01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 257 -> /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 258 -> /u01/app/oracle/oradata/orcl12c/cln1/undotbs01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 259 -> /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 260 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 261 -> /u01/app/oracle/oradata/orcl12c/cln1/sysaux01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 262 -> /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 263 -> /u01/app/oracle/oradata/orcl12c/cln1/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 264 -> /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf	lrwx——. 1 oracle oinstall 64 Oct 23 01:50 256 -> /u01/app/oracle/oradata/orcl12c/cln1/system01.dbf lr-x——. 1 oracle oinstall 64 Oct 23 01:50 257 -> /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 23 01:50 258 -> /u01/app/oracle/oradata/orcl12c/cln1/undotbs01.dbf lr-x——. 1 oracle oinstall 64 Oct 23 01:50 259 -> /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf lrwx——. 1 oracle oinstall 64 Oct 23 01:50 260 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 23 01:50 261 -> /u01/app/oracle/oradata/orcl12c/cln1/sysaux01.dbf lr-x——. 1 oracle oinstall 64 Oct 23 01:50 262 -> /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf lrwx——. 1 oracle oinstall 64 Oct 23 01:52 263 -> /u01/app/oracle/oradata/orcl12c/cln1/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 23 01:52 264 -> /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

Snapshot cloning and physical IO

A new exiting feature was recently investigated by several authors, see for instance:
https://oraganism.wordpress.com/2015/10/02/oracle-12c-pdb-snapshot-copy-on-regular-filesystem/

I decided to investigate deeper what is going on under the hood from physical IO perspective. Below are my results. Left column “snapshot clone db” CLN1 created from ORCL “template” DB. Right column usual pluggable DB created from the same “template”.

snapshot clone db	regular pluggable db
Here is a setup for my 2 gdb sessions: break pread64 commands p $rdi x/4s $rsi p $rdx end break io_submit
Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $107 = 263 — that is file descriptor (../cln1/users01.dbf) 0x6f972000: “\006\242” 0x6f972003: “” 0x6f972004: “G\230@” 0x6f972008: “\257\352\025” $108 = 8192 amount of byte to read (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $109 = 264 –../orcl/users01.dbf 0x6f972000: “” 0x6f972001: “” 0x6f972002: “” 0x6f972003: “” $110 = 8192 (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $111 = 263 ../cln1/users01.dbf 0x66c35000: “\006\242” 0x66c35003: “” 0x66c35004: “;.\300” 0x66c35008: “\341’\006” $112 = 40960 (gdb) c Continuing. Breakpoint 1, 0x00007fe5a840df40 in pread64 () from /lib64/libpthread.so.0 $113 = 264 –../orcl/users01.dbf 0x66c35000: “” 0x66c35001: “” 0x66c35002: “” 0x66c35003: “” $114 = 40960 (gdb) c Continuing.	Breakpoint 1, 0x00007fe8b7a8df40 in pread64 () from /lib64/libpthread.so.0 $55 = 259 — ../pdb1/users01.dbf 0x8d776000: “\006\242” 0x8d776003: “” 0x8d776004: “\275 @” 0x8d776008: “\242\356\025” $56 = 8192 (gdb) Continuing. Breakpoint 1, 0x00007fe8b7a8df40 in pread64 () from /lib64/libpthread.so.0 $57 = 259 — ../pdb1/users01.dbf 0x66c35000: “\006\242” 0x66c35003: “” 0x66c35004: “\303M\300” 0x66c35008: “gD\006” $58 = 40960
Here is a decypher of file descriptor for processes above, from /proc/>/fd
. . . . . lrwx——. 1 oracle oinstall 64 Oct 19 01:04 256 -> /u01/app/oracle/oradata/orcl12c/cln1/system01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 257 -> /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 258-> /u01/app/oracle/oradata/orcl12c/cln1/undotbs01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 259-> /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 260 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 261 -> /u01/app/oracle/oradata/orcl12c/cln1/sysaux01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 262 -> /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:04 263 -> /u01/app/oracle/oradata/orcl12c/cln1/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:04 264 -> /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf . . . . . .	. . . . . lrwx——. 1 oracle oinstall 64 Oct 19 01:01 256 -> /u01/app/oracle/oradata/orcl12c/pdb1/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:01 257 -> /u01/app/oracle/oradata/orcl12c/system01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:01 258 -> /u01/app/oracle/oradata/orcl12c/pdb1/sysaux01.dbf lrwx——. 1 oracle oinstall 64 Oct 19 01:01 259 -> /u01/app/oracle/oradata/orcl12c/pdb1/users01.dbf lr-x——. 1 oracle oinstall 64 Oct 19 01:01 3 -> /dev/null . . . . .

Below is a small visualization:
You can see that in case of “snapshot cloned PDB” Oracle reads own datafile first and then goes to base/parent datafile.