Oracle - Explain plan cost -- What is it and how optimizer calculated

Oracle - Explain plan cost -- What is it and how optimizer calculates it

In most of the Oracle DBA performance interviews and blogs, i observed one common question related to explain plan. 

Question is -- how do you define the cost mentioned in query explain plan? 

So here is the basic formula used by Oracle optimizer. 

For any SQL statement, oracle optimizer has to perform system I/O calls and CPUs to process the called blocks. Database information is stored in the logical form of ROWs and Columns of tables. Database storage unit is DB BLOCK (which is combination of OS Blocks based on the DB Block size) which stores the information. Each row of the table has ROW_ID, which is in hexadecimal format and donates the information about – Datafile, DB Block number & Row location in that DB block.

Now when oracle raises the request to read the number of blocks from disk, the system generates the calls for either single block read and/or multi-block-reads. This is called the IO cost.

The basic unit of the explain plan cost is one Single block random read.

Hence the IO costs can be formulated in two parts

Single block read cost = Number of single block read calls * Time to read one single block read

Multi block read cost = Number of multi block read calls * Time to read one multi block read

Along with the IO cost, CPU cost is also as important as IO cost for optimizer to decide the best possible path to serve the client query. Once the data is available in buffer, it require processing or the multiple gets call in the buffer itself. CPU cost has internal formula, actually, it is not a formula but a simple mathematics based on the CPU configurations.

Every CPU has its hardware specifications and two of them are used for calculating the CPU cost, i.e. ‘CPU Cycles’ & ‘CPU Speed’.

CPU Cost = CPU Cycles/CPU Speed

Now this is the time to combine the formula to calculate the optimizer cost.

Cost = Sum (Single block IO Cost + Multiblock reads IO Cost + CPU cost) / (Single block read time)

Query explain plan cost