Diagnosing a High Number of Executions Against Table SYS.DIR$ (Doc ID 2230171.1)

Last updated on FEBRUARY 09, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms



WORKLOAD REPOSITORY report for

DB Name DB Id     Instance Inst num Startup Time      Release      RAC
ORCL3   698463995 orcl3       1     18-Sep-16 03:09   12.1.0.2.0   NO

Host Name Platform            CPUs Cores Sockets Memory (GB)
my-host   Linux x86 64-bit     24    12     2      62.81

Snap Id           Snap Time          Sessions Cursors/Session
Begin Snap: 43100 17-Oct-16 00:00:35 66            3.0
End Snap:   43104 17-Oct-16 04:00:20 58            1.6
Elapsed: 239.75 (mins)
DB Time: 307.17 (mins)
.
.
.
Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class

DB CPU 7408.1 40.2
db file sequential read 1,444,192 5569.3 3.86 30.2 User I/O
read by other session     150,659 1081.5 7.18  5.9 User I/O
db file parallel read      35,236 278.2  7.89  1.5 User I/O
db file scattered read     34,328 260.9  7.60  1.4 User I/O
.
.
.
SQL ordered by CPU Time

CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
.
. /* Presence of Data Pump query against SYS.LOADER_DIR_OBJS while accessing an external table. */
.
132.00 880 0.15 1.78 132.05 99.96 0.01 f5ra7dru5fk5n Data Pump Worker SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS
.
.
.
SQL ordered by Executions

/* High number of executions of query against SYS.DIR$, although very few of them actually return any rows. */

Executions Rows Processed Rows per Exec Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text

1,481,940 3,540 0.00 43.34 98.8 0 67n5u0a0q4d7u select audit$, os_path from dir$...
.
.
.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms