Re: sql tuning on X$ table
- From: "Anjo Kolk" <anjo.kolk@xxxxxxxxxxx>
- To: eagle.f@xxxxxxxxx
- Date: Fri, 1 Dec 2006 15:57:24 +0100
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@xxxxxxxxx> 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
Other related posts:
