Re: Can view$ be pinned ?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, Frank B Hansen <frank4oraclel@xxxxxxxx>
- Date: Thu, 2 Dec 2004 09:52:50 +0100
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.
1) 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.
2) 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.
3) 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@xxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l[1]
--- Links ---
1
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
