indexes on long varchar columns slow?
Steffen Ramlow
a stmt like this runs about 1-2 seconds, an analysed index on path is not
used, ora makes a table scan
select * from x where path = '/dmsystem/docSpace1/structureFolder4/'
the same on sql-sr 2000 - without any indexes: 74 ms, if there is an index,
the index is used
has ora some probs with long indexes? and why is the max. length 40% of
block size?
Thomas Kyte
(if that email address didn't require changing)
We want to have at least 2 index entries per index leaf block. Considering SS
limited you to 1965 bytes / row or something like that (does it still do
that?)....
If you have an 16k block size, you can have a 7+k index entry. thats pretty
huge (absurdly huge)
lets see a describe of the table, a list of the indexes, and a cut and paste of
a session similar to the following.
you've either analyzed the table before loading it or something else is up.
As for the full scan taking 1-2 seconds, have you looked at your buffer cache or
anything like that?
On my laptop:
tkyte@TKYTE816> create table t unrecoverable
2 as
3 select * from all_objects
4 /
Table created.
tkyte@TKYTE816> create index t_idx on t(object_name)
2 /
Index created.
tkyte@TKYTE816> analyze table t compute statistics
2 /
Table analyzed.
tkyte@TKYTE816> select count(*) from t;
COUNT(*)
----------
21866
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> select * from t where object_name =
'/10076b23_OraCustomDatumClosur';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=174)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=2 Bytes=174)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=2)
That shows an index access plan using an analyzed table. And further, I full
scan doesn't take very long (see http://asktom.oracle.com/~tkyte/tkprof.html for
instructions on sql_trace+tkprof):
select /*+ FULL(t) */ *
from
t where object_name = '/10076b23_OraCustomDatumClosur'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.07 26 305 12 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.07 26 305 12 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 397
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL T
Now, if my buffer cache was tiny -- there would be lots more physical I/O's =
slowness....
--
Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp
Steffen Ramlow
inline
"Thomas Kyte" <tk...@us.oracle.com> wrote in message
news:u1jfht0iceuquh2gt...@4ax.com...
> We want to have at least 2 index entries per index leaf block.
ahh, ok
> Considering SS
> limited you to 1965 bytes / row or something like that (does it still do
> that?)....
no, this was until sql 6.5
now the limit is 8K
> lets see a describe of the table,
SQLWKS> desc structurefolder
Column Name Null? Type
------------------------------ -------- ----
OID NOT NULL VARCHAR2(35)
DOCSPACE VARCHAR2(35)
NAME VARCHAR2(50)
PATH VARCHAR2(2000)
DESCRIPTION VARCHAR2(200)
ACLID VARCHAR2(35)
> a list of the indexes,
no indexes
> and a cut and paste of
> a session similar to the following.
>
> you've either analyzed the table before loading it or something else is
up.
>
> As for the full scan taking 1-2 seconds, have you looked at your buffer
cache or anything like that?
no, whatz up with the buffer cache? too small?
>
SQLWKS> create table t unrecoverable as select * from all_objects;
Statement processed.
SQLWKS> create index t_idx on t(object_name);
Statement processed.
SQLWKS> analyze table t compute statistics;
Statement processed.
SQLWKS> select count(*) from t;
COUNT(*)
----------
1228
i took another whereclause, coz ur sample did not return any row
SQL> select * from t where object_name ='CUSTOMER';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=166)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1274 bytes sent via SQL*Net to client
673 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ FULL(t) */ * from t where object_name = 'CUSTOMER';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=2 Bytes=166)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=2 Bytes=166)
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
62 consistent gets
59 physical reads
0 redo size
1276 bytes sent via SQL*Net to client
691 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
> Now, if my buffer cache was tiny -- there would be lots more physical
I/O's => slowness....
ok, 62 phy. reads. does this mean that my buffer cache is too small?
i have a default install of the database, whats wrong with it?:
# replace "oracle" with your database name
db_name=ORCL
db_files = 1024 # INITIAL
control_files = C:\orant\DATABASE\ctl1ORCL.ora
db_file_multiblock_read_count = 8 # INITIAL
db_block_buffers = 1000 # INITIAL
shared_pool_size = 11534336 # INITIAL
log_checkpoint_interval = 10000
processes = 59 # INITIAL
parallel_max_servers = 5 #
SMALL
log_buffer = 8192 # INITIAL
sequence_cache_entries = 10 # INITIAL
sequence_cache_hash_buckets = 10 # INITIAL
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
global_names = TRUE
background_dump_dest=%RDBMS80%\trace
user_dump_dest=%RDBMS80%\trace
db_block_size = 2048
remote_login_passwordfile = shared
text_enable = TRUE
job_queue_processes = 2
job_queue_interval = 10
job_queue_keep_connections = false
distributed_lock_timeout = 300
distributed_transactions = 5
open_links = 4
andrew_webby at hotmail
"Steffen Ramlow" <s.ra...@gmx.de> wrote in message
news:9ffptc$400d2$1...@ID-54600.news.dfncis.de...
> hi there,
>
> inline
>
> "Thomas Kyte" <tk...@us.oracle.com> wrote in message
> news:u1jfht0iceuquh2gt...@4ax.com...
>
> > We want to have at least 2 index entries per index leaf block.
>
> ahh, ok
>
> > Considering SS
> > limited you to 1965 bytes / row or something like that (does it still do
> > that?)....
>
> no, this was until sql 6.5
> now the limit is 8K
>
>
> > lets see a describe of the table,
> SQLWKS> desc structurefolder
>
> Column Name Null? Type
>
> ------------------------------ -------- ----
>
> OID NOT NULL VARCHAR2(35)
>
> DOCSPACE VARCHAR2(35)
>
> NAME VARCHAR2(50)
>
> PATH VARCHAR2(2000)
>
> DESCRIPTION VARCHAR2(200)
>
> ACLID VARCHAR2(35)
>
so, a *possible* maximum ROW SIZE of 2355 bytes. Your db_block_size is
2048.... so you will run into excessive row chaining and migration almost
immediately. With 20,000 rows, you are *potentially* talking about a table
in excess of 45mb (well over 23,000 blocks)... though obviously that's
before considerations like PCTFREE, block overhead and the actual amount of
data in each row are considered.
You have a 2mb buffer cache (1000 * 2048blocksize)...
just out of interest... select bytes,blocks from user_segments where
segment_name = 'table_name';
and what do you get?
> > a list of the indexes,
>
> no indexes
that's not helping... though I'd probably suggest this isn't an ideal field
to be indexing. If you index that field, the resulting index is likely to be
huge as well and may require frequent rebuilding based on update/insertion
activity. Based on your block size, Oracle may even decide to do a full
table scan anyway...
physical reads doesn't necessarily mean your buffer cache is too small. It
just means this information wasn't in the buffer. If you run the select
twice, the second time there will probably be no physical reads... however,
as mentioned, your buffer cache is 2mb - <sweeping statement>and that's
almost certainly too low for any application</sweeping statement>.
> i have a default install of the database, whats wrong with it?:
What's wrong is it is a default install. You should CUSTOMIZE.
> # replace "oracle" with your database name
> db_name=ORCL
>
> db_files = 1024 # INITIAL
>
> control_files = C:\orant\DATABASE\ctl1ORCL.ora
>
> db_file_multiblock_read_count = 8 # INITIAL
Higher will help the performance of full table scans.
> db_block_buffers = 1000 # INITIAL
With a block size of 2k, this is 2mb. Probably way to small.
> shared_pool_size = 11534336 # INITIAL
and yet you devote 11mb to caching SQL...
> log_checkpoint_interval = 10000
>
> processes = 59 # INITIAL
>
> parallel_max_servers = 5 #
> SMALL
>
> log_buffer = 8192 # INITIAL
>
> sequence_cache_entries = 10 # INITIAL
>
> sequence_cache_hash_buckets = 10 # INITIAL
>
> max_dump_file_size = 10240 # limit trace file size to 5 Meg each
>
> global_names = TRUE
>
> background_dump_dest=%RDBMS80%\trace
> user_dump_dest=%RDBMS80%\trace
>
> db_block_size = 2048
>
> remote_login_passwordfile = shared
>
> text_enable = TRUE
>
> job_queue_processes = 2
> job_queue_interval = 10
> job_queue_keep_connections = false
>
> distributed_lock_timeout = 300
> distributed_transactions = 5
>
> open_links = 4
I would say that you really need to read the Oracle Concepts chapter to get
more of an idea of what's going on. While most people here could guide you
into tuning this particular select, it's a fair bet that your application
does a lot more than select from one table and there will no doubt be
considerations to that effect.
I'd suspect there are considerable design flaws within the application
judging from the above... and a thorough read of the Concepts chapter will
help to explain that.
HTH.
AW
Steffen Ramlow
inline
"andrew_webby at hotmail" <sp...@no.thanks.com> wrote in message
news:991655346.29569.0...@news.demon.co.uk...
> so, a *possible* maximum ROW SIZE of 2355 bytes. Your db_block_size is
> 2048.... so you will run into excessive row chaining and migration almost
> immediately. With 20,000 rows, you are *potentially* talking about a table
> in excess of 45mb (well over 23,000 blocks)... though obviously that's
> before considerations like PCTFREE, block overhead and the actual amount
of
> data in each row are considered.
yeah, ok, u r absolutely right.
but:
SQLWKS> select max(length(path)) maxpath from structurefolder;
MAXPATH
----------
154
1 row selected.
and my database is absolutely NOT tuned, default installation settings
>
> You have a 2mb buffer cache (1000 * 2048blocksize)...
>
> just out of interest... select bytes,blocks from user_segments where
> segment_name = 'table_name';
> and what do you get?
where segment_name = 'table_name';?
> that's not helping... though I'd probably suggest this isn't an ideal
field
> to be indexing. If you index that field, the resulting index is likely to
be
> huge as well and may require frequent rebuilding based on update/insertion
> activity. Based on your block size, Oracle may even decide to do a full
> table scan anyway...
this is indeed the problem, on another database i have an index on the
column path, but it is not used
only: select path from structurefolder where path = '...' uses an index
scan, all others a table scan
u can imagine the path columns content as the paths in a filesystem:
structfolder1/structfolder1.1/structfolder1.1.1/...
would f1/f1.1/f1.1.1 be better? i think so.
> With a block size of 2k, this is 2mb. Probably way to small.
>
> > shared_pool_size = 11534336 # INITIAL
>
> and yet you devote 11mb to caching SQL...
well, this is what the oracle installer did ... :-)
really a bit strange!
i changed it to:
db_file_multiblock_read_count = 16
db_block_buffers = 5000
shared_pool_size = 5000000
does not help very much.
> I'd suspect there are considerable design flaws within the application
> judging from the above...
u mean the the rowsize may be greater than the block_size? i think a
production DB (80% selects, 20% insert/update) would have a block size
greater than 2k?
andrew_webby at hotmail
Tom was saying you may have perhaps analyzed the table before data loading,
so:
analyze table structurefolder compute statistics;
select num_rows,blocks,avg_row_len,chain_cnt from user_tables where
table_name = 'STRUCTUREFOLDER'
select bytes,extents from user_segments where segment_name =
'STRUCTUREFOLDER';
This will tell you the number of rows, how many blocks it is in (so how much
you have to read on a full table scan), the average row length and how many
of those rows are split over blocks. And also the current size of the table,
and how many extents it is currently in. If the table is huge (somehow) and
you created it with default sizing, that probably won't help matters either.
Your cache is now 10mb, so try Tom's explanation of how to Explain what
Oracle is doing. Don't create a new table, just run the select's on your
structurefolder table and post the statistics.
ps. remember when I said?
"I would say that you really need to read the Oracle Concepts chapter to get
more of an idea of what's going on. While most people here could guide you
into tuning this particular select, it's a fair bet that your application
does a lot more than select from one table and there will no doubt be
considerations to that effect."
That's still very true. I know you want the 'quick fix', but it really will
be just that - and only for this particular little problem. Unless you
really understand what's going on, you're just going to frustrate yourself.
"Steffen Ramlow" <s.ra...@gmx.de> wrote in message
news:9ffun2$3q74t$1...@ID-54600.news.dfncis.de...
> thx 4 ur fast answer !
>
>
Thomas Kyte
(if that email address didn't require changing)
>hi there,
>
>inline
>
>"Thomas Kyte" <tk...@us.oracle.com> wrote in message
>news:u1jfht0iceuquh2gt...@4ax.com...
>
>> We want to have at least 2 index entries per index leaf block.
>
>ahh, ok
>
>> Considering SS
>> limited you to 1965 bytes / row or something like that (does it still do
>> that?)....
>
>no, this was until sql 6.5
>now the limit is 8K
>
thats about the limit on the size of our biggest index.... rowsize (not
including clobs) is 4,000,000 ;)
>
>> lets see a describe of the table,
>SQLWKS> desc structurefolder
>
>Column Name Null? Type
>------------------------------ -------- ----
>OID NOT NULL VARCHAR2(35)
>DOCSPACE VARCHAR2(35)
>NAME VARCHAR2(50)
>PATH VARCHAR2(2000)
>DESCRIPTION VARCHAR2(200)
>ACLID VARCHAR2(35)
>
>
>> a list of the indexes,
>
>no indexes
>
well, your original question was:
>>>>a stmt like this runs about 1-2 seconds, an analysed index on path is not
>>>>used, ora makes a table scan
so that answers why we don't take the index path - you didn't have one?
>> and a cut and paste of
>> a session similar to the following.
>>
>> you've either analyzed the table before loading it or something else is
>up.
>>
>> As for the full scan taking 1-2 seconds, have you looked at your buffer
>cache or anything like that?
>
>no, whatz up with the buffer cache? too small?
>
it's 2meg, thats pretty tiny. In this case, we decided to read the data right
from disk. I would start with a 10meg cache and try from there. I have a
modest 23meg cache and tables like this are done without physical IO (after the
first time of course)
Steffen Ramlow
> >no indexes
> >
>
> well, your original question was:
>
> >>>>a stmt like this runs about 1-2 seconds, an analysed index on path is
not
> >>>>used, ora makes a table scan
>
> so that answers why we don't take the index path - you didn't have one?
yeah ok, no indexes on THIS table in THIS DB
but i have another DB with the same table, WITH an fresh analyzed index on
column path - the index is NOT used
> it's 2meg, thats pretty tiny. In this case, we decided to read the data
right
> from disk. I would start with a 10meg cache and try from there. I have a
> modest 23meg cache and tables like this are done without physical IO
(after the
> first time of course)
using 10MB or 20MB - it helps nothing
only if i call "analyze table structurefolder compute statistics;" the
statement becomes fast (even without the index on path in my DB), but the
statistics do not survive the instance shutdown??? after the
shutdown/restart the statement is slow again, coz of 2500 phy. reads - until
i run "analyze table structurefolder compute statistics;" then 0 phy. reads
r made and then the statement is fast of course
am i really missing the concepts?
Steffen Ramlow
select num_rows,blocks,avg_row_len,chain_cnt from user_tables where
table_name = 'STRUCTUREFOLDER';
NUM_ROWS BLOCKS AVG_ROW_LE CHAIN_CNT
---------- ---------- ---------- ----------
20701 2509 197 0
1 row selected.
SQLWKS> select bytes,extents from user_segments where segment_name =
'STRUCTUREFOLDER';
BYTES EXTENTS
---------- ----------
6563840 15
then i did the trace:
SQL> select * from structurefolder where path =
'/dmsystem/docSpace1/structureFolder4/';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=242 Card=2301 Bytes=
453297)
1 0 TABLE ACCESS (FULL) OF 'STRUCTUREFOLDER' (Cost=242 Card=2301
Bytes=453297)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2510 consistent gets
2509 physical reads
0 redo size
1316 bytes sent via SQL*Net to client
711 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
this i can execute serveral times - always 2509 phy. reads
then i did the analyze
SQL> analyze table structurefolder compute statistics;
Table analyzed.
it's always the same statics:
select num_rows,blocks,avg_row_len,chain_cnt from user_tables where
table_name = 'STRUCTUREFOLDER';
NUM_ROWS BLOCKS AVG_ROW_LE CHAIN_CNT
---------- ---------- ---------- ----------
20701 2509 197 0
1 row selected.
SQLWKS> select bytes,extents from user_segments where segment_name =
'STRUCTUREFOLDER';
BYTES EXTENTS
---------- ----------
6563840 15
and now
SQL> select * from structurefolder where path =
'/dmsystem/docSpace1/structureFolder4/';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=242 Card=2301 Bytes=
453297)
1 0 TABLE ACCESS (FULL) OF 'STRUCTUREFOLDER' (Cost=242 Card=2301
Bytes=453297)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2510 consistent gets
0 physical reads
0 redo size
1321 bytes sent via SQL*Net to client
710 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
no more phy. reads!
now i can execute the statement - it's always fast
then: shutdown/restart the instance: statement is slow again, analyze
table -> statement becomes fast, are the stats dropped when shutting down
the instance?
"andrew_webby at hotmail" <sp...@no.thanks.com> wrote in message
news:991664167.3820.0...@news.demon.co.uk...