Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!
From: Gerard H. Pille on 9 Sep 2009 05:48
Hallo Charles,

I'd like to see your statistics for this select:

SELECT /*+ index (t1 ind_t1) */
ID,
DESCRIPTION
FROM
sys.T1 t1
WHERE
ID >= 9991
/

I suppose you were aware of the skew in the index, but how does this
account for the difference in performance?

On my old PC - with a new disk - the FTS takes 2 minutes, the IS 20
minutes, selecting id between 1 and 400 via index takes over 3 hours.

Kind regards,

Gerard

From: Charles Hooper on 9 Sep 2009 20:12
On Sep 9, 5:48 am, "Gerard H. Pille" <ghpi...(a)hotmail.com> wrote:
> Hallo Charles,
>
> I'd like to see your statistics for this select:
>
> SELECT /*+ index (t1 ind_t1) */
>   ID,
>   DESCRIPTION
> FROM
>   sys.T1 t1
> WHERE
>   ID >= 9991
> /
>
> I suppose you were aware of the skew in the index, but how does this
> account for the difference in performance?
>
> On my old PC - with a new disk - the FTS takes 2 minutes, the IS 20
> minutes, selecting id between 1 and 400 via index takes over 3 hours.
>
> Kind regards,
>
> Gerard

Yes, there is a skew in the data - it should be more densely packed at
each end of the range.

Here is a slightly modified version of the script you requested I run
so that we are able to see the costs, and potentially some of the
effects of an unset filesystemio_options parameter by executing the
same query a second time:
set linesize 150
set pagesize 2000
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
SET ARRAYSIZE 100

spool /u01/app/oracle/results/filesystemio_options_unset.txt

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT /*+ index (t1 ind_t1) */
ID,
DESCRIPTION
FROM
T1 t1
WHERE
ID >= 9991;

SET ARRAYSIZE 100
SET AUTOTRACE TRACEONLY STATISTICS

SET TIMING ON

SELECT /*+ index (t1 ind_t1) */
ID,
DESCRIPTION
FROM
T1 t1
WHERE
ID >= 9991;

SELECT /*+ index (t1 ind_t1) */
ID,
DESCRIPTION
FROM
T1 t1
WHERE
ID >= 9991;

spool off

This is the output:
Execution Plan
----------------------------------------------------------
Plan hash value:
634656657

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 5273K| 99458
(1)| 00:19:54
|
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100K| 5273K| 99458
(1)| 00:19:54
|
|* 2 | INDEX RANGE SCAN | IND_T1 | 100K| | 212
(1)| 00:00:03
|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation
id):
---------------------------------------------------
2 - access
("ID">=9991)


2847287 rows selected.

Elapsed: 00:05:04.91

Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
2568572 consistent
gets
2540316 physical
reads
0 redo
size
173286364 bytes sent via SQL*Net to
client
313716 bytes received via SQL*Net from
client
28474 SQL*Net roundtrips to/from
client
0 sorts
(memory)
0 sorts
(disk)
2847287 rows
processed


2847287 rows selected.

Elapsed: 00:00:19.39

Statistics
----------------------------------------------------------
0 recursive
calls
0 db block
gets
2568572 consistent
gets
2536953 physical
reads
0 redo
size
173286364 bytes sent via SQL*Net to
client
313716 bytes received via SQL*Net from
client
28474 SQL*Net roundtrips to/from
client
0 sorts
(memory)
0 sorts
(disk)
2847287 rows
processed


The first execution completed in 5 minutes and almost 5 seconds. The
second execution completed in 19.39 seconds with a nearly identical
value for physical block reads.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: Gerard H. Pille on 10 Sep 2009 03:37
On 10 sep, 02:12, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
>
> Yes, there is a skew in the data - it should be more densely packed at
> each end of the range.
>

Unless I made a mistake building the test data, that is not the case:
not at each end, but only at *the* end of the range. If I remember
right, 4K records for id 1, 1M for id 10000.

> The first execution completed in 5 minutes and almost 5 seconds.  The
> second execution completed in 19.39 seconds with a nearly identical
> value for physical block reads.
>

So your physical reads are anything but (the second time round). On
my poor PC, 2Gb of memory, 500Mb SGA of which only 200Mb for data, the
physical reads mostly are physical indeed.

Why would my system have such trouble fetching 2.5M records for ids 1
to 400, and perform much better fetching 2.8M records for ids 9991 to
10000? The FTS, of course, doesn't care which ids it has to fetch.
But for the IS, it seems to matter a lot.

Kind regards,

Gerard
From: Mladen Gogala on 10 Sep 2009 04:26
Na Wed, 09 Sep 2009 17:12:12 -0700, Charles Hooper napisao:


> The first execution completed in 5 minutes and almost 5 seconds. The
> second execution completed in 19.39 seconds with a nearly identical
> value for physical block reads.

Question: was direct I/O enabled here? If not, the blocks may have been
in the system buffer cache in which case oracle would still report them
as "physical reads" but blocks would be coming from memory instead.



--
http://mgogala.freehostia.com
From: Gerard H. Pille on 10 Sep 2009 05:20
On 10 sep, 10:26, Mladen Gogala wrote:

>
> Question: was direct I/O enabled here? If not, the blocks may have been
> in the system buffer cache in which case oracle would still report them
> as "physical reads" but blocks would be coming from memory instead.
>


Why do you ask this? Charles clearly states that it was not. Will
you believe him next time??
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!