CHECK IF A NUMBER IS PERFECT SQUARE OR NOT IN PYTHON
def sqrt(x):
sqrt = x ** (1/2)
if x % (sqrt) == 0:
return round(sqrt)
else:
return -1EXPORT/IMPORT A MATERIALIZED VIEW
In this article, mview is exported from source and imported back to same source for demonstration and should not be performed in production.
Check the existing mview to be exported.
SQL> col OWNER for a20
SQL> col MVIEW_NAME for a20
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE FRESH
Can we export a materialized view using expdp ? Yes, it can be achieved by “INCLUDE” option.
SQL> conn kish/password
Connected.
SQL>
SQL>
SQL> CREATE MATERIALIZED VIEW mvremote BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM KISH.YTBL;
Materialized view created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@xhydra ~]$ expdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=MATERIALIZED_VIEW
Export: Release 19.0.0.0.0 - Production on Mon Jun 12 19:33:06 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/mview.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 19:34:37 2023 elapsed 0 00:01:25
Also it is always advisable to export mview along with its container table as well to avoid any import errors with,
ORA-39083: Object type MATERIALIZED_VIEW:”KISH”.”MVREMOTE” failed to create with error:
ORA-00942: table or view does not exist
[oracle@xhydra ~]$ expdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" reuse_dumpfiles=Y
Export: Release 19.0.0.0.0 - Production on Mon Jun 12 20:24:30 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') reuse_dumpfiles=Y
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "KISH"."MVREMOTE" 277.0 MB 5000000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/mview.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 20:24:57 2023 elapsed 0 00:00:26
To identify the container table, use the below query.
SQL> col OWNER for a20
SQL> SELECT owner,object_name, object_type FROM dba_objects WHERE object_name = 'MVREMOTE';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- --------------------
KISH MVREMOTE TABLE
KISH MVREMOTE MATERIALIZED VIEW
To import a materialized view, use the below steps.
[oracle@xhydra ~]$ impdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" table_exists_action=replace
Import: Release 19.0.0.0.0 - Production on Mon Jun 12 20:29:28 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KISH"."MVREMOTE" 277.0 MB 5000000 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Jun 12 20:29:35 2023 elapsed 0 00:00:06
After importing the mview newly in the database, the staleness of mview becomes IMPORT because of the incomplete refresh. So a complete refresh need to be performed in order to synchronize the status to FRESH.
SQL> col OWNER for a20
SQL> col MVIEW_NAME for a20
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE IMPORT <===
Also, do we need to reregister the mview after import? No, since the container table is also imported with mview which are referenced objects, there is no need to register the mviews after import.
SQL> col NAME for a20
SQL> col OWNER for a20
SQL> col QUERY_TXT for a20
SQL> col MVIEW_SITE for a20
SQL> col VERSION for a20
SQL> set lines 200 pages 1000
SQL> select * from dba_registered_mviews where name ='MVREMOTE';
OWNER NAME MVIEW_SITE CAN UPD REFRESH_MET MVIEW_ID VERSION QUERY_TXT
-------------------- -------------------- -------------------- --- --- ----------- ---------- -------------------- --------------------
KISH MVREMOTE EXHYDRA YES NO PRIMARY KEY 21 ORACLE 8 MATERIALIZE SELECT "YTBL"."XNAME
D VIEW " "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."CO
KISH MVREMOTE DB9ZX YES NO PRIMARY KEY 81 ORACLE 8 MATERIALIZE SELECT "YTBL"."XNAME
D VIEW " "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."COAll the registered mviews details are stored in internal table SYS.REG_SNAP$.
SQL> SELECT * from SYS.REG_SNAP$;
SOWNER
--------------------------------------------------------------------------------------------------------------------------------
SNAPNAME
--------------------------------------------------------------------------------------------------------------------------------
SNAPSITE SNAPSHOT_ID FLAG REP_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ----------
COMMENT$
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY_TXT
--------------------
KISH
MVREMOTE
EXHYDRA 21 2097253 2
SELECT "YTBL"."XNAME
" "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."CO
KISH
MVREMOTE
DB9ZX 81 2097257 2
After a complete refresh, the mview is back to FRESH mode
SQL> show user
USER is "SYS"
SQL> conn kish/password
Connected.
SQL> EXEC dbms_mview.refresh('MVREMOTE');
PL/SQL procedure successfully completed.
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE FRESH
INFLUENCE PARALLEL DEGREE FOR SQL PROFILE
When it comes to sql profile, there are lot of questions which arise on the basic functionality. It is not always recommended to use a parallel sql profile in a on peak database because of the sudden burst of parallel spawns.
Even oracle itself ignored the parallel plan during the tuning process.
DBMS_SQLTUNE.REPORT_TUNING_TASK('A6BWKQ27KB1J2_TT')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : a6bwkq27kb1j2
SQL Text : SELECT * from ytbl
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "coe_a6bwkq27kb1j2_192604670" exists for this statement and was
ignored during the tuning process.
- Can we change parallel for sql profile manually?
- Can multiple sql use same sql profile?
- Can multiple sql profiles be used for one sql?
- If sql id changes, then can sql profile be used for the different one?
Does sql profile has anything to do with parallel degree for a sql statement?
No matter the number of parallel degree used in the sql profile, if the table property has a degree of n, then parallel n is used for the sql statement during execution unless any hint is specified explicitly to override the table property.
Set the table property for parallel as 8.
SQL> ALTER TABLE ytbl parallel 8;
Table altered.
Execute the query and check the parallel used for the query. There are 8 parallel slaves spawned for the query.
SQL> SELECT * from ytbl;
SQL>col spid for a10
SQL>set lines 200 pages 1000
col NAME for a20
SELECT pxp.server_name,pxp.status,pxp.spid,
pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
pss.degree,pss.req_degree,sn.name,
pxp.server_name,pxp.status,pxp.spid
ORDER BY sn.name;SQL>SQL> 2 3 4 5 6 7 8 9 10 11 12
SERV STATUS SPID SID SERIAL# QCSID QCSERIAL# DEGREE REQ_DEGREE NAME VALUE
---- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
P004 IN USE 36800 265 28 237 48799 8 8 physical reads 341
P007 IN USE 36806 33 6187 237 48799 8 8 physical reads 312
P005 IN USE 36802 270 20347 237 48799 8 8 physical reads 312
P001 IN USE 26532 38 35986 237 48799 8 8 physical reads 312
P006 IN USE 36804 37 40124 237 48799 8 8 physical reads 312
P003 IN USE 26557 273 41465 237 48799 8 8 physical reads 312
P002 IN USE 26555 32 50163 237 48799 8 8 physical reads 312
P000 IN USE 26530 263 56350 237 48799 8 8 physical reads 312
P004 IN USE 36800 265 28 237 48799 8 8 physical reads direc 341
t
P007 IN USE 36806 33 6187 237 48799 8 8 physical reads direc 312
t
P005 IN USE 36802 270 20347 237 48799 8 8 physical reads direc 312
t
P001 IN USE 26532 38 35986 237 48799 8 8 physical reads direc 312
t
P006 IN USE 36804 37 40124 237 48799 8 8 physical reads direc 312
t
P003 IN USE 26557 273 41465 237 48799 8 8 physical reads direc 312
t
P002 IN USE 26555 32 50163 237 48799 8 8 physical reads direc 312
t
P000 IN USE 26530 263 56350 237 48799 8 8 physical reads direc 312
t
16 rows selected.
Check the sql id and hash value for the sql
SQL> SELECT SQL_ID,PLAN_HASH_VALUE from v$sql_plan WHERE sql_id='a6bwkq27kb1j2';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
a6bwkq27kb1j2 192604670
Check the execution plan for the sql. Degree 8 is used.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 310 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 986K| 217M| 310 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 8 because of table property
26 rows selected.
Merge the sql id and execution plan to create a sql profile script.
SQL> @coe_xfr_sql_profile.sql a6bwkq27kb1j2 192604670
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
192604670 1.241
......
Execute coe_xfr_sql_profile_a6bwkq27kb1j2_192604670.sql
on TARGET system in order to create a custom SQL Profile
with plan 192604670 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_a6bwkq27kb1j2_192604670.sql
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_a6bwkq27kb1j2_192604670 completed
Check if the sql profile is used for the sql.
SQL>col SQL_TEXT for a20
SQL>col SQL_PROFILE for a20
SQL>SELECT sql_id,sql_text,sql_profile FROM v$sql WHERE sql_id='a6bwkq27kb1j2';
SQL_ID SQL_TEXT SQL_PROFILE
------------- -------------------- --------------------
a6bwkq27kb1j2 SELECT * from ytbl coe_a6bwkq27kb1j2_19
2604670
SQL profile is executed with degree of parallelism from table property.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 310 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 310 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 8 because of table property
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
26 rows selected.
To check the contents of the sql profile, use the below query. Note that there is no parallel hints in the sql profile and parallelism is independent of sql profile created
SQL>SELECT extractvalue(VALUE(ohint), '.') "Outline hint" from DBMSHSXP_SQL_PROFILE_ATTR sp, TABLE(xmlsequence(extract(xmltype(sp.comp_data), '/outline_data/hint'))) ohint WHERE profile_name='coe_a6bwkq27kb1j2_192604670';
Outline hint
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "YTBL"@"SEL$1")
END_OUTLINE_DATA
8 rows selected.
Increase the parallel at table level.
SQL>ALTER TABLE ytbl parallel 16;
Table altered.
Irrespective of the number of degree of parallel, sql profile is used for the query.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 155 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 16 because of table property
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
26 rows selected.
Check the parallel properties for degree.
SQL> show parameter parallel_degree
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
containers_parallel_degree integer 65535
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
Change the parallel_degree_policy to auto and parallel degree limit to a value other than default value CPU.
SQL> ALTER SYSTEM set parallel_degree_policy=AUTO;
System altered.
SQL> ALTER SYSTEM set parallel_degree_limit=8;
System altered.
Disable the parallel for the table.
SQL> ALTER TABLE ytbl parallel 1;
Table altered.
Now, full table scan is used without parallel. Also same sql profile can be seen used for multiple plans as well.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a6bwkq27kb1j2, child number 1
-------------------------------------
SELECT * from ytbl
Plan hash value: 2390399380
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2232 (100)| |
| 1 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 2232 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
17 rows selected.
Another observation is that, even if the sql id change for the query, still the profile is used!!
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 82fsp243u4fxg, child number 0
-------------------------------------
SELECT * from ytbl
Plan hash value: 2390399380
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2232 (100)| |
| 1 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 2232 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- SQL profile coe_a6bwkq27kb1j2_192604670 used for this statement
17 rows selected.
After changing the query logic itself, the sql profile cannot be used.
SQL> SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9yhf852k9177m, child number 0
-------------------------------------
SELECT * from ytbl WHERE rownum < 5
Plan hash value: 1669163498
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| YTBL | 4 | 208 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<5)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
23 rows selected.
Drop the sql profile.
SQL> BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_a6bwkq27kb1j2_192604670');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
Another way to use parallel with sql profile is to use parallel hint in the sql and create a sql profile on top of the parallel plan.
SQL> ALTER table ytbl parallel 1;
Table altered.
SQL> SELECT /*+parallel(16)*/ * from ytbl;
SQL>SELECT * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 68fn0x88bm4jc, child number 0
-------------------------------------
SELECT /*+parallel(16)*/ * from ytbl
Plan hash value: 192604670
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 155 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| YTBL | 1000K| 49M| 155 (1)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 16 because of hint
- SQL profile coe_68fn0x88bm4jc_192604670 used for this statement
26 rows selected.
library cache: mutex X
lcmx is an event which occur due to shared pool contention in SGA.
Shared pool is used to share the SQL area to be reused by multiple cursors. Every objects in shared pool are interrelated.
In order to understand any shared pool events, it is a prerequisite to understand CURSOR
cursor is an object in programming world to allocate a connection or pipeline to execute SQL queries or PLSQL. cursor can be used to create a connection, execute, fetch, pin, invalidate and reload etc.
To get an idea on the cursor, below example demonstrates a practical example,
Number of parse calls increase for sql statement as per the individual parameter or setting.
Child number increase as per the different settings used to execute the same sql.
kIsH@STAX<>alter session set "_hash_join_enabled"=false;
Session altered.
kIsH@STAX<>select count(*) from xtbl;
COUNT(*)
----------
1000000
kIsH@STAX<>alter session set "_hash_join_enabled"=true;
Session altered.
kIsH@STAX<>select count(*) from xtbl;
COUNT(*)
----------
1000000
kIsH@STAX<>SELECT sql_id from v$sql where sql_text like '%select count(*) from xtbl%';
SQL_ID
-------------
fmhv6xyq0tzg6
fmhv6xyq0tzg6
kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>SELECT SQL_ID,VERSION_COUNT,LOADED_VERSIONS,LOADS,INVALIDATIONS,PARSE_CALLS from v$sqlarea WHERE sql_id='fmhv6xyq0tzg6';
SQL_ID VERSION_COUNT LOADED_VERSIONS LOADS INVALIDATIONS PARSE_CALLS
------------- ------------- --------------- ---------- ------------- -----------
fmhv6xyq0tzg6 2 2 2 0 2
kIsH@STAX<>SELECT SQL_ID,CHILD_NUMBER,LOADED_VERSIONS,LOADS,INVALIDATIONS,PARSE_CALLS from v$sql WHERE sql_id='fmhv6xyq0tzg6';
SQL_ID CHILD_NUMBER LOADED_VERSIONS LOADS INVALIDATIONS PARSE_CALLS
------------- ------------ --------------- ---------- ------------- -----------
fmhv6xyq0tzg6 0 1 1 0 1
fmhv6xyq0tzg6 1 1 1 0 1
kIsH@STAX<>alter session set optimizer_mode=first_rows;
Session altered.
kIsH@STAX<>select count(*) from xtbl;
COUNT(*)
----------
1000000
kIsH@STAX<>SELECT SQL_ID,CHILD_NUMBER,LOADED_VERSIONS,LOADS,INVALIDATIONS,PARSE_CALLS from v$sql WHERE sql_id='fmhv6xyq0tzg6';
SQL_ID CHILD_NUMBER LOADED_VERSIONS LOADS INVALIDATIONS PARSE_CALLS
------------- ------------ --------------- ---------- ------------- -----------
fmhv6xyq0tzg6 0 1 1 0 1
fmhv6xyq0tzg6 1 1 1 0 1
fmhv6xyq0tzg6 2 1 1 0 1
Example: Consider table X. This table has a column c. There are millions of rows r in X.
If there is a maintenance activity on column c. If c is either dropped or existing attributes are modified for the column, then all relational objects become invalid and SQL statements which rely on the column data are affected. When the cursor become invalid, they need to be recompiled and reloaded into the shared pool again which has to go through latch, pin and locks. Cursor should be compiled to make the object valid. During compilation process, the cursor is pinned and locked to prevent other process from modifying them which further leads to cursor pin events, if the process holds the lock for long time. Thus, concurrency is maintained.
https://alphaoragroup.com/?p=18617
Primary reason for lcmx is inefficient parsing of sql statements
- Hard parse
- High number of Child cursors
- Cursor Invalidations
- Cursor Recompilation and Reloads
WHO PGM W_CHAINS SQL_ID TOTAL CHART_WCLASS STATE TAASH IAAS
------- ----- ---------------------------------------- ------------- ---------- -------------------- ---------- ------ ----------
SYS TNS V library cache: mutex X 11c4s0a35j48v 1506123 c/_\c/_\c/_\c/_\c/_ LONG_WAIT 20 .000077683
1-V3 \c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c
SYS TNS V latch free 11c4s0a35j48v 1329348 OOOOOOOOOOOOOOOOOOO LONG_WAIT 20 .000088013
1-V3 OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOO
SYS TNS V library cache: mutex X 766025 c/_\c/_\c/_\c/_\c/_ LONG_WAIT 20 .000152737
1-V3 \c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_\c/_
\c/_\c/_\c/_\c/_
SYS TNS V On CPU 11c4s0a35j48v 476114 ################### BURN_CPU 20 .000245739
1-V3 ####################
#######
SYS TNS V |latch free [|]<~~~ latch free| 11c4s0a35j48v 391262 OOOOOOOOOOOOOOOOOOO LONG_WAIT 20 .000299032
1-V3 OOOOOOOOOOOOOOOOOOO
SYS TNS V On CPU d8dxvkk7hxvq0 353550 ################### BURN_CPU 20 .000330929
1-V3 ###############
6 rows selected.
kIsH@STAX<>alter session set tracefile_identifier='lbm1';
Session altered.
kIsH@STAX<>alter session set events 'immediate trace name library_cache level 12';
Session altered.
kIsH@STAX<>select count(*) from kish.xtbl;
COUNT(*)
----------
1000000
kIsH@STAX<>alter session set "_hash_join_enabled"=false;
Session altered.
kIsH@STAX<>select count(*) from kish.xtbl;
COUNT(*)
----------
1000000
kIsH@STAX<>alter session set events 'immediate trace name library_cache off';
Session altered.
Bucket: #=69360 Mutex=0x764078a8(777389080576, 6, 0, 6)
LibraryHandle: Address=0x6cd99550 Hash=3970ef0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD Subpool=1
ObjectName: Name=CDB$ROOT.KISH.XTBL
FullHashValue=e2ae0aa2266ca38b5dd42deb03970ef0 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) ContainerId=1 ContainerUid=1 Identifier=74650 OwnerIdn=108
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=4
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
Concurrency: DependencyMutex=0x6cd99600(0, 2, 0, 0) Mutex=0x6cd996a0(181, 47, 0, 6)
Flags=PIN/TIM/[00002801] Flags2=[0000]
WaitersLists:
Lock=0x6cd995e0[0x6cd995e0,0x6cd995e0]
Pin=0x6cd995c0[0x6cd995c0,0x6cd995c0]
LoadLock=0x6cd99638[0x6cd99638,0x6cd99638]
Timestamp: Current=12-14-2022 17:26:06
HandleReference: Address=0x6cd99720 Handle=0x6cd662f8 Flags=OWN[200]
ReferenceList:
Reference: Address=0x6bef89b8 Handle=0x6c650728 Flags=DEP[01]
Timestamp=12-14-2022 17:26:06 InvalidatedFrom=0
Reference: Address=0x76e4f428 Handle=0x6cdc58b0 Flags=DEP[01]
Timestamp=12-14-2022 17:26:06 InvalidatedFrom=0
LibraryObject: Address=0x74631438 HeapMask=0000-0701-0701-0000 Flags=EXS/LOC[0004] Flags2=/CSO[8000000] Flags3=/DVC[0020] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^3970ef0 pins=0 Change=NONE
Heap=0x6ccd8ff8 Pointer=0x74631518 Extent=0x74631378 Flags=I/-/-/A/-/-/-
FreedLocation=0 Alloc=1.531250 Size=3.976562 LoadTime=01-24-2023 15:35:43
Block: #='8' name=KGLS^3970ef0 pins=0 Change=NONE
Heap=0x74631810 Pointer=0x6a0d9440 Extent=0x6a0d8760 Flags=I/-/-/A/-/-/-
FreedLocation=0 Alloc=5.078125 Size=7.898438 LoadTime=01-24-2023 15:35:43
Block: #='9' name=KGLS^3970ef0 pins=0 Change=NONE
Heap=0x74631968 Pointer=0x6a0d7590 Extent=0x6a0d6760 Flags=I/-/-/A/-/-/-
FreedLocation=0 Alloc=0.429688 Size=3.937500 LoadTime=01-24-2023 15:35:43
Block: #='10' name=KGLS^3970ef0 pins=0 Change=NONE
Heap=0x74631a00 Pointer=0x6a0d6670 Extent=0x6a0d5760 Flags=I/-/-/A/-/-/-
FreedLocation=0 Alloc=4.601562 Size=7.898438 LoadTime=01-24-2023 15:35:43
Bucket: #=99385 Mutex=0x7652d370(777389080576, 9, 0, 6)
LibraryHandle: Address=0x6f53e270 Hash=73218439 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD Subpool=1
ObjectName: Name=select count(*) from kish.xtbl
FullHashValue=175ea436befe4c3153afc49073218439 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=1931576377 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=4 LoadCount=3 ActiveLocks=0 TotalLockCount=4 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0
Concurrency: DependencyMutex=0x6f53e320(0, 2, 0, 0) Mutex=0x6f53e3c0(181, 50, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
WaitersLists:
Lock=0x6f53e300[0x6f53e300,0x6f53e300]
Pin=0x6f53e2e0[0x6f53e2e0,0x6f53e2e0]
LoadLock=0x6f53e358[0x6f53e358,0x6f53e358]
Timestamp: Current=01-24-2023 21:05:43
HandleReference: Address=0x6f53e450 Handle=(nil) Flags=[00]
ReferenceList:
Reference: Address=0x6bef7670 Handle=0x6c650300 Flags=ROD[21]
Reference: Address=0x7463ac68 Handle=0x6cd99978 Flags=ROD[21]
LibraryObject: Address=0x75f46708 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] Flags3=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^73218439 pins=0 Change=NONE
Heap=0x6f27f0e0 Pointer=0x75f467e8 Extent=0x75f46648 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=5.445312 Size=7.953125 LoadTime=01-24-2023 15:35:43
ChildTable: size='16'
Child: id='0' Table=0x75f47570 Reference=0x75f47108 Handle=0x6cdc58b0
Child: id='1' Table=0x75f47570 Reference=0x6bef9768 Handle=0x6c650728
NamespaceDump:
Parent Cursor: sql_id=57by4k1tk311t parent=0x75f467e8 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0
CursorDiagnosticsNodes:
ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(12) size=2x476 _hash_join_enabled= false true
ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(12) size=2x476 _hash_join_enabled= true false KGLH0 is a heap object which is connected to the Shared Pool’s SQL Area. KGLH0 is used by each child cursor.
When there are many of child cursors and the parent cursor is not shared, large allocation in KGLH0 can happen.
sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
sqlplus /nolog
connect / as sysdba
alter system set events '10235 level 65536';
exit
Then wait a while for the ORA-4031 (or at least the memory allocations) to occur, and then execute:
sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
once done you can disable the event using
alter system set events '10235 off';kIsH@STAX<>SELECT NAMESPACE,sum(GETS),sum(PINS),sum(RELOADS),sum(INVALIDATIONS) from dba_hist_librarycache GROUP BY NAMESPACE ORDER BY sum(GETS) DESC;
NAMESPACE SUM(GETS) SUM(PINS) SUM(RELOADS) SUM(INVALIDATIONS)
--------------- ---------- ---------- ------------ ------------------
SQL AREA 1359605 1810760712 5253 10401
TABLE/PROCEDURE 511634 902108609 2282 9
SCHEMA 247617 0 0 0
DBLINK 170599 0 0 0
BODY 164637 901113014 45 0
SQL AREA STATS 84322 84322 0 0
SQL AREA BUILD 83669 0 0 0
TRIGGER 62821 62821 0 0
EDITION 60946 60946 0 0
CLUSTER 23775 27501 0 0
INDEX 15179 12515 0 0
FED APP 5481 5481 0 0
CMP 2787 2787 0 0
OBJECT ID 2506 0 0 0
DIRECTORY 621 657 0 0
ACCOUNT_STATUS 549 0 0 0
APP STATE 324 0 0 0
PDB 270 0 0 0
QUEUE 255 345 0 0
PDBOPER 147 0 0 0
DBINSTANCE 90 0 0 0
SUBSCRIPTION 88 88 0 0
RULESET 45 135 0 0
USER PRIVILEGE 2 4 0 0
APP CONTEXT 2 4 0 0
XML SCHEMA 1 0 0 0
26 rows selected.
kIsH@STAX<>var x varchar2(10)
kIsH@STAX<>exec :x := 369
PL/SQL procedure successfully completed.
kIsH@STAX<>SELECT count(*) from t1 where N1=:x;
COUNT(*)
----------
1
kIsH@STAX<>select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 93uqjw965fzpy, child number 0
-------------------------------------
SELECT count(*) from t1 where N1=:x
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=TO_NUMBER(:X))
19 rows selected.
kIsH@STAX<>var x varchar2(20)
kIsH@STAX<>exec :x := 369
PL/SQL procedure successfully completed.
kIsH@STAX<>SELECT count(*) from t1 where N1=:x;
COUNT(*)
----------
1
kIsH@STAX<>select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 93uqjw965fzpy, child number 0
-------------------------------------
SELECT count(*) from t1 where N1=:x
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=TO_NUMBER(:X))
19 rows selected.
SHARED POOL OBJECTS IN 19C
kIsH@STAX<>SELECT name,bytes/1048576 MB from v$sgastat where pool='shared pool';
NAME MB
-------------------------- ----------
free memory 96.5071487
ksbxic target obj .002487183
object temp hash buckets .25
archive_lag_target .009185791
ktgCircBuf:MinActScn Trc .03817749
KTMPJ Priv latches .001678467
KGLH0 39.6559677
object temp hash table la .1796875
PDBHP .853179932
ASM kfk state object .003585815
KCBFLC SGA .000022888
NAME MB
-------------------------- ----------
MGA service .096534729
AQ skiplist state object .01260376
kkcnesg info .000419617
SGA Param Source Table .000648499
SWRF Metric Instance SGA .000083923
KTTE File Cache .002082825
stat hash values .007766724
mga fixvar size .000045776
INCCKEY .00201416
Memory for TopN Table Des .000015259
PDB exp set list .000648499
NAME MB
-------------------------- ----------
KGLDA .188652039
LISTEN ADDRESS TABLE .001953125
KTMB CR Buf latch cleanup .000488281
snapshot loc for 4036 inc .001152039
ksq test state obj .000030518
cross-platform compliance .002227783
PGA total .000114441
vproblem_bucket .00201416
ksmsq SGA .000045776
ksmpga list .005249023
event descriptor table .102737427
NAME MB
-------------------------- ----------
alter system errs: kspnfy .4453125
sim kghx free lists 7.6294E-06
kwsmcMmLat rec .000030518
ASM SR state object .033592224
SGA structure for kelr .0027771
KTU lat struct .000152588
KTLI buck-fl ptrs .000022888
PDB rf-release limits .001274109
KTU lat struct .000152588
zlllab Group Tree Heap De .000160217
ksp master psta .001052856
NAME MB
-------------------------- ----------
HTTP fixed headers .000068665
ksdhng: cache history .000305176
vproblem_lastinc .004943848
log_simultaneous_copies .000411987
name-service request .002609253
SQL Memory Manager Base W .012779236
SGA umbrella cascade map .004348755
kghrcx RO batches .077362061
kghx free lists .119468689
Undo Meta Data .000389099
recovery apply status .024147034
NAME MB
-------------------------- ----------
KESTB existence bitvec se .001953125
dbkgcpsga .000244141
kdfi hash bucket child la .002441406
HM Wt Evt Stats Array .219841003
Redo Transsport Destinati .023193359
Exadirect acl .002494812
qksncg_signature .02746582
KEWS External IDs .001464844
KUPP subheap desc .000221252
SGA Deferred Parameter Ta .005332947
message pool context area .030052185
NAME MB
-------------------------- ----------
ksipc state object 3.18215179
INCIDENT_FILE .00201416
ksunfy : SSO free list .003601074
dirty object counts array .046875
DDE_USER_INCIDENT_TYPE .001464844
locator state object .045639038
Service-level trace setti .000511169
ktsimaCtx .500152588
change tracking state cha .003707886
ASM rollback operations .004074097
krvxlctx .000244141
NAME MB
-------------------------- ----------
kfasga .007835388
dbgefgHtAddSK-1 .459922791
IM extent map 1.00046539
call .296455383
ORL error information .000022888
KSWL anchor .000190735
IPS_FILE_METADATA .00201416
KQR X PO 9.74732208
ksupd proc death .000022888
IPDDB SGA ctx 7.6294E-06
KOKTD 1.44858551
NAME MB
-------------------------- ----------
6218.kgght .159446716
dbktb: trace buffer 2.515625
DDE_USER_ACTION .002380371
ksleid alloc .000228882
ktilm hash table .015625
KCB buffer wait statistic .000221252
KGKP randnum shadow .038146973
krsv_notifier_cb .008049011
qtree_kwqspqctx .000045776
qesbl old rows .001220703
IM segment gdh_metadata .000015259
NAME MB
-------------------------- ----------
ksbxic obj .038085938
438.kgght .035079956
423.kgght .035079956
NETWORK BUFFER .063575745
keomg: entry list .295898438
quiesce system context .000419617
KTSJ subheap .003822327
HM_RECOMMENDATION .004211426
ksmpdb per-process SGA re .000572205
kwspNfy-1 .000091553
dir_ext .002746582
NAME MB
-------------------------- ----------
HM RSLN RC .003540039
HM Hang Stats .005981445
ksbsa pointer array .004051208
DNFS Disp context arr 7.6294E-06
ksu:ksusg_emptysta .015640259
top_proc_pdb .002288818
kwsoptstat2 .000061035
parameter string values .379333496
SQLK .038604736
event list array to post .000068665
ksasga.ksasgmsgqst .018310547
NAME MB
-------------------------- ----------
local undo segment bit ve .00390625
Label Cache Heap descrptr .000152588
KTI latches .001556396
bitvec serial number vali 1
ksws service object .010925293
ksunfy: in-use PSO Numa A 7.6294E-06
ksuloi: long op used list .000015259
ksgm sga instance configu .00818634
KSIPC AM MDT .000213623
ksipc gsment table .000015259
OS proc request holder .035217285
NAME MB
-------------------------- ----------
pid .037315369
DNFS Disp Cle q Rec .000053406
Database type info cache .000030518
name-service entry .002609253
resize operation history .02747345
DBWR array of pointers to 7.6294E-06
ksfd SGA state .0001297
ksfd SGA I/O outlier 1.26647949
ksfdsscloneinfo allocatio .003051758
KSFD Kernel IO outlier co .000038147
KCN hash latches .048828125
NAME MB
-------------------------- ----------
branch so .000282288
array for shared redo blo .000068665
event list to post commit .000137329
KTMPJ msz pools .000083923
Inmemory xmem 64KB anchor .140800476
row cache mutex state 1.44818878
kglsim count of pinned he .018936157
kglsim count of unpinned .018936157
kqaq_req .003738403
Read Only Database Accoun .000152588
DISPATCHER CONFIG ENTRY .000076294
NAME MB
-------------------------- ----------
KXFR Psuedo Hash Array fo .002929688
Sql controlelement latch .00012207
replication session stats .003768921
kpssnfy: kpsssgct .000068665
qmps connections .062255859
xssinfo .008926392
kfkid hrec .000030518
kelt translation table .000366211
Service-level ID trace se .004592896
SWRF Metric DRM Table .007339478
Active Session History - .001457214
NAME MB
-------------------------- ----------
SGA Spare structure for a 7.6294E-06
KEBM SGA struct for Autot .002334595
kelr system metrics table .000282288
KPDBE SGA Migrate Vld Cal 7.6294E-06
KCB tablespace encryption .00151062
HM_MESSAGE .002929688
DFW_PURGE .003479004
vips_package_size .001464844
vips_package_main_problem .001647949
PLDIA 5.71815491
kwqicaqe2kc1 .0259552
NAME MB
-------------------------- ----------
qmn tasks .003913879
KEBM SGA struct (PDB) - P 7.6294E-06
358.kgght .004524231
finding tracking element .019836426
kskpdbopennfy .009246826
latch where stats .018493652
ksfd: consumer group i/o .004058838
KTU latches .000114441
KTU latch cleanup .00038147
TEMP UNDO STAT INFO .039550781
KTUR HIST INFO .004615784
NAME MB
-------------------------- ----------
SAGE commit cache Allocat .000015259
change notification regis .015655518
change notification obj m .015655518
KTCNQROW colnames .005249023
KTCN: Obj Invalidation Se .03187561
kkaenc .001342773
kzxiIniSGA:Late binding D .000427246
ksxmsg_htab .000488281
Result Cache: Memory Mgr .000198364
kponPdbNfy: alo eshdinfo 7.6294E-06
kecuNotifier: alloc subhe .0001297
NAME MB
-------------------------- ----------
Static PDB SGA variables 7.6294E-06
Key Management heap .000152588
Result Cache .187683105
kwqmnasctx: alloc kwqmnsc .000045776
KKCNB .001785278
KSRMF anchor .000190735
X$KSVII table .000488281
KGSKI scheduler heap 1 de .0001297
pspool_kfsg .000076294
clear orls state object .003829956
kebm test replies .1171875
NAME MB
-------------------------- ----------
KTMC hash latch cleanup .017578125
Pre-Warm Initialized Seti .000137329
ksdhng: blkrs cache .007537842
cgidrec_kfnvsg .000030518
gwm_sga_notifier .001060486
os statistics .01600647
pmon slaves array .001144409
IM extent map mem .000030518
kghpinfo freelist .000045776
ktcncchildlatch .007080078
kspvpftbl .020652771
NAME MB
-------------------------- ----------
obj stats hash table .000915527
kcrfa structures .015075684
kgnfs gid list .000099182
ksipc gsm xrccm .000244141
populate state object .002464294
kdlwss .043258667
KTMCsrchlatclnup .000488281
KYWM RPLY CROSS INSTANCE .203125
SGA Non-Inheritable PDB P .000259399
SWRF Alerted Metric Metad .003967285
ksm asr bitv .5
NAME MB
-------------------------- ----------
HM Lcl Sess Info .079551697
ksfd I/O slave ctx .000244141
ksolt LWT ksmd handles 7.6294E-06
KFM state obj .003341675
kcfis state object .003097534
ksfs per pdb mnt structs .027008057
ksir PrivOp State Object .002609253
KSIR SGA .000175476
KSGL Latch Groups .000183105
LGWR PIC .000213623
KTSL reco struct .01171875
NAME MB
-------------------------- ----------
kebm action descriptors .000457764
sys event stats for Other .05859375
flashback_marker_cache_si .008773804
sot->data_kdliso .000106812
global container timeout .001953125
Sort Segment file descrip .003936768
PDB inst stats array .000198364
TG shared direct path inf .000099182
channel context areas .063354492
parameter text value .01133728
KGX .170036316
NAME MB
-------------------------- ----------
ksfv instance descriptors .007568359
vincident_file .002380371
pending bcast scn array .000068665
kgnfs htab array .004615784
ksfv subheap descriptor .000175476
um pid .002487183
SHRINK STAT .002944946
RULEC .019691467
ltxid: ltxid context .086425781
vproblem .00567627
Inmemory 1MB anchor .140800476
NAME MB
-------------------------- ----------
kglsim size of pinned mem .037612915
KTMC latch cleanup .000183105
KSIPC ASPC Instance Addre .125
enqueue state object .00213623
TXN TABLE SEGMENTED ARRAY .240249634
latch descriptor dynamic .022750854
PX scan rate stats .001029968
file # to first dba, exte .002304077
ksunfy_meta 1 12.0059814
kgnfs stats array .036659241
AQ Message cache driver s .048370361
NAME MB
-------------------------- ----------
PDB_PROBLEM .002197266
ams_xaction .001281738
kfmsg .031272888
qmpspdbonfy:0 .062255859
kwsbgini-fmPool_kwsbgsgn .002243042
per-PDB PGA Stats .00176239
nonlogged block cache wri .002365112
LRMPD SGA Table .578170776
PLS cca hp desc .000335693
PDB stats array .015640259
ksb ci process list (each .001304626
NAME MB
-------------------------- ----------
kecuNotifier: alloc subhe .0001297
enqueue resources .374061584
ksdhng: els blkrs cache .648193359
kwsoptSGAINfy:optlist .002883911
modification hash table c .796875
highest_untunable .000244141
krbfusg .000045776
KTMPJHT latch cleanup .000022888
ktimextht latches .15625
ufgtab_kfmdsg .23286438
relmd_ext .002563477
NAME MB
-------------------------- ----------
KFK idtab_kfksga .096595764
parameter blocks .041290283
poisoned memory .03125
ts encryption key afn rev .003059387
ksbxic chname .000022888
segmented arrays .018280029
kss global so cache .000526428
logwriteds .271156311
KSK VT POOL .778465271
ktsj_init_sga task stat .000160217
KSKQ SGA:kskqlmspld .06363678
NAME MB
-------------------------- ----------
KPDB latch structure .000152588
qmkmProtocol:qmdpsg .001525879
SQL Workarea Histogram .00100708
Backup Appliance state ch .003585815
kscdnfyinitflags 7.6294E-06
process allocation slots .007629395
Real-Time ADDM control st .000801086
INC_METER_CONFIG .002563477
process count for each CI .001167297
ksfs cdb mnt structs .000022888
kghrcx batch 2.3671875
NAME MB
-------------------------- ----------
KTMPJ InMemEsz pools .000083923
SYSTEM PARAMETERS .969665527
joxs struct .000572205
dgtab_kfmdsg .23286438
sga node map .000015259
KTCN global operators .000061035
txncallback .081741333
kodosgi kodos .000030518
handoff .002510071
sched job queue .003585815
imc-txn-wrk .047836304
NAME MB
-------------------------- ----------
X$KSFQP ANCHOR .000076294
SGA cascade list .004364014
KSK PDB SGA kskpdbmetric .000305176
KCN SCN/Time map .01171875
I/O stat latches .004730225
RTA boundary array .100708008
SQLK .011581421
DTP proc structure bitmap .002479553
KSM SGA Slave ctx .000038147
KSK OSS PLAN .000190735
KTU PDB dynamic SGA local .000038147
NAME MB
-------------------------- ----------
system default language h .000541687
session idle latches .000152588
KTA latches .001930237
spfile cleanup structure .015983582
kscdnfyglobalflags 7.6294E-06
post stats .002258301
snapshot size for 4036 in .001152039
ksuloi: garbage collectio 7.6294E-06
KGNFS PLDLIST head .000015259
KSIPCASPC SGA .002548218
ksws service events .064453125
NAME MB
-------------------------- ----------
dskm rac entity id .000091553
DNFS Disp Iss q Rec .000038147
DNFS Disp Com q Rec .000045776
db_block_hash_latches .78125
sim lru segments .001373291
kcrfw_slave_queue_init: t .000160217
ksfd: components i/o stat .153816223
ksfd: file i/o stats poin .003372192
Block List .370857239
arrays for shared redo bl .000022888
KTRSGA 7.6294E-06
NAME MB
-------------------------- ----------
Global dictionary read-wr .15625
kglsim used list arr .002593994
kglsim hash table .007827759
kglPdbDebugType .001228333
kzxssuf heap .000152588
Key Management heap .000152588
PX subheap desc .000244141
PQ/BizCard .001464844
Sql control element free .000244141
statement queuing .003341675
X$KSVIS table .00012207
NAME MB
-------------------------- ----------
kkzias .000137329
trace_knlasg .000480652
kwsbgcdsjini-kwsbgjta .000305176
kwsbtmCrtLatStatNode:allo .000038147
heap_kfsg .000198364
KFG SO child .010635376
KFK SGA .004241943
kfmdsg .000946045
KEW PDB SGA Structure .000083923
KEWR PDB SGA - UMF MDB En .002182007
SWRF Metric Eidbuf .214820862
NAME MB
-------------------------- ----------
kebm slave descriptors .037597656
KEBM Autotask state objec .019393921
kerpiAllocSgaSubHeap:desc .0001297
jsksncb:62 .000991821
sched job slv .028717041
SGA structure for ADR lay .000015259
dbkea msgs .001144409
gwm_sgctx .000953674
parameter table block 2.22982788
KSK Redo session info .006370544
kgsk subheap descriptor .0001297
NAME MB
-------------------------- ----------
KGKP sga .000045776
ksevn window .00038147
OFS latch heap ds .0001297
ktcnqrow columns .008605957
HM_FDG_SET .001831055
DDE_USER_ACTION_PARAMETER .004394531
IPS_PACKAGE_FILE .001831055
KESTB existence bitvec .25
kwqmncal: allocate buffer .003921509
KKTIN .004364014
dso->mds_kdlidso .000747681
NAME MB
-------------------------- ----------
MinActScn Array .000305176
ktgCircBuf:MinActScn Trc .03817749
KTCN: Hash Table Segmente .008651733
kodosgi kopfdo .000396729
rules engine context .000190735
SWRF Metric CHBs .209526062
KQR X SO .614151001
kcbi io desc .005371094
dbkgcParsePatchInfo_12:1 .031547546
kwsbgcsoa .000053406
kwsbsmStSlv: sptr for loc .000106812
NAME MB
-------------------------- ----------
db_block_hash_buckets 8.00390625
kzsrs filename .000511169
HM VFY TRCFIL .000495911
parameter value memory .00403595
KTC txn rsrc cnt .000083923
kstm drifts table .000999451
pdb latch bitmap .00012207
L2 file global fob .000076294
KEWS PDB System Stat valu .000183105
network connections .093803406
branch .161125183
NAME MB
-------------------------- ----------
gc ping pin array .0078125
primem_kfmdsg .007820129
KCB cacherm .101638794
alls-ktimcem 1.00023651
UNDO INFO HASH .015838623
object level stat table .001373291
ksm pdb struc .005767822
kdfi dsga .000068665
KTMA Space latch cleanup .001121521
name-service recovery .003829956
slave class sga anchor .000343323
NAME MB
-------------------------- ----------
kramh message flag .000015259
kghrcx RO freelist header .000045776
per-PDB stats .34400177
vips_package_history .002929688
PDB rf-release hints .001152039
reserved entries for all .011413574
KKJ SHRD WRQS .000228882
kelr other metrics table .000053406
KTMPJ esz pools .000083923
memory pool stats .005187988
temp lob duration state o .003463745
NAME MB
-------------------------- ----------
ADR_INVALIDATION .001464844
vadr_control .003845215
global container timeout .001953125
pending ckpt array for ti .015411377
kghx free lists .011741638
IUT Hash Table Base .000488281
PDB inst stats array .000198364
IMC extent map .045890808
KSRMFV2 State Object .017623901
file # translation table .038208008
vips_pkg_file .003295898
NAME MB
-------------------------- ----------
KSRPC I/O Stat array ptr .000022888
UNDO INFO .000015259
krssrtt .00378418
dbkea msg body .048828125
UNDO INFO .000015259
DNFS Disp context .000061035
Auto BMR for File Header .000053406
PG latch table .000610352
instance cnxn information .021606445
htab_keiutsg .000488281
qksceAllocateSga:ce .001075745
NAME MB
-------------------------- ----------
enqueue 1.11907196
SCHEDULING POLICY TABLE .000061035
KCB Table Scan Bitmap .000244141
vinc_meter_info .003662109
ktilm hash latches .015563965
dbkplssga .001335144
Sql control element child .002441406
qksbgCreateSystemEnv: qks .000236511
krbabrAncp .000091553
kdnnfy : array of kdnss .003601074
KGLHD .341308594
NAME MB
-------------------------- ----------
channel handle .169372559
KKJ WRK LAT .000457764
INC_METER_INFO .002929688
message pool freequeue 2.91075134
KTCNC inv ht .00012207
SpaceReuse Stats .502243042
kcrlflcr: FLC logmnr sess .000053406
kfklsodtab .324081421
LGWR-network Server info .031509399
per-PDB PGA totals .001274109
incctx PDB indices .000572205
NAME MB
-------------------------- ----------
KSN WaitID .001983643
Temporary RTA boundary ar .000099182
prmtzdini tz region .764289856
kqf runtime defined table .022666931
kponfy .001228333
resumable .002853394
kwsoptSGAINfy:inactive .000617981
listener addresses .000015259
State object pools .006408691
kcbl seq io throughput .011444092
UM root latch recovery ob .000213623
NAME MB
-------------------------- ----------
krvxmctx .000038147
kebm action context .000068665
vproblem_int .005126953
KGLSG 5.01721191
DBWR event stats array .000221252
432.kgght .035079956
sim_knlasg .001220703
ksupd List head .000022888
L2 enabled array .000015259
Kernel Component SGA I/O .177383423
SGA Parameters Families .020652771
NAME MB
-------------------------- ----------
ksupd List head .000022888
vem_user_actlog1 .001464844
kglsim sga .000549316
Log Archive Config Contex .00428009
VIEW .002197266
HM PC Rank .000244141
KTMC latches .001678467
plis struct .000167847
KTI freelists .000083923
kfklsod list .020446777
HM SGA init var .000030518
NAME MB
-------------------------- ----------
kga sga .000015259
DTP proc structure alloc .037704468
kdlwl_sga_subh .003822327
kjxgrgctx SGA .000328064
XCT SGA 7.6294E-06
INSTANCE INFO .000053406
ktimextht recovery area .0625
KGNFS PLDLIST array 7.6294E-06
KGI Session State .002182007
kcbbl2df L2 disabling fla 7.6294E-06
dev2node map .00390625
NAME MB
-------------------------- ----------
knstoggall_knstsga .000152588
KTU latches .000114441
ksmscrsas:2 .000045776
ksunfy: pdb counts segmen .002006531
ksuloi: long op free list .000015259
KSI resource miss count .002861023
ksim client list .000183105
done Q child latches .000213623
ksvr msg cache .002365112
KSIPC ASPC State Object .027549744
ksdhng: blkers cache .014755249
NAME MB
-------------------------- ----------
Processor group descripto .001098633
RVWR post requested array 7.6294E-06
dbwr suspend/resume ptr a .000015259
dbwriter coalesce bitmap .00012207
parallel kcbibr dbwr bitv .000015259
dbwr actual working sets .000022888
Prefetch client count per .000038147
KRSF SGA .000038147
SRL error information .000022888
ksfdodm SGA state .00012207
ksfd: block i/o stats poi .003051758
NAME MB
-------------------------- ----------
KCN hash buckets .015625
temporary table lock .002510071
SAGE commit cache node Al .001953125
PARALLEL T RECO LATCH .003051758
KTPR HIST TB .002677917
redo allocation latch(es) .00793457
change tracking DBA latch .000152588
LGWR per strand PIC array .004760742
KTMB latches .001678467
KTMA space latches .003204346
KTFBN SGA .043014526
NAME MB
-------------------------- ----------
IM segment hash table mem .000022888
ksmtimcronsg .080078125
kdlxdup derztc .000915527
kdmfssgaheap .0001297
kdmfsdwlheap .0001297
kglsim latch area .002593994
kglsim object batch .644874573
kglPdbDebugStat 7.6294E-06
kzekm per-PDB TDE_CONFIGU .001152039
Global Context Heap descr .00038147
343.kgght .035079956
NAME MB
-------------------------- ----------
Result Cache: State Objs .002632141
knlsg .000228882
knlu_init_btree:init .000091553
kodosgi kopfdo .000396729
SGA structure for KWQDL h .000358582
kwslbSGAData .001785278
procs_kfgbsg .005455017
ASM connection state .070556641
kffgsg .000534058
SGA structure for AWR (KE .00025177
SWRF Metric CHBs .77280426
NAME MB
-------------------------- ----------
kebm slave reply .004928589
KEBM autotask descriptors .000083923
KEWS statistic name .002799988
jsksncb:57 .000305176
plwppwp:PLW_STR_NEW_VAL_V .000030518
dbgefgHtInit-2 .000053406
KPDB Hash Table Latches .000022888
KPDBE Children List .000083923
KPDBFL SGA .00063324
parameter handle .569366455
pool srv list .000305176
NAME MB
-------------------------- ----------
ksfslath .004150391
IPS_FILE_COPY_LOG .002197266
alert_ext .010070801
DFW_PURGE_ITEM .002746582
v_ipsprbcnt .001464844
vproblem1 .005310059
v_inc_meter_info_problem .001647949
vproblem_bucket_count .002197266
vpdb_problem .003479004
LGWR PIC request array .012084961
KSK scheduler .16822052
NAME MB
-------------------------- ----------
PRTMV .784118652
AWR Flushing Error Contro .000076294
KEBM SGA struct (PDB) - P 7.6294E-06
KTSMG undo usage local mo .000045776
kdmgdfreeHTqueue .003929138
krbmror 2.6394043
kkj jobq slave descriptor .001220703
kghpinfo min lru .000091553
pdb event stats segment s .000030518
system default language h .000541687
ktcncchildlatch .007080078
NAME MB
-------------------------- ----------
kghifx descriptors .000213623
KTCN global clauses ht .00012207
SWRF Metric Eidbuf .070205688
SWRF Metric WCTimebuf .001045227
SWRF Metric RRM Table .001022339
dbkgcPopulatePatchInfo:1 .000236511
kkcnRstatIni .003097534
kfgbsg .000045776
vshowincb_i .001831055
ksmd unit test 1 .002609253
buffer_pool_desc_array .004272461
NAME MB
-------------------------- ----------
EM_TARGET_INFO .002563477
IPS_CONFIGURATION .002746582
krvxfda-dka .001037598
PDB wait limits .001274109
per_pg_set_descriptor_arr .019226074
KKKI consumer .008239746
KSRMA Object Descriptors .026779175
KTCN global operands ht .00012207
kwscSGAInitNfy: flst_kwsm .0025177
KQF optimizer stats table .002555847
namrec_kfdsg .000030518
NAME MB
-------------------------- ----------
IMCDT Mgmt .044403076
Securefile fast space usa .001121521
ktimextht ht .015625
dbktb: sga ctx .000038147
Reset Parameter Handle .000221252
undo temp .107521057
KSIPC ASPC InstMap .03125
AWR View Info (KEW layer) .000152588
generic process shared st .000961304
KGNFS DMLIST head .000015259
event classes .002563477
NAME MB
-------------------------- ----------
IM MRGLB 7.6294E-06
KSFD Cache Hints for IO R .000083923
KCB Table Scan .000038147
media recovery state obje .010940552
skgxp ANT slave netstat m .039825439
PX msg pool struct .000053406
pdb event stats .014663696
sys event stats 1.17188263
ksp master psta .12387085
pdb event stats .014663696
kfiac_sg .002319336
NAME MB
-------------------------- ----------
KMMSGPDBSA .002250671
kjci CIC table .013671875
KSI Indexes .000274658
PDB_SPACE_MGMT .002746582
row cache child latch .009307861
ksfs SGA state .052108765
pso tbs: ksunfy .148773193
pdb latch table .007583618
PGA totals ptrs 7.6294E-06
ksdhng: cbufs .000061035
ptr to sessions under idl 7.6294E-06
NAME MB
-------------------------- ----------
KSRMA Free Rlogs 1
incctx untunable mem .001144409
quiescing session .003829956
dbktb: trace pool .043487549
IPS_PACKAGE .003845215
Entity type array .000053406
temporary tabl .005828857
KYWM CROSS INSTANCE .000366211
kspd run-time context .00793457
diagv_incident .006774902
PDB stats array .015640259
NAME MB
-------------------------- ----------
KTLI Log Hash Table .000068665
sctx .049255371
Big SQL Shared Memory SO .002922058
KSZ sga subheap descripto .0001297
kgnfs authp .015701294
lock state hash table .617675781
EM_DIAG_JOB .002746582
system node table .000015259
KSIPC anchor .020996094
recov_kgqbtctx .006980896
koh dur heap .000404358
NAME MB
-------------------------- ----------
kss global sga cache .000038147
kcrfw_slave_phase_initall .000091553
sim trace buf .004905701
kzsc sga heap .000152588
v_actinc .001647949
KGLSGSTA .00995636
KQR X PO 1.15827942
RTT parent context .000030518
kjxgrrr SGA .000053406
1105.kgght .035079956
ktilm hash table .015625
NAME MB
-------------------------- ----------
KSI ENQUEUES .069061279
Inmemory xmem 1MB anchor .140800476
441.kgght .035079956
444.kgght .035079956
kokcd .000099182
INC_METER_SUMMARY .003112793
Per pdb buffers from RM .031272888
kxfpdp pointers .249992371
kpummst global in the SGA .005775452
1:kngisga .030662537
KTU avail cache .003845215
NAME MB
-------------------------- ----------
max allowable # log files .482940674
ksmpga stat hist .003921509
kdCStat hash table .015625
trace buffer header array .000198364
starting comp sizes snaps .000183105
SQLP 1.49718475
sess Q child latches .000213623
KSK MEMINFO .750549316
KLCS .735572815
KSRMA anchor .323982239
PICKLEERR .002380371
NAME MB
-------------------------- ----------
Result Cache: Cache Mgr .000793457
Prefetch history buffer .012153625
osp allocation .167129517
ksacm service obj array .00075531
KSZ SGA .000099182
vips_file_metadata .00201416
Wait event pointers .000297546
cp srv array .002609253
event-class map .007347107
KSK plan change state seg .022270203
kcrfwy: shutdowns 7.6294E-06
NAME MB
-------------------------- ----------
KTC latches .001678467
PDB nwatch .001152039
client/application info l .000152588
ksbxic sga .000053406
enqueue_hash_chain_latche .000152588
enque hash chain latch re .000083923
kscdnfyinitnext .00062561
ksgl handle .032432556
dia* process descriptor .000015259
kjxgrvr SGA .000038147
buffer handle 1.37329102
NAME MB
-------------------------- ----------
object queue hash table d .001304626
object queue header hash .009887695
sim segment hits .001373291
dbwr working sets kcbdbws .000015259
Redo Transport krsstcb Ar .000061035
kcrfw_slave_group_initall .000297546
UNDO STAT INFO .057128906
KTUR HIST INFO .004615784
MinActScn Array .000305176
KTLI Parent DESC LC .000076294
IM CAGLB .00025177
NAME MB
-------------------------- ----------
KTMPJ latch cleanup .000366211
IM affinity hash table .015625
Inmemory extension anchor 1.00003052
kdlxdup swapp .000022888
kdfi cmap elem child latc .009765625
Role(rg) graph heap .000152588
Role(rs) graph heap .000152588
435.kgght .035079956
kzaac_vsga 7.6294E-06
cp pool array .002845764
PX QC deq stats .001304626
NAME MB
-------------------------- ----------
kokcss .000022888
kpondsg emon info .000488281
SGA structure for KPON ks .000419617
kwqmncini-stat .000167847
kfdsga .000274658
ASM map operations .003585815
ssetrec_kfdsvsg .000030518
kfvacfssga 7.6294E-06
Client ID trace setting h .000511169
Array of TopN Table delta .000015259
SWRF Metric WCTimebuf .002388
NAME MB
-------------------------- ----------
KEWS sysstat values .000343323
diag fixed SGA .000144958
dbkea msgq .000030518
pga accounting .006141663
dbke_incid_cache_init .022888184
ksu:ksunf_freelist .023071289
DDE_USER_ACTION_DEF .002746582
DFW_CONFIG_CAPTURE .001647949
vips_package_file .002746582
vproblem2 .005493164
vproblem_bucket1 .00201416
NAME MB
-------------------------- ----------
v_actprob .001464844
vips_pkg_incident_candida .006774902
kwssnmapAlloc .006744385
qesmmaInitialize: pa_qesm .010574341
affs-ktimcem .015625
zafwctx .174240112
KEW PDB SGA Structure .000083923
KEWR PDB SGA - UMF MDB En .002182007
SWRF Metric DRM Table .001495361
KEWS PDB SGA 7.6294E-06
Role(ud) graph heap .000152588
NAME MB
-------------------------- ----------
Role(rs) graph heap .000152588
kwssnmapAlloc .006744385
dso->iods_kdlidso .00050354
AQ Slave list .000778198
dlo fib struct .012237549
IM PJGLB .000160217
State object subpools .029144287
IPS_PACKAGE_UNPACK_HISTOR .002197266
LGWR post requested array 7.6294E-06
KTU latch cleanup .00038147
kghpinfo freelist .000076294
NAME MB
-------------------------- ----------
rules engine aggregate st .001411438
Securefile fast space usa .001121521
ASM KFFD SO .002853394
jsksncb: 12 .000015259
ksb cic process list .002326965
KGNFS DMLIST array 7.6294E-06
KKSSP 2.42475128
bloom filter .003097534
resize request state obje .323486328
x$ksmfs table .000152588
grplut_kfgsg .00390625
NAME MB
-------------------------- ----------
srsrec_kfnvsg .000030518
PRE_11g UNDOSEG DROP BITV .000495911
RMAN wallet access state .002853394
HM user parameter memory .000045776
krsoproc .002197266
log file size history arr .000160217
kwsbjcAllocMem: allocate .023048401
KTSL lat struct .01953125
AQ: Non Durable Sub state .021606445
19973.kgght .035079956
KTSJ state object .062561035
NAME MB
-------------------------- ----------
kglsim latches .0001297
temporary foreign ref .002784729
kfnvsga .000404358
vips_package_main_int .001464844
Dep type array .000053406
DISPATCHERS INFO .002510071
KSKQ SGA .332397461
BRANCH TABLE SEGMENTED AR .215713501
ksdhng: cbuf .03125
KSVR SGA .008224487
gc granule chunks .002288818
NAME MB
-------------------------- ----------
incr ckpt write count arr .000160217
AQ: Master/Slave state ob .052871704
ksmplincctx .000068665
kskpdbstats .249511719
keswxNotify:tabRwsStatGro .001495361
ktud-usd hash table .00025177
DFW_CONFIG_ITEM .00201416
SGA for UMF (KEUM) 7.6294E-06
msg Q child latches .000213623
KSK PDB SGA .009246826
KTM PDB SGA .000053406
NAME MB
-------------------------- ----------
process group array .08644104
parallel_max_servers .003662109
plwspv:PLW_STR_NEW_VAL .000015259
sga latch where stats .074279785
plwppwp:garbage handle .000030518
v_swperrcount .001464844
Managed Standby Process A .0546875
obj stats allocation chun 4.921875
kspload:comment .000061035
rules engine context .000190735
Alert log .000495911
NAME MB
-------------------------- ----------
list .004882813
grptab_kfgsg .002388
KTMSJ free lists ptrs .001953125
IPS_PACKAGE_HISTORY .002746582
pso child tracebuf ptrs .002288818
kcrfwy: slaves 7.6294E-06
OFS req heap ds .0001297
param hash values .020645142
alls-ktsimcsgtinfo .211174011
RTA read control file ind .00402832
ksu:stats_freelist_array .000183105
NAME MB
-------------------------- ----------
ksmscrsas:1 7.6294E-06
write state object 7.65174866
VIRTUAL CIRCUITS 2.94650269
kralastcomp .000167847
KTSL subheap 2.19135284
HM SGA context .075164795
ksuplsb: plsql call stack .001953125
PDB SGA versioning .001274109
KTU avail cache .003845215
PLS chunk .000335693
kdCStat hash table .015625
NAME MB
-------------------------- ----------
KTCN global operators .000061035
v_ipsprbcnt1 .001464844
ENQUEUE FREELISTS .000175476
MTTR advisory context .001571655
ksipc netcaps .000015259
Result Cache: Cache Mgr .000793457
cinfo_kfnvsgasmb .003913879
pdb_untunable_held .002288818
ASMCMD BG process .003341675
broker globals .00025177
KSI resource types .020629883
NAME MB
-------------------------- ----------
connection_brokers spec .000053406
dbcomp session .002365112
KTCNQROW colname ptrs .000328064
KSRMA Allocated Rlogs 1
kgh latchdir entries .0078125
kghmpt jp records .03125
kghrcx recovery structure .000518799
ksim cached group entry l .0859375
ksbcpuwtm_ctx_entry .016670227
kso: sched delay history .027557373
invalid low rba queue .000549316
NAME MB
-------------------------- ----------
writes stopped lock conte .000030518
dbwr suspend/resume array .000015259
KCB Table Scan Buffer 4.00390625
sim state object .000038147
Redo Transmit Queue Descr .000823975
Nettimeout Histogram .144271851
kcrfw_slave_barrier_inita .000045776
Online Datafile Move sess .002609253
am statistics .007102966
plugin datafile array .003448486
krdm mira recovery instan .007911682
NAME MB
-------------------------- ----------
change tracking recovery .25
KTPR SUBHEAP .000160217
KTI pool states .000053406
change notification regis .015655518
change notification obj m .015655518
KTCN: query inv Segmented .003829956
KTMCsrchlatches .0390625
IM SJGLB .000053406
IM segment base objn arra .031272888
row cache hash 5.79273987
kglsim recovery area .001556396
NAME MB
-------------------------- ----------
DISPATCHER CONFIG TABLE .001953125
SCHEDULING POLICY VECTOR .000099182
cp cmon array .003669739
KXFR Hash Array latches a .002807617
Expression usage element .002685547
kolbsgi: KOLB's SGA initi 7.6294E-06
emon con array .002975464
qtree_kwqbspse .000045776
KWQDL SGA Ddtc gen cleanu .000045776
SGA structure for KWQDL s .000022888
time manager context .000038147
NAME MB
-------------------------- ----------
kwspNfy-2 .000045776
xscalc .003952026
kfkhsh_kfdsg .003913879
ASM scan context .002609253
kffssga 7.6294E-06
SWRF Metric ksuTimebuf .002388
KEBM autotask control arr .332038879
KEWS sesstat values .356506348
kerpi:heap .007789612
jsksncb: 7 .000015259
jsksncb:59 .004135132
NAME MB
-------------------------- ----------
SGA PDB Info Array 3.347229
KGSKI sga .000022888
HM SGA kjzs ctx .000022888
PX subheap .037467957
vincident .006774902
vips_file_copy_log .003479004
Sort Segment descriptors .003761292
Sort Segment extent descr .003967285
KSKQ master's compat seg .012870789
KSKQ master CG stats seg 3.50444794
358.kgght .007621765
NAME MB
-------------------------- ----------
ksupd SQL Context .00087738
PLMCD 13.109169
KGLA 1.27052307
TG subheap descriptor .0001297
krvxfda-dta .012779236
KKTIN .005249023
ksupd SQL Context .000854492
qesmmaInitialize: ta_qesm .00025177
AWR Flushing Error Contro .000076294
jsksncb: 3 .003814697
kskcgmetric .00491333
NAME MB
-------------------------- ----------
mixed PDB latch descripto .000183105
PDB Autotask SGA .000770569
parameter blocks .041290283
pdb latch bitmap .00012207
SAGE commit cache node Al .001953125
KTM PDB dynamic SGA local .000053406
INSTANCE INFO .000053406
KGLNA .018318176
kcbi io desc slot .8046875
kghrcx latches .002593994
ksm_obc_path .000038147
NAME MB
-------------------------- ----------
VIEWCOL .001647949
EM_USER_ACTIVITY .002563477
db_files .921165466
KCFIS SGA 7.6294E-06
kss global so cache heap .000137329
LGWR debug module memory .0078125
kghpinfo min lru .000091553
KTI-UNDO 3.79371643
vnot_exist_incident .006591797
KTC latch cleanup .000457764
PDB limit margins .001274109
NAME MB
-------------------------- ----------
ksv reaper .028457642
joxs heap .005142212
KGLH0 1.81302643
ksolt pool allocation .002670288
ksm pdb struc .005767822
prirec_kfmdsg .000030518
messages .073242188
kghrcx alloc latch state .000022888
Detached dump directory .000495911
kswl ctxt .022666931
kebm slave message .011360168
NAME MB
-------------------------- ----------
SO private sga 15.0291138
ksipc shared nothing stat .032432556
kzekm heap descriptor .000289917
Policy Cache H .011756897
vhm_run .004211426
krdrsb read violation arr .534057617
kdCStat latch table .015563965
imc buf hdl 1.93745422
ASH buffers 2
DFW_PATCH_ITEM .001831055
file queue buckets .09375
NAME MB
-------------------------- ----------
kskpdbmetric .000305176
kkae edition name cache .001342773
ssethsh_kfdsvsg .000495911
KFG state obj .006546021
standby event stats .001159668
tmplim and timeout for 40 .002250671
global container context .000144958
PDB top Heap Descriptor .000144958
pdb event stats segment s .000030518
kzxinfy:Child latches .000152588
ksfs per pdb stat structs .059165955
NAME MB
-------------------------- ----------
ksz parent .01675415
ksws java patching contex .00113678
KTLI Bkt latches .002311707
property service SO .003707886
dbgdInitEventGrp: eventGr .000205994
incctx freeable mem .001144409
ksupd latch recovery .000160217
KESTB existence bitvec se .001953125
PX QC msg stats .002983093
private strands 6.62402344
ksm_ob_path .000015259
NAME MB
-------------------------- ----------
Static PDB SGA variables 7.6294E-06
ksolt lwt allocation .006698608
diag descriptor .000015259
sga latch table .007583618
pdb event stats segment 1.29776001
KTCN: Row Change Segmente .033035278
dispatcher rate .001251221
pdb event stats segment .123596191
KGFM vsga .000267029
kcbl statistics .006835938
KYWM SGA .00063324
NAME MB
-------------------------- ----------
AWR Table Info (KEW layer .002906799
kxfpSO q freelists .000350952
KTMB latch cleanup .000183105
KGLSGTYPSTA .011054993
dummy .037467957
KSK PDB IO stats ptr .031272888
trace buf hdr xtend .000991821
hot latch diagnostics .000152588
KTMSJ latches .0390625
KFD extent enqueue obj .069740295
v_incfcount .001647949
NAME MB
-------------------------- ----------
Background process state .000045776
write info array .0625
incctx alloc mem .001144409
keomgNotify 1:descriptor .000488281
groups_kfgbsg 1.734375
row cache mutex 8.6891098
ksv slave class .478317261
osp pool handles .000282288
KSI resource hash masks .001464844
trace events array .068664551
ksmplsctx .000656128
NAME MB
-------------------------- ----------
ktg hash buckets .012947083
KPDBE SGA Tag Id Mapping .002815247
ksmtimcrwnsg .080078125
processes .002288818
KSK PDB SGA stats .249511719
ksolt sga memory .000343323
shadow lost write work bl .000244141
KSK OSS PLAN ENTRIES .390914917
HM_INFO .00201416
KXDBIO Recovery Area .000053406
kscdnfyinithead .000053406
NAME MB
-------------------------- ----------
Client ID trace settings .003829956
AQ coordinator job list .003952026
latch classes .000335693
ksu:system-global statist .015640259
Security Class heap .0001297
ksupd proc death .000022888
ksfdss bitmap array .01171875
kscx capability table .000801086
sys_event stats 7.6294E-06
KEW Reset ID Array in Ins .003990173
OS file lock .005325317
NAME MB
-------------------------- ----------
KEWS statistic maps .003822327
kghrcx RO hash chain .001968384
HM_FINDING .005859375
ksdhng: cache ctx .000198364
procs: ksunfy 1.56555176
Auto tune undo info .000091553
keommDbopListNotify table .001525879
Auto tune undo info .000091553
dgmhsh_kfnvsg .00025177
ksv slave .030410767
kkcnecqnsg info .001098633
NAME MB
-------------------------- ----------
iosts_kfmdsg .022888184
kglsim per-gran bkt .073242188
KGLS 21.2633438
Prefetch history buffer a .000335693
buffers waiting for write .000022888
joxs struct .000572205
alert threshol .003822327
gc region array .000030518
KTMA jlatches .003204346
parallel kcbibr .000038147
kdfi buf child latch .01953125
NAME MB
-------------------------- ----------
KTU SGA .000038147
xslongops .015640259
XCT SGA 7.6294E-06
kdlxdup swappl .001296997
local undo segment bit ve .00390625
current pin time statisti .0625
ket_nfy:iter task alloc .001396179
Banner Storage .001953125
column vectors in finding .003967285
KSRPC I/O Stat array .000228882
kglsim alloc latches .0001297
NAME MB
-------------------------- ----------
SQLA 81.6235504
KTL latches .001678467
IM segment hash table glo .211174011
ksv work msg .0078125
kghmpt sp records .03125
ksm_obh_path .000038147
State object sizes .001602173
ksunfy: instance statisti .000198364
ksunfy : array of SSO fre .000183105
ksuloi: child latches for .000152588
ksim request outstanding .000015259
NAME MB
-------------------------- ----------
reset parameter blocks .041290283
error message file name .000061035
kscdnfyinitprev .00062561
ksws RLB SGA ctx .000076294
ksir State Object .003829956
ksdhng: wtr cache .007537842
ksdhng: fblkrs cache .021972656
ksacm service root .000083923
KSGL anchor .005058289
block_sizes_array .000053406
FileOpenConsolidatedBlock .253189087
NAME MB
-------------------------- ----------
Transportable DB Converte .004615784
krdm mira information arr .000259399
TEMP UNDO STAT INFO .039550781
copy latch aux .000015259
KTF MAPPINGS .01171875
KTCN queries hash table .00012207
KTMSJ Heaps .033203125
IM ADG GLB .03401947
External table user state .11075592
latches for protecting ls .000457764
Global dictionary parent .15625
NAME MB
-------------------------- ----------
kglsim size of unpinned m .037612915
kglsim free obj list .000389099
Policy Cache Heap descrpt .000152588
zasasga .000015259
kzxiIniSGA:Late binding D .000427246
cp connection .005760193
PX server deq stats .001304626
Monitor Modification Hash .000488281
Sql controldata element l .00012207
Temporary Tables State Ob .157089233
per-PDB Bound Stats .00188446
NAME MB
-------------------------- ----------
emon srv array .004196167
KFIAC Fencing state objec .00831604
SWRF Metric TMM Table .000160217
kea advisor definition ca .001686096
jsksncb: 9 .00062561
kqlpWrntoStr:string .000099182
KGSKI scheduler heap 2 de .0001297
KGKP randnum .038146973
pool srv hash sbuckets .000343323
ADR_CONTROL .003112793
DDE_USER_INCIDENT_ACTION_ .001464844
NAME MB
-------------------------- ----------
IPS_PACKAGE_INCIDENT .00201416
IPS_REMOTE_PACKAGE .00201416
INC_METER_IMPT_DEF .002197266
INC_METER_PK_IMPTS .002197266
v_nfcinc .00201416
vips_package_incident_det .010070801
active checkpoint queue e .002853394
KTC latch subh .044815063
modification hash table e .015869141
6216.kgght .159416199
KKTDF .006225586
NAME MB
-------------------------- ----------
Reset Parameter Handle fo .000221252
KTT File Space Usage (PDB .002082825
kwspnfy-pdb .000091553
kkj jobq work items .003952026
ksusessnetstat .092285156
IM segment gdh_metadata .000015259
Policy Cache Heap descrpt .000152588
Array of TopN Table delta .000015259
SWRF Metric ksuTimebuf .001045227
qmkmProtocol:qmdpsg .001525879
jsksncb: 6 .004646301
NAME MB
-------------------------- ----------
set_descriptor_array .000068665
kspvpfbv .000648499
KSFD SGA I/O b 3.99625397
kghmpt sp context .000038147
session 4.59857178
ksu:ksusg_emptyinfo .000152588
krvxfda-dka .001037598
Allocate KPDB Hash Table .0078125
DNFS Disp comp array coun .001144409
KTCN global operands ht .00012207
KWQDL child latches .003479004
NAME MB
-------------------------- ----------
block iostats dynamic seg .009292603
memory transfer history .018318176
enqueue_hash .044815063
ASM generic network state .002609253
latch where range .037139893
KCB where statistics arra .078186035
SHARED SERVERS INFO .002426147
spilled over messages .00038147
kdmadohtlatc .001525879
KTCTSNL freelists .000083923
kolfsgi: KOLF's SGA initi 7.6294E-06
NAME MB
-------------------------- ----------
dpslut_kfdsg .00390625
UNDO INFO SEGMENTED ARRAY .106155396
KGLDA 3.64208984
KEBM MetaData of RBB in I .004127502
jsksncb: 2 .014640808
database creation languag .001083374
KGX rowlist .012054443
kso req alloc heapds .000267029
Cleanup state objects .001464844
ksbdpu structure bitmap .002479553
sskgpwcr: Sem set array 7.6294E-06
NAME MB
-------------------------- ----------
KSFV SGA .000793457
zafwctx .174240112
finding tracking element .007873535
top_proc_untunable .000244141
ksuprcwt .029754639
KTMC hash latches .0390625
PX server msg stats .002983093
dbwr message active flag 7.6294E-06
pmon map array .002288818
ksb process so list .002326965
knlpmclsini - alloc .009185791
NAME MB
-------------------------- ----------
KTLI Parent Bkt LC .000389099
vshowcatview .001831055
SWEEPERR .00201416
ASM rcl group .08731842
kghpinfo bathdr .000015259
KTCNQROW colnames .005249023
DFW_PATCH_CAPTURE .001647949
KGNFS scontext .501579285
pdb latch table .007583618
ksws service hash table .000022888
PRTDS .9193573
NAME MB
-------------------------- ----------
KTCN: Row Change Segmente .036605835
KSTM SGA .000022888
Sync NoAffirm Pending I/O .016113281
PDB wallet status .000495911
kwqmncini-tbl .004844666
ksmpdb SGA recovery .062545776
x$rule_set .037391663
kdmgdFreeHTQueue .003921509
pdb latch where stats .074279785
sim trace buf context .000190735
kglsim free heap list .000389099
NAME MB
-------------------------- ----------
ksoltholder array .009155273
PDB nabort .001152039
dsktab_kfgsg .074623108
kjcifxpoolplus .00100708
kebm run-once actions .000106812
sga dev dict .000053406
KTMA jlatch cleanup .001441956
name-service table .015922546
kglsim Java timestamps .125
latch descriptor state .059997559
qmtb_init_data .001647949
NAME MB
-------------------------- ----------
KSQ event description .033203125
kks cached parse errors .000015259
V$DATAGUARD_STATUS array .068359375
KSI resource reuse count .002861023
KKBTD .00579071
6217.kgght .159431458
ksb rolling migration sta .000099182
vem_user_actlog .001464844
ksipc_hashs .187324524
fencing reid .010269165
kdlwl_sga_subheap .000221252
NAME MB
-------------------------- ----------
ksusessnetstat .092285156
kxfpSO qref freelists .000350952
KSWL LSPCIDS .000015259
kzekm PDB status check in .001152039
615.kggec .527633667
KGLPDBSG .000137329
SEQ S.O. .090026855
obj hash table pointers .000228882
KSK PDB SGA kskcgmetric .00491333
KTTE TS Info .003967285
Core dump directory .000495911
NAME MB
-------------------------- ----------
ksolt pool ksmd handles 7.6294E-06
KTI latch structure .007781982
KGLS 1.43690491
kodosgi kodos .000030518
per-PDB unified PGA total .001274109
namhsh_kfdsg .003913879
KSK SGA .020904541
logout storm management .002288818
PMON blockers .005722046
KTMSJ latch cleanup .003921509
fixed allocation callback .000076294
NAME MB
-------------------------- ----------
KTCNQROW colname ptrs .000328064
SQLA 1.53575134
User dump directory .000495911
per-PDB PGA limits .001274109
incctx inuse mem .001144409
ksuprlwt .029754639
ksbxic dtp .000083923
ksbcwt .04334259
channel sga anchor .000389099
ksws service hash table b .001953125
ksws listener service nam .000030518
NAME MB
-------------------------- ----------
DNFS Disp spawn ctx .000022888
ksns pdb hdl tab .125061035
UM pid recovery object .000022888
ts encryption global key .003067017
Context of deferred reuse .002441406
buffer handles .022911072
object queue hash buckets .75
simulator hash buckets .50390625
dbwr outstanding ios per .000076294
ksfdgfib recovery .000015259
ksfd: consumer group i/o .004058838
NAME MB
-------------------------- ----------
kcbl state objects .012207031
KRBMROS ANCHOR .000091553
time-based incremental me .054946899
kcnm.c@206 .0001297
transaction 1.47973633
KTCTSNL subheaps .001426697
SAGE commit cache Allocat .000015259
KTCN: Obj Invalidation Se .028305054
ktfa dynamic SGA allocati .001091003
SGA structure for ktslgt .022506714
Space Background Task Des .625
NAME MB
-------------------------- ----------
Inmemory 64KB anchor .140800476
kdmadohtlatrec .000457764
kglsim hash table bkts 4
kglsim heap .435180664
KGLSGTYPIDX .000160217
Role(ud) graph heap .000152588
distributed_transactions- .034248352
pesldT03_Instance .035209656
PX list of chunk lists .001068115
sga listelement .001953125
Result Cache: Memory Mgr .000198364
NAME MB
-------------------------- ----------
SGA structure for KPOND k .000152588
AW SGA .000038147
kfkrec_kfdsg .000030518
kfkid hash .003913879
kfdsvsga .000015259
SWRF Metric RBSM struct .001800537
kebmnfy:pdb status array .016670227
Autotask Error Control in .000076294
keomm:emptyMon_keommSg .015899658
plwppwp:PLW_STR_NEW_LEN_V .000015259
KXDBIO SGA .012298584
NAME MB
-------------------------- ----------
ksu:stats_freelist 2.42414856
kpoxcAppContSG .118263245
KGSK scheduler .2029953
OFS ctx heap ds .0001297
HM_RUN .004577637
KQR X SO 4.42948151
TG subheap des .031593323
gwm_ons_info .000106812
kpscad: kpscscon .000511169
qesmmaInitialize: .000106812
jsksncb: 4 .003845215
NAME MB
-------------------------- ----------
Reset Parameter Blocks fo .041290283
ksupdbstaen_hdl_tab 7.6294E-06
expression usage tracking .011901855
kghpinfo bathdr .000022888
latch descriptor state .001281738
pdb latch where stats .074279785
ksupdbstaen_ent_tab 7.6294E-06
database creation languag .001083374
KTU Recovery SGA .000076294
UNDO INFO HASH .015838623
UNDO STAT INFO .057128906
NAME MB
-------------------------- ----------
KTCN: query inv Segmented .007400513
qksceAllocateSga:ce .001075745
kponPdbNfy: alo einfo .000068665
SWRF Metric TMM Table .000061035
SWRF Metric RBSM struct .001800537
KEWS PDB System Stat valu .000183105
Autotask Error Control in .000076294
KCB tablespace encryption .001083374
KEUM SGA struct (PDB) - P 7.6294E-06
KEWR array of TopN stats .01587677
11G QMN so .000610352
NAME MB
-------------------------- ----------
policy hash table descrpt .000267029
XS state object handle .002853394
ksfm state object .000030518
IM segment hash table .211174011
sga latch bitmap .00012207
analytic workspace .003585815
KSKQ SGA:pdbtgt .015640259
java static objs .041061401
kghrcx RO batch headers .000015259
kzscnfy: 3 .001525879
joxs heap .003952026
NAME MB
-------------------------- ----------
kmgsb circular statistics .155273438
SGA Obsolete Param Source .000022888
file state object 5.60751343
ofs context .001213074
threshold alert structure .003845215
ksacl dbru19 .00100708
incctx procs .002288818
kcbi memory desc .002380371
X$KCNT array .003601074
kdmadohtincar .00390625
kghrcx RO latch director .000015259
NAME MB
-------------------------- ----------
dgmrec_kfnvsg .000030518
KEWS fixed SGA .007553101
reservation state object .002754211
DML lock .673065186
UNDO INFO SEGMENTED ARRAY .109695435
ksdhng: el wtr cache .129638672
IP acl table h .003822327
array of sessions .003601074
PLS SGA hp .050895691
ksgl SSO .027549744
vtest_exists .002380371
NAME MB
-------------------------- ----------
dbf io stats .003051758
per-PDB PGA targets .001274109
KSRMA State Object 2.38793182
cgidhsh_kfnvsg .0001297
kglsim alloc latch area .002593994
global container context .000144958
KQFUTTAB COLS .001098633
krbmrsr .000297546
kxfpsga snapshots .015625
KTMA sort latches .003204346
kwstxnInitSga: tspl_kwsmc .005264282
NAME MB
-------------------------- ----------
AQ Propagation Scheduling .061035156
heap_kdfisga .000244141
constraints .114212036
ksupd latch recovery .000160217
KKTSR .012527466
hash bucket array allocat .09375
parameter text value .000244141
PDB start index array .001152039
kglsim Java per-gran bkt .073242188
Checkpoint queue .000549316
KSFQ buffer pool .003829956
NAME MB
-------------------------- ----------
kebm slave pool descripto .000160217
dbwriter coalesce buffer 4.00390625
latch where map .018569946
ENQUEUE STATS .038520813
KXDBIO state obj .560462952
VM OSD context .000091553
ksoreqholder latches .000335693
L2 file db unique name .000030518
kss unit test .002655029
KTMPJ latches .001678467
kohsg .000015259
NAME MB
-------------------------- ----------
sim cache sizes .000686646
kspcmhdl .010543823
ksuloi: long op statistic .232696533
kspcmhdl .010543823
FileIdentificatonBlock .864891052
ktg hash buckets .012947083
dbgefgHtInit-1 .00050354
KGLHD 10.189682
ksb action records .006065369
kexsvAllocFeatureUsageHea .0001297
per-PDB qesmmISg .010063171
NAME MB
-------------------------- ----------
key vector .003585815
CDB Service Hash Table El .003967285
pool srv hash buckets .000030518
ksbtnfy: infrequent actio .002029419
KTMB CR Buf latches .0390625
DNFS Disp comp array .004577637
kghpinfo ptrs .031272888
kfgsga .013702393
kglPdbDebugNsp .001106262
v_inccount .001464844
CGS system incarn array .023712158
NAME MB
-------------------------- ----------
ksunfy: is parent statist .023460388
kdmadoht .015625
ksfv subheap .015724182
kfnvsgasmbs .002197266
ket (AutoTask) SGA .005897522
KCB buffer wait statistic .004798889
KQF runtime defined table .009613037
LISTENER_NETWORKS PARAMET .000061035
post/wait queues .061523438
429.kgght .035079956
426.kgght .035079956
NAME MB
-------------------------- ----------
PROBLEM .003479004
tablespace encryption key .000190735
ksfdsspdbnfy1 .00012207
vshowincb .001831055
kswl sctxt .020225525
file queue pointers .000183105
log_ext .00769043
KCB incremental ckpt entr .001426697
Array of cached attr .00038147
KEWS aggregation objs .003669739
kghifx descriptors .000213623
NAME MB
-------------------------- ----------
Closed Thread SCN Bitvec .032226563
kks sga .000015259
ksns_obj .445823669
kcrlflcr: FLC root .001747131
SWRF Metric Global SGA .010375977
kjcipctx SO freelist .1121521
KKJ SGA .002067566
ksfcd SGA state .015655518
JSX SGA .017601013
sort segment handle .009849548
sga blobs .03125
NAME MB
-------------------------- ----------
writes stopped lock state .000022888
procs:bitvec .000038147
IMEXT hash table .000022888
INCIDENT .006225586
KEWS PDB SGA 7.6294E-06
kghmpt jp context .000038147
latch where stats .303344727
ksunfy: nodes of hierarch .000724792
ksunfy: Reserved PSO Numa 7.6294E-06
ksim cached group entry s .001953125
kgnfs ctab array .002326965
NAME MB
-------------------------- ----------
KSK PDB IO stat per pdb .000335693
mga fixvar info .000183105
DNFS Disp cleanup ctx .000022888
signature element table .002723694
L2 file map array .10848999
sim cache nbufs .000686646
sim segment num bufs .000686646
simulator latch/bucket st .11328125
dbwriter coalesce struct .000045776
kcrfw_slave_queue_init: s 7.6294E-06
ksfd shared pool recovery .000022888
NAME MB
-------------------------- ----------
file iostats dynamic seg .02406311
block media rcv state obj .002609253
log_checkpoint_timeout .011787415
object lock state object .003829956
kcnm.c@195 .0001297
kcnm.c@203 .023780823
kcnibr.c@2920 .0001297
KTC variable sga .000022888
KTU Recovery SGA .000076294
bcast ack latency base ar .000068665
KTCN global clauses ht .00012207
NAME MB
-------------------------- ----------
KTCN: Hash Table Segmente .005081177
txn jrnl .251937866
ktsj_init_sga proc desc .015640259
allocate segment hash tab .1953125
latches for protecting fs .000915527
latches for protecting ws .000915527
kdmfsdwrheap .0001297
row cache .022338867
kglsim timestamps .125
KGLSGNSPIDX .000144958
kglPdbDebug .000068665
NAME MB
-------------------------- ----------
KKDLP SGA Property Tables .000358582
kzekm rsafipspath .003913879
kzekm PDB P-Cache in ROOT .001274109
PARAMETER TABLE .001953125
PARAMETER ENTRY .000083923
LISTEN ADDRESS ENTRY .000144958
Result Cache: Bloom Fltr .001953125
X$KSVIT table .000488281
knstsg .000053406
session migration enqueue .002059937
qtree_kwqbsgn .000045776
NAME MB
-------------------------- ----------
KWQDL SGA Dtc gen cleanup .000045776
kwsoptSGAINfy:uncached .000617981
SGA structure for KPON kp .001457214
kwsbgini-actvmPooltab_kws .0025177
kwsbgini: vmPool_kwsbgsgn .003898621
kwspNfy-3 .000083923
namhsh_kfgsg .000282288
srshsh_kfnvsg .00025177
kfiosg .000030518
AWR Data Source Info (KEW 7.6294E-06
SWRF Metric RRM Table .005401611
NAME MB
-------------------------- ----------
KEWS statistic metadata .023712158
dbkea sga .000022888
KPDBE SGA Validation Call .000061035
KGSK sga .09980011
kso req alloc .003822327
pmon sga .001121521
KSXP test table columns d .002746582
IPS_PROGRESS_LOG .002746582
ADR_CONTROL_AUX .001831055
object queue header free .142272949
monitoring column usage e .023712158
NAME MB
-------------------------- ----------
KSKQ master's svcracbitma 1.00408173
work area table entry .253540039
qesmmaInitialize: ia_qesm .00025177
qesmmaInitialize: oa_qesm .000106812
kwrsnfy: kwrsoe .001930237
KESTB existence bitvec .25
sot .000099182
mixed PDB latch .000457764
ksuseplsrctx memory .000022888
KSK PDB IO stat per pdb .000335693
KTCN queries hash table .00012207
NAME MB
-------------------------- ----------
KTCNC inv ht .00012207
ktfa dynamic SGA allocati .001091003
zasasga .000015259
qksbgCreateSystemEnv: qks .000236511
SWRF Metric Global SGA .010375977
615.kggec .004676819
Policy Cache H .012657166
kzxssuf heap .000152588
Role(rg) graph heap .000152588
KKKI consumer .005516052
kwqicaqe2kc1 .0259552
NAME MB
-------------------------- ----------
Saved job ids .061035156
KTTE File Info .003952026
1641 rows selected.
kIsH@STAX<>spool off
ORA-00942,ORA-38818 Error On Creating Materialized View
- Database link is not used.
- Materialized view is created from view and not from table.
- All privileges are in place.
Creation of materialized view as select on a view gives the error,
ORA-00942: table or view does not exist
Creation of materialized view as select on a subquery gives the error,
ORA-22818: subquery expressions not allowed here
Creation of materialized view as select on a customized function gives the error,
ORA-00904: “FUNCTION NAME”: invalid identifier
DDL of the view shows that the view is editioned.
CREATE OR REPLACE FORCE EDITIONABLE VIEW …..
SQL failed because the referenced object could not be found. This error typically occurs when the referenced object is dropped or you do not have permission to reference it.
See the document Doc ID 18536.1 for details:
OERR: ORA-942 “table or view does not exist” Reference Note (Doc ID 18536.1)
Messages in the 10046 trace shows parse error with err=10980.
PARSE ERROR #18446744071504374656:len=26 dep=1 uid=44 oct=3 lid=44 tim=9578336260726 err=10980
select * from <view_name> <<<===
CLOSE #18446744071504374656:c=1,e=1,dep=1,type=0,tim=9578336260882
PARSE #18446744071504444384:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1028580536,tim=9578336261030
BINDS #18446744071504444384:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=ffffffff7d7c49e8 bln=16 avl=16 flg=05
value=00000EB8.0002.0009
EXEC #18446744071504444384:c=118,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1028580536,tim=9578336261184
FETCH #18446744071504444384:c=6,e=7,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=1028580536,tim=9578336261228
CLOSE #18446744071504444384:c=4,e=4,dep=2,type=3,tim=9578336261256
The name of the view is in uppercase <VIEW_NAME> as per the DDL metadata but the view name specified is in lowercase <view_name> during the mview creation which caused ORA-00942 due to case sensitivity.
After specifying the view name in uppercase, ORA-38818 error is observed.
CREATE MATERIALIZED VIEW <mview_name>
as select * from “<TABLE_NAME>“;
ORA-38818: illegal reference to editioned object APPS.AR_CUSTOMERS
the error is expected behavior due to reference to editioned schema and the materialized view that you create is non editioned.
38818, 00000, “illegal reference to editioned object %s.%s”
// *Cause: An attempt was made to violate the rule “A noneditioned object
// may not depend on an editioned object.”
// *Action: Either make this object editioned; or do not make the illegal
// reference.
“A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions.”
“An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition.”
Editionable and Noneditionable Schema Object Types
These schema objects types are editionable:
SYNONYM
VIEW
All PL/SQL object types:
FUNCTION
LIBRARY
PACKAGE and PACKAGE BODY
PROCEDURE
TRIGGER
TYPE and TYPE BODY
All other schema object types are noneditionable. Table is an example of an noneditionable type.
A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.
A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled. A table is an example of an noneditioned object.
Creation of mview from edition enabled view with simple test case:
SQL> create table t as select * from dba_source;
SQL>create force editionable view dv as select * from t;
View created.
SQL>create materialized view mv as select * from edition_test.dv;
create materialized view mv as select * from edition_test.dv
*
ERROR at line 1:
ORA-38818: illegal reference to editioned object EDITION_TEST.DV <=========== expected error
Workaround:
1)Create a new non edition schema similar to the “APPS” schema
2) Create the underlying objects similar to the edition schema “APPS”
3) And create mview using the view
DIFFERENCE BETWEEN PHYSICAL_READ_BYTES_DELTA AND DISK_READS_DELTA IN dba_hist_sqlstat
What does these columns in dba_hist_sqlstat denote and what makes the difference between these values?
Trace the query with 10046 if needed.
kIsH@xHydra<>alter session set statistics_level=all;
Session altered.
kIsH@xHydra<>alter session set tracefile_identifier='10046_dbss';
Session altered.
kIsH@xHydra<>alter session set events '10046 trace name context forever, level 12';
Session altered.
kIsH@xHydra<>SELECT /*+parallel(8)*/ * from dba_source;
kIsH@xHydra<>alter session set events '10046 trace name context off';
Session altered.
kIsH@xHydra<>exit
Check dba_hist_sqlstat view for PHYSICAL_READ_BYTES_DELTA and DISK_READS_DELTA
SQL> select SQL_ID,PHYSICAL_READ_BYTES_DELTA,DISK_READS_DELTA from dba_hist_sqlstat where SQL_ID='3hbbrg389qazm';
SQL_ID PHYSICAL_READ_BYTES_DELTA DISK_READS_DELTA
------------- ------------------------- ----------------
3hbbrg389qazm 61587456 7518
Let us verify the and sum up the metric “physical read total bytes” from v$mystat which shows the same statistics as dba_hist_sqlstat
SQL> SELECT a.name,sum(b.value) PHYSICAL_READ_BYTES_DELTA ,c.sql_id from v$statname a,v$mystat b, v$sql c where c.sql_id='3hbbrg389qazm' and a.name in ('physical read total bytes') group by a.name,c.sql_id;
NAME SUM(B.VALUE) SQL_ID
------------------------- ------------ -------------
physical read total bytes 61587456 3hbbrg389qazm
In autotrace, the "physical reads" output from execution plan statistics shows the same value as DISK_READS_DELTA.
SQL> set lines 200 pages 1000
SQL> set autot traceonly
SQL> SELECT /*+parallel(8)*/ * from dba_source;
Statistics
----------------------------------------------------------
168 recursive calls
0 db block gets
8610 consistent gets
7518 physical reads <===============
0 redo size
29862441 bytes sent via SQL*Net to client
213836 bytes received via SQL*Net from client
19405 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
291053 rows processed
Summary:
PHYSICAL_READ_BYTES_DELTA – represents the number of data in bytes read from disk between begin interval and end interval time of snapshot
DISK_READS_DELTA – is the number of data blocks read from disk between begin interval and end interval time of snapshot
CONVERT NON-CDB TO CDB IN 19C
Generally, lower version non- CDB cannot be both converted and upgraded to a PDB at the same time.
Example: If 12c non-CDB should be upgraded to 19c PDB, then
- Either upgrade 12c non-CDB to 19c non-CDB and then convert to PDB
- Or convert 12c non-CDB to 12c PDB and then upgrade to 19c
Source – 19c non-CDB ; Target – 19c PDB
source NON-CDB – shutdown the database and open in read only mode.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 1778381840 bytes
Fixed Size 8897552 bytes
Variable Size 419430400 bytes
Database Buffers 1342177280 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
Export the manifest file into server. This xml file contains all the tablespaces and datafile information of non cdb to be plugged in to target CDB.
SQL> exec DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/non_cdb.xml');
PL/SQL procedure successfully completed.
target CDB:
Always, dont skip or forget this step. This step is important in verifying the compatibility of the source non cdb to be eligible for plug in. If the output is “YES”, then well and good to go. If output is “NO”, then there are certain violations which are related to mismatch and compatibility which need to be cleared.
SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/non_cdb.xml')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL> 2 3 4 5 6 7 8 9
YES <================
PL/SQL procedure successfully completed.
Check PDB_PLUG_IN_VIOLATIONS irrespectively, either the above procedure return YES or NO.
SQL> SELECT count(*) FROM PDB_PLUG_IN_VIOLATIONS;
COUNT(*)
----------
2
SQL> col NAME for a20
SQL> col CAUSE for a20
SQL> col MESSAGE for a20
SQL> SELECT name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS;
XSCALIBA Non-CDB to PDB WARNING PDB plugged in is a PENDING
non-CDB, requires no
ncdb_to_pdb.sql be r
un.
Create a new pluggable database in CDB so that the non CDB can be plugged into this PDB
SQL> CREATE PLUGGABLE DATABASE newpdb USING '/tmp/ncdb.xml'
2 COPY
3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/XSCALIBAL/datafile/o1_mf','/apps01/oradata/HYDRA/');
Pluggable database created.
Execute the noncdb_to_pdb.sql script to convert the non CDB to PDB
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
Below are the logs which are generated as part of noncdb_to_pdb.sql script
-rw-r--r-- 1 oracle oinstall 543 May 22 15:58 apxremov1_con_catcon_12797.lst
-rw-r--r-- 1 oracle oinstall 42 May 22 15:58 apxremov1_con_catcon_12989.done
-rw-r--r-- 1 oracle oinstall 42 May 22 15:58 apxremov1_con_catcon_12988.done
-rw-r--r-- 1 oracle oinstall 439 May 22 15:58 apxremov1_con0.log
-rw-r--r-- 1 oracle oinstall 439 May 22 15:58 apxremov1_con1.log
-rw-r--r-- 1 oracle oinstall 543 May 22 15:59 apxremov2_con_catcon_13009.lst
-rw-r--r-- 1 oracle oinstall 42 May 22 15:59 apxremov2_con_catcon_13030.done
-rw-r--r-- 1 oracle oinstall 439 May 22 15:59 apxremov2_con0.log
-rw-r--r-- 1 oracle oinstall 42 May 22 15:59 apxremov2_con_catcon_13031.done
-rw-r--r-- 1 oracle oinstall 439 May 22 15:59 apxremov2_con1.log
-rw-r--r-- 1 oracle oinstall 2360 May 26 16:06 ncdb2pdb.settings.sql
Open the pdb and check the converted new pdb.
SQL> alter pluggable database newpdb open;
Pluggable database altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
8 NEWPDB READ WRITE NO
SQL> alter session set container=newpdb;
Session altered.
SQL> SELECT name from v$datafile;
NAME
--------------------------------------------------------------------------------
/apps01/oradata/HYDRA/_system_l70lf69m_.dbf
/apps01/oradata/HYDRA/_sysaux_l70lfzfy_.dbf
/apps01/oradata/HYDRA/_undotbs1_l70lggjf_.dbf
/apps01/oradata/HYDRA/_users_l70lghln_.dbf
ORA-65005: missing or invalid file name pattern for file –
Cause:
Syntax issue in FILE_NAME_CONVERT parameter
SQL> CREATE PLUGGABLE DATABASE newpdb USING '/tmp/ncdb.xml'
COPY
FILE_NAME_CONVERT=("/u01/app/oracle/oradata/XSCALIBAL/","/apps01/oradata/HYDRA"); 2 3
FILE_NAME_CONVERT=("/u01/app/oracle/oradata/XSCALIBAL/","/apps01/oradata/HYDRA")
*
ERROR at line 3:
ORA-65005: missing or invalid file name pattern for file -
Solution:
SQL> CREATE PLUGGABLE DATABASE newpdb USING '/tmp/ncdb.xml'
2 COPY
3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/XSCALIBAL/','/apps01/oradata/HYDRA');
ORA-65054: Cannot open a pluggable database in the desired mode.
Can we open a PDB in read write mode when CDB is in read only mode?
Since the CDB is in open read only mode, the underlying PDB cannot be opened because of the metadata synchronization between CDB and PDB
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBHY1 READ ONLY
4 PROX1 MOUNTED
SQL> SELECT name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
HYDRA1 READ ONLY
Close the PDB
SQL> alter session set container=PDBHY1;
Session altered.
SQL> shu immediate;
Pluggable Database closed.
Open the PDB throws ORA-65054
SQL> alter pluggable database PDBHY1 open;
alter pluggable database PDBHY1 open
*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.
This means that, if CDB is in open read only and other PDB need to be opened in read write mode, then the above error is observed.
SQL> !oerr ora 65054
65054, 00000, "Cannot open a pluggable database in the desired mode."
// *Cause: An attempt was made to open a pluggable database (PDB) in a mode
// incompatible with the multitenant container database (CDB) or,
// if the PDB belonged to an application container, in a mode
// incompatible with the application root.
// *Action: Open the CDB or the application root in a compatible mode
// first and retry the operation.
//
Make the CDB in read write mode to open the underlying PDB.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1778382528 bytes
Fixed Size 9135808 bytes
Variable Size 419430400 bytes
Database Buffers 1342177280 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBHY1 READ WRITE NO
This error occur to prevent inconsistency or corruption of the metadata for internal objects.