当前位置:Gxlcms > mysql > Oracle11g使用sql

Oracle11g使用sql

时间:2021-07-01 10:21:17 帮助过:52人阅读

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Byte

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

  | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | 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 | | |

  对于数据库中已经捕获的 SQL,通过其 SQL_ID,使用 DBMS_SQLTUNE 程序包中的

  REPORT_SQL_MONITOR 函数,我们可以生成更为直观的 SQL 报告输出,辅助分析和诊断。

  通常情况下,提供 SQL_ID 等少数参数,即可生成报告,TYPE 参数用于指定报告类型,

  这里可以指定生成:TEXT、HTML、XML、ACTIVE 模式的报告。ACTIVE 模式的报告最为

  华丽直观。

  首先可以通过查询 v$sql_monitor 获得那些被监控收集过的 SQL 信息:

  idle> select sql_id from v$sql_monitor;

  SQL_ID

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

  6gvch1xu9ca3g

  5zruc4v6y32f9

  6jfz01hn2n1mj

  53c2k4c43zcfx

  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_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) := :crednam;

  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 : 11s

  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 | Read | Write | Write |

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

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

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

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

  REPORT_SQL_MONITOR_LIST查看在11gR2在v$sql_monitor中的总理性能

  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_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.0s | 53c2k4c43zcfx | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_OS_OPT_S | | 12s | 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 | 11s | 5zruc4v6y32f9 | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_SQ_SQL_S | | 10s | 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.05s | 6jfz01hn2n1mj | 16777216 | 01/12/2014 | SYS | sqlplus@eagle (/- | | 0.05s | 2 | select /*+ monitor */ count(*) from |

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

  | DONE | 19s | 6gvch1xu9ca3g | 16777216 | 01/12/2014 | SYS | | | 19s | 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]

人气教程排行