Oracle Live

11/04/2016

Monitoring Real Time Database Operations

Filed under: New 12c,SQL Tuning — mogukiller @ 11:04 pm

Nueva funcionalidad que permite capturar la actividad de una determinada session y generar informes del rendimiento de las queires.

Version: 12.1.0.2.0

-- Configuramos el parametro de inicio
SQL> ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' SCOPE=SPFILE SID='*';
SQL> ALTER SYSTEM SET STATISTICS_LEVEL='TYPICAL' SCOPE=SPFILE SID='*';

-- Ponemos una sesion a tracear.

sqlplus mogu/temporal@PRUEBA

'nota: habilitamos el traceo'
VAR dbop_eid NUMBER;
EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION ('ORA.MOGU.select', forced_tracking => 'Y');


'nota: Ejecutamos el proceso a tracear'
BEGIN
	EJECUTA_CARGA;
END;
/

'nota: Paramos la monitorizacion'
EXEC DBMS_SQL_MONITOR.end_operation ('ORA.MOGU.select', :dbop_eid  );

-- Para monitorizar el traceo
V$SQL_MONITOR

-- Para sacar los informes de rendimiento

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'ORA.MOGU.select',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;

/* SALIDA

Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  MOGU (452:47737)
 DBOP Name           :  ORA.MOGU.select
 DBOP Execution ID   :  1
 First Refresh Time  :  12/22/2015 13:05:11
 Last Refresh Time   :  12/22/2015 13:14:51
 Duration            :  580s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@tehol051.mgmt.dc.es.telefonica (TNS V1-V

Global Stats
==============================
| Elapsed |   Cpu   | Buffer |
| Time(s) | Time(s) |  Gets  |
==============================
|    0.02 |    0.02 |      7 |
==============================

*/

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;

/* SALIDA
                                                                                SQL Monitoring List
                                                                               =====================


=================================================================================================================================================================================
|      Status       | Duration | Inst Id |  SQL Id or DBOP   | Exec Id  |       Start       |       User        |   Module/Action   | Dop | DB Time |  IOs  |     SQL Text      |
|                   |          |         |       Name        |          |                   |                   |                   |     |         |       |                   |
=================================================================================================================================================================================
| DONE (ALL ROWS)   |     9.0s |    1    |   3n1afddgbcung   | 16777216 |    12/22/2015     | SYS               | emagent_SQL_rac/M |     |    9.2s | 45705 | select round((sys |
|                   |          |         |                   |          |     09:59:59      |                   | E$LAST_BACKUP_    |     |         |       | date-min(t))*24,2 |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | ) as last_backup  |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | from ( select max |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | (b.CHECKPOINT_TIM |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | E) t from         |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | v$backup_datafile |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | b, v$tablespace   |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | ts, v$datafile f  |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | where INCLUDED_IN |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | _DATABASE_BACKUP= |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | 'YES' and         |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | f.file#=b...      |
| DONE (ALL ROWS)   |     9.0s |    1    |   3n1afddgbcung   | 16777216 |    12/21/2015     | SYS               | emagent_SQL_rac/M |     |    9.2s | 44280 | select round((sys |
|                   |          |         |                   |          |     09:59:59      |                   | E$LAST_BACKUP_    |     |         |       | date-min(t))*24,2 |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | ) as last_backup  |
|                   |          |         |                   |          |                   |                   |                   |     |         |       | from ( select max |

*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: