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.

Create a New AWS DB Parameter / Option Group

1)Create a new Parameter group:

2)Create a Option group:

3) Modify the customised parameter group and option group to meet your needs.

4)Create RDS instances by using new created parameter / option group.

Index on Timestamp Column in Oracle Database

SYMPTOM

Index on timestamp is not used when compared with systimestamp value.

SQL> desc tets_tbl
Name         Null?    Type
------------ -------- ----------------------
...
..
EXPIRY_TIME          TIMESTAMP(6)
...
..

Create an index on expiry_time column, and see the index is not used.

SQL>CREATE INDEX TEST_TBL_IDX1 ON TEST_TBL (EXPIRY_TIME);
SQL>SET AUTOT trace exp;
SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 692824678
---------------------------------------------------------------------
| Id  | Operation         | Name   | Rows|Bytes |Cost(%CPU)|Time
---------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 8147| 1630K| 1347  (1)|00:00:17
|*  1 |  TABLE ACCESS FULL|TEST_TBL| 8147| 1630K| 1347  (1)|00:00:17 
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

CAUSES

TIMESTAMP datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has contains fractional seconds but does NOT have a time zone.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

SOLUTION

1) Create a function index:

SQL> create index test_tbl_idx2_fn on test_tbl( sys_extract_utc(expiry_time));

Index created.

SQL> select * from test_tbl where EXPIRY_TIME<systimestamp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3272729254
--------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 |SELECT STATEMENT            |                    |  8147 |  1630K|  1224   (0)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TBL         |  8147 |  1630K|  1224   (0)| 00:00:15 |
|*  2 |   INDEX RANGE SCAN          | TEST_TBL_IDX2_FN |  1467 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("EXPIRY_TIME"))< 
SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

2) Define the column as “TIMESTAMP WITH TIME ZONE” instead of “TIMESTAMP”, then a function index is created automatically for this column.

3) Rewrite query by using sysdate instead of systimestamp:

SQL> select * from test_tbl where EXPIRY_TIME<sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash value: 205107284

-------------------------------------------------------------------------
| Id | Operation                  | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 1    | 205   | 4 (0)      | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL      | 1    | 205   | 4 (0)      | 00:00:01 |
|* 2 | INDEX RANGE SCAN           | TEST_TBL_IDX1 | 1    |       | 3 (0)      | 00:00:01 |
--------------------------------------------------------------------------

Too many AQ$_PLSQL_NTFN_XXXXX jobs In Oracle Database

SYMPTOM

There are more than one million AQ$_PLSQL_NTFN_xxxxxxxx scheduler jobs in oracle database.

sql hangs for querying dba_scheduler_jobs.

Please note there are a couple of bugs on this issue, the steps are not necessarily working for every database versions.

SQL>select count(*) from dba_scheduler_jobs;

WORKAROUND

1)set job_queue_processes =0

SQL>alter system set job_queue_processes=0 scope=memory;

2)Stop and Drop all those AQ$_PLSQL_NTFN scheduler jobs:

— add rownum<100000 into below sqls, if the sql hangs.

SQL>select 'EXEC DBMS_SCHEDULER.STOP_JOB ('''|| job_name ||''', force => TRUE);' 
    from dba_scheduler_jobs 
    where job_name like 'AQ$_PLSQL_NTFN_%'
    and state='RUNNING';


SQL> select ' exec dbms_scheduler.drop_job('''||job_name ||''');' 
     from dba_scheduler_jobs 
     where job_name like 'AQ$_PLSQL_NTFN_%';

SQL>select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
    where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

DECLARE 
po dbms_aqadm.aq$_purge_options_t; 
BEGIN 
po.block := TRUE; 
DBMS_AQADM.PURGE_QUEUE_TABLE( 
queue_table => 'SYS.AQ_SRVNTFN_TABLE_1', 
purge_condition => 'qtview.queue = ''AQ$_AQ_SRVNTFN_TABLE_1_E''', 
purge_options => po); 
END; 
/

select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

4) Collect stats.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

5) Rebuild indexes of DBA_SCHEDULER_JOBS

6) Bounce the instance.

SQL>shutdown immediate ;
SQL>startup;
SQL>alter system set job_queue_processes=1000 scope=memory;