AIOUG Performance Tuning Day ! Hyderabad…

AIOUG is hosting a full day Performance Tuning day on 30th May 2015 in Hyderabad. I will be presenting on Oracle Query Optimizer and Performance. These sessions will be a mix of technical demos and real life examples. Hope to see a large gathering. Registrations are open at Performance Tuning Day.

Auto DOP ! 11g Feature…

Recently, during one of the Performance POC on an Oracle Engineered System, I got to implement Auto DOP feature for one of their critical Batch Job. This Batch job is a a set of multiple processes. Some of the processes run as a Serial Process, some these are just one process, but the query is hinted to use Oracle Parallelism and some of these are parallel application threads.

For AUTO DOP to work, there are two mandatory settings. These are IO Calibration and setting of parallel_degree_policy (which defaults to MANUAL, means no AUTO DOP). I started with AUTO settings as LIMITED requires changing DEGREE for all (or critical huge) tables. AUTO setting means, Optimizer will calculate and come out with the Degree of Parallelism. Further, this setting also enables two features: Parallel_Statement_Queuing and In-Memory Parallel Execution. In-Memory Parallel execution might disable Exadata Smart Scan, therefore, I modified _parallel_cluster_cache_policy to ADAPTIVE i.e no In-Memory Parallel Execution. IO Calibration was done, so that, optimizer can take the advantage of various values (as can be seen from DBA_RSRC_IO_CALIBRATION) to come out with Degree of Parallelism.

My initial test run was not that successful. While investigating this, I came across a very informative article from Gwen Shapira. As per this article, for AUTO DOP considers MAX_PMBPS value only and this can be set manually to 200. In my case, since the calibration process was executed, the value for MAX_PMBPS was around 3000+. As I understand, this value depicts the throughput and therefore, larger the value, lower the DOP will be. I followed the advice in the article and deleted the rows from resource_io_calibrate$.

delete from resource_io_calibrate$;
insert into resource_io_calibrate$ values(current_timestamp, current_timestamp, 0, 0, 200, 0, 0);
commit;

Post setting this value manually, the database needs a restart. This change did the trick. The performance of many of the processes improved drastically. For some of the queries, we could also see Statement Queuing working effectively i.e. waiting for the resources to be available and taking up the required DOP, rather than downgrading the parallel degree. However, the customer was bit apprehensive about AUTO word and therefore, decided to Implement the optimizer calculations of DOP manually into each of these queries and disabling this feature. This change worked as well.

My observations from this nice feature (and I may be wrong as I did not get much time to investigate or work on this), to name a few are :

  • Any Query that takes more than 10 Seconds (Optimizer Estimation) will be subject to Auto DOP
  • A Query with parallel hint will also go through serial execution run time evaluation. This means, if optimizer estimates the runtime of serial execution to be less than 10 seconds, parallel hint will be ignored.
  • In my opinion, AUTO DOP is one of the best innovation.

    CPU Cycles for Column Skipping…

    I wanted to write this long back, however, got busy with some critical Performance POC on an Oracle Engineered System and this took much of my time.

    Database Design requires careful planning. This blog of mine is on Table structure and the strategy for placement of columns. Whether to place NULL columns at the end to save on Storage space or to place frequently accessed columns at the top, are the two dominant discussions. This blog will focus on the motivational reasons for placing frequently queried columns at the top of the table design.

    For the demonstration purpose, I will create two tables T1 & T2. Both tables are exactly the same except for one column N2, which is a 3rd column of T1 and 13th Column of table T2. We will run a query against both the tables and the resulting output will be exactly same as well. However, the amount of work required for a query on T2 will more than T1.

    create table T1 as
    select 	level ID, mod(level,2) N1, mod(level,10) N2,  mod(level,100) N3, mod(level,1000) N4, 
    mod(level,1000) N5, mod(level,10000) N6, mod(level,5) N7, mod(level,50) N8, mod(level,500) N9, 
    case when mod(level,10000)=0 then 'AIOUG' else dbms_random.string('A',10) end V6,
    mod(level,5000) N10, mod(level,50000) N11
    from dual connect by level <= 100000;
    
    create table T2 as
    select 	level ID, mod(level,2) N1, mod(level,100) N3, mod(level,1000) N4, mod(level,1000) N5,
    mod(level,10000) N6, mod(level,5) N7, mod(level,50) N8, mod(level,500) N9, 
    case when mod(level,10000)=0 then 'AIOUG' else dbms_random.string('A',10) end V6,
    mod(level,5000) N10, mod(level,50000) N11,
    mod(level,10) N2	
    from dual connect by level <= 100000;
    
    exec dbms_stats.gather_table_stats(user,'T1');
    exec dbms_stats.gather_table_stats(user,'T2');
    
    select owner, num_rows, blocks, last_analyzed from dba_tables where table_name ='T1';
    
    OWNER		       NUM_ROWS     BLOCKS DEGREE     LAST_ANALYZED
    -------------------- ---------- ---------- ---------- --------------------
    SCOTT			 100000        846	    1 30-APR-2015 13:42:24
    
    select owner, num_rows, blocks, last_analyzed from dba_tables where table_name ='T2';
    
    OWNER		       NUM_ROWS     BLOCKS DEGREE     LAST_ANALYZED
    -------------------- ---------- ---------- ---------- --------------------
    SCOTT			 100000        846	    1 30-APR-2015 13:42:25
    

    The two tables are identical in terms of number of rows and blocks. The only difference is the column ordering.

    select column_id, column_name, num_distinct, num_nulls from user_tab_columns where table_name='T1' order by 1;
    
     COLUMN_ID COLUMN_NAME			  NUM_DISTINCT	NUM_NULLS
    ---------- ------------------------------ ------------ ----------
    	 1 ID					100000		0
    	 2 N1					     2		0
    	 3 N2					    10		0 <-- Column # 3
    	 4 N3					   100		0
    	 5 N4					  1000		0
    	 6 N5					  1000		0
    	 7 N6					 10000		0
    	 8 N7					     5		0
    	 9 N8					    50		0
    	10 N9					   500		0
    	11 V6					100000		0
    	12 N10					  5000		0
    	13 N11					 50536		0
    
    13 rows selected.
    
    select column_id, column_name, num_distinct, num_nulls from user_tab_columns where table_name='T2' order by 1;
    
     COLUMN_ID COLUMN_NAME			  NUM_DISTINCT	NUM_NULLS
    ---------- ------------------------------ ------------ ----------
    	 1 ID					100000		0
    	 2 N1					     2		0
    	 3 N3					   100		0
    	 4 N4					  1000		0
    	 5 N5					  1000		0
    	 6 N6					 10000		0
    	 7 N7					     5		0
    	 8 N8					    50		0
    	 9 N9					   500		0
    	10 V6					 99864		0
    	11 N10					  5000		0
    	12 N11					 50536		0
    	13 N2					    10		0 <-- Column # 13
    
    13 rows selected.
    
    

    To come out with the CPU Calculation for column skip, we will have to first get the CPU_Cost for a Full Table Scan of these tables.

    delete from plan_table;
    explain plan for
    select id,n1 from t1;
    
    /* Blocks = 846 as per dba_tables
    select round(7121.44*&blocks+(150*100000)+(20*100000*(2-1))) from dual; 
    
    ROUND(7121.44*846+(150*100000)+(20*100000*1))
    ---------------------------------------------
    				     23024738
    
    
    SQL> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
    
    OPERATION		       OPTIONS			  COST	 CPU_COST    IO_COST	   TIME
    ------------------------------ -------------------- ---------- ---------- ---------- ----------
    SELECT STATEMENT					   434	 23024738	 425	      1
    TABLE ACCESS		       FULL			   434	 23024738	 425	      1
    
    

    The CPU_Cost for a Full Table Scan of T1 is 23024738. I assume, the other numbers are Oracle calculations and are the defaults. 7121.44 Cpu Cycles per block multiplied by number of blocks. 150 CPU Cycles for each Row and 20 CPU Cycles for Column Skip. So, 20*100000*(2-1) is for 20 CPU Cycles multiplied by Highest ID – Lowest ID (2-1) of the columns referred in the query. Since the two tables are same in terms of number of rows and blocks, the calculation for a FTS should remain same. Lets see..

    delete from plan_table;
    
    explain plan for
    select id,n1 from t2;
    
    /* Blocks = 846 for this table as well */
    select round(7121.44*&blocks+(150*100000)+(20*100000*(2-1))) from dual;
    
    ROUND(7121.44*846+(150*100000)+(20*100000*1))
    ---------------------------------------------
    				     23024738
    
    SQL> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
    
    OPERATION		       OPTIONS			  COST	 CPU_COST    IO_COST	   TIME
    ------------------------------ -------------------- ---------- ---------- ---------- ----------
    SELECT STATEMENT					   434	 23024738	 425	      1
    TABLE ACCESS		       FULL			   434	 23024738	 425	      1
    
    

    CPU_Cost is same for both as the two have same number of blocks, rows and the table is similar in structure until column N1. The CPU Cost of 23024738 will be used in our calculations further and therefore you may want to note down this figure.

    Next, we shall run the same query for the two tables, but with a minor change. We will add a predicate WHERE N2=:b2. Remember, this column differentiate the two tables else everything remains identical.

    
    delete from plan_table;
    
    explain plan for
    select id, n1 from t1 where n2=:b1;
    
    /* 7121.44 is now replaced with the CPU_Cost Value */
    select 23024738+150*100000+(20*100000*(3-2)) from dual;
    
    /* The calculation says */
    23024738+150*100000+(20*100000*(3-2))
    -------------------------------------
    			     40024738
    
    SQL> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
    
    OPERATION		       OPTIONS			  COST	 CPU_COST    IO_COST	   TIME
    ------------------------------ -------------------- ---------- ---------- ---------- ----------
    SELECT STATEMENT					   441	 40024738	 425	      1
    TABLE ACCESS		       FULL			   441	 40024738	 425	      1
    
    

    The CPU Cost has gone up to 40024738 and this is evident as predicate filtering requires CPU Cycles as well. Let us now check for T2.

    delete from plan_table;
    
    explain plan for
    select id, n1 from t2 where n2=:b1;
    
    /* in this case 13 is the ID for n2 */
    select 23024738+150*100000+(20*100000*(13-2)) from dual;
    
    23024738+150*100000+(20*100000*(13-2))
    --------------------------------------
    			      60024738
    
    SQL> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
    
    OPERATION		       OPTIONS			  COST	 CPU_COST    IO_COST	   TIME
    ------------------------------ -------------------- ---------- ---------- ---------- ----------
    SELECT STATEMENT					   449	 60024738	 425	      1
    TABLE ACCESS		       FULL			   449	 60024738	 425	      1
    
    

    This is interesting. The two tables are exactly same with only a difference of column ordering. This difference is good enough for the increased CPU_Cost (60024738 against 40024738) and the Cost of the Query has also gone up from 441 to 449. Don’t get surprised to see the increased cost (and the change in the plan) post adding a new column to a table and including the newly added column in your frequently executed queries.

    This holds true for queries with Index Scan as well. I have a demonstration with Index Scans as well and can be shared on request.

    For my readers – What about a column, which is created as INVISIBLE and later made VISIBLE (post adding many more columns to the table) ? What will be the impact of querying this now VISIBLE column ? Interesting to test this out…

    Table with 255 columns or more…

    I wrote a BLOG on the Intra Row Chaining and the issues around this for a table that has more than 255 columns. Jonathan Lewis has written a very fantastic blog on this subject with some more explanation and a real life example. Worth Reading.

    Jonathan’s Link

    During my recent AIOUG OTN Yathra Tech Day in Chandigarh, I also demonstrated the CPU Cycles required for a Column Skip. This means, apart from the extra I/O required to read a column of a larger table, the CPU consumption will also be higher. Since this topic has come up and Jonathan nicely touching this subject, I would post the CPU calculation Blog in a day or so.

    While the issue due to Intra Block Chaining is already published, the next blog will focus more on the CPU Cycles for column skipping for a small table (say with 14 columns) and then, this can be further co-related for a table with more columns. This will help DBA’s predict or evaluate the impact of the change (adding a new column) before being implemented on production. Also, a reason to have frequently queried on the top (Storage Space v/s CPU Consumption).

    12c Enhancement in Authorization Check Failed issue…

    I am very disappointed to miss the OTN Yathra User Group in Bangalore due to bad health. Not still recovered. Hope to present in Bangalore in near future.

    One of my slide on “Real Life Issues” demonstrated the library cache mutex issue due to multiple child cursors. I wrote about an issue in one of my previous BLOG. The demonstration in the blog was in Oracle 11g. While preparing for the User Group Event, I re-worked on this demonstration in 12c and was surprised to see a critical enhancement. In 12c, with 300 Schemas, there were only 300 Child Cursors (1 for each schema) as against 8000+ in 11g or below. This means, if the customer upgrades to 12c, the issue due to multiple child cursors would surely get resolved. I assume, this improvement is due to the multitenancy feature as multiple pdb’s can have tables with same name and therefore, similar queries across pdb’s.

    Change of Role and Profile….(and so an expansion of Technical learning)

    It’s been long I wrote a blog on my experience with Oracle Database Performance. Thought of writing about the reason for this long break. I moved from a Customer Support Profile to an Asia Pacific Sales Profile. While this is a kind of Sales Profile, I am still a Technical Resource. The change is in the number of production issues that I used to work on under customer support role has come down drastically.  However, this also means that previously when I used to focus only on Oracle Database (working on Latches, mutexes, optimizer, query optimization etc), this profile demands production infrastructure optimization and does not restrict my skillset just to Oracle Database.

    These days, I have been working on Solaris and ZFS Storage extensively. Though these were new technologies for me, my APAC team had been very supportive on getting me up to the speed. Being an Oracle Database expert, this additional knowledge is playing a critical role, as I can now take a top-down approach in solving a customer problem. I remember some of my user group presentations, where I used so present a slide with a production architecture that comprises of “Database – Application – OS” and I used to speak more about Database and Application Optimization.  I am now working towards adding another layer i.e. OS and Storage. Optimal Performance of these stacks are critical as well. So, don’t be surprised, if, in future, I blog on some of these stacks  ;).

    Ah..let me also mention this. The best thing about this role had been my nomination and participation in “Elite Engineering Exchange” program. I could interact with Worldwide Oracle Experts, from all Domains (Server, Storage, Database, Network) and obvious to say, this interaction brought a big change in me.

    AIOUG ! Sangam 14

    The Annual AIOUG Event “Sangam 14″ is scheduled for November 2014. I am presenting this year. The Agenda will be published soon on AIOUG Website. Thomast Kyte will be back in India for this event, along side Maria Colgan from Oracle Optimizer Development Team. Syed Jaffar Husain will also be presenting.

    Looking forward to meet Oracle Community.

    SQL Optimization ! Yet another example ……

    Edited on 30th October 2014, as the Query recommendation had a flaw, which I could test during my preparation for an Event.

    This is another classic example where the Developers needs to know a better way of writing a code. I wrote about one such example in my earlier post. There are many ways of achieving a target, but very few to achieve this in an efficient manner.

    I came across a performance issue, which triggered acute slowness during peak load. This slowness was evident post increase in data volume. A Query, which was doing around 28k Logical I/O’s was not doing around 48K per execution. The concurrency on this query was so high that in a 30 minutes windows (as seen from the AWR reports), the total number of logical I/O’s, cummulative for all executions, was around 1 Billion. In terms of concurrency, I could see that out of 15 Active sessions, 10 sessions were executing this query concurrently, thus consuming high CPU. I will not post the Original query but will demonstrate an example.

    The example is from SH Schema on Customers Table. I have created following Indexes :

    create index customers_cust_city_idx on customers(cust_city);
    create index customers_cust_state_idx on customers(cust_state_province);
    

    The application requirement is to design a screen for users to retrieve Cust_ID and cust_last_name based on queried cust_city and/or cust_state_province. The user has an option to either query on both or either of theses, with no option of leaving both NULL. To get this done, the developers coded the query as mentioned below. I have also pasted the run time execution plan.

    ## With CUST_CITY as NULL
    variable b1 varchar2(32);
    variable b2 varchar2(32);
    exec :b1:='Maharashtra';
    exec :b2:=null;
    
    select cust_id, cust_last_name
    from 	customers
    where	(cust_city=:b2 or :b2 is null)
    and	(cust_state_province=:b1 or :b1 is null);
    
    SQL_ID  554u5htwuan4z, child number 0
    -------------------------------------
    select cust_id, cust_last_name from  customers where (cust_city=:b2 or
    :b2 is null) and (cust_state_province=:b1 or :b1 is null)
    
    Plan hash value: 2008213504
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |       |       |   406 (100)|          |
    |*  1 |  TABLE ACCESS FULL| CUSTOMERS |   162 |  5508 |   406   (1)| 00:00:05 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(((:B2 IS NULL OR "CUST_CITY"=:B2) AND (:B1 IS NULL OR
                  "CUST_STATE_PROVINCE"=:B1)))
    
    ## With CUST_STATE_PROVINCE as NULL
    
    exec :b1:=null;
    exec :b2:='Mumbai Bombay';
    
    SQL_ID  554u5htwuan4z, child number 0
    -------------------------------------
    select cust_id, cust_last_name from  customers where (cust_city=:b2 or
    :b2 is null) and (cust_state_province=:b1 or :b1 is null)
    
    Plan hash value: 2008213504
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |       |       |   406 (100)|          |
    |*  1 |  TABLE ACCESS FULL| CUSTOMERS |   162 |  5508 |   406   (1)| 00:00:05 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(((:B2 IS NULL OR "CUST_CITY"=:B2) AND (:B1 IS NULL OR
                  "CUST_STATE_PROVINCE"=:B1)))
    
    ## With both NON NULL Values
    
    exec :b1:='Maharashtra';
    exec :b2:='Mumbai Bombay';
    
    SQL_ID  554u5htwuan4z, child number 0
    -------------------------------------
    select cust_id, cust_last_name from  customers where (cust_city=:b2 or
    :b2 is null) and (cust_state_province=:b1 or :b1 is null)
    
    Plan hash value: 2008213504
    
    -------------------------------------------------------------------------------
    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |           |       |       |   406 (100)|          |
    |*  1 |  TABLE ACCESS FULL| CUSTOMERS |   162 |  5508 |   406   (1)| 00:00:05 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(((:B2 IS NULL OR "CUST_CITY"=:B2) AND (:B1 IS NULL OR
                  "CUST_STATE_PROVINCE"=:B1)))
    
    

    For each of these executions, the plan was a Full Table Scan of CUSTOMERS Table. Even if the values for both the bind variables were provided, the query optimizer selected a Full Scan and this is because of the way this query is written. A Full Table Scan will impact the performance and as the volume increases, the impact will gradually increase. One of the better way for writing this query would be to have IF ELSE condition, as I have demonstrated in my previous posts. There is another way as well. See below :

    
    variable b1 varchar2(32);
    variable b2 varchar2(32);
    exec :b1:='Maharashtra';
    exec :b2:=null;
    
    select cust_id, cust_last_name, cust_city, cust_state_province
    from 	customers
    where	cust_state_province=:b1
    and	:b2 is null
    union 
    select cust_id, cust_last_name, cust_city, cust_state_province
    from 	customers
    where	cust_city=:b2
    and	:b1 is null
    union
    select cust_id, cust_last_name, cust_city, cust_state_province
    from 	customers
    where	cust_city=:b2
    and	cust_state_province=:b1;
    
    SQL_ID  946qyhrzz882s, child number 0
    -------------------------------------
    select cust_id, cust_last_name, cust_city, cust_state_province from
    customers where cust_state_province=:b1 and :b2 is null union select
    cust_id, cust_last_name, cust_city, cust_state_province from  customers
    where cust_city=:b2 and :b1 is null union select cust_id,
    cust_last_name, cust_city, cust_state_province from  customers where
    cust_city=:b2 and cust_state_province=:b1
    
    Plan hash value: 3095663828
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                          |       |       |   217 (100)|          |
    |   1 |  SORT UNIQUE                       |                          |   280 |  9520 |   217  (42)| 00:00:05 |
    |   2 |   UNION-ALL                        |                          |       |       |            |          |
    |*  3 |    FILTER                          |                          |       |       |            |          |
    |   4 |     TABLE ACCESS BY INDEX ROWID    | CUSTOMERS                |   189 |  6426 |   127   (0)| 00:00:03 |
    |*  5 |      INDEX RANGE SCAN              | CUSTOMERS_CUST_STATE_IDX |   189 |       |     1   (0)| 00:00:01 |
    |*  6 |    FILTER                          |                          |       |       |            |          |
    |   7 |     TABLE ACCESS BY INDEX ROWID    | CUSTOMERS                |    90 |  3060 |    85   (0)| 00:00:02 |
    |*  8 |      INDEX RANGE SCAN              | CUSTOMERS_CUST_CITY_IDX  |    90 |       |     1   (0)| 00:00:01 |
    |   9 |    TABLE ACCESS BY INDEX ROWID     | CUSTOMERS                |     1 |    34 |     2   (0)| 00:00:01 |
    |  10 |     BITMAP CONVERSION TO ROWIDS    |                          |       |       |            |          |
    |  11 |      BITMAP AND                    |                          |       |       |            |          |
    |  12 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |
    |* 13 |        INDEX RANGE SCAN            | CUSTOMERS_CUST_CITY_IDX  |    90 |       |     1   (0)| 00:00:01 |
    |  14 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |
    |* 15 |        INDEX RANGE SCAN            | CUSTOMERS_CUST_STATE_IDX |    90 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter(:B2 IS NULL)
       5 - access("CUST_STATE_PROVINCE"=:B1)
       6 - filter(:B1 IS NULL)
       8 - access("CUST_CITY"=:B2)
      13 - access("CUST_CITY"=:B2)
      15 - access("CUST_STATE_PROVINCE"=:B1)
    
    

    The plan changed from Full Table Scans to Index Scans. Another alternative would be to rewrite the code, using IF ELSE, writing three different queries depending upon the user entries for :b1 and :b2. This may mean, a bit of lengthy code.

    Authorization Check Failed ! Multiple Child Cursors…..

    Recently, got an oppurtunity to work on a Mutex related issue causing database to halt. While the issue started from April 2013, the frequency of this increased since September 2013. This is a Banking Customer and every month, they add few Branches into the system. With the Branches, the concurrency also goes up. This increase in concurrency could be one of the reason of frequent Library Cache Mutex Issue.

    In this case, the issue was found to be due to high number of Child Cursors. The top 4 queries, based on number of child cursors, had around 30k child cursors. From the v$sql_shared_cursor, the reason for these many childs was AUTH_CHECK_MISMATCH, which stands for “Authorization Check Mismatch”. Further investigation revealed this to be an Implementation issue.

    The Bank has around 580 Branches. For Scalability purpose, the Application Team has implemented 290 Branches on One Database and other 280 Branches on another Database. These are named as BDB1 and BDB2. For each of the Branches, they have individual schema. Therefore, in BDB1 and BDB2 they have 290 and 280 schemas respectively. Each Schema has it’s own table, procedure, functions etc…The name of the tables, their columns etc are same as well across each of these schema’s. For example, each of the schema has a table USER_SESSION_LOGIN_DTL with same number and names of the columns. They only differ in data stored within each of these tables. Further, each schema has around 800+ tables. Therefore, a query executed against Branch 1 is executed against Branch 2, due to the textual similarity, they match in HASH Values and therefore share a common parent. The logic at the application level is that if a user connects to the application, the Branch Name is returned based on which, he or she connects to the schema (based on the branch name) and executes the application level business trasaction. Another user from another branch will open the same module but will connect to different schema. Therefore, while the queries are textually same for each of the Branches, since the underlying objects are different, we see multiple child cursors being populated.

    In my view, this is an inefficient Database Design Issue. This will also have a serious manageability issue, as any change in the business logic, will have to be replicated across all the schemas. Any Optimization, i.e. creation of a New Index, will have to be replicated across each of the Schemas. This list can go on…

    This is very easy to demonstrate.

    ## This pl/sql will create 300 Users with names as F1, F2, F3 and so on....
    
    declare
    l_statement	varchar2(100);
    begin
     for i in 1..300
     loop
       l_statement:='drop User F'||i||' cascade';
       execute immediate l_statement;
       l_statement:='Create User F'||i||' identified by F'||i;
       execute immediate l_statement;
       l_statement:='grant dba to F'||i;
       execute immediate l_statement;
     end loop;
    end;
    /
    
    ## Next we create a Table EMP in each of these schemas. In this case, I am inserting same data into these tables.
    
    declare
    l_statement	varchar2(100);
    begin
     for i in 1..300
     loop
       execute immediate 'alter session set current_schema=F'||i;
       l_statement:='create table emp as select * from scott.emp';
       execute immediate l_statement;
     end loop;
    end;
    /
    
    ## Next let us execute a query on each of the emp schemas (randomly). These will be 30000 queries with different Bind Variables.
    
    declare
    l_emp		number;
    l_num		number;
    l_sch		number;
    l_statement	varchar2(1000);
    l_ename		varchar2(32);
    begin
     for i in 1..30000
     loop
       l_num:=round(dbms_random.value(1,14),0);
       l_sch:=round(dbms_random.value(1,300),0);
       execute immediate 'alter session set current_schema=F'||l_sch;
       select empno into l_emp from (
         select empno, row_number() over(order by empno) rno from scott.emp)
       where rno=l_num;
       l_statement := 'select /*+ vivek1 */ ename from emp where empno=:b1';
       execute immediate l_statement into l_ename using l_emp;
     end loop;
    end;
    /
    

    Once the queries are executed, we can query v$sql_shared_cursor to check for the number of child cursors.

    SQL> SQL> @sql_text
    Enter value for 1: select /*+ vivek1
    old   2: from v$sqlarea where sql_text like '&1%'
    new   2: from v$sqlarea where sql_text like 'select /*+ vivek1%'
    
    SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT PLAN_HASH_VALUE
    ------------- -------------------------------------------------- ----------- ---------- ------------- ---------------
    1c9w6s580jtpd select /*+ vivek1 */ ename from emp where empno=:b        5403       1801            30      3956160932
                  1
    
    SQL> select count(*) from v$sql_shared_cursor where sql_id='1c9w6s580jtpd';
    
      COUNT(*)
    ----------
          8084
    
    SQL> select sql_id, child_number, AUTH_CHECK_MISMATCH, reason from v$sql_shared_cursor where sql_id='1c9w6s580jtpd' and rownum<=5;
    
    SQL_ID        CHILD_NUMBER A REASON
    ------------- ------------ - --------------------------------------------------------------------------------
    1c9w6s580jtpd           94 Y 9437Authorization Check f
    1c9w6s580jtpd           95 Y 9537Authorization Check f
    1c9w6s580jtpd           96 Y 9637Authorization Check f
    1c9w6s580jtpd           97 Y 9737Authorization Check f
    1c9w6s580jtpd           98 Y 9837Authorization Check f
    
    

    For 30000 Executions, we have 8000 Child Cursors. This block was executed from a Single Session and no one else working. Imagine the impact of such an implementation in a highly concurrent environment.

    Issue with Autotrace ! Criticality of Using a Right tool…..

    As mentioned in my previous blog, my visit to Oracle Open World was very exciting and enriching. Every session I attended, I got to know something new and therefore thought of sharing this with my readers. I actually wanted to demonstrate this during my Sangam 2013 presentation. However, due to some official commitments, I had to pull out from the event.

    In many of my presentations, especially for Developers, I had covered some concepts (along with the Demonstration) on Bind Peeking and issues with “Explain Plan” or Autotrace, as these are not bind-aware. This means, if a query contains a bind variable and if the execution plan of that query is dependant on the value provided to the bind, then the execution plan displayed (or generated) by “explain plan” / “autotrace” is not guaranted to be the same as that of runtime optimizer. Even today, I see many developers (and DBA’s as well) use either “explain plan for” or “autotrace” utilities to check for the execution plan of the queries they are working on.

    I attended a very good session that was jointly presented by Maria Colgan and Jonathan Lewis. Maria mentioned about an issue with “set autot trace explain”, which has a potential of creating a (sub-optimal) plan and can cause a performance bottleneck as this sub-optimal plan can then be shared by other users.

    Randolf Geist has already published a very good note on this issue and should help my readers understand this as it has been explained with good examples. However, since I was working on the demonstration to be presented during Sangam 2013, thought of sharing this here.

    The demonstration is as under :

    
    ## Table Creation
    
    drop table t1;
    
    create table t1 as
    select a.* from all_objects a, all_objects b
    where rownum<=1e6;
    
    create index t1_idx on t1(temporary);
    
    select temporary, count(*) from t1 group by temporary;
    
    exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1, for columns temporary size 100');
    

    We created a table t1 with 1 Million Rows and an Index on Temporary Column. This Index has been created to demonstrate the difference in the plan when we query data on Temporary Column. Further, in order for the optimizer to generate different plan, we gather histogram on this column. This additional statistics will ensure that optimizer generates / computes the cardinality and the plan based on the input value.

    Next, we execute two queries, one each with a Literal Value of ‘Y’ and a Bind Variable with Y passed as a value to the Bind. In both the case, the Optimizer computes nearly accurate cardinality and Index Scan Access Path. For the execution with Bind, the optimizer peeked into Bind to come out with the cardinality and Index Access Path.

    select /*+ vivek_y */ OWNER, OBJECT_NAME from t1 where temporary='Y';
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------
    SQL_ID  7zfa7mstt63gv, child number 0
    -------------------------------------
    select /*+ vivek_y */ OWNER, OBJECT_NAME from t1 where temporary='Y'
    
    Plan hash value: 546753835
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |       |       |    47 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  2680 | 88440 |    47   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_IDX |  2680 |       |     7   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("TEMPORARY"='Y')
    
    variable b1 varchar2(32);
    exec :b1:='Y';
    
    select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;
    
    SQL_ID  9c5pp1gt64s7q, child number 0
    -------------------------------------
    select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where
    temporary=:b1
    
    Plan hash value: 546753835
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |       |       |    47 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  2680 | 88440 |    47   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_IDX |  2680 |       |     7   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Peeked Binds (identified by position):
    --------------------------------------
    
       1 - :B1 (VARCHAR2(30), CSID=178): 'Y'
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("TEMPORARY"=:B1)
    
    
    

    Let us check for the behaviour of “explain plan for” and “autotrace” utilities. Since these are not bind-aware, the plan displayed & generated will be a Full Table Scan. The computed cardinality is based on #Rows/NDV, which is 1000000/2 = 500000. Also, note the I/O’s (consistent read) as generated by autotrace.

    explain plan for
    select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;
    
    SQL> @utlxpls
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |   500K|    15M|  3968   (2)| 00:00:48 |
    |*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("TEMPORARY"=:B1)
    
    set autot trace
    select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;
    
    2327 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |   500K|    15M|  3968   (2)| 00:00:48 |
    |*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("TEMPORARY"=:B1)
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            841  consistent gets <--- Note the I/O
              0  physical reads
              0  redo size
    
    set autot trace
    

    Last, we execute the query with “set autot trace explain” and the same query with “set autot off”. In this case, while the value passed to the bind is ‘Y’, the optimizer will re-use the plan (Full Table Scan) generated by “set autot trace explain” and this is where the problem starts. With “set autot trace explain”, the plan generated is stored in the Shared Pool and is shared by the same query, if run from an application and this can be a problem. Assuming the query executed next is a part of the application query and a developer runs the exactly same query using “set autot trace explain”, the plan generated will be without bind peek and will be sub-optimal.

    set autot trace explain
    select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;
    
    
    SQL> @ap
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  cbf51h7v1276h, child number 0
    -------------------------------------
    select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1
    
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |  3968 (100)|          |
    |*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("TEMPORARY"=:B1)
    
    
    18 rows selected.
    
    set autot off
    select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;
    
    select plan_table_output from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));
    
    SQL_ID  cbf51h7v1276h, child number 0
    -------------------------------------
    select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1
    
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |  3967 (100)|          |
    |*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3967   (2)| 00:00:48 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("TEMPORARY"=:B1)
    
    new   1: select sql_id, sql_text, executions, buffer_gets, elapsed_time, rows_processed from v$sqlarea where sql_id='cbf51h7v1276h'
    
    SQL_ID        SQL_TEXT                                           EXECUTIONS BUFFER_GETS ELAPSED_TIME ROWS_PROCESSED
    ------------- -------------------------------------------------- ---------- ----------- ------------ --------------
    cbf51h7v1276h select /*+ with_y */ OWNER, OBJECT_NAME from t1 wh          1       14393      3563761           2327
                  ere temporary=:b1
    
    

    The last query is exactly same to the one that was executed with “set autot trace explain” and shared the plan generated by “set autotrace explain”, which is a Full table scan and is not the one that should have been generated. Interestingly, check for the I/O’s of the last Full Table Scan plan query. The query has done 14393 Logical reads, whereas, in one of the previous execution with “autotrace on”, the I/O’s shown was 891 (and the plan says full table scan). Why is there a difference between the two I/O’s of Full Table Scans ? For an explanation, read the blog from Randolf Geist.

    Follow

    Get every new post delivered to your Inbox.

    Join 163 other followers

    %d bloggers like this: