0

From web server i initiate two separate concurrent threads/requests to read from two different DBMS(oracle) tables(employee and department). Both web server and oracle are on separate node(quad core processor).

Thread1
        Select * from employee;

Thread2
        Select * from department;

My question is , Once request comes to oracle node, can DBMS can execute both read operation from hard disk at same time ? Here contested resource is Hard disk, can two operation read concurrently from hard disk ? I means internally it will be sequential or parallel read ?

0

Prologue

I'm assuming this setup is a theoretical question. The only time you would do this is if the two tables are part of two different Federated Data sets as determined by your Legal department.

Even across multiple databases, you would still match the data, between the two tables, within the RDBMS using the JOIN syntax.

Parallel Reads

Can the disks do multiple reads simultaneously? It depends.

Hard Disks

Modern hard disks use a communication protocol called SCSI (Small Computer Systems Interface). SAS (Serial Attached SCSI) drives use it. Fibre Channel drives use a super set of SCSI. SATA (Serial ATA) drives use a subset of SCSI.

Part of the SCSI command set allows the OS to queue IO requests. The disk internals can then mix/match IO requests with a single swipe of the head. I believe you need to use ASYNCHRONOUS IO to take advantage of that feature. This would need to be enabled at the OS level (linux) and within the RDBMS.

RAID

In addition to SCSI queued commands, the IO could be split across multiple drives. The data for Table1 could be on disks 1-20 while the data for Table2 could be on disks 21-45. As the IO requests trickle down to the physical disks, the reads can be done in parallel.

Other Options

SQL

The best way to retrieve related data in a serial fashion is

select *
from Table1 a
  join Table2 b on a.table1_id=b.table1_id

The best way to retrieve related data in a parallel fashion is

select * -- tables need to be PARALLEL enabled
from Table1 a
  join Table2 b on a.table1_id=b.table1_id

The best way to retrieve related data in a parallel fashion using multiple nodes (RAC) is

select * -- tables need to be PARALLEL enabled
from Table1 a
  join Table2 b on a.table1_id=b.table1_id

Which one will the RDBMS use? That depends on the resources that are available and if the CBO has decided that parallelization is worth the overhead of creating/managing slave threads. Additionally, you can give a hint to the CBO that you think that running the job in /*+ parallel */ could have less cost.

CLUSTER the data

If you want the data for both Table1 and Table2 to exist in the same data block, organized so that they appeared to be already JOIN-ed, you will need to CREATE CLUSTER. Remember, the database reads data from the disk by data blocks, not individual rows.

Due to how it works, CLUSTER-ed tables can slow down DML actions against the tables. As such, it is not a commonly used feature.

You access the data from a CLUSTER like this

select *
from Table1 a
  join Table2 b on a.table1_id=b.table1_id

Epilogue

As shown, the code to access the data does not change with respect to how it is actually accessed or physically stored.

This is one of the powers of using a database. The language to access the data is independent of how the data is physically accessed. Do not treat the database as a data dump.

(PS - the Network will become a bottleneck well before Disk I/O will become a bottleneck.)

  • Thanks for detailed answer. But still I am not clear. My question is simple I have database on single node. Consider it has two tables and which does not have any realtion b/w them. Now two concurrent request comes up to read from two different tables. can read from disk happen exactly same time or will it be sequential ? – user3198603 Aug 12 '18 at 13:51
  • The simple answer is it depends. You have to have the hardware to support asynchronous IO, you have to have the OS configured for asynchronous IO, and you have to have the database configured for asynchronous IO. Then, and only then, can parallel reads have a chance to occur. – Michael Kutz Aug 12 '18 at 16:06
  • Ok It means by default(i.e. without no explicit configuration), there is sequential read. – user3198603 Aug 13 '18 at 7:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.