My Oracle Support Banner

select count(*) from all_synonyms is hung (Doc ID 2806903.1)

Last updated on OCTOBER 19, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information 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


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