Re: Can view$ be pinned ?

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 2 Dec 2004 09:52:50 +0100
Message-Id: <200412020852.iB28qoHD011956@webmail.nexlink.net>

Frank,

IMHO it is a bad idea to try, in whichever way, to improve on the way SYS objects work. If Oracle cannot properly take care of its own data, then the situation is hopeless. I would rather say that the thing to check is the deepreason behind those calls.

SELECT SYSDATE FROM DUAL : is it useful? Why not use SYSDATE directly in the INSERT or UPDATE statements? Is the time component of the date really useful in your application? Otherwise I guess that the initialisation sectiàon of a package, assuming of course that sessions don't stay connected overnight, could do.
Sequences. You know how it works. Oracle updates the next value by 'increment' into seq$, and loads 'increment' values up to this 'next value' on disk in a cache. Once all values in cache have been used, it does it again. Increase the CACHE value of the sequences you use most.
Recursive dictionary queries are run because the information isn't found in the library cache. Either you have an enormous number of views, each user having his/her own set, in which case it would be wise to see how they are defined (do they use pseudo columns like USER or functions such as sys_context(), allowing a same view to return different dat to different users). Or your library cache is definitely too tiny (V$LIBRARYCACHE ?) and the solution is probably to increase the size of your shared_pool.

Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com

On Thu, 2 Dec 2004 09:12 , Frank B Hansen <frank4oraclel_at_yahoo.dk> sent:

Hi List

I do have a performance problem... The following stats are summed up over 10 days.

#Parse = #Executions !! (not good)

SQL text Execs Rows proc Parse calls % impact
---------------------------------------- -------- --------- -----------

Query 1 854391 854391 854391 12.14
Query 2 1303493 1303491 1303493 18.52
Query 3 3926789 3926775 3926789 55.79

Query 1: SELECT SYSDATE FROM DUAL

Query 2: update seq$ set increment$=:2,minvalue=:...

Query 3: select text from view$ where rowid=:1

Any suggestions how to get the number of parses down ? Can objects owned by SYS be pinned or is that the wrong idea ?

Thanks, Frank

--

http://www.freelists.org/webpage/oracle-l[1]

Links --- 1 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 02 2004 - 02:32:55 CST