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.

Advertisements

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. Some of these are my real life examples, which I hope, you would find interesting. Comments are always a welcome. The Technical Observations & Views here are my own and not necessarily those of Oracle or its affiliates. These are purely based on my understandings, learnings and resolutions of various customer issues.

4 Responses to SQL Optimization ! Yet another example ……

  1. Mahesh Padhmanabhan says:

    Thanks for Sharing vivek.
    One doubt. the cost of the FTS query is less than that of index.
    also elapsed time 5sec vs 8 sec.

    Like

  2. Rohan Naik says:

    Hi Sir, But the cost of index scan is greater than that of full table scan. So it will hit the performance of database. Need your comment on this.

    Like

    • Vivek Sharma says:

      Rohan, in the demo case, the table was created on my Laptop with a small subset of data. Therefore, the emphasis should not be on the cost. The idea is to demonstrate a better rewrite example. On production, the FTS query was taking longer, with huge I/O’s and the index path was faster with very few I/O’s.

      Like

  3. Pingback: Top 50 Oracle SQL Blogs for 2016 - Complete IT Professional

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s