How to Find Interval Partition Name by Date Column Value

Since  HIGH_VALUE  in DBA_TAB_PARTITION view is LONG type, so it is difficult to get the system generated interval partition name straight away. The following will lead you how to get it easily.

SQL> desc dba_tab_partitions
Name              Null?    Type
----------------- -------- ------------
TABLE_OWNER                VARCHAR2(30)
TABLE_NAME                 VARCHAR2(30)
COMPOSITE                  VARCHAR2(3)
PARTITION_NAME             VARCHAR2(30)
SUBPARTITION_COUNT         NUMBER
HIGH_VALUE                 LONG
...
.

Subscribe to get access

Read more of this content when you subscribe today.

Test Linux Disk IO Performance

As a DBA, sometime we need know how the disk IO performs  in Linux when database hits performance issue. Particularity SQLs have high IO waitings.

1)  Discard caches before testing :

# sync; echo 3 >  /proc/sys/vm/drop_caches

The sync command allows the kernel write as many dirty cache pages to disk as it can.
To clear PageCache only run: # sync; echo 1 > /proc/sys/vm/drop_caches
To clear dentries (Also called as Directory Cache) and inodes run:  # sync; echo 2 > /proc/sys/vm/drop_caches

Page cache is memory held after reading files. Linux kernel prefers to keep unused page cache assuming files being read once will most likely to be read again in the near future, hence avoiding the performance impact on disk IO.

dentry and inode_cache are memory held after reading directory/file attributes, such as open() and stat(). dentry is common across all file systems, but inode_cache is on a per-file-system basis. Linux kernel prefers to keep this information assuming it will be needed again in the near future, hence avoiding disk IO.

2) To measure server throughput (write speed) :

$dd if=/dev/zero of=/tmp/test1.dmp bs=1024k count=1024 oflag=dsync

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 3.87519 s, 277 MB/s

3) To measure server latency ( read speed ):

$ dd of=/dev/zero if=/tmp/test1.dmp bs=1024k count=1024 oflag=dsync
  • oflag=dsync (oflag=dsync) : Use synchronised I/O for data. Do not skip this option. This option get rid of caching and gives you good and accurate results.
  • conv=fdatasyn: Again, this tells dd to require a complete “sync” once, right before it exits. This option is equivalent to oflag=dsync.

4) File checksum:

-- Md5sum

$ time /usr/bin/md5sum /tmp/test1.dmp
cd573cfaace07e7949bc0c46028904ff /tmp/test1.dmp

real 0m1.861s
user 0m1.609s
sys 0m0.243s

-- Sha1 

$ time sha1sum  /tmp/test1.dmp
2a492f15396a6768bcbca016993f4b4c8b0b5307  /tmp/test1.dmp

real    0m1.296s
user    0m1.050s
sys     0m0.241s

-- Sha256

$ time sha256sum  /tmp/test1.dmp
49bc20df15e412a64472421e13fe86ff1c5165e18b2afccf160d4dc19fe68a14 
/tmp/test1.dmp

real    0m2.520s
user    0m2.244s
sys     0m0.258s

enq: TM – contention

It is common for a DBA  to see “enq: TM – contention” wait events. We are seeing this wait events from DMLs against a parent table ( teachers ).

SQL> insert into teachers values (3, 'Teacher 3');

Subscribe to get access

Read more of this content when you subscribe today.