Re: index with very high LIO

  • From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • To: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
  • Date: Mon, 21 Nov 2011 12:16:34 -0800

- could you leverage a parallel NLJ to engage more CPUs on the problem
to reduce the run time? I'm assuming that if you have such a high BCHR
it must be a CPU bound query then, right? How many CPUs do you have? (
I guess that depends on how fast you need this to run and how much
resources are available).
- do they use RMAN or back this up via filer snaps?
- what did you change to get different CBO cardinality estimates?  Stats?
- any chance of partitioning helping out?

Can you put a number on HUGE [buffer cache]?  My HUGE may be different
than your HUGE.  :D

I do agree with you that if you can only get 35MB/s then this system
can't really leverage PX FTS the way a more modern system could (like
a 2 socket Xeon with 1-2 GB/s).

On Mon, Nov 21, 2011 at 11:46 AM, Jeremy Schneider
<jeremy.schneider@xxxxxxxxxxxxxx> wrote:
> Ok, here are a few useful figures.  I think I'm finally doing my math
> right.  For this customer:
>   35 MB/s throughput (parallel direct path)
>   6 ms response times
>   400GB tables
> time to full-scan the table:
>   400GB x 1024 = 409600 MB
>   409600 MB / 35 = 11703 sec
>   11703 / 60 / 60 = 3.25 hrs
> how many rows in 3.25 hrs with range scan (about 1.2 blocks per row)?
>   6 ms / 1000 = 0.006 sec/block
>   1.2 blocks/row * 0.006 = 0.0072 sec/row
>   11703 sec / 0.0072 = about 1.6 million rows in 3.25 hrs
> how many rows in 3.25 hrs with index join (about 4 LIOs per row)?
>   4 blocks/row * 0.006 = 0.024 sec/row
>   11703 sec / 0.024 = about 500,000 rows in 3.25 hrs
> I guess there's also a case where you can hash join using the index, but i'm
> skipping that one for now...
> ==========
> However there's another factor skewing things - it's only the rows *from
> disk* that count - and this customer has a HUGE buffer cache, and were
> seeing over 90% BCHR on some of these million-row index-based executions.
> The direct path FTS will never benefit from the buffer cache.  So accounting
> for this cache effect, the breakpoints become 5 million for the second table
> and 16 million for the first.  This lines up with testing we've done.  It
> seems that index path is still most efficient for the first table.  And
> quite conveniently, as soon as the CBO got cardinality estimates right, it
> picked the index on the first table and the FTS on the second.  :)
> Regarding their lack hardware, it's worth pointing out that this is the
> first and only query I'm aware of to get millions of rows on this system.
> Also, I believe that the data volume has been growing over the past few
> months - so it's only recently that this solo query moved close to the 10
> million row range.  It's a *very* busy transactional system - so total
> throughput isn't a major point.  And 6ms is decent response time.
> My initial 30MB/s *was* a rough estimate from elapsed time and data sizes...
> I was only looking at trace files to verify the measurement... also the
> first test ran in the middle of the night and I wasn't at the console.  :)
> I reran some small tests today.  Had trouble with iptraf; seemed to give me
> numbers that were clearly wrong... but I did get byte-counts from ifconfig
> and verified about 30-35 MB/s max throughput on the adapter during a
> parallel run.

Greg Rahn

Other related posts: