Re: SELECT statement efficiency question

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 9 Apr 2007 13:52:45 -0700
Message-ID: <1176151964.961425.213060@q75g2000hsh.googlegroups.com>

On Apr 9, 2:10 pm, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net> wrote:
> On Mon, 09 Apr 2007 09:40:57 -0700, hpuxrac wrote:
> > Mladen what you wrote just doesn't make sense. Any time you add indexes
> > to any table you affect to some degree the scalability of an
> > application. Tom Kyte's test harness is an easy way to look at the
> > impact of adding indexes. A 10046 trace is another way.
>
> The 10046 is not a good way as it doesn't show writing to indexes. The
> only writes done by the user process are direct writes. In other words,
> you don't see much of an impact from the 10046 trace. The only measure you
> can use to measure the overall impact of an index is timing, precisely
> what my test did. That is what "set timing on" was all about. Look at
> the Charles's trace: you don't see anything like "single block writes",
> indicative of an index maintenance. The only thing you see is an increased
> wait for the log sync, but you cannot quantify that.
>
> Furthermore, my statement said that I've never seen an unacceptable
> impact. Mostly, for the OLTP transactions, indexes will extend the
> transaction duration from 300 milisec to 500 milisec or 1/2 of a second.
> Most users will not complain about 1/5 of a second. The only cases when
> this impact becomes significant is when we start working with large batch
> transactions, usually scheduled not to run during the peak usage. If a
> load runs from 1-3 AM instead of 1-2 AM is a big difference performance-
> wise, but I will not give it a second thought because it doesn't have any
> business impact.
>
> --http://www.mladen-gogala.com

This is an interesting discussion (I find that it is helping my understanding). I performed additional translation of the wait events in the TEST_WITH_INDEXES trace file to identify the objects indicated in the wait events. If one were to manually navigate the 10046 trace file, you might see the following at dep=1: select file# from file$ where ts#=:1
update tsq$ set blocks=:3,maxblocks= ... update seg$ set type#=:4,blocks=:5,extents= ...

True, there were no writes indicated in the wait events. We can see where the SELECT statement apparently affects the execution performance - I can tie each of the above three statements to a particular group of wait events listed below.

Translation of the TEST_WITH_INDEXES trace file wait events (with time delta from the start of the trace file):

Time	Wait Time	Wait Event	Object
0.088	0.079	db file sequential read	TABLE  OWNER.T3    Block:
3295081  Blocks Read:         1
0.089	0.000	db file sequential read	TABLE  OWNER.T3    Block:
3295082  Blocks Read:         1
0.102	0.013	db file scattered read	INDEX  OWNER.T3_1  Block:
3295340  Blocks Read:         5
0.112	0.009	db file scattered read	INDEX  OWNER.T3_2  Block:
3295700  Blocks Read:         5
0.124	0.012	db file scattered read	INDEX  OWNER.T3_3  Block:
3295828  Blocks Read:         5
0.127	0.003	db file scattered read	INDEX  OWNER.T3_4  Block:
3296028  Blocks Read:         5
0.139	0.011	db file scattered read	INDEX  OWNER.T3_5  Block:
3296092  Blocks Read:         5
0.152	0.013	db file scattered read	INDEX  OWNER.T3_7  Block:
3296164  Blocks Read:         5
0.156	0.003	db file scattered read	INDEX  OWNER.T3_10 Block:
3296188  Blocks Read:         5
0.158	0.002	db file scattered read	INDEX  OWNER.T3_11 Block:
3296204  Blocks Read:         5
0.161	0.002	db file scattered read	INDEX  OWNER.T3_12 Block:
3296220  Blocks Read:         5
0.165	0.004	db file scattered read	INDEX  OWNER.T3_13 Block:
3296236  Blocks Read:         5
0.178	0.013	db file scattered read	INDEX  OWNER.T3_14 Block:
3296308  Blocks Read:         5
0.195	0.016	db file scattered read	INDEX  OWNER.T3_15 Block:
3296404  Blocks Read:         5
0.196	0.001	db file scattered read	INDEX  OWNER.T3_16 Block:
3296412  Blocks Read:         5
0.197	0.001	db file scattered read	INDEX  OWNER.T3_17 Block:
3296420  Blocks Read:         5
0.209	0.011	db file scattered read	INDEX  OWNER.T3_18 Block:
3296484  Blocks Read:         5
0.221	0.011	db file scattered read	INDEX  OWNER.T3_19 Block:
3296548  Blocks Read:         5
0.227	0.000	db file sequential read	INDEX  OWNER.T3_2  Block:
3295698  Blocks Read:         1
0.228	0.000	db file sequential read	INDEX  OWNER.T3_2  Block:
3295697  Blocks Read:         1
0.311	0.033	db file sequential read	INDEX  OWNER.T3_14 Block:
3296306  Blocks Read:         1
0.311	0.000	db file sequential read	INDEX  OWNER.T3_14 Block:
3296305  Blocks Read:         1
0.324	0.010	db file sequential read	INDEX  OWNER.T3_1  Block:
3295338  Blocks Read:         1
0.324	0.000	db file sequential read	INDEX  OWNER.T3_1  Block:
3295337  Blocks Read:         1
0.345	0.010	db file sequential read	INDEX  OWNER.T3_3  Block:
3295826  Blocks Read:         1
0.345	0.010	db file sequential read	INDEX  OWNER.T3_3  Block:
3295826  Blocks Read:         1
0.346	0.000	db file sequential read	INDEX  OWNER.T3_3  Block:
3295825  Blocks Read:         1
0.353	0.006	db file sequential read	INDEX  OWNER.T3_13 Block:
3296234  Blocks Read:         1
0.354	0.000	db file sequential read	INDEX  OWNER.T3_13 Block:
3296233  Blocks Read:         1
0.360	0.002	db file sequential read	INDEX  OWNER.T3_4  Block:
3296026  Blocks Read:         1
0.371	0.011	db file sequential read	INDEX  OWNER.T3_4  Block:
3296025  Blocks Read:         1
0.382	0.011	db file sequential read	INDEX  OWNER.T3_5  Block:
3296090  Blocks Read:         1
0.383	0.000	db file sequential read	INDEX  OWNER.T3_5  Block:
3296089  Blocks Read:         1
0.397	0.012	db file sequential read	INDEX  OWNER.T3_17 Block:
3296418  Blocks Read:         1
0.397	0.000	db file sequential read	INDEX  OWNER.T3_17 Block:
3296417  Blocks Read:         1
0.398	0.000	db file sequential read	INDEX  OWNER.T3_18 Block:
3296482  Blocks Read:         1
0.399	0.000	db file sequential read	INDEX  OWNER.T3_18 Block:
3296481  Blocks Read:         1
0.400	0.000	db file sequential read	INDEX  OWNER.T3_19 Block:
3296546  Blocks Read:         1
0.401	0.000	db file sequential read	INDEX  OWNER.T3_19 Block:
3296545  Blocks Read:         1
0.431	0.023	db file scattered read	INDEX  OWNER.T3_8  Block:
3296172  Blocks Read:         5
0.528	0.077	db file scattered read	INDEX  OWNER.T3_6  Block:
3296156  Blocks Read:         5
0.528	0.077	db file scattered read	INDEX  OWNER.T3_6  Block:
3296156  Blocks Read:         5
0.788	0.123	db file sequential read	INDEX  OWNER.T3_10 Block:
3296186  Blocks Read:         1
0.788	0.123	db file sequential read	INDEX  OWNER.T3_10 Block:
3296186  Blocks Read:         1
0.909	0.121	db file sequential read	INDEX  OWNER.T3_10 Block:
3296185  Blocks Read:         1
1.093	0.135	db file sequential read	INDEX  OWNER.T3_12 Block:
3296218  Blocks Read:         1
1.093	0.135	db file sequential read	INDEX  OWNER.T3_12 Block:
3296218  Blocks Read:         1
1.106	0.013	db file sequential read	INDEX  OWNER.T3_12 Block:
3296217  Blocks Read:         1
1.750	0.270	log buffer space	INDEX  OWNER.T3_12 Block:         0
Blocks Read:         0
1.750	0.270	log buffer space	INDEX  OWNER.T3_12 Block:         0
Blocks Read:         0
2.281	0.402	log buffer space	INDEX  OWNER.T3_12 Block:         0
Blocks Read:         0
2.281	0.402	log buffer space	INDEX  OWNER.T3_12 Block:         0
Blocks Read:         0
2.483	0.170	db file sequential read	INDEX  OWNER.T3_11 Block:
3296202  Blocks Read:         1
2.483	0.170	db file sequential read	INDEX  OWNER.T3_11 Block:
3296202  Blocks Read:         1
2.484	0.000	db file sequential read	INDEX  OWNER.T3_11 Block:
3296201  Blocks Read:         1
2.627	0.041	log buffer space	INDEX  OWNER.T3_11 Block:         0
Blocks Read:         0
2.627	0.041	log buffer space	INDEX  OWNER.T3_11 Block:         0
Blocks Read:         0
2.901	0.153	db file sequential read	INDEX  OWNER.T3_6  Block:
3296154  Blocks Read:         1
2.901	0.153	db file sequential read	INDEX  OWNER.T3_6  Block:
3296154  Blocks Read:         1
2.901	0.000	db file sequential read	INDEX  OWNER.T3_6  Block:
3296153  Blocks Read:         1
3.063	0.150	log buffer space	INDEX  OWNER.T3_6  Block:         0
Blocks Read:         0
3.457	0.018	log buffer space	INDEX  OWNER.T3_6  Block:         0
Blocks Read:         0
3.457	0.018	log buffer space	INDEX  OWNER.T3_6  Block:         0
Blocks Read:         0
3.881	0.405	db file sequential read	INDEX  OWNER.T3_15 Block:
3296402  Blocks Read:         1
3.881	0.405	db file sequential read	INDEX  OWNER.T3_15 Block:
3296402  Blocks Read:         1
3.881	0.000	db file sequential read	INDEX  OWNER.T3_15 Block:
3296401  Blocks Read:         1
4.074	0.178	db file sequential read	INDEX  OWNER.T3_16 Block:
3296410  Blocks Read:         1
4.074	0.178	db file sequential read	INDEX  OWNER.T3_16 Block:
3296410  Blocks Read:         1
4.075	0.000	db file sequential read	INDEX  OWNER.T3_16 Block:
3296409  Blocks Read:         1
4.083	0.000	db file sequential read	INDEX  OWNER.T3_7  Block:
3296162  Blocks Read:         1
4.083	0.000	db file sequential read	INDEX  OWNER.T3_7  Block:
3296162  Blocks Read:         1
4.083	0.000	db file sequential read	INDEX  OWNER.T3_7  Block:
3296161  Blocks Read:         1
4.613	0.298	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
4.613	0.298	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
5.585	0.933	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
6.413	0.587	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
6.413	0.587	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
7.268	0.818	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
7.268	0.818	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
8.509	1.000	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
8.710	0.031	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
9.596	0.845	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0
9.596	0.845	log buffer space	INDEX  OWNER.T3_7  Block:         0
Blocks Read:         0

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Apr 09 2007 - 15:52:45 CDT