本文共 29976 字，大约阅读时间需要 99 分钟。
/* 打印对应spid或sid所对应的sql以及其执行计划 */CREATE OR REPLACE PROCEDURE PRINTSQL (i_n_id IN NUMBER, i_vc_idtype IN VARCHAR2)IS /* 功能： 打印对应spid或sid所对应的sql以及其执行计划 作者： 老熊，dbsnake 创建日期：2010-11-12 输入参数： i_n_id: 输入的spid或sid i_vc_idtype : 输入的ID的类型，'SPID'表示输入的是spid，'SID'表示输入的是sid. 输出参数： 无 输入输出参数： 无 调用到的存储过程： 无[oracle@txy ~]$ ps -ef | grep 2837oracle 2837 2724 24 16:21 pts/2 00:00:12 sqlplus oracle 2839 2837 0 16:21 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 2896 2866 0 16:22 pts/3 00:00:00 grep 2837set serveroutput on size 1000000--传入 2839 注意传入的是SPID 不是 PIDexec PRINTSQL(2839,'SPID') 其中会吧alter system kill session '138,8628' immediate; 打印出来 */ o_vc_return_flag VARCHAR2 (4000); TYPE typsqltext IS TABLE OF VARCHAR2 (1000) INDEX BY BINARY_INTEGER; typsqltexts typsqltext; vc_paddr VARCHAR2 (4000); n_hashvalue NUMBER; n_childnumber NUMBER; rec_session v$session%ROWTYPE; rec_sessionwait v$session_wait%ROWTYPE; rec_sql v$sql%ROWTYPE; /*select hash_value,child_number,plan_hash_value,executions,buffer_gets,buffer_gets/decode(executions,0,1,executions) gets_per_exec, rows_processed,rows_processed/decode(executions,0,1,executions) rows_per_exec, disk_reads,disk_reads/decode(executions,0,1,executions) reads_per_exec, cpu_time/1000000 cpu_time,cpu_time/decode(executions,0,1,executions)/1000000 cpu_per_exec, ELAPSED_TIME/1000000 ELAPSED_TIME,ELAPSED_TIME/decode(executions,0,1,executions)/1000000 ela_per_exec from v$sql where hash_value=&1 and rownum<=100;*/ TYPE type_hash_value IS TABLE OF v$sql.HASH_VALUE%TYPE INDEX BY BINARY_INTEGER; hash_values type_hash_value; TYPE type_child_number IS TABLE OF v$sql.CHILD_NUMBER%TYPE INDEX BY BINARY_INTEGER; child_numbers type_child_number; TYPE type_plan_hash_value IS TABLE OF v$sql.PLAN_HASH_VALUE%TYPE INDEX BY BINARY_INTEGER; plan_hash_values type_plan_hash_value; TYPE type_execution IS TABLE OF v$sql.EXECUTIONS%TYPE INDEX BY BINARY_INTEGER; executions type_execution; TYPE type_buffer_get IS TABLE OF v$sql.BUFFER_GETS%TYPE INDEX BY BINARY_INTEGER; buffer_gets type_buffer_get; TYPE type_gets_per_exec IS TABLE OF v$sql.BUFFER_GETS%TYPE INDEX BY BINARY_INTEGER; gets_per_execs type_gets_per_exec; TYPE type_rows_processed IS TABLE OF v$sql.ROWS_PROCESSED%TYPE INDEX BY BINARY_INTEGER; rows_processeds type_rows_processed; TYPE type_rows_per_exec IS TABLE OF v$sql.ROWS_PROCESSED%TYPE INDEX BY BINARY_INTEGER; rows_per_execs type_rows_per_exec; TYPE type_disk_read IS TABLE OF v$sql.DISK_READS%TYPE INDEX BY BINARY_INTEGER; disk_reads type_disk_read; TYPE type_reads_per_exec IS TABLE OF v$sql.DISK_READS%TYPE INDEX BY BINARY_INTEGER; reads_per_execs type_reads_per_exec; TYPE type_cpu_time IS TABLE OF v$sql.CPU_TIME%TYPE INDEX BY BINARY_INTEGER; cpu_times type_cpu_time; TYPE type_cpu_per_exec IS TABLE OF v$sql.CPU_TIME%TYPE INDEX BY BINARY_INTEGER; cpu_per_execs type_cpu_per_exec; TYPE type_ELAPSED_TIME IS TABLE OF v$sql.ELAPSED_TIME%TYPE INDEX BY BINARY_INTEGER; ELAPSED_TIMEs type_ELAPSED_TIME; TYPE type_ela_per_exec IS TABLE OF v$sql.ELAPSED_TIME%TYPE INDEX BY BINARY_INTEGER; ela_per_execs type_ela_per_exec; -- 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 (32767); 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 180) 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 DBMS_OUTPUT.put_line ( '--------------------------------------------------------------------------------------'); IF (UPPER (i_vc_idtype) = 'SPID') THEN SELECT addr INTO vc_paddr FROM v$process WHERE spid = TO_CHAR (i_n_id); SELECT * INTO rec_session FROM v$session WHERE paddr = vc_paddr; SELECT * INTO rec_sessionwait FROM v$session_wait WHERE sid = rec_session.SID; SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) INTO n_hashvalue FROM v$session WHERE sid = rec_session.SID; SELECT sql_text BULK COLLECT INTO typsqltexts FROM v$sqltext WHERE hash_value = n_hashvalue ORDER BY piece; IF (typsqltexts.COUNT > 0) THEN FOR i IN typsqltexts.FIRST .. typsqltexts.LAST LOOP ---这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制,并且考虑到了sql语句中可能有中文 LOOP EXIT WHEN typsqltexts (i) IS NULL; DBMS_OUTPUT.put_line (SUBSTRB (typsqltexts (i), 1, 254)); typsqltexts (i) := SUBSTRB (typsqltexts (i), 255); END LOOP; END LOOP; ELSE DBMS_OUTPUT.put_line ( 'The sql text has been aged out from the shared pool.'); END IF; ELSIF (UPPER (i_vc_idtype) = 'SID') THEN SELECT * INTO rec_session FROM v$session WHERE sid = i_n_id; SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) INTO n_hashvalue FROM v$session WHERE sid = i_n_id; SELECT sql_text BULK COLLECT INTO typsqltexts FROM v$sqltext WHERE hash_value = n_hashvalue ORDER BY piece; IF (typsqltexts.COUNT > 0) THEN FOR i IN typsqltexts.FIRST .. typsqltexts.LAST LOOP ---这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制,并且考虑到了sql语句中可能有中文 LOOP EXIT WHEN typsqltexts (i) IS NULL; DBMS_OUTPUT.put_line (SUBSTRB (typsqltexts (i), 1, 254)); typsqltexts (i) := SUBSTRB (typsqltexts (i), 255); END LOOP; END LOOP; ELSE DBMS_OUTPUT.put_line ( 'The sql text has been aged out from the shared pool.'); END IF; ELSE DBMS_OUTPUT.put_line ('invalid input id type parameter!'); RETURN; END IF; DBMS_OUTPUT.put_line ( '--------------------------------------------------------------------------------------'); DBMS_OUTPUT.put_line ('The session id is ' || rec_session.SID); DBMS_OUTPUT.put_line ('The status is ' || rec_session.STATUS); DBMS_OUTPUT.put_line ( 'The sql hash value is ' || rec_session.SQL_HASH_VALUE); --dbms_output.put_line('The child cursor number is ' || rec_session.SQL_CHILD_NUMBER); DBMS_OUTPUT.put_line ( 'The prev hash value is ' || rec_session.PREV_HASH_VALUE); --dbms_output.put_line('The prev child cursor number is ' || rec_session.PREV_CHILD_NUMBER); DBMS_OUTPUT.put_line ('The osuser is ' || rec_session.OSUSER); DBMS_OUTPUT.put_line ('The machine is ' || rec_session.MACHINE); DBMS_OUTPUT.put_line ('The terminal is ' || rec_session.TERMINAL); DBMS_OUTPUT.put_line ('The program is ' || rec_session.PROGRAM); DBMS_OUTPUT.put_line ('The event is ' || rec_sessionwait.EVENT); DBMS_OUTPUT.put_line ( '--------------------------------------------------------------------------------------'); --demo: alter system kill session '417,21188' immediate; DBMS_OUTPUT.put_line ( 'alter system kill session ''' || rec_session.SID || ',' || rec_session.SERIAL# || ''' immediate;'); SELECT hash_value, child_number, plan_hash_value, executions, buffer_gets, buffer_gets / DECODE (executions, 0, 1, executions) gets_per_exec, rows_processed, rows_processed / DECODE (executions, 0, 1, executions) rows_per_exec, disk_reads, disk_reads / DECODE (executions, 0, 1, executions) reads_per_exec, cpu_time / 1000000 cpu_time, cpu_time / DECODE (executions, 0, 1, executions) / 1000000 cpu_per_exec, ELAPSED_TIME / 1000000 ELAPSED_TIME, ELAPSED_TIME / DECODE (executions, 0, 1, executions) / 1000000 ela_per_exec BULK COLLECT INTO hash_values, child_numbers, plan_hash_values, executions, buffer_gets, gets_per_execs, rows_processeds, rows_per_execs, disk_reads, reads_per_execs, cpu_times, cpu_per_execs, ELAPSED_TIMEs, ela_per_execs FROM v$sql WHERE hash_value = n_hashvalue AND ROWNUM <= 100; IF (hash_values.COUNT > 0) THEN FOR i IN hash_values.FIRST .. hash_values.LAST LOOP DBMS_OUTPUT.put_line ( '--------------------------------------------------------------------------------------'); DBMS_OUTPUT.put_line ('The hash_value is ' || hash_values (i)); DBMS_OUTPUT.put_line ('The child_number is ' || child_numbers (i)); DBMS_OUTPUT.put_line ( 'The plan_hash_value is ' || plan_hash_values (i)); DBMS_OUTPUT.put_line ('The execution is ' || executions (i)); DBMS_OUTPUT.put_line ('The buffer_gets is ' || buffer_gets (i)); DBMS_OUTPUT.put_line ('The gets_per_exec is ' || gets_per_execs (i)); DBMS_OUTPUT.put_line ( 'The rows_processed is ' || rows_processeds (i)); DBMS_OUTPUT.put_line ('The rows_per_exec is ' || rows_per_execs (i)); DBMS_OUTPUT.put_line ('The disk_reads is ' || disk_reads (i)); DBMS_OUTPUT.put_line ( 'The reads_per_exec is ' || reads_per_execs (i)); DBMS_OUTPUT.put_line ('The cpu_time is ' || cpu_times (i)); DBMS_OUTPUT.put_line ('The cpu_per_exec is ' || cpu_per_execs (i)); DBMS_OUTPUT.put_line ('The ELAPSED_TIME is ' || ELAPSED_TIMEs (i)); DBMS_OUTPUT.put_line ('The ela_per_exec is ' || ela_per_execs (i)); DBMS_OUTPUT.put_line ( '--------------------------------------------------------------------------------------'); s_hash_value := n_hashvalue; s_child_num := child_numbers (i); -- 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; END LOOP; END IF;EXCEPTION WHEN OTHERS THEN o_vc_return_flag := 'E' || '_' || SQLCODE || '_' || SQLERRM; DBMS_OUTPUT.put_line (o_vc_return_flag); RETURN;END PRINTSQL;/ 转载于:https://www.cnblogs.com/cure-t-x-y/p/4241409.html