SQL Text (PD_PDSQ)

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 - RM 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_14_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 - RM 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 - RM 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

Fields

PFM - View name
(PFM - Manager name)
DescriptionSummaryFormatDeltaSupported versionData source
Addrhash
(ADDRHASH)
Address and hash value used to identify the cached cursor--string(38)NoAllV$SQLTEXT.ADDRESS V$SQLTEXT.HASH_VALUE
Command Type
(COMMAND_TYPE)
Type of the SQL statement--string(10)NoAllV$SQLTEXT.COMMAND_TYPE
Explain Plan
(EXPLAIN_PLAN)
Execution plan for SELECT, UPDATE, INSERT, and DELETE statements chosen by the Oracle optimizer--string(30000)NoAllRemote Monitor Collector
Record Time
(RECORD_TIME)
Collection termination time for the performance data stored in the record--time_tNoAllRemote Monitor Collector
Record Type
(INPUT_RECORD_TYPE)
Record name (always PDSQ)--string(4)NoAllRemote Monitor Collector
SQL Text
(SQL_TEXT)
Portion of the SQL text--string(30000)NoAllV$SQLTEXT.SQL_TEXT
Start Time
(START_TIME)
Collection start time for the performance data stored in the record--time_tNoAllRemote Monitor Collector
VA DeviceID
(VADEVICEID)
Device ID of virtual agent.--string(256)NoAllRemote Monitor Collector