Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.
SQL> desc t3 Name Null? Type ----------------------------- -------- -------------------- ID1 NOT NULL NUMBER ID2 NOT NULL NUMBER V1 VARCHAR2(40) V2 VARCHAR2(40) PADDING VARCHAR2(500) SQL> desc t4 Name Null? Type ----------------------------- -------- -------------------- ID1 NOT NULL NUMBER ID2 NOT NULL NUMBER V1 VARCHAR2(40) V2 VARCHAR2(40) PADDING VARCHAR2(500) SQL> select index_name, column_name 2 from user_ind_columns 3 where table_name in ('T3','T4') 4 order by table_name, index_name, column_position; INDEX_NAME COLUMN_NAME -------------------- -------------------- T3_PK ID1 ID2 T4_PK ID1 ID2 4 rows selected. SQL> truncate table t3; Table truncated. SQL> truncate table t4; Table truncated. SQL> insert into t3 select * from t1; 2000 rows created. SQL> insert into t4 select * from t1; 2000 rows created. SQL> commit; Commit complete. SQL> set autotrace traceonly SQL> select max(v1) from t3; 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2110918630 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 2003 | | 1 | SORT AGGREGATE | | 1 | 3 | | | 2 | INDEX FAST FULL SCAN| T3_PK | 2000 | 6000 | 2003 | --------------------------------------------------------------- Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2012 consistent gets 0 physical reads 0 redo size 409 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select max(v1) from t4; 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4293386624 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 2003 | | 1 | SORT AGGREGATE | | 1 | 3 | | | 2 | INDEX FAST FULL SCAN| T4_PK | 2000 | 6000 | 2003 | --------------------------------------------------------------- Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 409 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
The question – how come, following a truncate and with an index fast full scan in both cases, the number of consistent gets varies so much between the two tables ? You may also wonder why the cost is so high for a fast full scan on such a small amount of data.
In case it helps, here’s another little bit of information:
1 select 2 index_name, blevel, leaf_blocks, 3 avg_leaf_blocks_per_key avg_lpk, 4 avg_data_blocks_per_key avg_dpk, 5 clustering_factor cluf 6 from 7 user_indexes 8 where 9 table_name in ('T3','T4') 10 order by 11* index_name SQL> / INDEX_NAME BLEVEL LEAF_BLOCKS AVG_LPK AVG_DPK CLUF ---------- ------- ----------- ---------- ---------- ------- T3_PK 1 8 1 1 2000 T4_PK 1 12 1 1 2000
Very interesting. Both IOTs are simple tables without any partitions. You obviously didn’t fiddle with PCTFREE nor INITRANS much, too. I don’t have access to any of my databases, right now, but in my mind it has something to do with IOT OVERFLOW segments. I’l have to check it later.
Regards
Pavol Babel
Comment by Pavol Babel — November 25, 2011 @ 5:40 pm GMT Nov 25,2011 |
Well, now I have time to add some points.
1. CLUSTERING FACTOR is very important information. This is the reason why are started thinking of OVERFLOW SEGMENT. When overflow segment takes no place, the clustering factor for primary key of IOT is always 0, so both tables have some overflow segment.
2. LEAF BLOCKS statistic is very important in this example. 8 and 12 are small numbers, so high cost and high consistent gets definitely caused by overflow segments
3. explain plan has bug in 10g and does not contain any information about scanning overflow segs.
4. The overflow segment has at least 2000 blocks in Jonathan’s case (CF is 2000 for both tables)
5. v1 is the first column after PK in both IOT tables. Reading from the execution plans, optimizer knows v1 could occur in overflow segment for some rows in both tables! That means none of tables is created with “INCLUDING id2 OVERFLOW” syntax (but it was good guess in Vladimir’s post without any access to database), but rather with changed PCTTHRESHOLD clause.
6. numbers from the following example are close to Jonathan’s, however they do not match exactly. I suggest the main idea has been revealed.
Comment by Pavol Babel — November 26, 2011 @ 1:13 am GMT Nov 26,2011 |
Well, 4. is not correct. It is was just easier fo me to simulate CF 2000 for T3_PK and T4_PK with overflow semgents at size of 2000 blocks.
One more remark. It is not important whether jonathan’s T1 is heap table, IOT, hash cluster, or whatever. So I dind’t use SELECT insetad of t1 im my examples
Regards
Pavol Babel
Comment by Pavol Babel — November 26, 2011 @ 4:53 pm GMT Nov 26,2011 |
Hmmm very interesting.. can you show us:
SELECT table_name, iot_type, iot_name FROM dba_tables where table_name in (‘t3’, ‘t4’);
Overflow area can only contains non-primary key columns and just maybe has something to do with v1 :)
Regards,
Luís Marques
Comment by Luís Marques — November 25, 2011 @ 5:56 pm GMT Nov 25,2011 |
Luis,
Comment by Jonathan Lewis — November 25, 2011 @ 6:40 pm GMT Nov 25,2011 |
Luis,
I was just a little unsporting in my previous reply – I answered the question you asked, rather than answering the question you were probably thinking of, which would have resulted in the following information:
This shows that t3 and t4 are index organized tables with overflow segments.
Although it’s probably not guaranteed, this output also suggests that t1 is a heap organized table, and t2 (which I haven’t mentioned above) is an index organized table without an overflow segment. This is based on the observation that all the tables are showing similar last_analyzed dates, but t1 has a block count and t2 doesn’t.
Comment by Jonathan Lewis — November 26, 2011 @ 10:01 am GMT Nov 26,2011 |
Well, i will leave here my preliminary tests regarding OVERFLOW area :)
Here we get 254 consistent gets vs 403 consistent gets on IOT with overflow area. Don’t know if this is the case with JL quiz, but the values for CG are very distinct :)
Comment by Luís Marques — November 25, 2011 @ 6:25 pm GMT Nov 25,2011 |
I don’t have DB now to confirm that, but I think that both tables has overflow segment:
T3 with INCLUDING id2
T4 with INCLUDING v1
That’s why T4 has more leaf blocks than T3.
Then “select max(v1) from T4” scans only leaf block but “select max(v1) from T3” has to scan overflow segment too.
Comment by Vladimir Jelinek — November 25, 2011 @ 8:32 pm GMT Nov 25,2011 |
Finaly I have option to test on DB. I stick to my original idea of different including clause.
It’s pretty close to original numbers. The crucial point is clustering factor. So the testing has to be generated so that Oracle can’t use blocks from overflow area physical order, but has to jump between them for each row from primary key. It leads to 2000 gets.
My testcase:
Comment by Vladimir Jelinek — November 26, 2011 @ 5:22 pm GMT Nov 26,2011 |
previous post wan’t complete. Reposting:
Comment by Vladimir Jelinek — November 26, 2011 @ 5:53 pm GMT Nov 26,2011 |
Hi Vlado,
You posted your example later, but I have to admit your’s is more accurate, it better describes the CBO’s decision. I though CBO knew it didn’t have to visit OVERFLOW SEGMENT when “INCLUDING v1 OVERFLOW” was used (and the default PCTTHRESHOLD 50). CBO has enough information to consider that non-key column v1 will be always stored in PRIMARY INDEX part of IOT, because id1, id2 and v1 together will never reach 50% of 8192 (or event 4096 o 2048) byte block.
However optimizer obviously predicts the OVERFLOW SEGMENT inspection for every row when any non-key column is specified in SQL statement and it’s quite significant different from the runtime execution.
Regards
Pavol Babel
Comment by Pavol Babel — November 26, 2011 @ 9:56 pm GMT Nov 26,2011
Hi Palo,
your are right. Optimizer could recognize that for table T3 he always hit v1 in PK. But apparently he doesn’t do such deep analysis and create the execution plan with same cost. In reality both execution plans has very different cost.
I posted my example later but, in fact I was motivated by Jonathan’s post from November 26, 2011 @ 10:01 am UTC. It’s apparent from it, that overflow segment is small just few hundreds block, so the crucial thing is clustering factor and additional logical read which results from it.
IMO the worst thing is how the execution plan is misleading, because when I see INDEX FAST FULL SCAN, I expecting SEQUENTIAL READS from index segment which is true for T3 table but in case of T4 I have the same execution plan but in reality I have few sequential reads from index segment and LOT of RANDOM READS from overflow segment. On the overflow segment bigger than buffer cache with bad clustering factor I will get many random physical reads and execution times will be very different.
Comment by Vladimir Jelinek — November 27, 2011 @ 10:33 am GMT Nov 27,2011
exactly. The execution plan is very misleading in this situation. It was quite clear CF is the most importan statistic in this case, but the situation is even worst thah I expected.
Great example by Jonathan and great observations by you :)
Regarda
Pavol Babel
Comment by Pavol Babel — November 27, 2011 @ 3:50 pm GMT Nov 27,2011
I have no idea what overflow segments are (the data in IOTs?) so another area of reading for me.
Since we don’t know what the state of play was before the truncate my guess is:
– Histogram stats on t4 and not t3 ?
As a side question, if Oracle has been asked to do a MAX on a PK column, why doesn’t it just read the index FFS in reverse?
Comment by Darryl Griffiths — November 26, 2011 @ 10:21 am GMT Nov 26,2011 |
“As a side question, if Oracle has been asked to do a MAX on a PK column, why doesn’t it just read the index FFS in reverse?”
OK, just read that back, and realised that it’s not the PK you’re using.
Comment by Darryl Griffiths — November 26, 2011 @ 10:26 am GMT Nov 26,2011 |
Hi Jonathan,
Very much interesting test case. As per my analysis
1. we need to check the type of “segment space management” (MSSM) and physical attributes of the segment you are delaing ? (Reason for that is since if I look at the CF of the index segment its high – that make me thinking that, you index segment (used space) is almost equal to the primary key which makes the data to overflow for next blocks.
2. Coming to difference in “consistent gets”, I doubt it Oracle would might have known with the index information on next fetch of segment (t4). But how I still in trying to understand.
Comment by Pavan Kumar N — November 26, 2011 @ 2:52 pm GMT Nov 26,2011 |
Are there any ongoing transcations on t3 so oracle has to apply undo to get consisten blocks ?:)
Regards
GregG
Comment by goryszewskig — November 26, 2011 @ 3:07 pm GMT Nov 26,2011 |
GregG,
No; I wasn’t trying to do anything subtle or devious in this example.
Comment by Jonathan Lewis — November 27, 2011 @ 10:49 am GMT Nov 27,2011 |
Plenty of good comments here – and the answers are related to overflow.
I’ll be writing a follow-up posting this evening, or possibly tomorrow evening.
Comment by Jonathan Lewis — November 27, 2011 @ 10:48 am GMT Nov 27,2011 |
[…] was good to see the answers to the last Quiz Night accumulating. The problem posed was simply this: I have two IOTs and I’ve inserted the same […]
Pingback by IOT Answer « Oracle Scratchpad — November 27, 2011 @ 10:04 pm GMT Nov 27,2011 |
[…] Here’s an odd little detail about the to_char() function that happened to show up in a little demonstration code I used to create some data for last Friday’s quiz night. […]
Pingback by to_char() « Oracle Scratchpad — December 2, 2011 @ 7:49 am GMT Dec 2,2011 |
[…] A little quiz on IOT execution plans and the answer (Nov 2011) […]
Pingback by IOTs | Oracle Scratchpad — February 11, 2021 @ 5:46 pm GMT Feb 11,2021 |