| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT statement efficiency question
On Apr 8, 1:17 pm, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net>
wrote:
> On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:
> > Good advice given so far. I would caution against creating too many
> > indexes, as this will likely negatively impact performance of other
> > parts of the system
>
> Charles, this is the line I frequently find in many books, CBT and
> manuals and yet I have never seen insert or delete slowed down to the
> unacceptable levels because of too many indexes. The only method to
> diagnose that this is indeed happening would be to observe significant
> increase in average I/O time on the underlying data file.
> Again, I've never even seen this happening. I believe that this thing
> with too many indexes is dangerous only in the extreme situations and it
> is very hard to diagnose because the process that waits for writing the
> index blocks is DBWR so the users never wait for the blocks to be
> written. Users may wait for checkpoints or log file sync but not for the
> index
>
> --http://www.mladen-gogala.com
I have also seen the statement written in books, blogs, and articles stating that having too many indexes on tables causes performance problems, essentially that a table update that would have required one I/O is turned into one I/O plus three or four I/Os per index (or maybe it was two to three I/Os per index). I never tested this, since it seemed to make sense, after all the I/O system can only handle so many logical I/O and so many physical I/Os per second. I devised a quick test earlier today using DBA_OBJECTS, but aborted the test after 15+ minutes, and instead decided to use a physical table for the source data for testing.
First, I created a very simple table that includes the first 19
columns of my source table:
CREATE TABLE T3 AS
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY, MINIMUM_ORDER_QTY, MAXIMUM_ORDER_QTY,
This is what the test table looks like:
DESC T3
Name Null? Type ----------------------------------------- -------- ------------ ID NOT NULL VARCHAR2(30) DESCRIPTION VARCHAR2(40) STOCK_UM NOT NULL VARCHAR2(15) PLANNING_LEADTIME NOT NULL NUMBER ORDER_POLICY NOT NULL CHAR(1) ORDER_POINT NUMBER(14,4) SAFETY_STOCK_QTY NUMBER(14,4) FIXED_ORDER_QTY NUMBER(14,4) DAYS_OF_SUPPLY NUMBER MINIMUM_ORDER_QTY NUMBER(14,4) MAXIMUM_ORDER_QTY NUMBER(14,4) ENGINEERING_MSTR VARCHAR2(3) PRODUCT_CODE VARCHAR2(15) COMMODITY_CODE VARCHAR2(15) MFG_NAME VARCHAR2(30) MFG_PART_ID VARCHAR2(30) FABRICATED NOT NULL CHAR(1) PURCHASED NOT NULL CHAR(1) STOCKED NOT NULL CHAR(1)
Now, in the database with no other activity, I bring the source
table's blocks into the KEEP pool:
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY, MINIMUM_ORDER_QTY, MAXIMUM_ORDER_QTY,
Now, I set up for the first test:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_NO_INDEXES';
And just perform a mass insert from the source table:
INSERT INTO
T3
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY, MINIMUM_ORDER_QTY, MAXIMUM_ORDER_QTY,
Now, I switch to another trace file and reset the test table back to
the starting point:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_CLEAN_UP';
TRUNCATE TABLE T3;
Let's create a simple B*Tree index on each column of our test table:
CREATE INDEX T3_1 ON T3(ID); CREATE INDEX T3_2 ON T3(DESCRIPTION); CREATE INDEX T3_3 ON T3(STOCK_UM); CREATE INDEX T3_4 ON T3(PLANNING_LEADTIME); CREATE INDEX T3_5 ON T3(ORDER_POLICY); CREATE INDEX T3_6 ON T3(ORDER_POINT); CREATE INDEX T3_7 ON T3(SAFETY_STOCK_QTY); CREATE INDEX T3_8 ON T3(FIXED_ORDER_QTY); CREATE INDEX T3_9 ON T3(DAYS_OF_SUPPLY); CREATE INDEX T3_10 ON T3(MINIMUM_ORDER_QTY); CREATE INDEX T3_11 ON T3(MAXIMUM_ORDER_QTY); CREATE INDEX T3_12 ON T3(ENGINEERING_MSTR); CREATE INDEX T3_13 ON T3(PRODUCT_CODE); CREATE INDEX T3_14 ON T3(COMMODITY_CODE); CREATE INDEX T3_15 ON T3(MFG_NAME); CREATE INDEX T3_16 ON T3(MFG_PART_ID); CREATE INDEX T3_17 ON T3(FABRICATED); CREATE INDEX T3_18 ON T3(PURCHASED); CREATE INDEX T3_19 ON T3(STOCKED);
Let's try the test again now that the 19 indexes are on the columns:
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_WITH_INDEXES';
INSERT INTO
T3
SELECT
ID,
DESCRIPTION,
STOCK_UM,
PLANNING_LEADTIME,
ORDER_POLICY,
ORDER_POINT,
SAFETY_STOCK_QTY,
FIXED_ORDER_QTY,
DAYS_OF_SUPPLY, MINIMUM_ORDER_QTY, MAXIMUM_ORDER_QTY,
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'DONE';
>From the TEST_NO_INDEXES trace file, the wait events that appeared:
Wait Time Wait Event Wait Event Raw Details 0.036 db file sequential read WAIT #3: nam='db file sequential read' ela= 36131 file#=4 block#=3 blocks=1 obj#=12373 tim=3652347137 0.036 db file sequential read WAIT #3: nam='db file sequential read' ela= 36131 file#=4 block#=3 blocks=1 obj#=12373 tim=3652347137 0 db file sequential read WAIT #3: nam='db file sequential read' ela= 234 file#=4 block#=4 blocks=1 obj#=12373 tim=3652347440 0 db file sequential read WAIT #3: nam='db file sequential read' ela= 204 file#=4 block#=5 blocks=1 obj#=12373 tim=3652347707 0 db file sequential read WAIT #3: nam='db file sequential read' ela= 203 file#=4 block#=6 blocks=1 obj#=12373 tim=3652347971 0 db file sequential read WAIT #3: nam='db file sequential read' ela= 171 file#=4 block#=7 blocks=1 obj#=12373 tim=3652348181 0 db file sequential read WAIT #3: nam='db file sequential read' ela=195 file#=4 block#=8 blocks=1 obj#=12373 tim=3652348434
>From the TEST_WITH_INDEXES trace file, the wait events that appeared:
Wait Time Wait Event Wait Event Raw Details 0.079 db file sequential read WAIT #4: nam='db file sequential read' ela= 79485 file#=4 block#=3295081 blocks=1 obj#=15076 tim=3735217458 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 205 file#=4 block#=3295082 blocks=1 obj#=15076 tim=3735218917 0.013 db file scattered read WAIT #4: nam='db file scattered read' ela= 12548 file#=4 block#=3295340 blocks=5 obj#=15077 tim=3735231979 0.009 db file scattered read WAIT #4: nam='db file scattered read' ela= 8933 file#=4 block#=3295700 blocks=5 obj#=15078 tim=3735241489 0.012 db file scattered read WAIT #4: nam='db file scattered read' ela= 11927 file#=4 block#=3295828 blocks=5 obj#=15079 tim=3735253891 0.003 db file scattered read WAIT #4: nam='db file scattered read' ela= 2692 file#=4 block#=3296028 blocks=5 obj#=15080 tim=3735257040 0.011 db file scattered read WAIT #4: nam='db file scattered read' ela= 11249 file#=4 block#=3296092 blocks=5 obj#=15081 tim=3735268791 0.013 db file scattered read WAIT #4: nam='db file scattered read' ela= 12550 file#=4 block#=3296164 blocks=5 obj#=15083 tim=3735281801 0.003 db file scattered read WAIT #4: nam='db file scattered read' ela= 3421 file#=4 block#=3296188 blocks=5 obj#=15086 tim=3735285609 0.002 db file scattered read WAIT #4: nam='db file scattered read' ela= 2223 file#=4 block#=3296204 blocks=5 obj#=15087 tim=3735288147 0.002 db file scattered read WAIT #4: nam='db file scattered read' ela= 2243 file#=4 block#=3296220 blocks=5 obj#=15088 tim=3735290688 0.004 db file scattered read WAIT #4: nam='db file scattered read' ela= 3825 file#=4 block#=3296236 blocks=5 obj#=15089 tim=3735294815 0.013 db file scattered read WAIT #4: nam='db file scattered read' ela= 12564 file#=4 block#=3296308 blocks=5 obj#=15090 tim=3735307829 0.016 db file scattered read WAIT #4: nam='db file scattered read' ela= 16333 file#=4 block#=3296404 blocks=5 obj#=15091 tim=3735324649 0.001 db file scattered read WAIT #4: nam='db file scattered read' ela= 1026 file#=4 block#=3296412 blocks=5 obj#=15092 tim=3735325925 0.001 db file scattered read WAIT #4: nam='db file scattered read' ela= 982 file#=4 block#=3296420 blocks=5 obj#=15093 tim=3735327191 0.011 db file scattered read WAIT #4: nam='db file scattered read' ela= 11291 file#=4 block#=3296484 blocks=5 obj#=15094 tim=3735338933 0.011 db file scattered read WAIT #4: nam='db file scattered read' ela= 11297 file#=4 block#=3296548 blocks=5 obj#=15095 tim=3735350677 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 458 file#=4 block#=3295698 blocks=1 obj#=15078 tim=3735356766 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 252 file#=4 block#=3295697 blocks=1 obj#=15078 tim=3735357279 0.033 db file sequential read WAIT #4: nam='db file sequential read' ela= 33085 file#=4 block#=3296306 blocks=1 obj#=15090 tim=3735440346 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 196 file#=4 block#=3296305 blocks=1 obj#=15090 tim=3735440775 0.01 db file sequential read WAIT #4: nam='db file sequential read' ela= 10447 file#=4 block#=3295338 blocks=1 obj#=15077 tim=3735453346 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 215 file#=4 block#=3295337 blocks=1 obj#=15077 tim=3735453758 0.01 db file sequential read WAIT #4: nam='db file sequential read' ela= 10359 file#=4 block#=3295826 blocks=1 obj#=15079 tim=3735475243 0.01 db file sequential read WAIT #4: nam='db file sequential read' ela= 10359 file#=4 block#=3295826 blocks=1 obj#=15079 tim=3735475243 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 253 file#=4 block#=3295825 blocks=1 obj#=15079 tim=3735475696 0.006 db file sequential read WAIT #4: nam='db file sequential read' ela= 5589 file#=4 block#=3296234 blocks=1 obj#=15089 tim=3735482850 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 218 file#=4 block#=3296233 blocks=1 obj#=15089 tim=3735483299 0.002 db file sequential read WAIT #4: nam='db file sequential read' ela= 1916 file#=4 block#=3296026 blocks=1 obj#=15080 tim=3735489517 0.011 db file sequential read WAIT #4: nam='db file sequential read' ela= 10727 file#=4 block#=3296025 blocks=1 obj#=15080 tim=3735500455 0.011 db file sequential read WAIT #4: nam='db file sequential read' ela= 10945 file#=4 block#=3296090 blocks=1 obj#=15081 tim=3735512137 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 330 file#=4 block#=3296089 blocks=1 obj#=15081 tim=3735512661 0.012 db file sequential read WAIT #4: nam='db file sequential read' ela= 11793 file#=4 block#=3296418 blocks=1 obj#=15093 tim=3735526329 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 283 file#=4 block#=3296417 blocks=1 obj#=15093 tim=3735526806 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 266 file#=4 block#=3296482 blocks=1 obj#=15094 tim=3735527871 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 271 file#=4 block#=3296481 blocks=1 obj#=15094 tim=3735528379 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 376 file#=4 block#=3296546 blocks=1 obj#=15095 tim=3735529787 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 306 file#=4 block#=3296545 blocks=1 obj#=15095 tim=3735530308 0.023 db file scattered read WAIT #4: nam='db file scattered read' ela= 22865 file#=4 block#=3296172 blocks=5 obj#=15084 tim=3735560714 0.077 db file scattered read WAIT #4: nam='db file scattered read' ela= 77052 file#=4 block#=3296156 blocks=5 obj#=15082 tim=3735658131 0.077 db file scattered read WAIT #4: nam='db file scattered read' ela= 77052 file#=4 block#=3296156 blocks=5 obj#=15082 tim=3735658131 0.123 db file sequential read WAIT #4: nam='db file sequential read' ela= 122864 file#=4 block#=3296186 blocks=1 obj#=15086 tim=3735917834 0.123 db file sequential read WAIT #4: nam='db file sequential read' ela= 122864 file#=4 block#=3296186 blocks=1 obj#=15086 tim=3735917834 0.121 db file sequential read WAIT #4: nam='db file sequential read' ela= 120743 file#=4 block#=3296185 blocks=1 obj#=15086 tim=3736038702 0.135 db file sequential read WAIT #4: nam='db file sequential read' ela= 134798 file#=4 block#=3296218 blocks=1 obj#=15088 tim=3736222728 0.135 db file sequential read WAIT #4: nam='db file sequential read' ela= 134798 file#=4 block#=3296218 blocks=1 obj#=15088 tim=3736222728 0.013 db file sequential read WAIT #4: nam='db file sequential read' ela= 12658 file#=4 block#=3296217 blocks=1 obj#=15088 tim=3736235504 0.27 log buffer space WAIT #4: nam='log buffer space' ela= 270380 p1=0 p2=0 p3=0 obj#=15088 tim=3736880230 0.27 log buffer space WAIT #4: nam='log buffer space' ela= 270380 p1=0 p2=0 p3=0 obj#=15088 tim=3736880230 0.402 log buffer space WAIT #4: nam='log buffer space' ela= 402235 p1=0 p2=0 p3=0 obj#=15088 tim=3737410490 0.402 log buffer space WAIT #4: nam='log buffer space' ela= 402235 p1=0 p2=0 p3=0 obj#=15088 tim=3737410490 0.17 db file sequential read WAIT #4: nam='db file sequential read' ela= 170460 file#=4 block#=3296202 blocks=1 obj#=15087 tim=3737612923 0.17 db file sequential read WAIT #4: nam='db file sequential read' ela= 170460 file#=4 block#=3296202 blocks=1 obj#=15087 tim=3737612923 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 346 file#=4 block#=3296201 blocks=1 obj#=15087 tim=3737613373 0.041 log buffer space WAIT #4: nam='log buffer space' ela= 40699 p1=0 p2=0 p3=0 obj#=15087 tim=3737756910 0.041 log buffer space WAIT #4: nam='log buffer space' ela= 40699 p1=0 p2=0 p3=0 obj#=15087 tim=3737756910 0.153 db file sequential read WAIT #4: nam='db file sequential read' ela= 153445 file#=4 block#=3296154 blocks=1 obj#=15082 tim=3738030523 0.153 db file sequential read WAIT #4: nam='db file sequential read' ela= 153445 file#=4 block#=3296154 blocks=1 obj#=15082 tim=3738030523 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 316 file#=4 block#=3296153 blocks=1 obj#=15082 tim=3738030946 0.15 log buffer space WAIT #4: nam='log buffer space' ela= 150208 p1=0 p2=0 p3=0 obj#=15082 tim=3738192472 0.018 log buffer space WAIT #4: nam='log buffer space' ela= 18194 p1=0 p2=0 p3=0 obj#=15082 tim=3738587208 0.018 log buffer space WAIT #4: nam='log buffer space' ela= 18194 p1=0 p2=0 p3=0 obj#=15082 tim=3738587208 0.405 db file sequential read WAIT #4: nam='db file sequential read' ela= 404755 file#=4 block#=3296402 blocks=1 obj#=15091 tim=3739010542 0.405 db file sequential read WAIT #4: nam='db file sequential read' ela= 404755 file#=4 block#=3296402 blocks=1 obj#=15091 tim=3739010542 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 295 file#=4 block#=3296401 blocks=1 obj#=15091 tim=3739010977 0.178 db file sequential read WAIT #4: nam='db file sequential read' ela= 178125 file#=4 block#=3296410 blocks=1 obj#=15092 tim=3739204175 0.178 db file sequential read WAIT #4: nam='db file sequential read' ela= 178125 file#=4 block#=3296410 blocks=1 obj#=15092 tim=3739204175 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 284 file#=4 block#=3296409 blocks=1 obj#=15092 tim=3739204575 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 466 file#=4 block#=3296162 blocks=1 obj#=15083 tim=3739212628 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 466 file#=4 block#=3296162 blocks=1 obj#=15083 tim=3739212628 0 db file sequential read WAIT #4: nam='db file sequential read' ela= 206 file#=4 block#=3296161 blocks=1 obj#=15083 tim=3739212885 0.298 log buffer space WAIT #4: nam='log buffer space' ela= 297733 p1=0 p2=0 p3=0 obj#=15083 tim=3739742943 0.298 log buffer space WAIT #4: nam='log buffer space' ela= 297733 p1=0 p2=0 p3=0 obj#=15083 tim=3739742943 0.933 log buffer space WAIT #4: nam='log buffer space' ela= 932660 p1=0 p2=0 p3=0 obj#=15083 tim=3740714395 0.587 log buffer space WAIT #4: nam='log buffer space' ela= 586746 p1=0 p2=0 p3=0 obj#=15083 tim=3741542848 0.587 log buffer space WAIT #4: nam='log buffer space' ela= 586746 p1=0 p2=0 p3=0 obj#=15083 tim=3741542848 0.818 log buffer space WAIT #4: nam='log buffer space' ela= 817764 p1=0 p2=0 p3=0 obj#=15083 tim=3742397903 0.818 log buffer space WAIT #4: nam='log buffer space' ela= 817764 p1=0 p2=0 p3=0 obj#=15083 tim=3742397903 1 log buffer space WAIT #4: nam='log buffer space' ela= 1000039 p1=0 p2=0 p3=0 obj#=15083 tim=3743638629 0.031 log buffer space WAIT #4: nam='log buffer space' ela= 31071 p1=0 p2=0 p3=0 obj#=15083 tim=3743839418 0.845 log buffer space WAIT #4: nam='log buffer space' ela= 844796 p1=0 p2=0 p3=0 obj#=15083 tim=3744726107 0.845 log buffer space WAIT #4: nam='log buffer space' ela= 844796p1=0 p2=0 p3=0 obj#=15083 tim=3744726107
2.653 db file sequential read 0.302 db file scattered read 8.671 log buffer space
0.072 seconds is definitely less that 11.626 seconds. This looks like a severe penalty from having the indexes in place - 161.5 times longer to insert the same data. I did not test update performance, although I would anticipate individual updates of a single row on all columns in this table by a "screen painter" that automatically updates all columns regardless of whether or not the columns changed to take roughly 57 times longer than if no indexes existed on the table: 1: (1+19*3). This increased time may be small compared to improved query performance by end users.
The above was an isolated test case, and may very well exibit different bahavior in other environments.
Mladen, thanks for prompting the test. I was curious about this also.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Sun Apr 08 2007 - 19:17:07 CDT
 |
 |