I don't have enough information to explain the increased elapsed time. If you
run the tests again with event 10046 set at level 8 instead of level 1 (which is
how sql_trace is implemented) we should be able to explain it.

There is however a better solution here than the index organized table. Why
don't you try a single table hash cluster? That will just require 1 block get
per fetch, instead of 2 (or 3 as you had at first).


-----Original Message-----
Sent: Wednesday, 30 August 2000 23:33


Our application uses so-called dynamic constants instead of standard
Oracle constants, stored in packages and procedures, i.e. there's a simple
table
	APP_CONSTANT (
	 constant_name	varchar2(250),
	 constant_type	varchar2(4),
	 value_num		number,
	 value_date		date,
	 value_str		varchar2(250),
         constraint app_constant__PK PRIMARY KEY (constant_name))
that stores all the constants used througout the application.
So, when we need the value of any constant, we just call a function
(get_num_constant, get_str_constant, etc.) with simple SELECT expression
like this:
	select value_num
		into XXX
		from app_constant
		where constant_name = :CName
			and constant_type = 'NUM';

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0          0
Execute  38244      4.48       5.25          0          0          0      38287
Fetch    38244      4.97       6.18          0     114732          0      38244
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total    76488      9.45      11.43          0     114732          0      76531

Misses in library cache during parse: 0

Upon analyzing TKPROF output we realized, that total number of buffers
(query) could be descreased if using index-organized table. So it was (see
new results below), but total elapsed time surprisingly increased:

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0          0
Execute  35505      4.65      11.55          0          0          0      35542
Fetch    35504      4.81      12.82          0      71082          0      35504
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total    71009      9.46      24.37          0      71082          0      71046

Misses in library cache during parse: 0

Is there any explanation of this phenomenon?
Are there any other (better) ways of managing this table?