My Oracle Support Banner

The SQL Plan Directive Written to the SYSAUX Tablespace Is Not Accounted For in V$SYSAUX_OCCUPANTS (Doc ID 2464177.1)

Last updated on NOVEMBER 04, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Goal

-----------------------------------------------------------------
SQL> Select (sum(bytes)/1024/1024) from dba_segments
  2  where tablespace_name='SYSAUX';

 (SUM(BYTES)/1024/1024)
 ----------------------
           33000.0000
-----------------------------------------------------------------

-----------------------------------------------------------------
SQL> select SCHEMA_NAME,OCCUPANT_NAME,trunc(SPACE_USAGE_KBYTES/1024,1) "SPACE_USAGE_MBYTES"
  2  from v$sysaux_occupants order by 3 desc;

SCHEMA_NAME     OCCUPANT_NAME            PACE_USAGE_MBYTES
--------------- ------------------------ -----------------
SYS             SM/OPTSTAT                         18000.0
SYS             SM/ADVISOR                          3000.0
PERFSTAT        STATSPACK                            140.0
-----------------------------------------------------------------

 


 - OPT_FINDING_OBJ$
 - OPT_DIRECTIVE$

And when we put data in OPT_FINDING_OBJ $ with INSERT below, It was confirmed that it was not reflected in V$SYSAUX_OCCUPANTS.

=============================================
begin
for i IN 1..100000000 loop
INSERT INTO OPT_FINDING_OBJ$ (
       F_ID, F_OBJ#, OBJ_TYPE, CVEC_SIZE, FLAGS
) VALUES (
       0020, 4294953191,
       1,
       0,
       8
) ;
end loop;
commit;
end;
/
=============================================

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.