Using window function to reduce consistent get

DB: Oracle 11.2.0.3
OS: Redhat Linux 6.3 x86_64

Requirement

Table HISTOGRAM_TEST_2 has a number column status. We need to get the min and max value as well as their counts.

Method 1 Nest Loop

WITH min_max AS
(
select min(status) min_status, max(status) max_status
from histogram_test_2
)
select status, count(*) ct
from histogram_test_2, min_max
where histogram_test_2.status = min_max.min_status
or histogram_test_2.status = min_max.max_status
group by status;
Execution Plan
----------------------------------------------------------
Plan hash value: 1082392014

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22768 | 867K| 910 (3)| 00:00:11 |
| 1 | HASH GROUP BY | | 22768 | 867K| 910 (3)| 00:00:11 |
| 2 | NESTED LOOPS | | 22768 | 867K| 908 (3)| 00:00:11 |
| 3 | VIEW | | 1 | 26 | 454 (3)| 00:00:06 |
| 4 | SORT AGGREGATE | | 1 | 13 | | |
| 5 | TABLE ACCESS FULL| HISTOGRAM_TEST_2 | 1144K| 14M| 454 (3)| 00:00:06 |
|* 6 | TABLE ACCESS FULL | HISTOGRAM_TEST_2 | 22768 | 289K| 454 (3)| 00:00:06 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - filter("HISTOGRAM_TEST_2"."STATUS"="MIN_MAX"."MIN_STATUS" OR
"HISTOGRAM_TEST_2"."STATUS"="MIN_MAX"."MAX_STATUS")

Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
3436 consistent gets   <---------- Note the consistent get number
0 physical reads
0 redo size
649 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

Method 2 Window Function

select     status,
ct
from (
select    sum(ct) over() sample_size,
min(status) over() min_status,
max(status) over() max_status,
status,
ct
from (
select      status,
count(*) ct
from       histogram_test_2
group by  status
)
)
where status = min_status
or status = max_status;
Execution Plan
----------------------------------------------------------
Plan hash value: 3090043826
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1144K| 56M| 516 (15)| 00:00:07 |
|* 1 | VIEW | | 1144K| 56M| 516 (15)| 00:00:07 |
| 2 | WINDOW BUFFER | | 1144K| 14M| 516 (15)| 00:00:07 |
| 3 | HASH GROUP BY | | 1144K| 14M| 516 (15)| 00:00:07 |
| 4 | TABLE ACCESS FULL| HISTOGRAM_TEST_2 | 1144K| 14M| 454 (3)| 00:00:06 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STATUS"="MIN_STATUS" OR "STATUS"="MAX_STATUS")

Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1617 consistent gets   <--- Consistent get dropped significantly
0 physical reads
0 redo size
649 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
Advertisements

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