Print Friendly, PDF & Email
ORA-1652 error is continuously reported in the alert log file. The error is caused by the query with SQL_ID dx4nqvbtu06bx.
SYMPTOMS

The following error messages are frequently generated in the database alert log

Sat May 23 07:00:48 2020
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP02
Sat May 23 07:09:32 2020
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP02
Sat May 23 07:09:34 2020
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP02
Sat May 23 07:09:39 2020
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP02
NOTE

If the temporary tablespace is big enough, there would be no errors like those, but you can confirm high usage of temp tablespace by querying the DBT_TEMPUSAGE view.

SELECT * FROM DBT_TEMPUSAGE WHERE STATUS='ACTIVE' AND TEMP_USED_GB>0;

USERNAME   SID SERIAL# PID  STATUS   SQL_ID        ELAPSED_SEC EVENT                       CONNNECTED_FROM TABLESPACE  SEGTYPE TEMP_USED_GB PERCENT_USED
-------- ----- ------- ---- -------- ------------- ----------- --------------------------- --------------- ----------- ------- ------------ ------------
DBSNMP     634    5349 6362 ACTIVE   dx4nqvbtu06bx         595 direct path write temp      dbpilot.net     TEMP02      HASH           106.9         83.5
DBSNMP    2907   56461 1191 INACTIVE                         6 SQL*Net message from client dbpilot.net     TEMP02      LOB_DATA           0            0

In this demo, the query dx4nqvbtu06bx uses 83.5% (106.9GiB) of all available space in TEMP2 tablespace.

 
In an AWR report of the database, the following can be observed:

1.  A high value for the direct path write temp wait event

2. A high elapsed time for the statement with the sql_id dx4nqvbtu06bx

awr_dx4nqvbtu06bx_002.png

SQL execution statistics from V$SQL view show that most of the query elapsed time is IO wait time

**************************************** : ******************
                                  SQL_ID : dx4nqvbtu06bx
                                  Child# : 0
                           Cursor Status : VALID
                     Is Cursor Obsolete? : N
                          Parsing Schema : DBSNMP
                        System Timestamp : 08-05-2020 11-52-50
                               First Run : 2020-04-26/18:25:08
                        Last Active Time : 08-05-2020 11-52-48
                   Elapsed Time Total(s) : 520116.41
                       CPU Time Total(s) : 207325.58
                   IO Wait Time Total(s) : 319430.49
          Concurrency Wait Time Total(s) : 5.66
          Application Wait Time Total(s) : 0
              Cluster Wait Time Total(s) : 0
                              Executions : 942
                Elapsed Time per Exec(s) : 552.14
                    CPU Time per Exec(s) : 220.09
                IO Wait Time per Exec(s) : 339.1
       Concurrency Wait Time per Exec(s) : .01
       Application Wait Time per Exec(s) : 0
           Cluster Wait Time per Exec(s) : 0
                                         :
                              Disk Reads : 154609
                             Buffer Gets : 455867
                           Direct Writes : 2175471508
                           Row Processed : 0
                                         :
                       Number of latches : 0
                             Sql Profile :
                        Sql Command Type : 3
**************************************** : ******************

In this demo, the IO wait time of the query is about 61% of Elapsed Time.

SOLUTION

Apply patch 14078947 on the target database.

Or, Use one of the three available workarounds as suggested by Doc ID 2201982.1

1. Use a rule hint in the query as follows (which would have to be modified using the DBMS_SPM package):

SELECT /*+ rule*/ MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B1
AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1;

2. Execute the following against the target database:

NOTE

This workaround doesn't help much. The query still uses lots of temporary space.

exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');
alter system flush shared_pool;
alter system flush shared_pool;

3. To disable the metric which executes this query from Enterprise Manager, please refer to MOS Doc ID 2235163.1.

Or, Gather Table Stats for X$ tables as suggested by Doc ID 2575123.1
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KCCBP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KCCBS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KCCRSR');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KSFQP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KRBMRST');
RESULT

Patch 14078947 fixed the issue with the performance of the query dx4nqvbtu06bx. New query execution statistic is the following

*************************************** : ******************
                                  SQL_ID : dx4nqvbtu06bx
                                  Child# : 0
                           Cursor Status : VALID
                     Is Cursor Obsolete? : N
                          Parsing Schema : DBSNMP
                        System Timestamp : 15-07-2020 12-08-31
                               First Run : 2020-06-20/17:03:10
                        Last Active Time : 15-07-2020 12-03-12
                   Elapsed Time Total(s) : 2422.46
                       CPU Time Total(s) : 1269.47
                   IO Wait Time Total(s) : .21
          Concurrency Wait Time Total(s) : 0
          Application Wait Time Total(s) : 0
              Cluster Wait Time Total(s) : 0
                              Executions : 6002
                Elapsed Time per Exec(s) : .4
                    CPU Time per Exec(s) : .21
                IO Wait Time per Exec(s) : 0
       Concurrency Wait Time per Exec(s) : 0
       Application Wait Time per Exec(s) : 0
           Cluster Wait Time per Exec(s) : 0
                                         :
                              Disk Reads : 223
                             Buffer Gets : 9899
                           Direct Writes : 0
                           Row Processed : 6002
                                         :
                       Number of latches : 0
                             Sql Profile :
                        Sql Command Type : 3
 *************************************** : ******************

The Elapsed Time of the query reduced significantly.

REFERENCES

EM 13c: After Upgrade to Enterprise Manager 13.2 Cloud Control, a Recurring Query in 11.2.0.4 Target Database (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) Takes Five Minutes, Using Huge Amount of Temp Space (Doc ID 2201982.1)
Bug 14078947 - Select from V$RMAN_STATUS very slow when database is open (Doc ID 14078947.8)
Queries on V$RMAN_STATUS are Very Slow Even After GATHER_FIXED_OBJECTS_STATS is Run (Doc ID 1525917.1)
EM13c : A Recurring Query in Database targets has performance issues (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) (Doc ID 2235163.1)
"ORA-1652: Unable To Extend Temp Segment By 128 In Tablespace TEMP" Due to X$ Tables (Doc ID 2575123.1)

Written At
15 JUL 202017:00
Red Hat Release
7.x x64
Enterprise Manager
13.3.0.0.0
Database Release
11.2.0.4