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.