The Sql-monitore bug.

Http://www.mamicode.com/info-detail-1659243.html

The stored procedure cannot do sql-monitor,

And the stored procedure runs the SQL (only through the AWR report to see SQL_ID), then also can not do sql-monitor.

Because the following statement only sees the sql_id of the stored procedure.

SELECT status, KEY, SID, sql_id, Elapsed_time, Cpu_time, fetches, buffer_gets,disk_reads from V$sql_monitor where status= ' Executing '

1. Execute in a timely manner but you can see the relevant information from V$sql_monitor, and you want to generate a SQL Monitor report: And you cannot generate the sql-monitor of the stored procedure.

Sql> SELECT Dbms_sqltune.report_sql_monitor (

sql_id = ' cygs51q4a5tm3 ',

Report_level = ' All ',

Type=> ' TEXT ') from dual;

Dbms_sqltune. Report_sql_monitor (sql_id=> ' cygs51q4a5tm3 ',report_level=> ' all ',type=> ' TEXT ')

-----------------------------------------------------------------------------------------------------------

SQL Monitoring Report

Generated is empty, tried several times, also changed the text format to HTML format, is also empty.

Select Sql.sql_text Sql_text,
T.used_urec Records,
T.used_ublk Blocks,
(T.USED_UBLK * 8192/1024) Kbytes
From V$transaction T, v$session S, v$sql sql
where t.addr = S.taddr
and s.sql_id = sql.sql_id
ORDER BY t.used_ublk Desc;

2. While stored procedures run SQL (only through the AWR report to see SQL_ID), it is also not possible to do sql-monitor. To run 1 of the results, it is still empty and can only be obtained using the catch cursor method. It's also a sql-monitor bug.

SELECT * FROM table (Dbms_xplan. Display_cursor (' cygs51q4a5tm3 ', format=> ' all ');

##############

SELECT A.username,
A.sid,
a.serial#,
A.opname,
A.target,
A.start_time,
A.last_update_time,
C.osuser,
C.machine,
C.program,
ROUND (A.sofar * 100/totalwork, 0) | | '% ' as Jindu,
A.time_remaining,
A.elapsed_seconds,
B.sql_text
From Gv$session_longops A,
Gv$sql B,
Gv$session C
WHERE a.sql_address = b.address
and A.sql_hash_value=b.hash_value
and A.sid = C.sid
and c.status = ' ACTIVE '

SELECT A.username,
A.sid,
a.serial#,
A.opname,
A.target,
A.start_time,
A.last_update_time,
C.osuser,
C.machine,
C.program,
ROUND (A.sofar * 100/totalwork, 0) | | '% ' as Jindu,
A.time_remaining,
A.elapsed_seconds,
B.sql_text
From Gv$session_longops A, Gv$sql B, Gv$session C
WHERE a.sql_address = b.address
and A.sql_hash_value = B.hash_value
and A.sid = C.sid
and c.status = ' ACTIVE '
and C.username= ' Igrsvise '

Col sql_id format A20
Col key format 99999999999999999
SELECT status, KEY, SID, sql_id, Elapsed_time, Cpu_time, fetches, buffer_gets,disk_reads from V$sql_monitor where status= ' Executing ';

Select plan_line_id, Plan_operation, Plan_options starts, output_rows
From V$sql_plan_monitor
where key=193273569471;

SELECT * FROM table (Dbms_xplan. Display_cursor (' cygs51q4a5tm3 ', format=> ' all ');

The

Sql-monitore bug.