Who makes the decisions? I thought it was Optimizer

-


Direct path read decision

Some decisions are actually not taken by the optimizer.

We started to experience a performance problem with an sql query which we can not change the software code and tried to find a solution in solely database layer. 

Sql query was a sophisticated one, but problem can be summarized with the short one below.

Here is the plan output.

Products table was a large table exceeding 50 Gb size and query's execution time was 180 - 200 seconds. 

Query was searching in PRODUCTS table for the HTML_CONTENT with not null records, but for all rows,  HTML_CONTENT column values was null and as this column is not indexed, query was performing a full table scan. And also, html_content column data type was CLOB. For that reason I could not create a functional index and i could not rewrite the query as the source code is not available.

I tried to execute the query with  /*+ PARALLEL(2) */ hint, execution completed within milliseconds.  In Parallel Execution with Oracle Database White Paper document it is stated that Scaling linearly is the ultimate goal of parallel processing. But for this query it was executing 200 times faster than the original one. I checked the execution plan, but access paths were same. 

I tried to find a clue to explain why execution times differ that much. I used 10046 event tracing to get a clear picture and used TKPROF tool. Here is the elapsed time outputs of tkprof analysis respectively (original and hinted one).

Cell multiblock physical read and cell single block physical read wait events are  the direct reads of data from multiple/single blocks in a cell to a  memory location. Cell multiblock physical read is generally caused due to full table scan queries whose processing is not offloaded to the storage layer. If we were not using Exadata and using traditional (not Oracle engineered) servers, we would be observing db file scattered read and db file sequential read events. 

We observed cell smart table scans. Cell smart table scan is the capability of an Oracle Database to offload SQL processing to the Exadata Storage Servers. Exadata Smart Scan works in collaboration with Exadata Storage Indexes, which reduces the amount of IO required to satisfy the smart scan process. I am not writing this post for marketing purposes. 

What is interesting here is, despite the same access paths are choosen by the optimizer, WITH THE /*+ PARALLEL */ hint in use, the decision of reading the blocks from disk to memory changed with the decision of reading the blocks from disk directly. A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.  This decision is not taken by the OPTIMIZER, who is in charge was the database engine itself. 

This behavior is controlled by the hidden "_serial_direct_read" parameter. And how this decision(buffered read or direct path read) is taken is explained in detail by Tanel Poder blog post, Optimizer statistics-driven direct path read decision for full table scans .

In short, these are the factors involved in the decisions:

    * Are block counts taken from optimizer stats or segment header?

    * Do the segment header and/or optimizer stats block counts change?

    * Does the buffer cache size change (thus the _small_table_threshold too)?

    * Does the amount of cached blocks of a segment change?

    * Parallel vs Serial execution

    * Are buffered parallel full scans allowed (the in-memory PX feature of 11.2)

    * Did a new child cursor get created or the old one reused

     * Etc.

According to the How To Force Direct Path Read for SQL Statements (Doc ID 2426051.1) document, there are two methods to force direct path read for sql statements. First one use running sql statement with parallel hint (That is what i did), and second one is "Setting the statistics of the tables involved in the SQL such that the no.of blocks of tables > _small_table_threshold to enable the serial direct path read".

Another mystery is solved and now we can patch our sql statement with a sql patch.  From now on, it will be working super fast.

Hope it helps.


Comments

Post a Comment

Popular posts from this blog

Using table stats hint to force a direct path read but with a rownum predicate surprise

-
Image
Manipulating table statistics for the statement period On my blog post " Who makes the decisions? I thought it was Optimizer ", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables play role in direct path read decision.  STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516 MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580 VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100 I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.   Before 11.2.0.2 release, the direct path read d
Read more

Oracle Grid Release Update by using Ansible Playbooks

-
Image
Ansible is a friend in need and a friend indeed for DBAs. In this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it. Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks. Ansible stands out from other configuration management tools because it doesn't need agents installed on target hosts. Instead, it leverages SSH for connecting to them and executes commands directly through the shell. This means Ansible communicates with remote hosts securely using SSH, eliminating the need for additional software on the managed machines. It's like h
Read more

Oracle Database Release Update by Using Ansible Playbooks

-
Image
Ansible to Rule Them All In the previous post " Oracle Grid Software Patching by using Ansible Playbooks" , I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching more clean and simple way. Now I will also patch database software by using Ansible Playbooks. In this post,  I will use blt01 host as the ansible host. Target environment is a two node RAC database. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time.  Although, there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about a complete Oracle database software patching.  Ansible is simply installed by "yum install ansible"  from EPEL repository. Add your hosts(gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for oracle and root users. We need:     * OPatch -   p6880880_122010_Linux-x86-64.zip     * 19.22 RU -  p35940989_190000_Linux-x86-64.
Read more