The SQL Plan Directive Written to the SYSAUX Tablespace Is Not Accounted For in V$SYSAUX_OCCUPANTS
(Doc ID 2464177.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Goal
- In DBA_Segments, the area of V$SYSAUX_OCCUPANTS was not large although there are many SYSAUX tablespaces.
-----------------------------------------------------------------
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
-----------------------------------------------------------------
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
-----------------------------------------------------------------
- Therefore, when checking with the extent information acquisition script of the tablespace, it seemed that the following area is occupying a large percentage.
- 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;
/
=============================================
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 |