Is it possible to run both queries with sql_trace on and
statistics_level=all (can be set at the session level) and then post the
execution statistics (cpu, elapsed, consistent reads) for each line in the
access path. Maybe that will shed some light on what is going on.
At 05:48 PM 4/10/2003 -0800, you wrote:
> > How did you rebuild the table ?
>Among other things, on each server I did imports to new schema owners with
>new tablespaces. No monkey business with ordering the data. I've been
>testing on a new database with same db_block_size for all tablespaces. The
>only activity is my testing and there are no cache buffer chaining
>problems on either server.
>
>The execution paths were identical on both servers and the only difference
>was the LIO count. Since the execution paths are identical statistics
>aren't an issue, right? Stats are the same anyway. Since the query is
>based on one table and two indexes which are created via the same *.dmp
>imported using equivalent parfiles, data access should be the same. There
>are no disk reads for the query so file fragmentation shouldn't be and
>issue. I guess I'll bump up the trace level to 12 but if the execution
>plans are the same and the data is the same am I really going to find
>anything which comes close to explaining a 10 fold performance difference?
>
>I believe I've addressed everyones questions except for the bothersome
>"why" question on the different LIO counts. Regardless of the LIO count
>weirdness, the 10 fold performance improvement due to a mere 96K
>difference in L1 cache seems incredulous. Granted I'm just running a
>single query that is LIO intensive and doesn't go to disk. Anyone else
>have similar servers with diffent L1 cache to test on?
>
>Curiosity may have killed the cat but it makes the dog wise when he asks
>good questions. :-)
>
>best regards,
>Steve
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: breitliw_at_centrexcc.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Apr 10 2003 - 22:33:36 CDT
