select count(*) from all_synonyms is hung
(Doc ID 2806903.1)
Last updated on JULY 22, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
During the process of Database upgrade from 12.1.0.2 to 19c
select query hung with "direct path read temp ' wait event
As part of the upgrade you may notice following sql just hung and upgrade may not proceed
SID SQL_HASH_VALUE SQL_TEXT
---------- -------------- ----------------------------------------------------------------
482 529246018 SELECT COUNT(*) FROM ALL_SYNONYMS WHERE SYNONYM_NAME = :B1 AND O
WNER = 'PUBLIC' AND TABLE_OWNER = 'SYS'
> Below execution plan could be seen:
a FTS happening on USER$ and SYN$ in SELECT * from all_synonyms:
|* 5 | FILTER | | | | | | |
|* 6 | HASH JOIN | | 9119K| 1599K| 1599K| 1444K (0)| |
| 7 | INDEX FULL SCAN | I_USER2 | 3002 | | | | |
|* 8 | HASH JOIN | | 9119K| 1483K| 1483K| 1816K (0)| |
| 9 | TABLE ACCESS FULL | USER$ | 3002 | | | | | <<<<<<<<<<<<
|* 10 | HASH JOIN | | 9119K| 661M| 23M| 37M (1)| 850K|
| 11 | TABLE ACCESS FULL | SYN$ | 9070K| | | | |
|* 12 | TABLE ACCESS FULL | OBJ$ | 9119K| | | | |
|* 13 | FIXED TABLE FULL | X$KZSPR | 23 | | | | |
|* 14 | FILTER | | | | | | |
|* 15 | FILTER | | | | | |
Changes
During the process of Database upgrade from 12.1.0.2 to 19c
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |