欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle11g使用sql

程序员文章站 2022-06-11 20:21:53
...

欢迎进入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]

Oracle11g使用sql