Some missing optimization

Last time I blogged about incorrect BITMAP CONVERSION TO ROWIDS operation costing by Oracle optimizer, here is my “near real life” example.

I have 2 huge tables (1TB and 100++GB) and
I have some “unit_id” column where id value repeats quite often,
both tables have bitmap indexes on “init_id”.

I need to find “unit ids” in table1 which already exists in table2.
First idea was to compare indexes which are relatively small instead of manipulating huge tables.

Initial setup

set lines 200
set pages 0

DROP TABLE IDXTEST;
DROP TABLE IDXTEST2;

CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad('a',399,'a') filler
from dual connect by level < 5e5
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);

CREATE TABLE IDXTEST2 AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad('a',399,'a') filler
from dual connect by level < 1e6
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST2_IDX1 ON IDXTEST2 (id2);

execute dbms_stats.gather_table_stats(null, 'IDXTEST');

execute dbms_stats.gather_table_stats(null, 'IDXTEST2');

So I have started with

select id2 FROM IDXTEST WHERE id2 IN (SELECT id2 FROM IDXTEST2);


and observed

                                                                                                                        
Plan hash value: 2149971028        
                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |      1 |        |       |       |  1962 (100)|          |    499K|00:00:10.53 |     766 |
|*  1 |  HASH JOIN SEMI               |               |      1 |    499K|  5859K|  8792K|  1962   (1)| 00:00:24 |    499K|00:00:10.53 |     766 |
|   2 |   BITMAP CONVERSION TO ROWIDS |               |      1 |    499K|  2929K|       |   225   (0)| 00:00:03 |    499K|00:00:01.19 |     258 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1  |      1 |        |       |       |            |          |    500 |00:00:00.01 |     258 |
|   4 |   BITMAP CONVERSION TO ROWIDS |               |      1 |    999K|  5859K|       |   450   (0)| 00:00:06 |    499K|00:00:01.15 |     508 |
|   5 |    BITMAP INDEX FAST FULL SCAN| IDXTEST2_IDX1 |      1 |        |       |       |            |          |    500 |00:00:00.01 |     508 |
-------------------------------------------------------------------------------------------------------------------------------------------------
                                   
Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       
                                   
   1 - access("ID2"="ID2")         

So 1st of all I see that only indexes were used as I’ve expected and that is good.
I also see 2 “BITMAP CONVERSION TO ROWIDS” operations in the plan which is not so good.
I’ve almost immediately realized that 1st “BITMAP CONVERSION TO ROWIDS” (line marked 2) is completely my fault. I need to specify that only unique ids are interesting for me otherwise “BITMAP CONVERSION TO ROWIDS” operation is unavoidable (to provide as many repeating values as exists in table rows).
However I have been a bit surprised by operation on line 4 (which I believed unnecessary).

So I have rewritten my query as


select distinct id2 FROM IDXTEST WHERE id2 IN (SELECT id2 FROM IDXTEST2)


and got

Plan hash value: 3034616292                                                                                                                                                                             
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
|   0 | SELECT STATEMENT                |                 |      1 |        |       |   708 (100)|          |    500 |00:00:11.64 |     766 |                                                           
|   1 |  HASH UNIQUE                    |                 |      1 |    500 |  6000 |   708   (5)| 00:00:09 |    500 |00:00:11.64 |     766 |                                                           
|*  2 |   HASH JOIN                     |                 |      1 |    500K|  5859K|   693   (3)| 00:00:09 |    499K|00:00:10.38 |     766 |                                                           
|   3 |    VIEW                         | VW_DTP_6945D498 |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                           
|   4 |     HASH UNIQUE                 |                 |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                           
|   5 |      BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1    |      1 |        |       |            |          |    500 |00:00:00.01 |     258 |                                                           
|   6 |    BITMAP CONVERSION TO ROWIDS  |                 |      1 |    999K|  5859K|   450   (0)| 00:00:06 |    999K|00:00:02.35 |     508 |                                                           
|   7 |     BITMAP INDEX FAST FULL SCAN | IDXTEST2_IDX1   |      1 |        |       |            |          |   1000 |00:00:00.01 |     508 |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("ITEM_1"="ID2")

So I successfully got rid 1st BITMAP CONVERSION TO ROWIDS operation
but have quite ineffective HASH JOIN (line 2) which produced a big data set eliminated by HASH UNIQUE (line 1), and I still have 2nd “BITMAP CONVERSION TO ROWIDS”

So I have rewritten a bit more as


select id2 FROM (select distinct id2 FROM IDXTEST t1) t11 WHERE t11.id2
IN (SELECT t2.id2 FROM IDXTEST2 t2)

and corresponding execution plan is a bit better.

Plan hash value: 174643609                                                                                                                                                                              
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                      | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                              
------------------------------------------------------------------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT               |               |      1 |        |       |   693 (100)|          |    500 |00:00:04.42 |     533 |                                                              
|*  1 |  HASH JOIN SEMI                |               |      1 |    500 |  9500 |   693   (3)| 00:00:09 |    500 |00:00:04.42 |     533 |                                                              
|   2 |   VIEW                         |               |      1 |    500 |  6500 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                              
|   3 |    HASH UNIQUE                 |               |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                              
|   4 |     BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1  |      1 |    499K|  2929K|   225   (0)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                              
|   5 |   BITMAP CONVERSION TO ROWIDS  |               |      1 |    999K|  5859K|   450   (0)| 00:00:06 |    499K|00:00:01.11 |     275 |                                                              
|   6 |    BITMAP INDEX FAST FULL SCAN | IDXTEST2_IDX1 |      1 |        |       |            |          |    500 |00:00:00.01 |     275 |                                                              
------------------------------------------------------------------------------------------------------------------------------------------                                                              
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - access("T11"."ID2"="T2"."ID2")                                                                                                                                                                   

Here I’ve got initially desired access to the first table
but still ineffective access to second 2

and finally I have transformed my query as

select id2 FROM (select distinct id2 FROM IDXTEST t1) t11
WHERE id2 IN (SELECT t22.id2
FROM (SELECT /*+NO_MERGE */ distinct t2.id2 FROM IDXTEST2 t2) t22);


I obtained plan I was looking for initially

-------------------------------------------------------------------------------------------------------------------------------------------                                                             
| Id  | Operation                       | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                             
-------------------------------------------------------------------------------------------------------------------------------------------                                                             
|   0 | SELECT STATEMENT                |               |      1 |        |       |   719 (100)|          |    500 |00:00:00.07 |     766 |                                                             
|*  1 |  HASH JOIN SEMI                 |               |      1 |    500 | 13000 |   719   (7)| 00:00:09 |    500 |00:00:00.07 |     766 |                                                             
|   2 |   VIEW                          |               |      1 |    500 |  6500 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                             
|   3 |    HASH UNIQUE                  |               |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                             
|   4 |     BITMAP INDEX FAST FULL SCAN | IDXTEST_IDX1  |      1 |    499K|  2929K|   225   (0)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                             
|   5 |   VIEW                          | VW_NSO_1      |      1 |   1000 | 13000 |   479   (7)| 00:00:06 |    941 |00:00:00.03 |     508 |                                                             
|   6 |    VIEW                         |               |      1 |   1000 |  2000 |   479   (7)| 00:00:06 |    941 |00:00:00.02 |     508 |                                                             
|   7 |     HASH UNIQUE                 |               |      1 |   1000 |  6000 |   479   (7)| 00:00:06 |    941 |00:00:00.02 |     508 |                                                             
|   8 |      BITMAP INDEX FAST FULL SCAN| IDXTEST2_IDX1 |      1 |    999K|  5859K|   450   (0)| 00:00:06 |   1000 |00:00:00.01 |     508 |                                                             
-------------------------------------------------------------------------------------------------------------------------------------------                                                             
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - access("ID2"="ID2")                                                                                                                                                                              

Advertisements

Anomaly in “BITMAP CONVERSION TO ROWIDS” estimation

Let’s create our test harness first:

CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad(‘a’,399,’a’) filler
from dual connect by level <2e6
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);

I’ve created a table with column id2 having a single unique value per 1000 rows.

Let’s have a look at 2 execution plans (of 2 a bit different queries):
1st one uses bitmap index to return all rows for given indexed column

SQL_ID  bv039034q966g, child number 1                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select id2 FROM IDXTEST                                                                                                                                                                                 
                                                                                                                                                                                                        
Plan hash value: 1842016195                                                                                                                                                                             
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   900 (100)|          |                                                                                                           
|   1 |  BITMAP CONVERSION TO ROWIDS |              |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |       |       |            |          |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                    

Second one returns distinct value of bitmap indexed column:

SQL_ID  0gtn94fcg82hj, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select distinct id2 FROM IDXTEST                                                                                                                                                                        
                                                                                                                                                                                                        
Plan hash value: 473080087                                                                                                                                                                              
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   949 (100)|          |                                                                                                           
|   1 |  HASH UNIQUE                 |              |  2000 | 12000 |   949   (6)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                        

You can see that Oracle estimate the 2nd query a bit higher and it doesn’t estimate “BITMAP CONVERSION TO ROWIDS” at all which is very suspicious for me as that operation on big datasets can be both CPU and IO(temp) intensive.
Let’s see what is in reality?

Let’s repeat our exercise one more time with ALTER SESSION SET statistics_level=ALL
And show execution plan as select * from TABLE(dbms_xplan.display_cursor(null,null,’typical +iostats last’));
instead of select * from TABLE(dbms_xplan.display);

So here is my results:
1st execution:

SQL_ID  bv039034q966g, child number 2                                                                                                  
-------------------------------------                                                                                                  
select id2 FROM IDXTEST                                                                                                                
                                                                                                                                       
Plan hash value: 1842016195                                                                                                            
                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   900 (100)|          |   1999K|00:00:05.93 |    2012 | 
|   1 |  BITMAP CONVERSION TO ROWIDS |              |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   1999K|00:00:05.93 |    2012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |        |       |            |          |   2000 |00:00:00.05 |    2012 | 
---------------------------------------------------------------------------------------------------------------------------------------

2nd execution:

SQL_ID  0gtn94fcg82hj, child number 1                                                                                                  
-------------------------------------                                                                                                  
select distinct id2 FROM IDXTEST                                                                                                       
                                                                                                                                       
Plan hash value: 473080087                                                                                                              
                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
--------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   949 (100)|          |   2000 |00:00:00.02 |    1012 | 
|   1 |  HASH UNIQUE                 |              |      1 |   2000 | 12000 |   949   (6)| 00:00:01 |   2000 |00:00:00.02 |    1012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   2000 |00:00:00.01 |    1012 | 
--------------------------------------------------------------------------------------------------------------------------------------- 

You can see that in reality 2nd execution is faster more than 100 times (0.02 sec vs 5 sec).
It seems a severe limitation of Oracle optimizer that can block potentially very beneficial (from performance perspective) execution plans. I’m going to publish some “near real life” example in my next blog.

No Parse execution and on the fly DDL change

There was an interesting question on PGDay17

“What is going on if we have a cursor cached on server side and suddenly (for the client) underlying DDL changed (added column, changed column type, etc. etc.).  I was claiming that an additional round trip would happened with re-parse request, but I was wrong!

I made an experiment and here is results:

“usual execution“: w/o DDL change for the underlying table,
That is an execution after cursor have been cached both on server and client side, actually I have not closed the cursor, but reused the same cursor:

execution after DDL: I’ve added a single column to the underlying table:
It is the same cursor and the same round of execution as above, but I’ve just added a column.

You can see Oracle sends the same packet as after 1st execution and  parsing so client aware of a new column.

For just in case the very 1st query execution result is here:

PS.
Oracle resend column definitions to client after any changes that invalidate underlying cursor. For example I executed ALTER TABLE … DEALLOCATE UNUSED and observed the same effect.

 

Remote daemon autot

When you do performance troubleshooting and investigation it is quite common to measure some “deltas” (for example v$sesstat deltas) . It is also important that your measurement do not posses a “quantum” effect :), i.e.  doesn’t influence your measured process. Sometimes you can neglect such effects, sometimes (for example for parsing investigations) when you measure a tiny effect it can be quite annoying.

So I decided to implement (and  just did it)  a small daemon to measure session stats on behalf of my “main” session.

The idea is quite simple:

1. You have a pretty small  web server which keeps connection/s with the same database as your main session.
2. You communicate with the daemon with a very underutilized sqlplus feature – “remote scripting” , i.e. you can call *.sql (or other file) not only from your local machine but rather from any other place on the network (like http://localhost:7777/some.sql).
3. Your daemon keeps your “main session” v$sesstat snapshots and gives you back “delta” results.

You can download the daemon from here:

https://github.com/drema201/RDAutot/tree/master/build/distributions

to run the daemon you need:

RDAutot.bat scott/tyger@localhost:1521:ORCL  [optional port]

and to use it from sqlplus:

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

select count(1) from dba_tables;

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

PS

statname takes as input REGEXP_LIKE expression and unfortunately you need to do URLEncode by yourself  😦 (Oracle http client inside sqlplus seems very primitive and in many cases just truncates http request after unknown character)

 

TNS protocol decypher – 1st execution

Here you can see a network packet for the 1st execution of the cursor:

• cursor number =0 – as no cursor has been opened on server side so far
• 0x0193 = 403 – a length of my SQL statement (SELECT /* ….. FROM YYYTEST …..)
• 0x0A = 10 – prefetch size for the cursor (default JDBC prefetch size)
• 0x28 = 40 – a size of my bind variables array, i.e. :f1, :f2, …., :f40

and after the sql text itself you can see a section of bind definitions:

My presentation on RuOUG meetup

Hi guys, sorry for delay.
I had to perform some bureaucracy actions before publish it.

Dances_with_cursors_Ext

Further TNS protocol decypher