Hello Chris,
at first I would use latchprof(X)
(https://github.com/tanelpoder/tpt-oracle/blob/master/latchprof.sql / ;
https://github.com/tanelpoder/tpt-oracle/blob/master/latchprofx.sql) by Tanel
Poder to troubleshoot the latch issue causer.
Afterwards you can drill down (with the gathered latch information) to the
specific sub-pool as each shared pool child latch (max 7) protects one sub-pool.
You can also capture why the latch is requested so often (from the causer) and
get stack traces all the way up but this is pretty tough to explain via mail :)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Chris Taylor <christopherdtaylor1994@xxxxxxxxx> hat am 31. Mai 2018 um 18:29--
geschrieben:
version 12.1
Linux x86-64
Background:
We've got a database with 4TB of RAM and a 260GB shared pool. Everything was
running normally until 2 nights ago when a regularly scheduled job that does
TONS of DDL suddenly blew up the shared pool.
Trying to query x$ksmsp for shared pool sizes is also causing huge amounts of
latch waits.
When these jobs aren't running, the db is working normally. Starting just
one of the jobs brings back the shared pool latching problem immediately.
I'm thinking the shared pool is heavily fragmented but am looking for
additional queries or views I can use to diagnose shared pool problems.
We're not running into any shared pool errors as far as memory allocations.
This query hangs and causes huge contention:
select ksmchcls "ChnkClass",
sum(ksmchsiz) "SumChunkTypeMem",
Max(ksmchsiz) "LargstChkofThisTyp",
count(1) "NumOfChksThisType",
round((sum(ksmchsiz)/tot_sp_mem.totspmem),2)*100||'%' "PctTotSPMem"
from x$ksmsp,
(select sum(ksmchsiz) TotSPMem from x$ksmsp) tot_sp_mem
group by ksmchcls, tot_sp_mem.TotSPMem
order by 5;
The Jobs in question do PARTITION EXCHANGES for many, many sites with temp
tables that are built as part of the job.
The DB has been up since: 04/03/2018 07:12:23 and the last 2 days are the
first time this has been seen and is repeatable by restarting any one of the
jobs (there are 8 total)
Thinking about flushing the shared pool (or restarting db off hours) as a
test to see if it resolves the issue (like throwing a grenade into a anthill
resolves a problem with ants...not the best approach but might be effective)
Any thoughts/comments/insults?
Thanks,
Chris
