Re: sql tuning on X$ table

Mr Eagle Fan,

Tuning something gives you 2 possible choices: 1) make something go faster
2) execute it less
(3) understand what you are doing

I have monitered and tuned quite a bit of databases and there is always a trade of on overhead of monitoring and detail of information. If this query takes so much of your time, it is because you execute so often or you do so little other work in the database.

I have plenty of experience with Direct SGA attach and I think you should stay away from it. Go back to the 2 (3) step program above and start fixing your monitoring tool.

Anjo

On 11/30/06, Eagle Fan <eagle.f_at_gmail.com> wrote:
>
> hi:
>
> We have a monitoring tool to monitor database performance.
>
> It run the following sql every few seconds and it cost a lot of CPU times.
> About 20% of total CPU time.
>
> select kslltnum latch#,sum(kslltwgt) gets,sum(kslltwff) misses,
> sum(kslltwsl) sleeps from x$ksllt group by
> kslltnum;
>
> The sql is used to collect latch statistics, latch#, gets, misses, sleeps
> and then get the top heavy latch contentions.
>
> I have no idea how to tune this sql. It's on x$ table.
>
> I have read kyle's presentation about direct SGA access and I think it may
> help the sql.
>
> Does anyone have experiences of using direct SGA access on production
> system?
>
> Any risks? How many performance gains?
>
> Thanks
>
> --
> Eagle Fan
>
> Oracle DBA

-- 
Anjo Kolk
Owner and Founder OraPerf Projects
tel:    +31-577-712000
mob: +31-6-55340888

--
http://www.freelists.org/webpage/oracle-l