Whats the value of _kghdsidx_count on that system*?*
On Fri, 1 Jun 2018, 01:55 Chris Taylor, <christopherdtaylor1994@xxxxxxxxx>
wrote:
Well, I wasn't aware such a thing could occur in a non-memory managed db
so that's kind of scary.
So, I just confirmed that the only resize operations that occurred were
the manual ones I did this morning as an attempt to see if the latching
improved. I *do* think that its related to the subpools related to
partition maintenance:
These were all mine as purely a troubleshooting step to see if it added
any relief:
PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
NOTE
db_cache_size 2200096997376 2195802030080 2195802030080 COMPLETE 05/31/2018
09:07 05/31/2018 09:07 Decreased Buffer Cache by 5GB
shared_pool_size 274877906944 275951648768 275951648768 COMPLETE 05/31/2018
09:08 05/31/2018 09:08 Increase shared pool by 1 GB
shared_pool_size 275951648768 279172874240 279172874240 COMPLETE 05/31/2018
09:09 05/31/2018 09:09 Increase shared pool by 4 GB
Thanks,
Chris
On Thu, May 31, 2018 at 1:29 PM, GG <grzegorzof@xxxxxxxxxx> wrote:
Hi,
You've got quite large shared pool, it is true that quering x$ksmsp is
not recommended due to excesive latch activity, but Oracle uses this query
to narrow down the shared pool issues:
SELECT * FROM ( SELECT ksmchidx subpool, 'DURATION:'||ksmchdur duration,
'CMNT: '||ksmchcom cmnt, ksmchcls type, SUM(ksmchsiz) total_size, COUNT(*)
allocations, 'AVG:' avg,AVG(ksmchsiz) average_size, MIN(ksmchsiz) min_size,
'MAX:' max,MAX(ksmchsiz) max_size FROM x$ksmsp
GROUP BY ksmchidx, ksmchdur, ksmchcom, ksmchcls ORDER BY SUM(ksmchsiz))
WHERE (ROWNUM < 21 AND total_size > 10000 AND cmnt NOT LIKE 'CMNT:free%')
OR cmnt LIKE 'CMNT: free%'order by 3,4,1,2
/
Your problem could be related to subpool defragmentation which seems to
be often the case with exchange partition (generally partition
maintenance?).
And indeed the shared pool can grow like crazy even when auto memory
management is inactive (_target parameters = 0), it is documented
SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled
(MEMORY_TARGET/SGA_TARGET=0) (Doc ID 1269139.1)
Check the resize ops like Tim said via query on :
V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS
Regards.
GG
