Month: February 2023

It is possible to resize a tablespace datafile is there is some free space at the end of the datafile. This is the quick and easy method. If you have data at the end of the datafile, you will need to move the objects so that segment will be recreated.

View tablespace size with free space

select  df.tablespace_name "Tablespace", df.totalspace "Total MB", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by 2 desc ;

Generate command to reduce tablespace (specify block size and tablespace name)

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents where tablespace_name= '&&tbs_name' 
group by file_id ) b
where a.file_id = b.file_id(+) and a.tablespace_name= '&&tbs_name'
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

Q Processes take more and more memory and does not release the memory.
pga_aggregate_limit=0 allows pga to grow unlimited hence the Q processes can consume all server memory.

Temporary workaround is to reset the AQ slaves so that it is not necessary to shutdown/startup the db instance

show parameter aq_tm_processes

--Reset AQ slaves:

connect / as sysdba
alter system set aq_tm_processes=0 scope=memory sid='*' ;

--delete AQ SESSIONS

SELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--Start back AQ slaves:

connect / as sysdba
alter system set aq_tm_processes=1 scope=memory sid='*';

Queries to Monitor Q Processes.

--PGA Memory Stat
SELECT name, round(((value/1000)/1024),2) as value_mb, round((((value/1000)/1024)/1024),2) as value_gb FROM V$PGASTAT;

--Find the Q Processes
SELECT SID,SERIAL#,OSUSER,PROCESS,MACHINE,PROGRAM,TYPE,MODULE,ACTION,EVENT 
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SELECT 'ALTER SYSTEM KILL SESSION ' || CHR(39) || SID || ',' || SERIAL# || CHR(39) || ' IMMEDIATE;'
FROM V$SESSION 
WHERE TYPE = 'BACKGROUND' AND MODULE = 'Streams' AND ACTION LIKE 'EMON%' AND EVENT = 'EMON slave idle wait'
ORDER BY SID, SERIAL#;

--03-memory-usage-in-details.sql - Q Processes
select
    to_char(ssn.sid, '999999')                           as session_id,
    ssn.serial#                                          as session_serial,
    nvl(ssn.username, nvl(bgp.name, 'background'))
    || '::'
    || nvl(lower(ssn.machine), ins.host_name)            as process_name,
    to_char(prc.spid, '999999999')                       as pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') as current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') as maximum_size_mb
from
    v$statname    stat1,
    v$statname    stat2,
    v$session     ssn,
    v$sesstat     se1,
    v$sesstat     se2,
    v$bgprocess   bgp,
    v$process     prc,
    v$instance    ins
where
    stat1.name         = 'session pga memory'
    and stat2.name     = 'session pga memory max'
    and se1.sid        = ssn.sid
    and se2.sid        = ssn.sid
    and se2.statistic# = stat2.statistic#
    and se1.statistic# = stat1.statistic#
    and ssn.paddr      = bgp.paddr (+)
    and ssn.paddr      = prc.addr  (+)
    and ssn.sid in (
        select sid
        from v$session
        --where username = 'MY_USERNAME' -- your user/schema name
        where type = 'BACKGROUND' and module = 'Streams' and action like 'EMON%' and event = 'EMON slave idle wait'
    )
order by
    maximum_size_mb desc;

Check also that AQ_SRVNTFN_TABLE_1 is set to dequeue. It is possible that DEQUEUE_ENABLED is set to NO.

AQ_SRVNTFN_TABLE_1
ENQUEUE_ENABLE=YES
DEQUEUE_ENABLED=NO

confirm the status of the queue:

-- Workaround to apply on all CDB and PDB
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where NAME = 'AQ_SRVNTFN_TABLE_Q_1';

select owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLED 
from dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS';

exec dbms_aqadm.stop_queue( 'AQ_SRVNTFN_TABLE_Q_1', true , true );
exec dbms_aqadm.start_queue( 'AQ_SRVNTFN_TABLE_Q_1' , true, true );

Last, times, we noticed high cpu and memory usage on our database server. Top memory consuming process was some ora_j000_* process which are spawned as part of execution of auto task scheduler job . It was using also lots of swap.

After reviewing, we found that the process was running “Optimizer statistics advisor” task name AUTO_STATS_ADVISOR_TASK ( job name ORA$AT_OS_OPT_SY_<…>)

And it was executing following recursive SQL:

SQL_ID: as2dr3ag24gay
select not_stale.obj# from (select s.obj# obj#, count(*) cnt, max(timestamp) max_time from
optstat_snapshot$ s, wri$_optstat_tab_history t where s.obj# = t.obj# and bitand(s.flags, :gather_stats_flag) > 0 and
t.analyzetime = (select max(analyzetime) from wri$_optstat_tab_history where analyzetime < s.timestamp) and
dbms_stats_internal.is_stale(s.obj#, null, null, s.inserts + s.updates + s.deletes, t.rowcnt, 0) is null group by s.obj#) not_stale,
(select obj# obj#, count(*) cnt from optstat_snapshot$ where bitand(flags, :gather_stats_flag) > 0 group by obj#) total,
(select obj#, max(timestamp) max_time from optstat_snapshot$ group by obj#) max where not_stale.obj# = total.obj# and
not_stale.cnt > 0.5 * total.cnt and not_stale.obj# = max.obj# and not_stale.max_time = max.max_time and
dbms_stats_internal.check_advisor_obj_filter(:rule_id, not_stale.obj#, :exec_mode) = 'T'

On oracle support site, we found that the issue is related to the following bug:
Bug 34201470 – JNNN PROCESSES CONSUMING 100% CPU AND HIGH MEMORY DURING AUTOTASK EXECUTION

To resolve the issue, you can apply patch for Bug 34201470
(Please note that this fix above does the same thing the workaround does below, it only turns off AUTO_STATS_ADVISOR_TASK)

 To workaround:  exec dbms_stats.set_global_prefs(‘AUTO_STATS_ADVISOR_TASK’, ‘FALSE’);

Please note that the Statistics Preference AUTO_STATS_ADVISOR_TASK is available ONLY when patch 26749785 is present otherwise, there is nothing that can be done.