`
dingjun1
  • 浏览: 213578 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

在Oracle 9i下的display_cursor脚本

阅读更多
转载:http://www.laoxiong.net/oracle9i_display_cursor.html
大家都知道Oracle 10g的dbms_stats包与Oracle 9i相比,功能增强了很多,比如增加了display_cursor这个过程,能够查看V$SQL_PLAN视图中的执行计划,如果在statistics_level参数设置为ALL,或者执行的sql使用了gather_plan_statistics hint,则在sql执行后,会在v$sql_plan_statistics_all视图中查到SQL的执行统计信息,例如逻辑读,物理读等等。这些数据对于性能诊断有着非常大的帮助。同时v$sql_plan中的执行计划,与通过EXPLAIN PLAN得到的执行计划相比,前者是oracle执行sql时真正使用的执行计划,而后者则可能不是真正的执行计划;同时有的时候,执行过的sql使用了绑定变量,而oracle在解析sql时通常会进行绑定变量窥探,这个时候我们不能使用EXPLAIN PLAN来得到那个sql的执行计划,就算得到的跟那个sql的真实的执行计划是不一样的,所以有时我们更愿意直接从v$sql_plan中得到执行计划。

但是在oracle 9i中的dbms_xplan包没有display_cursor这个过程。不过,本文根据一个开源软件SQLT中得到的一段脚本,经过修改后,能够显示 v$sql_plan和v$sql_plan_statistics中的执行计划和sql的执行统计数据。点击此处下载display_cursor_9i 代码

下面是使用这个代码的示例:
    SQL> select /*+ sqla */ count(*) from t1 where a<13;

      COUNT(*)
    ----------
         40000

在另一个会话中,得到这个SQL的hash_value , child_number以及在v$sql_plan中的执行计划。
    SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

    HASH_VALUE CHILD_NUMBER
    ---------- ------------
    1742773495            0

    SQL> @display_cursor_9i 1742773495 0
    原值  268:   s_hash_value := &1;
    新值  268:   s_hash_value := 1742773495;
    原值  269:   s_child_num := &2;
    新值  269:   s_child_num := 0;

    HASH_VALUE: 1742773495   CHILD_NUMBER: 0
    ---------------------------------------------------------------------------------------------
    select /*+ sqla */ count(*) from t1 where a<13

    Plan hash value: 3724264953

    ------------------------------------------------------------
    | Id   | Operation           | Name |  Rows | Bytes | Cost |
    ------------------------------------------------------------
    |    0 | SELECT STATEMENT    |      |       |       |   25 |
    |    1 |  SORT AGGREGATE     |      |     1 |     3 |      |
    | *  2 |   TABLE ACCESS FULL | T1   | 44444 |  133K |   25 |
    ------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    3 - filter(”A”<13)

    PL/SQL 过程已成功完成。

如果我们将statistics_level设置为ALL(注意:在oracle 9i中gather_plan_statistics这个hint无效),重新执行这个SQL:
    SQL> alter session set statistics_level=all;

    会话已更改。

    SQL> select /*+ sqla */ count(*) from t1 where a<13;

      COUNT(*)
    ----------
         40000

在会话2中重新进行之前的查询,只不过由于参数的参数,这个SQL有两个子游标,这次执行的游标其child_number为1:
    SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

    HASH_VALUE CHILD_NUMBER
    ---------- ------------
    1742773495            0
    1742773495            1

    SQL> @display_cursor_9i 1742773495 1
    原值  268:   s_hash_value := &1;
    新值  268:   s_hash_value := 1742773495;
    原值  269:   s_child_num := &2;
    新值  269:   s_child_num := 1;

    HASH_VALUE: 1742773495   CHILD_NUMBER: 1
    -------------------------------------------------------------------------------------------------------------------
    select /*+ sqla */ count(*) from t1 where a<13

    Plan hash value: 3724264953

    ----------------------------------------------------------------------------------------------------------------
    | Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------
    |    1 | SORT AGGREGATE     |      |      0 |      1 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
    | *  2 |  TABLE ACCESS FULL | T1   |      0 |  44444 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
    ----------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - filter(”A”<13)

    PL/SQL 过程已成功完成。

不幸的是,在另一个会话中查询v$sql_plan_statistics_all的一些结果并不正确。只有在那个执行SQL的会话(就是例子中的会话 1)中,才能得到正确的结果:
----------------------------------------------------------------------------------------------------------------
| Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      1 |      1 |      1 | 00:00:00.39 |     155 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      1 |  44444 |  40000 | 00:00:00.21 |     155 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------


set serveroutput on
set serveroutput on size 10000000
set linesize 150 pagesize 10000

declare
  -- cursor c_display_cursor(in_address varchar2, in_hash_value number, in_child_number number) is
  s_display_cursor varchar2(32767) := '
  select case when access_predicates is not null or filter_predicates is not null then ''*'' else '' '' end || substr(to_char(id, ''999''), -3) as p_id,
  lpad('' '', depth) || operation || '' '' || options as operation,
  object_name as name,
  starts,
  cardinality as e_rows,
  outrows as a_rows,
  to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), ''FM900'') || substr(e_time_interval, 14, 9) as a_time,
  crgets + cugets as buffers,
  case reads when 0 then null else reads end as reads,
  case writes when 0 then null else writes end as writes,
  case other_tag
  when ''PARALLEL_TO_SERIAL'' then ''P->S''
  when ''PARALLEL_COMBINED_WITH_PARENT'' then ''PCWP''
  when ''PARALLEL_COMBINED_WITH_CHILD'' then ''PCWC''
  when ''SERIAL_TO_PARALLEL'' then ''S->P''
  when ''PARALLEL_TO_PARALLEL'' then ''P->P''
  when ''PARALLEL_FROM_SERIAL'' then ''P<-S''
  else other_tag end as in_out,
  partition_start,
  partition_stop,
  distribution,
  mem_opt,
  mem_one,
  last_mem_used || case last_mem_usage when ''OPTIMAL'' then '' (0)'' when ''ONE PASS'' then '' (1)'' when ''MULTI-PASS'' then '' (M)'' end,
  case last_degree when 0 then null when 1 then null else last_degree end as last_degree,
  --opt_cnt,
  --one_cnt,
  --multi_cnt,
  --max_tmp,
  last_tmp,
  access_predicates,
  filter_predicates,
  dynamic_sampling_flag,
  id
  from (
  SELECT /*+ opt_param(''parallel_execution_enabled'', ''false'') */
                        id, position, depth , operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost,
                        cpu_cost, partition_start, partition_stop, object_node, other_tag, distribution, null, access_predicates, filter_predicates,
                        null, null, null, starts, outrows, crgets, cugets, reads, writes, etime, e_time_interval, mem_opt, mem_one, last_mem_used, last_degree, last_mem_usage,
                        opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp, dynamic_sampling_flag from (
                            select /*+ no_merge */
                            id, depth, position, operation, options,
                            cost, cardinality, bytes, object_node,
                            object_name, other_tag, partition_start,
                            partition_stop, distribution, temp_space, io_cost,
                            cpu_cost, filter_predicates, access_predicates, other,
                            last_starts starts,
                                      last_output_rows outrows,
                                      last_cr_buffer_gets crgets,
                                      last_cu_buffer_gets cugets,
                                      last_disk_reads reads,
                                      last_disk_writes writes,
                                      last_elapsed_time etime,
                                      to_char(numtodsinterval(round(last_elapsed_time/10000)*10000/1000000, ''SECOND'')) as e_time_interval,
                                      estimated_optimal_size mem_opt,
                                      estimated_onepass_size mem_one,
                                      last_memory_used last_mem_used,
                                      last_degree,
                                      last_execution last_mem_usage,
                                      optimal_executions opt_cnt,
                                      onepass_executions one_cnt,
                                      multipasses_executions multi_cnt,
                                      max_tempseg_size max_tmp,
                                      last_tempseg_size last_tmp,
                                      case 0 /*instr(other_xml, ''dynamic_sampling'')*/ when 0 then NULL else ''YES'' end as dynamic_sampling_flag
                from V$SQL_PLAN_STATISTICS_ALL vp
                --where address = hextoraw(:in_address)
                where hash_value = :in_hash_value
                and child_number = :in_child_number)
  )
  order by id';
  
  s_display_cursor2 varchar2(32767) := '
  select case when access_predicates is not null or filter_predicates is not null then ''*'' else '' '' end || substr(to_char(id, ''999''), -3) as p_id,
  lpad('' '', depth) || operation || '' '' || options as operation,
  object_name as name,
  cardinality as e_rows,
  bytes,
  temp_space,
  cost,
  cpu_cost,
  object_node,
  case other_tag
  when ''PARALLEL_TO_SERIAL'' then ''P->S''
  when ''PARALLEL_COMBINED_WITH_PARENT'' then ''PCWP''
  when ''PARALLEL_COMBINED_WITH_CHILD'' then ''PCWC''
  when ''SERIAL_TO_PARALLEL'' then ''S->P''
  when ''PARALLEL_TO_PARALLEL'' then ''P->P''
  when ''PARALLEL_FROM_SERIAL'' then ''P<-S''
  else other_tag end as in_out,
  partition_start,
  partition_stop,
  distribution,
  access_predicates,
  filter_predicates,
  dynamic_sampling_flag,
  id
  from (
  SELECT /*+ opt_param(''parallel_execution_enabled'', ''false'') */
                        id, position, depth , operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost,
                        cpu_cost, partition_start, partition_stop, object_node, other_tag, distribution, null, access_predicates, filter_predicates,
                        null, null, null, starts, outrows, crgets, cugets, reads, writes, etime, e_time_interval, mem_opt, mem_one, last_mem_used, last_degree, last_mem_usage,
                        opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp, dynamic_sampling_flag from (
                            select /*+ no_merge */
                            id, depth, position, operation, options,
                            cost, cardinality, bytes, object_node,
                            object_name, other_tag, partition_start,
                            partition_stop, distribution, temp_space, io_cost,
                            cpu_cost, filter_predicates, access_predicates, other,
                            0 starts,
                                      0 outrows,
                                      0 crgets,
                                      0 cugets,
                                      0 reads,
                                      0 writes,
                                      0 etime,
                                      0 e_time_interval,
                                      0 mem_opt,
                                      0 mem_one,
                                      null last_mem_used,
                                      0 last_degree,
                                      null last_mem_usage,
                                      0 opt_cnt,
                                      0 one_cnt,
                                      0 multi_cnt,
                                      0 max_tmp,
                                      0 last_tmp,
                                      case 0 /*instr(other_xml, ''dynamic_sampling'')*/ when 0 then NULL else ''YES'' end as dynamic_sampling_flag
                from V$SQL_PLAN vp
                --where address = hextoraw(:in_address)
                where hash_value = :in_hash_value
                and child_number = :in_child_number)
  )
  order by id';
    
  type t_list_varchar2 is table of varchar2(4000) index by pls_integer;
  type t_column_record is record (
  a_data        t_list_varchar2,
  b_has_data    boolean,
  s_heading     varchar2(255),
  b_is_number   boolean default false,
  s_alignment   varchar2(20),
  n_max_size    pls_integer);
  type t_column_list is table of t_column_record index by pls_integer;
  a_column_list t_column_list;
  n_row_size    pls_integer;
  s_row         varchar2(4000);
  a_access_pred t_list_varchar2;
  a_filter_pred t_list_varchar2;
  s_plan_hash   varchar2(255);
  a_dyn_sampl   t_list_varchar2;
  a_id_list     t_list_varchar2;
  s_output      varchar2(4000);
  s_sql_address varchar2(255);
  s_hash_value  varchar2(255);
  s_child_num   varchar2(255);
  b_has_stat boolean := TRUE;
  
  max_line_size constant pls_integer := 255;
  c_display_cursor sys_refcursor;
  n_cnt pls_integer;
  function has_collection_only_nulls(in_coll in t_list_varchar2)
  return boolean is
    b_return boolean := true;
  begin
    if in_coll.count > 0 then
      for i in in_coll.first..in_coll.last loop
        if in_coll(i) is not null then
          b_return := false;
          exit;
        end if;
      end loop;
    end if;
    return b_return;
  end has_collection_only_nulls;
  function get_max_size(in_coll in t_list_varchar2)
  return pls_integer is
    n_return pls_integer := 0;
  begin
    if in_coll.count > 0 then
      for i in in_coll.first..in_coll.last loop
        if in_coll(i) is not null then
          n_return := greatest(n_return, length(in_coll(i)));
        end if;
      end loop;
    end if;
    return n_return;
  end get_max_size;
  function display_cursor_format_number(in_data in varchar2)
  return varchar2 is
    s_return varchar2(20);
    s_trail varchar2(32767);
    s_data varchar2(32767);
    n_number number;
    n_delim_pos number;
    e_num_val_error exception;
    pragma exception_init(e_num_val_error, -6502);
  begin
    n_delim_pos := instr(in_data, ' ');
    if n_delim_pos > 0 then
      s_trail := substr(in_data, n_delim_pos);
      s_data := substr(in_data, 1, n_delim_pos - 1);
    else
      s_data := in_data;
    end if;
    n_number := to_number(s_data);
    s_return :=
    case
    when n_number >= 100000000000000000000 then to_char(n_number/1000000000000000000, 'FM99999') || 'E'
    when n_number >= 100000000000000000 then to_char(n_number/1000000000000000, 'FM99999') || 'P'
    when n_number >= 100000000000000 then to_char(n_number/1000000000000, 'FM99999') || 'T'
    when n_number >= 100000000000 then to_char(n_number/1000000000, 'FM99999') || 'G'
    when n_number >= 100000000 then to_char(n_number/1000000, 'FM99999') || 'M'
    when n_number >= 100000 then to_char(n_number/1000, 'FM99999') || 'K'
    else to_char(n_number, 'FM99999')
    end;
    return s_return || s_trail;
  exception
  when e_num_val_error then
    return in_data;
  end display_cursor_format_number;
  procedure put_line_smart(in_string in varchar2, in_line_prefix in varchar2 default '', in_line_size in pls_integer default 134) is
    n_offset pls_integer;
    s_delimiter varchar2(1);
    n_size_current_line pls_integer;
    n_line_counter pls_integer;
  begin
    n_offset := 1;
    n_size_current_line := in_line_size;
    n_line_counter := 1;
    while case when n_line_counter > 1 and length(in_line_prefix) > 0
          then length(in_string) + length(in_line_prefix)
          else length(in_string) end
          + 1 - n_offset > in_line_size loop
      -- dbms_output.put_line('Debug n_offset: ' || n_offset);
      if n_line_counter > 1 and length(in_line_prefix) > 0 then
        n_size_current_line := greatest(n_size_current_line - length(in_line_prefix), length(in_line_prefix) + 10);
      end if;
      -- dbms_output.put_line('Debug n_size_current_line: ' || n_size_current_line);
      loop
        s_delimiter := substr(in_string, n_offset - 1 + n_size_current_line, 1);
        exit when s_delimiter in (' ', chr(9), chr(10), chr(13)/*, '(', ')', '[', ']'*/) or n_size_current_line < 1;
        n_size_current_line := n_size_current_line - 1;
      end loop;
      if n_size_current_line < 1 then
        if n_line_counter > 1 and length(in_line_prefix) > 0 then
          n_size_current_line := greatest(n_size_current_line - length(in_line_prefix), length(in_line_prefix) + 10);
        else
          n_size_current_line := in_line_size;
        end if;
      end if;
      if s_delimiter in (chr(13), chr(10)) then
        n_size_current_line := n_size_current_line - 1;
      end if;
      dbms_output.put_line(case when n_line_counter > 1 then in_line_prefix end || substr(in_string, n_offset, n_size_current_line));
      if s_delimiter in (chr(13), chr(10)) then
        while substr(in_string, n_offset - 1 + n_size_current_line, 1) in (chr(10), chr(13)) loop
          n_size_current_line := n_size_current_line + 1;
        end loop;
      end if;
      n_offset := n_offset + n_size_current_line;
      n_size_current_line := in_line_size;
      n_line_counter := n_line_counter + 1;
    end loop;
    dbms_output.put_line(case when n_line_counter > 1 then in_line_prefix end || substr(in_string, n_offset));
  end put_line_smart;
begin
  s_hash_value := &1;
  s_child_num := &2;
  -- Header information
  dbms_output.put_line(chr(13));  
  put_line_smart(in_string => ' HASH_VALUE: ' || s_hash_value || '   CHILD_NUMBER: ' || s_child_num , in_line_size => max_line_size);
  put_line_smart(in_string => '---------------------------------------------------------------------------------------------------------------------------------------------', in_line_size => max_line_size);
  begin
  	execute immediate '
    select sql_text,
    plan_hash_value
    from v$sql
    where hash_value = to_number(:s_hash_value)
    and child_number = to_number(:s_child_num)'
    into s_output, s_plan_hash using s_hash_value, s_child_num;
  exception
  when NO_DATA_FOUND then
    null;
  when others then
    dbms_output.put_line('Error getting SQL text from V$SQL, check privileges');
  end;
  
  put_line_smart(s_output);
  dbms_output.put_line(chr(13));
  open c_display_cursor for s_display_cursor using to_number(s_hash_value), to_number(s_child_num);
  
  n_cnt := 1;
  
  fetch c_display_cursor into
    a_column_list(1).a_data(n_cnt), -- a_p_id(n_cnt),
    a_column_list(2).a_data(n_cnt), -- a_operation(n_cnt),
    a_column_list(3).a_data(n_cnt), -- a_name(n_cnt),
    a_column_list(4).a_data(n_cnt), -- a_starts(n_cnt),
    a_column_list(5).a_data(n_cnt), -- a_e_rows(n_cnt),
    a_column_list(6).a_data(n_cnt), -- a_a_rows(n_cnt),
    a_column_list(7).a_data(n_cnt), -- a_a_time(n_cnt),
    a_column_list(8).a_data(n_cnt), -- a_buffers(n_cnt),
    a_column_list(9).a_data(n_cnt), -- a_reads(n_cnt),
    a_column_list(10).a_data(n_cnt), -- a_writes(n_cnt),
    a_column_list(11).a_data(n_cnt), -- a_in_out(n_cnt),
    a_column_list(12).a_data(n_cnt), -- a_partition_start(n_cnt),
    a_column_list(13).a_data(n_cnt), -- a_partition_stop(n_cnt),
    a_column_list(14).a_data(n_cnt), -- a_distribution(n_cnt),
    a_column_list(15).a_data(n_cnt), -- a_last_mem_usage(n_cnt),
    a_column_list(16).a_data(n_cnt), -- a_last_degree(n_cnt),
    a_column_list(17).a_data(n_cnt), -- a_mem_opt(n_cnt),
    a_column_list(18).a_data(n_cnt), -- a_mem_one(n_cnt),
    --a_column_list(17).a_data(n_cnt), -- a_opt_cnt(n_cnt),
    --a_column_list(18).a_data(n_cnt), -- a_one_cnt(n_cnt),
    --a_column_list(19).a_data(n_cnt), -- a_multi_cnt(n_cnt),
    --a_column_list(22).a_data(n_cnt), -- a_max_tmp(n_cnt),
    a_column_list(19).a_data(n_cnt), -- a_last_tmp(n_cnt),
    a_access_pred(n_cnt),
    a_filter_pred(n_cnt),
    a_dyn_sampl(n_cnt),
    a_id_list(n_cnt);
      
  if c_display_cursor%notfound then
     close c_display_cursor;
     --dbms_output.put_line('Debug : Select V$SQL_PLAN');
     b_has_stat := FALSE;
     
     a_column_list(1).s_heading := 'Id'; --a_column_list(1).b_is_number := true;
     a_column_list(2).s_heading := 'Operation';
     a_column_list(3).s_heading := 'Name';
     a_column_list(4).s_heading := 'Rows'; a_column_list(4).b_is_number := true;
     a_column_list(5).s_heading := 'Bytes'; a_column_list(5).b_is_number := true;
     a_column_list(6).s_heading := 'TempSpc'; a_column_list(6).b_is_number := true;
     a_column_list(7).s_heading := 'Cost';a_column_list(7).b_is_number := true;
     a_column_list(8).s_heading := 'Cpu-Cost';a_column_list(8).b_is_number := true;     
     a_column_list(9).s_heading := 'TQ';
     a_column_list(10).s_heading := 'In-Out';
     a_column_list(11).s_heading := 'Pstart'; a_column_list(10).b_is_number := true;
     a_column_list(12).s_heading := 'Pstop'; a_column_list(11).b_is_number := true;
     a_column_list(13).s_heading := 'PQ Distrib';
     
     open c_display_cursor for s_display_cursor2 using to_number(s_hash_value), to_number(s_child_num);
     n_cnt := 0;
  else
     -- The plan statistics
     a_column_list(1).s_heading := 'Id'; --a_column_list(1).b_is_number := true;
     a_column_list(2).s_heading := 'Operation';
     a_column_list(3).s_heading := 'Name';
     a_column_list(4).s_heading := 'Starts'; a_column_list(4).b_is_number := true;
     a_column_list(5).s_heading := 'E-Rows'; a_column_list(5).b_is_number := true;
     a_column_list(6).s_heading := 'A-Rows'; a_column_list(6).b_is_number := true;
     a_column_list(7).s_heading := 'A-Time';
     a_column_list(8).s_heading := 'Buffers'; a_column_list(8).b_is_number := true;
     a_column_list(9).s_heading := 'Reads'; a_column_list(9).b_is_number := true;
     a_column_list(10).s_heading := 'Writes'; a_column_list(10).b_is_number := true;
     a_column_list(11).s_heading := 'In-Out';
     a_column_list(12).s_heading := 'Pstart'; a_column_list(12).b_is_number := true;
     a_column_list(13).s_heading := 'Pstop'; a_column_list(13).b_is_number := true;
     a_column_list(14).s_heading := 'PQ Distrib';
     a_column_list(15).s_heading := 'OMem'; a_column_list(15).b_is_number := true;
     a_column_list(16).s_heading := '1Mem'; a_column_list(16).b_is_number := true;
     a_column_list(17).s_heading := 'Used-Mem'; a_column_list(17).b_is_number := true; --a_column_list(15).s_alignment := 'RIGHT';
     a_column_list(18).s_heading := 'Last-Degree'; a_column_list(18).b_is_number := true;
     --a_column_list(19).s_heading := 'Opt-Cnt'; a_column_list(17).b_is_number := true;
     --a_column_list(20).s_heading := 'One-Cnt'; a_column_list(18).b_is_number := true;
     --a_column_list(21).s_heading := 'Multi-Cnt'; a_column_list(19).b_is_number := true;
     --a_column_list(19).s_heading := 'Max-Tmp'; a_column_list(19).b_is_number := true;
     a_column_list(19).s_heading := 'Last-Tmp'; a_column_list(19).b_is_number := true;
  
     n_cnt := 1;   
  end if;

  loop
    exit when c_display_cursor%notfound;
    n_cnt := n_cnt + 1;
    if b_has_stat then 
        fetch c_display_cursor into
        a_column_list(1).a_data(n_cnt), -- a_p_id(n_cnt),
        a_column_list(2).a_data(n_cnt), -- a_operation(n_cnt),
        a_column_list(3).a_data(n_cnt), -- a_name(n_cnt),
        a_column_list(4).a_data(n_cnt), -- a_starts(n_cnt),
        a_column_list(5).a_data(n_cnt), -- a_e_rows(n_cnt),
        a_column_list(6).a_data(n_cnt), -- a_a_rows(n_cnt),
        a_column_list(7).a_data(n_cnt), -- a_a_time(n_cnt),
        a_column_list(8).a_data(n_cnt), -- a_buffers(n_cnt),
        a_column_list(9).a_data(n_cnt), -- a_reads(n_cnt),
        a_column_list(10).a_data(n_cnt), -- a_writes(n_cnt),
        a_column_list(11).a_data(n_cnt), -- a_in_out(n_cnt),
        a_column_list(12).a_data(n_cnt), -- a_partition_start(n_cnt),
        a_column_list(13).a_data(n_cnt), -- a_partition_stop(n_cnt),
        a_column_list(14).a_data(n_cnt), -- a_distribution(n_cnt),
        a_column_list(15).a_data(n_cnt), -- a_last_mem_usage(n_cnt),
        a_column_list(16).a_data(n_cnt), -- a_last_degree(n_cnt),
        a_column_list(17).a_data(n_cnt), -- a_mem_opt(n_cnt),
        a_column_list(18).a_data(n_cnt), -- a_mem_one(n_cnt),
        a_column_list(19).a_data(n_cnt), -- a_last_tmp(n_cnt),
        a_access_pred(n_cnt),
        a_filter_pred(n_cnt),
        a_dyn_sampl(n_cnt),
        a_id_list(n_cnt);
    else
        fetch c_display_cursor into
        a_column_list(1).a_data(n_cnt), 
        a_column_list(2).a_data(n_cnt), 
        a_column_list(3).a_data(n_cnt), 
        a_column_list(4).a_data(n_cnt), 
        a_column_list(5).a_data(n_cnt), 
        a_column_list(6).a_data(n_cnt), 
        a_column_list(7).a_data(n_cnt), 
        a_column_list(8).a_data(n_cnt), 
        a_column_list(9).a_data(n_cnt), 
        a_column_list(10).a_data(n_cnt),
        a_column_list(11).a_data(n_cnt),
        a_column_list(12).a_data(n_cnt),
        a_column_list(13).a_data(n_cnt),        
        a_access_pred(n_cnt),
        a_filter_pred(n_cnt),
        a_dyn_sampl(n_cnt),
        a_id_list(n_cnt);
     end if;           
  end loop;
  
  close c_display_cursor;
  if a_column_list(1).a_data.count > 0 then
    dbms_output.put_line('Plan hash value: ' || s_plan_hash);
    dbms_output.put_line(chr(13));
    n_row_size := 1;
    for i in a_column_list.first..a_column_list.last loop
      if a_column_list(i).b_is_number then
        if a_column_list(i).a_data.count > 0 then
          for j in a_column_list(i).a_data.first..a_column_list(i).a_data.last loop
            begin
              a_column_list(i).a_data(j) := display_cursor_format_number(a_column_list(i).a_data(j));
            exception
            when others then
              dbms_output.put_line('Column:' || a_column_list(i).s_heading || ' Data: ' || a_column_list(i).a_data(j));
              raise;
            end;
          end loop;
        end if;
      end if;
      -- column size is greatest of max size of content + 2 (leading + trailing blanks) and size of column heading
      a_column_list(i).n_max_size := greatest(get_max_size(a_column_list(i).a_data) + 2, length(a_column_list(i).s_heading) + 2);
      a_column_list(i).b_has_data := not has_collection_only_nulls(a_column_list(i).a_data);
      if a_column_list(i).b_has_data then
        n_row_size := n_row_size + a_column_list(i).n_max_size + 1;
      end if;
    end loop;
    -- Header
    put_line_smart(in_string => lpad('-', n_row_size, '-'), in_line_size => max_line_size);
    s_row := '';
    for i in a_column_list.first..a_column_list.last loop
      if a_column_list(i).b_has_data then
        if a_column_list(i).s_alignment is null then
          if a_column_list(i).b_is_number then
            s_row := s_row || '|' || lpad(a_column_list(i).s_heading, a_column_list(i).n_max_size - 1) || ' ';
          else
            s_row := s_row || '|' || ' ' || rpad(a_column_list(i).s_heading, a_column_list(i).n_max_size - 1);
          end if;
        else
          if a_column_list(i).s_alignment = 'RIGHT' then
            s_row := s_row || '|' || lpad(a_column_list(i).s_heading, a_column_list(i).n_max_size - 1) || ' ';
          else
            s_row := s_row || '|' || ' ' || rpad(a_column_list(i).s_heading, a_column_list(i).n_max_size - 1);
          end if;
        end if;
      end if;
    end loop;
    s_row := s_row || '|';
    put_line_smart(in_string => s_row, in_line_size => max_line_size);
    -- Data
    put_line_smart(in_string => lpad('-', n_row_size, '-'), in_line_size => max_line_size);
    for j in a_column_list(1).a_data.first..a_column_list(1).a_data.last loop
      s_row := '';
      for i in a_column_list.first..a_column_list.last loop
        if a_column_list(i).b_has_data then
          if a_column_list(i).b_is_number then
            s_row := s_row || '|' || lpad(nvl(a_column_list(i).a_data(j), ' '), a_column_list(i).n_max_size - 1) || ' ';
          else
            s_row := s_row || '|' || ' ' || rpad(nvl(a_column_list(i).a_data(j), ' '), a_column_list(i).n_max_size - 1);
          end if;
        end if;
      end loop;
      s_row := s_row || '|';
      put_line_smart(in_string => s_row, in_line_size => max_line_size);
    end loop;
    -- Footer
    put_line_smart(in_string => lpad('-', n_row_size, '-'), in_line_size => max_line_size);
    -- Predicate information
    dbms_output.put_line(chr(13));
    dbms_output.put_line('Predicate Information (identified by operation id):');
    dbms_output.put_line('---------------------------------------------------');
    for j in a_column_list(1).a_data.first..a_column_list(1).a_data.last loop
      if a_access_pred(j) is not null or a_filter_pred(j) is not null then
        s_output := lpad(to_char(to_number(a_id_list(j)), 'FM9999'), 4, ' ') || ' - ';
        if a_access_pred(j) is not null then
          put_line_smart(s_output || 'access(' || a_access_pred(j) || ')', lpad(' ', length(s_output), ' '));
        end if;
        if a_filter_pred(j) is not null then
          if a_access_pred(j) is not null then
            put_line_smart(lpad(' ', length(s_output), ' ') || 'filter(' || a_filter_pred(j) || ')', lpad(' ', length(s_output), ' '));
          else
            put_line_smart(s_output || 'filter(' || a_filter_pred(j) || ')', lpad(' ', length(s_output), ' '));
          end if;
        end if;
      end if;
    end loop;
    --dbms_output.put_line('DEBUG:Begin Notes');
    -- Notes section
    if not a_column_list(4).b_has_data or a_dyn_sampl(1) = 'YES' then
      dbms_output.put_line(chr(13));
      dbms_output.put_line('Note');
      dbms_output.put_line('-----');
    end if;
    if a_dyn_sampl(1) = 'YES' then
      dbms_output.put_line('   - dynamic sampling used for this statement');
    end if;
    if not a_column_list(4).b_has_data then
      dbms_output.put_line('   - Warning: basic plan statistics not available. These are only collected when:');
      dbms_output.put_line('       * parameter ''statistics_level'' is set to ''ALL'', at session or system level');
    end if;
  else
    dbms_output.put_line('SQL information could not be found for HASH_VALUE: ' || s_hash_value || ',CHILD_NUMBER: ' || s_child_num);
    dbms_output.put_line('Please verify value of SQL address, hash_value and child_number;');
    dbms_output.put_line('It could also be that the plan is no longer in cursor cache (check v$sql_plan)');
  end if;
exception
when others then
  dbms_output.put_line(sqlerrm);
  dbms_output.put_line('Error getting plan information from V$SQL and V$SQL_PLAN_STATISTICS_ALL, check privileges.');
  dbms_output.put_line('You need SELECT privileges on V$SQL_PLAN_STATISTICS_ALL and V$SQL.');
end;
/
分享到:
评论

相关推荐

    oracle 9i 实现wm_concat函数

    oracle 9i 实现wm_concat函数 进行一列多行合并,之间使用,分割

    Oracle9i_DBA_OCP学习材料

    很全的Oracle9i_DBA_OCP资料

    Oracle9i+PL_SQL从入门到精通

    Oracle9i是一款由甲骨文公司(Oracle Corporation)推出的数据库管理系统,它在1999年发布,是Oracle数据库系统中的一个重要版本。该系统提供了强大的数据管理能力,广泛应用于企业级应用、数据仓库以及互联网服务等...

    Oracle9i_DBA_JumpStart.chm

    Oracle9i_DBA_JumpStart DBA手册

    oracle9i_centos_installation

    Oracle 9i在CentOS操作系统上的安装教程是一个重要的IT知识点,尤其对于数据库管理员和技术团队来说。这个教程可能包含了从环境准备到安装配置的全过程,帮助用户在Linux环境中部署Oracle数据库系统。 首先,让我们...

    Oracle_9i_AS_Portal_宝典1.pdf

    根据提供的文件信息,标题、描述和标签均指向“Oracle_9i_AS_Portal_宝典1.pdf”,这表明文档主要围绕Oracle 9i AS Portal的相关内容进行介绍。然而,给出的部分内容并未提供具体的技术细节,而是重复了关于电子书籍...

    oracle9i_linux_rac安装文档

    我个是我安装RAC实验环境的完整文档.按此文档安装,一定没问题! oracle9204_for_linux RHAS4.0 vmware gsx server 3.2 windows xp professional环境下做的实验.

    Oracle_9i_AS_Portal_宝典6.pdf

    根据给定的信息,“Oracle_9i_AS_Portal_宝典6.pdf”似乎是一本专注于Oracle 9i AS Portal的专业书籍。尽管提供的具体内容非常有限,但从标题、描述和标签来看,我们可以推断出这本书主要涵盖了Oracle 9i AS Portal...

    [聊天留言]移动留言薄 oracle 9i + jsp_guestbook.rar

    1. 数据库脚本(SQL文件):用于创建Oracle 9i数据库中的留言簿表结构,可能包括用户表、留言表等。 2. JSP源代码:包含前端用户界面的JSP文件,用于输入、显示和管理留言。 3. JavaBeans或Servlets:这些Java组件...

    Oracle_9i_AS_Portal_宝典5.pdf

    Oracle_9i_AS_Portal_宝典5.pdf

    Oracle_9i_AS_Portal_宝典4.pdf

    Oracle_9i_AS_Portal_宝典4.pdf

    oracle_9i_rman_备份与恢复技术

    ### Oracle 9i RMAN 备份与恢复技术详解 #### 一、概述 Oracle Recovery Manager (RMAN) 是一种全面的数据保护工具,用于备份、恢复和灾难恢复Oracle数据库。本篇文章将详细介绍Oracle 9i版本中RMAN的备份与恢复...

    Oracle_9i_AS_Portal_宝典2.pdf

    尽管给定的部分内容并未直接提供关于《Oracle_9i_AS_Portal_宝典2.pdf》的具体信息,但从标题和描述可以推断,这份文档主要聚焦于Oracle 9i AS Portal的相关知识与应用技巧。Oracle 9i AS Portal是Oracle ...

    Oracle9i+PL_SQL从入门到精通.pdf

    本书《Oracle9i+PL_SQL从入门到精通》旨在帮助初学者和有一定基础的读者全面掌握Oracle 9i数据库系统以及PL/SQL编程语言。通过深入学习,读者可以了解到以下关键知识点: 1. **Oracle 9i基础**:了解Oracle数据库的...

    Oracle9i数据库管理讲座.rar_oracle_oracle 9_oracle9i_数据库管理

    这个“Oracle9i数据库管理讲座”资源可能包含了关于如何管理和优化这个数据库系统的详细讲解。下面,我们将深入探讨Oracle 9i的一些核心知识点。 1. **Oracle 9i概述**:Oracle 9i全称为Oracle Database 9.0.x,它...

    ORACLE_9I_FOR_AIX安装

    在本文中,我们将深入探讨如何在IBM AIX操作系统上安装ORACLE 9i。首先,我们需要确保系统环境满足安装要求。主机环境应包括特定的硬件配置,例如IBM M85机型,750MHz*8的CPU,至少12GB的内存(最小512MB),以及...

Global site tag (gtag.js) - Google Analytics