Temporary Tablespace Usage Issues With High Cost Query (Doc ID 2721027.1)

Last updated on AUGUST 26, 2024

Applies to:

Symptoms

 

ACTUAL BEHAVIOR
---------------
The following SQL is causing general issues on the production instance. The SQL is consuming the temporary tablespace
because of the very high cost query.


EXPECTED BEHAVIOR
-----------------------
Expect the temporary tablespace to be used without hitting a space issue.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. The SQL is:

/* Formatted on 8/26/2020 11:12:58 AM  */
  SELECT *
  FROM (SELECT DISTINCT
  ad.ATTACHED_DOCUMENT_ID,
  ad.DOCUMENT_ID AS DOCUMENT_ID,
  ad.LAST_UPDATE_DATE,
  ad.LAST_UPDATED_BY,
  u.USER_NAME LAST_UPDATED_BY_NAME,
  ad.ENTITY_NAME,
  ad.PK1_VALUE,
  ad.PK2_VALUE,
  ad.PK3_VALUE,
  ad.PK4_VALUE,
  ad.PK5_VALUE,
  DECODE (d.dm_node,
  NULL, (SELECT short_name
  FROM fnd_dm_nodes
  WHERE node_id = 0),
  0, (SELECT short_name
  FROM fnd_dm_nodes
  WHERE node_id = 0),
  node.short_name)
  LOCATION,
  d.DOCUMENT_ID DOCUMENT_ID1,
  d.DATATYPE_ID,
  d.DATATYPE_NAME,
  d.DESCRIPTION,
  DECODE (
  d.FILE_NAME,
  NULL, (SELECT MESSAGE_TEXT
  FROM fnd_new_messages
  WHERE message_name = 'FND_UNDEFINED'
  AND application_id = 0
  AND language_code = USERENV ('LANG')),
  d.FILE_NAME)
  FILE_NAME,
  d.MEDIA_ID,
  d.dm_type,
  d.dm_node,
  d.dm_folder_path,
  e.DATA_OBJECT_CODE,
  e.DOCUMENT_ENTITY_ID,
  'ALLOW_ATTACH_UPDATE' ALLOW_ATTACH_UPDATE,
  'ALLOW_ATTACH_DELETE' ALLOW_ATTACH_DELETE,
  ad.category_id category_id,
  cl.user_name attachment_category_name,
  ad.status,
  ad.creation_date,
  (SELECT u1.user_name
  FROM fnd_user u1
  WHERE u1.user_id = ad.CREATED_BY)
  ATTACHED_BY_NAME,
  DECODE (
  d.datatype_id,
  5, NVL (d.title, d.description)
  || '('
  || SUBSTR (d.URL, 1, LEAST (LENGTH (d.URL), 15))
  || '...)',
  DECODE (
  d.datatype_id,
  6, NVL (d.title, d.file_name),
  DECODE (
  D.TITLE,
  NULL, (SELECT MESSAGE_TEXT
  FROM fnd_new_messages
  WHERE message_name = 'FND_UNDEFINED'
  AND application_id = 0
  AND language_code = USERENV ('LANG')),
  D.TITLE)))
  FILE_NAME_SORT,
  d.usage_type,
  d.security_id,
  d.security_type,
  d.publish_flag,
  cl.category_id category_id_query,
  ad.seq_num,
  d.URL,
  d.TITLE
  FROM FND_DOCUMENTS_VL d,
  FND_ATTACHED_DOCUMENTS ad,
  FND_DOCUMENT_ENTITIES e,
  FND_USER u,
  FND_DOCUMENT_CATEGORIES_TL cl,
  FND_DM_NODES node
  WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID
  AND ad.ENTITY_NAME = e.DATA_OBJECT_CODE(+)
  AND ad.LAST_UPDATED_BY = u.USER_ID(+)
  AND cl.language = USERENV ('LANG')
  AND cl.category_id = NVL (ad.category_id, d.category_id)
  AND d.dm_node = node.node_id(+)) QRSLT
ORDER BY seq_num

 

Changes

Cause

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