SQL Text (PD_PDSQ)
- Organization of this page
Function
The SQL Text (PD_PDSQ) record stores performance data indicating the status (at a specific point in time) of the SQL text for a cursor in the shared cursor cache. PFM - Agent for Oracle creates one record for each SQL text for a cursor in the shared cursor cache. This is a multi-instance record.
Since this record in available only in real-time, it is not displayed in the Properties window that is displayed by clicking the agent icon on the Agents page of PFM - Web Console, and then clicking the Properties method.
You use this record only when calling the SQL Text report provided by the monitoring template as a drilldown report. You cannot display a report using this record individually.
If you are displaying a report using this record with SQL and the user executing the SQL statement is not the user who created LSC_13_PLAN_TABLE in the sp_inst.sql script, the system issues a FAILED message to the Explain Plan (EXPLAIN_PLAN) field. To display the correct report, the user specified in the oracle_user property must execute the sp_inst.sql script.
This record displays a drilldown report of the SELECT, INSERT, UPDATE, and DELETE statements. It does not display a drilldown report of any other SQL statement or any PL/SQL package.
- Notes
-
-
Before creating an account, check whether you want to acquire the value of the Explain Plan (EXPLAIN_PLAN) field in the SQL Text (PD_PDSQ) record for operations on the objects that belong to the SYS schema. If you want to do so, use sys as the account to be used by PFM - Agent for Oracle. If you use an account other than sys, you will no longer be able to acquire the value of that field. If the value of the EXPLAIN_PLAN field cannot be acquired, message Explain Plan Failed is stored in the field.
-
If the account used by PFM - Agent for Oracle has no privileges to access, or fails to reference, an object that belongs to a schema of the user who executed SQL, the following value cannot be acquired:
The value of the Explain Plan (EXPLAIN_PLAN) field in the SQL Text (PD_PDSQ) record
If the value of the EXPLAIN_PLAN field cannot be acquired, message Explain Plan Failed is stored in the field. If you want to acquire the value of the Explain Plan (EXPLAIN_PLAN) field, execute the SQL for manipulating the field in the owner.table-name format.
-
Default and changeable values
None
ODBC key fields
PD_PDSQ_ADDRHASH
Lifetime
None
Record size
-
Fixed part: 678 bytes
-
Variable part: 30,051 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$SQLTEXT.ADDRESS V$SQLTEXT.HASH_VALUE |
|
Command Type (COMMAND_TYPE) |
Type of the SQL statement |
-- |
string(10) |
No |
All |
V$SQLTEXT.COMMAND_TYPE |
|
Explain Plan (EXPLAIN_PLAN) |
Execution plan for SELECT, UPDATE, INSERT, and DELETE statements chosen by the Oracle optimizer |
-- |
string(30000) |
No |
All |
Agent Collector |
|
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 PDSQ) |
-- |
string(4) |
No |
All |
Agent Collector |
|
SQL Text (SQL_TEXT) |
Portion of the SQL text |
-- |
string(30000) |
No |
All |
V$SQLTEXT.SQL_TEXT |
|
Start Time (START_TIME) |
Collection start time for the performance data stored in the record |
-- |
time_t |
No |
All |
Agent Collector |