Re: slow full table scanning + partitioning qq on

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: cosmini@xxxxxxxxxxxxxxx
  • Date: Fri, 17 Nov 2006 16:50:19 +0200

FULL SCANs can do completely different things behind the scenes:
1) It can be FULL SCAN without anything more for example select * from big
2) It can be FULL SCAN with some static filter for example select *
from big where col1 = 1
3) It can be FULL SCAN with some dynamic filter for example select *
from big where my_very_flexible_and_elegant_user_defined_function(col1)
= my_very_flexible_and_elegant_user_defined_function2(col1)
4) It can be FULL SCAN with hidden nested loops join in it
5) and so on so on

So here are two query examples of my table big with index on owner and
line (which actually is dba_source * 16 :)
   FROM big
with following plan
Execution Plan
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3916 Card=1 Bytes=12)
  1    0   SORT (GROUP BY)
  2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
SELECT /*+  FULL (a) USE_NL (a b) */ count(DISTINCT a.type)
   FROM big a, big b
   WHERE a.owner = b.owner
     AND a.line = b.line
with following plan
Execution Plan
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1496480 Card=1 Bytes=38)
  1    0   SORT (GROUP BY)
  2    1     NESTED LOOPS (Cost=1496480 Card=6036307 Bytes=229379666)
  3    2       TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
  4    2       INDEX (RANGE SCAN) OF 'BIGIDX' (NON-UNIQUE) (Cost=1
Card=4 Bytes=52)

First query takes about 30 secs, but second more than 30 minutes
Both queries show only one full scan in v$session_longops and both
have the same number of 72061 blocks in totalwork column
But actually THE WORK BEHIND THE SCENES is very different.

I'm almost 100% sure that I've seen the same about hash joins, but
haven't time to make a test case just now...

So look on explain plans and/or in 10046 trace output to check your
queries are doing something more than just a plain full scan or hash

Gints Plivna

2006/11/16, cosmin ioan <cosmini@xxxxxxxxxxxxxxx>:
hey Dennis/All,

no, there's no partition pruning since the 'where' clause does not contain
the partition key.
I really don't care about the join, I think... as the engine did not even
get to that stage.... In the first step, it was still scanning (FTS) the
initial table (which on the first test, it was scanning the table a whole
lot faster).

Second QQ:  this is just index scanning and hash joining,  which again, I'm
noticing hash joining done a lot faster (#blocks/second) at times vs. other
times when it's painfully slow...

I probably need to take these tables and run an identical test on different
hardware, same Oracle version or compare against other versions  ;-)

thx for any feedback, gents,

Dennis Williams <oracledba.williams@xxxxxxxxx> wrote:

Have you verified that in the join situation that partition pruning is still
What type of join is occurring? Yes, not a surprise that joining two tables
and doing a FTS on both is potentially much slower than a straight query on
a single table. Need more details to diagnose.

QQ2: Your question is not clear. Is this a FTS or indexed access? Usually we
start with the number of rows in each table (or partition if we can perform
partitioned access, then the number of rows you must retrieve from each
table. This can give you a decision on which table you desire Oracle to use
as the driving table. Then we can get into FTS vs. indexes, etc.

Dennis Williams

On 11/16/06, cosmin ioan <cosmini@xxxxxxxxxxxxxxx> wrote:
> hi guys,
> I have a partitioned table, which, when queried on columns not indexed, by
itself, scans the table very fast, hundreds of blocks per second, or so...
> Same partitioned table, joined with another table, the full table scan
operation (on that partitioned table) that starts as the first process, is
going a whole lot slower, basically low digits blocks per second....
> Is this indicative of something at the hardware level or data  dictionary?

> I'm not even dealing with indexes here, just full table scans, and tried
to reduce a pretty complex problem to just this simplest of tests, so this
is where I'm having performance problems.
> QQ2:  joining two partitioned tables, both partitioned by a date field,
however, one table, half partitioned by quarter (range), to the most recent
quarter, then monthly, from then on, another one, fully partitioned by
month, is Oracle going to have an extremely hard time joining the partitions
(I do not have parallel set on any table and I'm just doing a full table
scan).   -- to me, it appears that it does not make any difference but it
may make a difference, when querying by a local index which might be
hashed/joined by another local index from the other table....  is my
assumption correct?
> thx much,
> Cosmin


Other related posts: