Hi Oracle Gurus,
We have 12.2 (with April PSU 2018) oracle database. When the application run, database sessions waiting on enq: TX - contention & L1 validation event event.
SELECT event,
ROUND (cnt \* 100 / (SUM (cnt) OVER ()), 2) "%",
ROUND (SUM (cnt) OVER () / 6, 2) "DB Time in Minute",
ROUND (SUM (cnt) OVER () / 6 /20,2) "AAS",
(select value from v$parameter where name='cpu\_count') "Cpu Count"
FROM (SELECT NVL (event, 'CPU') event, COUNT ( * ) cnt
FROM gv$active\_session\_history
GROUP BY event
ORDER BY 2 DESC);
SELECT sql_id, sample, ROUND (sample * 100 / SUM (sample) OVER (), 2) "%"
FROM (SELECT sql\_id, COUNT ( \* ) sample
FROM gv$active\_session\_history
WHERE event = 'L1 validation' --
GROUP BY sql\_id
ORDER BY 2 DESC);
This sql is a simple update statement. After then when we look the tablespace of updated table we see that tablespace have autoextend datafiles but cannot extend. We have a free space on ASM.
We manually extend the datafiles. And then issue was resolved.
What is the reason of these problem? We hit these issue also another 12.2 database. IS anybody seen this problem also?
Regards.
Baki.