What influences the performance of the query ?

What is the best definition of the performance of any query statement  in Oracle database (and every other relational database) ?

Short answer:

Performance=f(number_of_blocks,concurrent_access_of_the_blocks)

Slightly longest answer:

Performance=f(number_of_blocks,concurrent_access_of_the_blocks) 

where Number_of_blocks=f(db_cache,os_cache,san_cache,san_disk)

And the full answer:

Performance=f(number_of_blocks,concurrent_access_of_the_blocks) 

where Number_of_blocks=f(db_cache,os_cache,san_cache,smart_cache,san_disk)

+

Global workload on the physical server & storage work that are hosting our system.

(Because there can be a lot of virtual machines sharing the same physical server & storage work)

That’s (almost) all.

Concerning a single query statement there is the optimizer to hopefully choose the best plan and the runtime engine to execute it.

Why the optimizer choose the wrong decision ?

Oracle Cost based optimizer only takes care about the number of blocks and their supposed  repartition between the memory  and the disk.

It does not know anything about the concurrent access nor global server workload.

To be continued…

What is the cost ?

Why do I want to talk about the cost, since it has already been explained in the famous book by Jonathan Lewis ?

Because I want to give here some simple exemples for the DBAs who did not read Jonathan’s book (altough they should do it )

Also, I will try to compare the what the optimizer thinks and what the runtime engine will do.

So , how the cost is calculated ?

From Cost based optimizer by Jonathan Lewis:

According to the CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

 

where

#SRDs – number of single block reads
#MRDs – number of multi block reads
#CPUCycles – number of CPU Cycles
sreadtim – single block read time
mreadtim – multi block read time
cpuspeed – CPU cycles per second

Translated, this says the following:
The cost is the time spent on single-block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time it takes to do a single-block read. Which means the cost is the total predicted execution time for the statement, expressed in units of the single-block read time.

More precisely, the formulas are the following  (from the Select statement graph):

cost of the tablescan =ceiling(nblks/mbrc) *(mreadtim/sreadtim)+1

cost of the index scan = blevel + ceiling(leaf_blocks * effective index selectivity) + ceiling(clustering_factor * effective table selectivity)

 If we are using the workload system statistics, then sreadtim, mreadtim and mbrc are calculated and stored inside sys.aux_stats$ table.

If not, they are calculated as follows

sreadtim=ioseektim+ db_block_size/iotfrspeed
mreadtim=ioseektim+db_file_multiblock_read_count *bd_block_size/iotftspeed

mbrc=db_file_multiblock_read_count

 

Now let’s look at some most typical exemples, an index unique scan, the range scan, than  the  full scan, and let’s try to undestand how the cost is calculated.

But before do that , I’ll do another test: a range scan that I know will not return any rows, since there are no employees with the empno number less

than 1. Let’s how CBO will consider it, and also what the runtime engine needs to do.

 

select * from emp where empno<1;

 

We will  first use the autotrace, because it will tell us both and the optimizer estimation and the runtime work , and it  says the following:

 

Execution plan
———————————————————-
Plan hash value: 679380930

——————————————————————————–

——–

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

me     |

——————————————————————————–

——–

|   0 | SELECT STATEMENT            |          |     1 |    46 |     4   (0)| 00

:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    46 |     4   (0)| 00

:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_EMPNO |     1 |       |     3   (0)| 00

:00:01 |

——————————————————————————–

——–
Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPNO”<0)
Statistiques
———————————————————-
1  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
1021  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

 

So the optimizer has estimated the cost of 4 and the runtime engine says that the query 3 buffer gets.

Let’s try to find the easier part, the 3 buffer gets of the runtime engine.  Why we still need 3 buffer gets,even no data is being returned ?

SQL> select leaf_blocks,blevel,index_name from user_indexes;

LEAF_BLOCKS     BLEVEL INDEX_NAME
——————–   ———- ——————————
1950                                   2         IX_DEPTNO
0                                         0         IX_COUNTRY
2226                                   2        IX_EMPNO
0                                          0        IX_LANGUAGENO

 

If level=0, there is only one block in the index, the root block.
if level=1 there are root and branch blocks
if level=2 there is a root block, one or more branch blocks and one or more leaf blocks.

So if there is 2 level of the index ix_deptno, it must start with the root, go to the branch and go to the leaf to understand
that there is no rows corresponding to the search criteria, that makes 3 buffer gets

Now, let’s look to the optimizer, and find out why 3 buffer gets give cost of 4.

The 10053 trace says the following:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP  Alias: EMP
#Rows: 1000001  #Blks:  10100  AvgRowLen:  46.00  ChainCnt:  0.00
Index Stats::
Index: IX_COUNTRY  Col#: 10
LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
Index: IX_DEPTNO  Col#: 8
LVLS: 2  #LB: 1950  #DK: 3  LB/K: 650.00  DB/K: 5907.00  CLUF: 17721.00
Index: IX_EMPNO  Col#: 1
LVLS: 2  #LB: 2226  #DK: 1000001  LB/K: 1.00  DB/K: 1.00  CLUF: 5907.00
Index: IX_LANGUAGENO  Col#: 11
LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP[EMP]
Column (#1): EMPNO(
AvgLen: 5 NDV: 1000001 Nulls: 0 Density: 0.000001 Min: 1 Max: 1000001
Using prorated density: 0.000001 of col #1 as selectvity of out-of-range/non-existent value pred
Table: EMP  Alias: EMP
Card: Original: 1000001.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
Access Path: TableScan
Cost:  2750.36  Resp: 2750.36  Degree: 0
Cost_io: 2737.00  Cost_cpu: 271926944
Resp_io: 2737.00  Resp_cpu: 271926944
Using prorated density: 0.000001 of col #1 as selectvity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: IX_EMPNO
resc_io: 4.00  resc_cpu: 29036
ix_sel: 0.000001  ix_sel_with_filters: 0.000001
Cost: 4.00  Resp: 4.00  Degree: 1
Best:: AccessPath: IndexRange
Index: IX_EMPNO
Cost: 4.00  Degree: 1  Resp: 4.00  Card: 1.00  Bytes: 0

***************************************

So the cost is estimated as 4 with the density of 0,0000001
Go to the cardinality matrix and see that the cost of the index is=>

cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
cost=2+
(0,000001*2226)
2+ 0,002226
(0,000001*5907)
+0,005907
2,1

So where the cost 4 is coming from ?
As we can see,the DBA should always have a rough idea about the number of buffer gets needed for a query, but usually it’s not so easy to completely understand Oracle cost, altough it is quite simple  for this trivial exemple.
So in everyday optimization, the DBA will seldom try to fully explain the cost.
Jonathan’s book is just an excellent guide to give you an idea about what the cost approximately is.

 

Now, let’s look at the full scan exemple, and what’s the cost behaviour with it.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————-

Plan hash value: 2083865914

——————————————————————-
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
——————————————————————-
|   0 | SELECT STATEMENT   |      |     1 |  2744   (1)| 00:00:33 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K|  2744   (1)| 00:00:33 |
——————————————————————-

Perhaps, it is easier to understand if we query directly the plan table to see the stored figures:

SQL> select cpu_cost/(2818*10000),io_cost from plan_table;

cost=io_cost + cpu_cost/cpuspeed*sreadtim*1000

 

CPU_COST/(2818*10000)    IO_COST
——————— ———-
7,87532626       2737

2737+7=2744

 

Where the 2737 came from ?

It’s related to to “adjusted mbfrc”.

If you play with db_file_multiblock_read_count or with mbrc ,you will be able to draw a non-linear curve.

 

Where the 7,87 is coming from ?

 

This is the calculated CPU cost.

When we change the clause where by widening or narrowing the range ,we see that only the  CPU component is changing, not the IO component. Why? Because , the model assumption is that , in every case, the runtime  engine must read the all the block under the HWM , so the number of IOs remain unchanged. However, the CPU work will not be  the same, because the model logically suppose that a where clause that matches more lines will require more work in terms of CPU, to compare and return rows instead of just discarding them. (The same thing will happen if we query the first column, then the second, then the third , the small difference will be shown on the CPU component)

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 3956160932

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 952K| 41M| 2751 (1)| 00:00:34 |
|* 1 | TABLE ACCESS FULL| EMP | 952K| 41M| 2751 (1)| 00:00:34 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–
1 – filter(“SAL”>1000)
13 rows selected.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 3956160932

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 714K| 31M| 2750 (1)| 00:00:34 |
|* 1 | TABLE ACCESS FULL| EMP | 714K| 31M| 2750 (1)| 00:00:34 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–
1 – filter(“SAL”>2000)

13 rows selected.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 3956160932

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 476K| 20M| 2749 (1)| 00:00:33 |
|* 1 | TABLE ACCESS FULL| EMP | 476K| 20M| 2749 (1)| 00:00:33 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–
1 – filter(“SAL”>3000)

 

It’s more clear if we query directly the plan table:
SQL> select cpu_cost/(2818*10000),io_cost from plan_table;

CPU_COST/(2818*10000) IO_COST
——————— ———-
16.5778937 2737
15.7329824 2737
13.1982556 2737

 

 

 

 

Adaptive thresholds

 

The 2  pictures at the bottom of the page show the clear advantage of the adaptive treholds over fixes tresholds. With fixed  thresholds, we see that some deviation can be missed. More than that, the workload is not the same for OLTP and DW. So what is the unexpected value for OLTP may not be for DW. The adaptive tresholds is a learned curve, based on the past of your system activity, and is used to compare with your database behaviour in the future.

There are 2 types of adaptive tresholds, “Percentage of Maximum” and “Significance level“.

If we understand what is “Percentage of Maximum“, it is easier to understand the meaning of “Significance level“.

Percentage of Maximum is simply 99% of the observed value. If we set 100 as a treshold value for a metric X (ex.physical reads per second) , then the alert will be sent when we have 99 physical reads per seconds. (In fact, there is  another parameter called Occurencies, that indicates how many consecutive occurencies must happened before the alert is generated)

But what is the “Significance level” ?

As per Oracle documentation, there can be several levels of severity.

 

High 95%

Very High 99%

Severe 99.9t%

Extreme 99.99%

 

This means that with “Extreme Significance Level”  Oracle will alert on values that we would only expect to see once in a 10000 observations.

The curve of the adaptive treshold will be drawn to reflect it.

So, this method is used to capture unusual events and to alert on them.

 

 

The select statement graph

I will first start by presenting the  Select statement graph, with some essential cardinality formulas. Altough these formulas
will probably change from version to version, it’s important to remember them and understand the rationale behind them.

 

This select statement graph is drawn by compiling the information found in the following sources:

Jonathan Lewis
“CBO Fundamentals”

http://www.amazon.co.uk/Cost-Based-Oracle-Fundamentals-Experts-Voice/dp/1590596366

http://www.adellera.it

“New Density calculation in 11g”

http://www.centrexcc.com

“A Look under the Hood of CBO: The 10053 Event”
“Fallacies of the Cost Based Optimizer”
“Histograms – Myths and Facts”
“The Effects of optimizer_index_cost_adj and optimizer_index_caching on Access Plans”

Note for the reader: You should copy the picture on your disk  and zoom it bigger. I’m looking for the better solution to draw it at the moment

 

Tuning strategies : Jonathan Lewis versus Stephane Faroult

A lot has been said about the tuning of relational databases in the past 10 years.Especially, the internals of Oracle database were explained in details, perhaps more than any other RDBMS. It was mostly done by some people external to Oracle company, such as Jonathan Lewis, Wolfang Breitling to name a few. Also, a briliant work about the tuning of SQL statements was done by Stephane Faroult in his two books, “The Art of SQL” and “Refactoring SQL”. But, the tuning is still a very challenging task . So the DBAs need not only to optimise sql statements, but also to optimize their own work, and find the solution as soon as possible. Here, I will try to combine the 2 approaches: One is proposed by Jonathan Lewis in his “Cost based oracle optimizer” and the other is proposed by Stephane Faroult in the 2 mentioned books. Also, I will try to be as short as possible. For this purpose, there will be only one database model to show all exemples, and this is a well known Oracle emp/dept Scott schema, slighlty improved.

In the introduction of his CBO Fundamentals, Jonhatan Lewis says:

Why do we want to know how the CBO works? Because when we hit a problem where the optimizer produces a very bad execution plan, we want to understand what the problem is so that we can supply the right fix.

Of course, we may be able to fix that one problem by adding some hints to the SQL or doing a cunning little rewrite, but if we take that approach, we may have to do that same thing time and time again as the problem reappears in other places. On the other hand, if we understand the underlying issue, we can fix the issue once and
know that we have addressed every occurrence of the problem.

Through his book, the author is trying to explain how Oracle has calculated the cost for lot of different scenarios: tablescans, index scans, joins etc etc.

We can find a different approach in books written by Stephane Faroult:

Stephane tries to understand what the is the best way the retrieve data and then he tries to write the SQL Statement in order to make it obvious to the optimizer .

This is called close to the kernel.

“I didn’t really care what the execution plan is, because something is apparent from the text of the query itself: a table of 5 million rows is accessed 3 times, once per subquery…” says Stephane in one of his exemples. But this is showing the more global way of thinking is Stephane’s optimizing strategy.  He classifies the tables from one query into following groups:

a) Table to which a condition is applied but from which nothing is returned

b) Tables that are used only for joins

c) Tables from which we return data and to which conditions may be applied

etc.

So what is the better and faster approach to optimize the query ?

We will try to answer int this article.

(To be continued…)

Opening Menu