Thanks for the question, RAUF.
Asked: May 29, 2018 - 7:57 am UTC
Last updated: May 31, 2018 - 8:34 am UTC
Version: 11g 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Tom,
while creating explain plan for query, explain plan table is getting locked until commit or rollback. If we don't want to commit / rollback, is it a problem that explain plan table stays locked? also while it is locked we don't have any problems with creating new explain plans in current or another session.
Thanks
and Connor said...
By default, PLAN_TABLE is a synonym to a global temporary table
SQL> select * from dba_synonyms where synonym_name = 'PLAN_TABLE'
2 @pr
==============================
OWNER : PUBLIC
SYNONYM_NAME : PLAN_TABLE
TABLE_OWNER : SYS
TABLE_NAME : PLAN_TABLE$
DB_LINK :
ORIGIN_CON_ID : 0
PL/SQL procedure successfully completed.
SQL> select * from dba_tables where table_name = 'PLAN_TABLE$'
2 @pr
==============================
OWNER : SYS
TABLE_NAME : PLAN_TABLE$
TABLESPACE_NAME :
CLUSTER_NAME :
IOT_NAME :
STATUS : VALID
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT :
NEXT_EXTENT :
MIN_EXTENTS :
MAX_EXTENTS :
PCT_INCREASE :
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : NO
BACKED_UP : N
NUM_ROWS :
BLOCKS :
EMPTY_BLOCKS :
AVG_SPACE :
CHAIN_CNT :
AVG_ROW_LEN :
AVG_SPACE_FREELIST_BLOCKS :
NUM_FREELIST_BLOCKS :
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE :
LAST_ANALYZED :
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : Y
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : NO
USER_STATS : NO
DURATION : SYS$SESSION
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
DEPENDENCIES : DISABLED
COMPRESSION : DISABLED
COMPRESS_FOR : BASIC
DROPPED : NO
READ_ONLY : NO
SEGMENT_CREATED : YES
RESULT_CACHE : DEFAULT
CLUSTERING : NO
ACTIVITY_TRACKING :
DML_TIMESTAMP :
HAS_IDENTITY : NO
CONTAINER_DATA : NO
INMEMORY : DISABLED
INMEMORY_PRIORITY :
INMEMORY_DISTRIBUTE :
INMEMORY_COMPRESSION :
INMEMORY_DUPLICATE :
DEFAULT_COLLATION : USING_NLS_COMP
DUPLICATED : N
SHARDED : N
EXTERNAL : NO
CELLMEMORY :
CONTAINERS_DEFAULT : NO
CONTAINER_MAP : NO
EXTENDED_DATA_LINK : NO
EXTENDED_DATA_LINK_MAP : NO
INMEMORY_SERVICE :
INMEMORY_SERVICE_NAME :
CONTAINER_MAP_OBJECT : NO
PL/SQL procedure successfully completed.
which means it is not possible to have *blocking* row locks between sessions. If you are saying that locks just appear in various monitoring views, then this is not an issue. That's just normal database operation.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment