Relevant for Oracle until 10g
r1 - see the update below.
One possible way to check if
the table is empty or not is to count the rows. Of course not counting all rows,
that would be an overkill for large tables. The old trick ROWNUM = 1 can be
used. So the nice select
SELECT count(*) FROM
test WHERE ROWNUM = 1;
gives both, the test (if you
get 1 you know there are some rows in the table; receiving 0 the table is
guarantied empty) and the performance – only the first row (possible with
little overhead) is read from the table.
This approach was long time
successfully used in a not specified productive application. See the AUTOTRACE
log to verify the low resource consumption of the query:
SQL> select
count(*) from TEST where rownum = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=40145 Card=1)
1
0 SORT (AGGREGATE)
2
1 COUNT (STOPKEY)
3
2 PARTITION RANGE (ALL)
(Cost=40145 Card=7639515)
4
3 TABLE ACCESS (FULL) OF
'TEST' (TABLE) (Cost=40145 Card=7639515)
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
379
bytes sent via SQL*Net to client
504
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
1
rows processed
OK, 4 LIO may be more that
necessary to get one row, but it is acceptable.
Suddenly, one day ugly
problems appeared. The application is simple “hanging”, the response time
explodes. Why? There was no logical explanation. The application was deployed
long long time ago and above all nothing was changed! So what’s the matter?
An Oracle tuning expert was
called to determine the reason what’s causing the application to hang. The
quick analyse pointed to the rownum=1 select. Compare the previous AUTOTRACE
with the new one:
SQL> select
count(*) from TEST where rownum = 1;
Elapsed: 00:00:19.05
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21562 Card=1)
1
0 SORT (AGGREGATE)
2
1 COUNT (STOPKEY)
3
2 PX COORDINATOR
4
3 PX SEND* (QC (RANDOM))
OF ':TQ10000' (Cost=21562 Card=7639515)
:Q1000
5
4 PX BLOCK* (ITERATOR)
(Cost=21562 Card=7639515) :Q1000
6
5 TABLE ACCESS*
(FULL) OF 'TEST' (TABLE) (Cost=21562 Card=7639515) :Q1000
4 PARALLEL_TO_SERIAL
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
217
recursive calls
3
db block gets
100678
consistent gets
100260
physical reads
668 redo size
379
bytes sent via SQL*Net to client
504
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
10
sorts (memory)
0
sorts (disk)
1
rows processed
SQL>
Well, it is not funny to need
more than 100K PIO to get one row. Luckily the Oracle tuning expert give us the
solution. The main difference in the output of the AUTORACE consist of:
|
Line of AUTOTRACE |
Explanation of the
consultant |
|
6 PARALLEL_COMBINED_WITH_PARENT |
The father does the work
for the child |
|
5
PARALLEL_COMBINED_WITH_CHILD |
The children must do the
whole work for the parent |
|
4
PARALLEL_TO_SERIAL |
The parallel work is done |
OK, this is plausible explanation,
but why the response time explodes from zero to twenty seconds?
|
Line of AUTOTRACE |
Explanation of the
consultant |
|
PX COORDINATOR |
I’m the parallel
coordinator, I divide and conquer the work to be done. My slaves shall full scan all
partitions. |
|
COUNT (STOPKEY) |
After I receive the first
row the show is off |
You see, if those two guys
perform in the wrong order, there is lot of idle work to be done.
Caution: This is a true story
not an imagination.
Tested in 9i and 10g. The
table must be partitioned and a count(*) must be used (select * from and anon
partitioned table work fine). The troubles starts if a default degree of
parallelism of the table (DEGREE) is
set to a value greater than 1. A parallel execution plan is deployed, causing
the excessive scan of the full table.
After resetting the DOP to 1
the world was again in Ordnung.
alter table TEST parallel (degree 1);
Another example that breaks
the rule that parallel option equals to an effective and quick solution.
The moral of the story:
Don’t use a default
parallelism as simple “speed up” imperative! Consider each query if parallelism
is appropriate or not.
original version 10.10.2004
Updated 18.9.2006
The behavior was corrected in
10g r2 (tested in 10.2.0.1.0)
While testing this
"feature" in 10g r2 the first difference one notes is the change of
the format of the execution plan. The DBMS_XPLAN output is used to present the
explain plan. The more important thing is the line with ID = 5 in the execution
plan; an additional COUNT STOPKEY operation. This operation limits the number
of accessed records performed by each parallel slave. As a result no parallel full table scan as above is performed.
This is a good news. Though the
moral of the story remains valid event in 10g r2 - use parallelism only if appropriate!
Test case in 10.2.0.1.0
SQL> SELECT /*+
parallel(t,3) */ count(*) FROM test t
WHERE ROWNUM = 1;
Execution Plan
----------------------------------------------------------
Plan hash value:
1951569122
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Cost (%CPU)| Time | Pstart| Pstop | TQ
|IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3182
(2)| 00:01:04 | | | | | |
| 1 |
SORT AGGREGATE | | 1 | | | | | |
| |
|* 2 |
COUNT STOPKEY | | | | | | | | | |
| 3 |
PX COORDINATOR | | | | | | | |
| |
| 4 |
PX SEND QC (RANDOM) | :TQ10000 |
7639K| 3182 (2)| 00:01:04 | | | Q1,00 | P->S | QC (RAND) |
|* 5 |
COUNT STOPKEY | | | | | | | Q1,00 | PCWC | |
| 6 |
PX BLOCK ITERATOR |
| 7639K| 3182
(2)| 00:01:04 | 1 | 5 |
Q1,00 | PCWC | |
| 7 |
TABLE ACCESS FULL| TEST
| 7639K| 3182
(2)| 00:01:04 | 1 | 5 |
Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
2 -
filter(ROWNUM=1)
5 -
filter(ROWNUM=1)
Statistics
----------------------------------------------------------
9
recursive calls
0
db block gets
27
consistent gets
82
physical reads
0
redo size
411
bytes sent via SQL*Net to client
385
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
5
sorts (memory)
0
sorts (disk)
1
rows processed
Jaromir D.B. Nemec
The author
is a freelancer specialized on Oracle
based decision support systems. He can be reached on http://www.db-nemec.com