Database Reference
In-Depth Information
SQL Activity
The entry point for the SQL Details page is the SQL Activity tab. This view, offering the last hour's activity of the
SQL ID under investigation, along with the statement information and wait history, is displayed clearly in a standard
graph view (refer ahead to Figure
9-16
). The SQL shown in the example is for a data pump job on the
SYSTEM
tablespace.
For procedural calls, it is extremely helpful to be able to tie the background SQL process back to its parent.
To locate this from a SQL ID shown in the EM console, the following query can help:
select o.owner, o.object_name, o.object_type, s.program_line#
from v$sql s, dba_objects o
where sql_id = '<SQL_ID>'
and s.program_id=o.object_id;
The procedure or package, along with the line that the SQL call sourced from, will then be returned, thus quickly
letting you know the source. This can be run from a SQL *Plus session. If you prefer to run it from the EM12c console,
this can be accomplished by clicking Performance
➤
SQL
➤
Run SQL.
The interface offers a simple option to execute a SQL statement or load a SQL script. Input the login to be used to
execute the script (see Figure
9-15
).
Figure 9-15.
The Run SQL interface inEM12c, which allows SQL to be executed directly from the console
■
if you run sQl from the em12c console on a regular basis, it is best to ensure that the host and database
preferred credentials are set up to reduce issues with failed executions.
Tip
Once executed, the following information is returned:
OWNER OBJECT_NAME OBJECT_TYPE PROGRAM_LINE#
----------------------------------------------------------------------------
USER1 GET_NEW_FUNC FUNCTION 9
