Hi Michael,
Thanks for this, My intention is to pin the objects that have more reloads
Reg,
Venkat
michael.taylor@(protected):
Look at loads and invalidations in v$sqlarea.
PROMPT
SELECT 'session_cached_cursors' parameter, LPAD (VALUE, 5) VALUE,
DECODE (VALUE, 0, ' n/a', TO_CHAR (100 * used / VALUE, '990') || '%') USAGE
FROM (SELECT MAX (s.VALUE) used
FROM v$statname n, v$sesstat s
WHERE n.NAME = 'session cursor cache count' AND s.statistic# = n.statistic#),
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors', LPAD (VALUE, 5), TO_CHAR (100 * used / VALUE, '990') || '%'
FROM (SELECT MAX (SUM (s.VALUE)) used
FROM v$statname n, v$sesstat s
WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
AND s.statistic# = n.statistic#
GROUP BY s.SID),
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'open_cursors')
/
PROMPT SQL Parse Statistics
PROMPT
col sql format A80 wra
select substr(sql_text,1,900) sql, parse_calls, executions from v$sqlarea
where parse_calls > 100
and executions < 2*parse_calls
and executions > 100000
order by parse_calls desc, executions desc
/
PROMPT SQL With the Most Invalidations
PROMPT
col sql format A80 wra
SELECT SUBSTR(RTRIM(sql_text),1,900) "SQL", invalidations
FROM v$sqlarea
WHERE invalidations > 10
ORDER BY invalidations DESC
/
-Michael
-----Original Message-----
From: Venkat Rao [mailto:vvnrk2005@(protected)]
Sent: Thursday, November 23, 2006 1:04 PM
To: Drees, Torsten; 'clemens.bleile@(protected)
Subject: Re: AW: AW: [suse-oracle] Measure of CPU additions
Hi All,
Many thanks for your reply.
My next question is ..is it possible to find the reloads per object?
v$librarycache gives global wide and does not give individual object wise?
Reg,
Venkat
"Drees, Torsten" wrote:
Clemens is right, i verified it in the manual. should have done it BEFORE
the answer.
sorry for wrong answer.
Torsten
> -----Urspr?ngliche Nachricht-----
> Von: Clemens Bleile [mailto:clemens.bleile@(protected)]
> Gesendet: Donnerstag, 23. November 2006 10:50
> An: Drees, Torsten; suse-oracle@(protected)
> Betreff: RE: AW: [suse-oracle] Measure of CPU additions
>
>
> Don't change the parameter CPU_COUNT manually. Oracle detects
> the correct number of CPUs available automatically during startup.
>
> Clemens
>
> -----Original Message-----
> From: Drees, Torsten [mailto:Torsten.Drees@(protected)]
> Sent: Donnerstag, 23. November 2006 08:30
> To: suse-oracle@(protected)
> Subject: AW: [suse-oracle] Measure of CPU additions
>
>
>
> Hi,
>
> there is a database parameter called "cpu_count". If you increased the
> number
> of cpus after the initial database installation, you need to
> adjust it.
>
> Torsten
>
> > -----Urspr?ngliche Nachricht-----
> > Von: Venkat Rao [mailto:vvnrk2005@(protected)]
> > Gesendet: Mittwoch, 22. November 2006 21:26
> > An: suse-oracle@(protected)
> > Betreff: [suse-oracle] Measure of CPU additions
> >
> > Hi All,
> >
> > Apart from Statspack, load average(uptime) is there any other
> > tips/info that I can get to measure the gain of CPUs additions. our
> > syadmin had doubled the CPU's in our db server as lots of
> > process are in
> > the queue.
> >
> >
> > Reg,
> > Venkat
> >
> >
> >
> >
> > ---------------------------------
> > The all-new Yahoo! Mail goes wherever you go - free your
> > email address
> > from your Internet provider.
> >
>
> --
> To unsubscribe, email: suse-oracle-unsubscribe@(protected)
> For additional commands, email: suse-oracle-help@(protected)
> Please see http://www.suse.com/oracle/ before posting
>
--
To unsubscribe, email: suse-oracle-unsubscribe@(protected)
For additional commands, email: suse-oracle-help@(protected)
Please see http://www.suse.com/oracle/ before posting
---------------------------------
Try the all-new Yahoo! Mail . "The New Version is radically easier to use" - The Wall Street Journal
Send instant messages to your online friends http://uk.messenger.yahoo.com
