Re: latch: row cache objects - not parsing
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: kutrovsky.oracle@xxxxxxxxx
- Date: Wed, 2 Jun 2010 14:49:23 -0700
Do you have an ASH Report for the time in question?
you can run @?/rdbms/admin/ashrpt.sql
or
select output
from table(dbms_workload_repository.ash_report_text(
(select dbid from v$database),
1,
start_time,
end_time,
0)) ;
I like to use
http://www.perfvision.com/ash/aveactn.sql
to identify the problem timeslot and then run ashrpt.
output looks like
http://www.perfvision.com/ash/aveactn.txt
For the latch, seems like it should have to do with accessing info in the
data dictionary. The one case I heard of on this, the customer added hints
to the query to help the optimizer - or that was the theory and it seemed to
actually work as the latch waits went down, but then I never got to analyze
the situation myself. If this was the case and it applies to you then I'd
expect to see some sort of parsing or recursive queries. ASH says
"IN_SQL_EXECUTION" so it shouldn't be parsing (if it's to be trusted). Do
you have much recursive SQL showing up in TOP_LEVEL_SQL_ID in the ASH data?
Best Wishes
Kyle Hailey
http://db-optimizer.blogspot.com/
On Wed, Jun 2, 2010 at 1:04 PM, Christo Kutrovsky <
kutrovsky.oracle@xxxxxxxxx> wrote:
> Hello All,
>
> I am having a weird issue on Oracle 11.2 64 bit SPARC with T2+ CPUs.
>
> Occasionally my loading process (bulk insert statement) will have a large
> number of consecutive waits (as reported by ASH) of "latch: row cache
> objects". The statement is not been reparsed, and ASH reports
> "IN_SQL_EXECUTION".
>
> I do have "blocked by" information, and that is pointing to a parallel
> merge statement been executed at the same time. The blockee also shows "N"
> for all "IN_xx" columns in ASH. The
>
> Does anyone know how to get more information for which objects from the
> rowcache I am contending, and why at all?
>
> I am suspecting it's the parallel merge that is affecting the system
> globally, as a lot of queries just "don't run" during these short freezes.
>
>
> --
> Christo Kutrovsky
> Senior Consultant
> Pythian.com
> I blog at http://www.pythian.com/blogs/
>
Other related posts:
