Re: SELECT table.currval FROM dual faster way to do this???

The 'ratio' is actually number of X per execution, so it is 42 rows per execution. There are versions of Oracle, however, with a bug in the value of ROWS_PROCESSED in v$sql/v$sqlarea.

>b) cache_size=20 Default parameter left out.
>> >Executions 641

641 executions with a cache size of 20 would guarantee 32 updates to the seq$ entry, which may have been included in the costing.

> sequence_cache_entries=100
> sequence_cache_hash_buckets=50
>
>I left this one out of Init.ora...So it is default.
> sequence_cache_hash_entries=7
>
> 176 sequences in each of 2 Schemas.

Looks like I got the parameter name wrong, there are only two relating to sequences. 352 sequences, 100 cache entries - could result in much more access to seq$ to reload sequences if there was any degree of contention between them.

Try:

    alter session set sql_trace true;
    select 'adsf' from dual;
    select pdmlogseq.nextval FROM dual
    select pdmlogseq.currval FROM dual

then look at the actual details in the trace file to see what it says about cost of SQL.

BTW - just realised, rather late, that your statement was 'select currval' not 'select nextval' - which makes the problem rather more of a puzzle, since this implies the problem was down to getting a copy of the data block from DUAL, and nothing to do with the sequence !

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Wed Jun 16 1999 - 11:30:59 CDT