Use SQL for Oracle11g

Welcome to the Oracle Community Forum, interact with 2 million technical personnel | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Activity | ActivityDetail | (Estim) | Active (s) | Active | (Actual) | Reqs | Byte

Welcome to the Oracle Community Forum, interact with 2 million technical staff> enter | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Activity Detail | | (Estim) | Active (s) | Active | (Actual) | Reqs | Byte

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Activity Detail |

| (Estim) | Active (s) | Active | (Actual) | Reqs | Bytes | (%) | (# samples) |

| 0 | select statement | 1 | + 0 | 1 | 1 |

| 1 | sort aggregate | 1 | 1 | + 0 | 1 | 1 |

| 2 | table access full | EMP | 1 | 3 | 1 | 2 | 49152 |

For the captured SQL statements in the database, use the SQL _ID IN THE DBMS_SQLTUNE package

The REPORT_ SQL _MONITOR function allows you to generate more intuitive SQL Report output and assist in analysis and diagnosis.

Generally, a report can be generated by providing a few parameters such as SQL _ID. The TYPE parameter is used to specify the report TYPE,

You can specify the reports generated in the TEXT, HTML, XML, and ACTIVE modes. The most ACTIVE mode reports

Gorgeous and intuitive.

First, you can query v $ SQL _monitor to obtain the SQL information collected by monitoring:

Idle> select SQL _id from v $ SQL _monitor;

SQL _ID

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

6gvch1xu9ca3g

5zruc4v6y32f9

6jfz01hn2n1mj

53c2k4c43zcfx

Sets LONG 1000000

SET long chunksize 1000000

Set linesize 1000

Set pagesize 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SELECT DBMS_SQLTUNE.report_ SQL _monitor (SQL _id => '& sqlid', type => 'text ')

AS report FROM dual;

Enter value for sqlid: 5zruc4v6y32f9

Old 1: SELECT DBMS_SQLTUNE.report_ SQL _monitor (SQL _id => '& sqlid', type => 'text ')

New 1: SELECT DBMS_SQLTUNE.report_ SQL _monitor (SQL _id => '5zruc4v6y32f9 ', type => 'text ')

SQL Monitoring Report

SQL Text

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

DECLARE job BINARY_INTEGER: =: job;

Next_date timestamp with time zone: =: mydate;

Broken BOOLEAN: = FALSE;

Job_name VARCHAR2 (30): =: job_name;

Job_subname VARCHAR2 (30): =: job_subname;

Job_owner VARCHAR2 (30): =: job_owner;

Job_start timestamp with time zone: =: job_start;

Job_scheduled_start timestamp with time zone: =: job_scheduled_start;

Window_start timestamp with time zone: =: window_start;

Window_end timestamp with time zone: =: window_end;

Chain_id VARCHAR2 (14): =: chainid;

Credential_owner varchar2 (30): =: credown;

Credential_name varchar2 (30): =: credna m;

Destination_owner varchar2 (30): =: destown;

Destination_name varchar2 (30): =: destnam;

Job_dest_id varchar2 (14): =: jdestid;

Log_id number: =: log_id;

Begin declare ename VARCHAR2 (30 );

BEGIN ename: = dbms_sqltune.execute_tuning_task ('sys _ AUTO_ SQL _TUNING_TASK ');

END;: mydate: = next_date;

IF broken THEN: B: = 1;

ELSE: B: = 0;

End if; END;

Global Information

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

Status: DONE

Instance ID: 1

Session: SYS (60: 21)

SQL ID: 5zruc4v6y32f9

SQL Execution ID: 16777216

Execution Started: 01/12/2014 10:11:33

First Refresh Time: 01/12/2014 10:11:43

Last Refresh Time: 01/12/2014 10:11:44

Duration: 11 s

Module/Action: DBMS_SCHEDULER/ORA $ AT_SQ_ SQL _SW_63

Service: SYS $ USERS

Program: oracle @ eagle (J002)

Global Stats

========================================================== ======

| Elapsed | Cpu | IO | Concurrency | PL/SQL | Other | Buffer | Read | Write |

| Time (s) | Waits (s) | Gets | Reqs | Bytes |

========================================================== ==========

| 11 | 0.82 | 1.83 | 0.00 | 1.52 | 8.15 | 22904 | 1973 | 30 MB | 8 | 224KB |

========================================================== ============

REPORT_ SQL _MONITOR_LIST view the prime performance of 11gR2 in v $ SQL _monitor

Sets LONG 1000000

SET long chunksize 1000000

Set linesize 1000

Set pagesize 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SELECT DBMS_SQLTUNE.report_ SQL _monitor_list (type => 'text', report_level => 'all') AS report FROM dual;

SQL Monitoring List

==================================

| Status | Duration | SQL Id | Exec Id | Start | User | Module/Action | Dop | DB Time | IOs | SQL Text |

| DONE | 5.0 s | 53c2k4c43zcfx | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA $ AT_ OS _OPT_S | 12 s | 417 | MERGE/* + dynamic_sampling (ST 4) |

| 10:11:44 | dynamic_sampling_est_cdn (ST) */INTO |

STATS_TARGET $ st using (select staleness, |

OSIZE, OBJ #, TYPE #, AFLAGS, STATUS, SID, |

SERIAL #, PART #, BO # FROM (SELECT/* |

No_expand... |

| DONE | 11 s | 5zruc4v6y32f9 | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA $ AT_SQ_ SQL _S | 10 s | 1981 | DECLARE job BINARY_INTEGER: =: job; |

| 10:11:33 | next_date timestamp with time zone: |

: Mydate; broken BOOLEAN: = FALSE; job_name |

VARCHAR2 (30): =: job_name; job_subname |

VARCHAR2 (30): =: job_subname; job_owner... |

| DONE (ALL | 0.05 s | 6jfz01hn2n1mj | 16777216 | 01/12/2014 | SYS | sqlplus @ eagle (/-| 0.05 s | 2 | select/* + monitor */count (*) from |

| ROWS) | 10:05:04 | scott. emp where sal> 5000 |

| DONE | 19 s | 6gvch1xu9ca3g | 16777216 | 01/12/2014 | SYS | 19 s | 1373 | DECLARE job BINARY_INTEGER: =: job; |

| 09:52:27 | next_date DATE: =: mydate; broken BOOLEAN: = |

FALSE; BEGIN |

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS (); |

: Mydate: = next_date; IF broken THEN: B: = |

1; ELSE: B: = 0 ;... |

========================================================== ==================================

(Mos id 1380492.1)

[1] [2] [3]