SQL Text - Performance Based (PD_PDES)
- Organization of this page
Function
The SQL Text - Performance Based (PD_PDES) record stores performance data indicating the status of SQL text that satisfies the following conditions at a specific point in time (see Note). PFM - Agent for Oracle creates one record for each SQL statement that satisfies the conditions. This is a multi-instance record. A maximum of five instances can be created for this record.
- Note:
-
The following describes the conditions:
-
Data is sorted from the object with the most read operations per execution to the object with the least read operations per execution.
-
The result of step 1 indicates that the number of disk read operations per execution of an object exceeds 1,000. Alternatively, the number of I/O operations per execution of an object exceeds 10,000.
-
Default and changeable values
Item |
Default value |
Changeable |
---|---|---|
Collection Interval |
600 |
Y |
Collection Offset |
40 |
Y |
Log |
No |
Y |
LOGIF |
(Blank) |
Y |
Over 10 Sec Collection Time |
No |
N |
ODBC key fields
PD_PDES_ADDRHASH
Lifetime
From the loading to the unloading from the shared SQL area
Record size
-
Fixed part: 678 bytes
-
Variable part: 10,131 bytes
Fields
PFM - View name (PFM - Manager name) |
Description |
Summary |
Format |
Delta |
Supported version |
Data source |
---|---|---|---|---|---|---|
Addrhash (ADDRHASH) |
Address and hash value used to identify the cached cursor |
-- |
string(38) |
No |
All |
V$SQLAREA.ADDRESS + V$SQLAREA.HASH_VALUE |
Buffer Gets (BUFFER_GETS) |
Total buffer acquisitions over all child cursors Disk Reads |
-- |
double |
No |
All |
V$SQLAREA.BUFFER_GETS |
Disk Reads (DISK_READS) |
Total disk read operations over all child cursors |
-- |
double |
No |
All |
V$SQLAREA.DISK_READS |
Disk Reads/Exec (DISK_READS_PER_EXECUTION) |
Number of physical read operations required per execution |
-- |
double |
No |
All |
V$SQLAREA.DISK_READS / V$SQLAREA.EXECUTIONS |
Executions (EXECUTIONS) |
Number of times the object was executed after this object was placed in the library cache |
-- |
double |
No |
All |
V$SQLAREA.EXECUTIONS |
Hit % (HIT_PERCENTAGE) |
Ratio (as a percent) of buffer read operations to all read operations |
-- |
double |
No |
All |
100 * (V$SQLAREA.BUFFER_GETS - V$SQLAREA.DISK_READS ) / V$SQLAREA.BUFFER_GETS |
Logical Reads/Exec (LOGICAL_IO_PER_EXECUTION) |
Number of logical read operations required per execution |
-- |
double |
No |
All |
V$SQLAREA.BUFFER_GETS / V$SQLAREA.EXECUTIONS |
Parsing User (PARSING_USER) |
User who analyzed the SQL statement (if connecting to Oracle 12c R1 or later, the user ID) |
-- |
string(30) |
No |
All |
USER$.NAME where USER$.USER# = V$SQLAREA.PARSING_USER_ID
|
Record Time (RECORD_TIME) |
Collection termination time for the performance data stored in the record |
-- |
time_t |
No |
All |
Agent Collector |
Record Type (INPUT_RECORD_TYPE) |
Record name (always PDES) |
-- |
string(4) |
No |
All |
Agent Collector |
SEQNO (SEQUENCE) |
Number of times the cursor was found during data collection |
-- |
ulong |
No |
All |
Agent Collector |
SQL Text (SQL_TEXT) |
SQL text for the cursor |
-- |
string(10000) |
No |
All |
V$SQLTEXT.SQL_TEXT |
Sorts (SORTS) |
Total number of sort operations executed on all child cursors |
-- |
double |
No |
All |
V$SQLAREA.SORTS |
Start Time (START_TIME) |
Collection start time for the performance data stored in the record |
-- |
time_t |
No |
All |
Agent Collector |