使用SQLT來構建Oracle測試用例
在進行效能最佳化尤其是SQL最佳化時不能在生產環境進行測試,或者在升級時對SQL語句效能進行測試時,構建一個測試環境很重要,這篇文章介紹使用SQLT工具來快速簡單的構建測試用例的方法。其實要使用SQLT來構建一個測試用例不需要額外執行SQLT報告。執行SQLTXTRACT或SQLTXECUTE就能為測試用例建立足夠的資訊。
例如我要將在aix系統上Oracle 10g中執行的SQL語句在linux系統上Oracle 11g中來測試它的效能時,這就需要構建一個測試用例。要進行測試的語句如下:
select nvl(sum(real_pay), 0) as dYearA131 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c where a.hospital_id = b.hospital_id and a.serial_no = b.serial_no and a.treatment_type = '131' and a.indi_id = 5609194 and a.serial_apply = 135888 and a.valid_flag = '1' and b.valid_flag = '1' and a.serial_apply = c.serial_apply and c.valid_flag = '1' and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') and exists (select 1 from mt_pay_record_fin b where b.fund_id not in ('003', '999') and a.serial_no = b.serial_no);
為sqltxecute.sql指令碼建立一個來測試的sql檔案
[IBMP740-1:oracle:/oracle/sqlt/input/sample]$vi mysql2.sql -- execute sqlt xecute as sh passing script name -- cd sqlt -- #sqlplus sh -- SQL> start run/sqltxecute.sql input/sample/script1.sql REM Optional ALTER SESSION commands REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --ALTER SESSION SET statistics_level = ALL; REM Optional Binds REM ~~~~~~~~~~~~~~ VAR b1 NUMBER; EXEC :b1 := 10; REM SQL statement to be executed REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT /*+ gather_plan_statistics monitor bind_aware */ /* ^^unique_id */ "mysql2.sql" 35 lines, 756 characters /* ^^unique_id */ nvl(sum(real_pay), 0) as dYearA131 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c where a.hospital_id = b.hospital_id and a.serial_no = b.serial_no and a.treatment_type = '131' and a.indi_id = 5609194 and a.serial_apply = 135888 and a.valid_flag = '1' and b.valid_flag = '1' and a.serial_apply = c.serial_apply and c.valid_flag = '1' and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') and exists (select 1 from mt_pay_record_fin b where b.fund_id not in ('003', '999') and a.serial_no = b.serial_no); / / REM Notes: / / REM Notes: REM 1. SQL must contain token: /* ^^unique_id */ "mysql2.sql" 48 lines, 1259 characters
現在在Oracle 10g中對上面的SQL語句執行sqltxectue.sql來進行效能分析
[IBMP740-1:oracle:/oracle/sqlt/run]$sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 26 09:29:40 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected. SQL> @sqltxecute.sql /oracle/sqlt/input/sample/mysql2.sql PL/SQL procedure successfully completed. Parameter 1: SCRIPT name which contains SQL and its binds (required) Paremeter 2: SQLTXPLAIN password (required) Enter value for 2: sqlt PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Value passed to sqltxecute: SCRIPT_WITH_SQL: "/oracle/sqlt/input/sample/mysql2.sql" PL/SQL procedure successfully completed. *** *** NOTE: *** If you get error below it means SQLTXPLAIN is not installed: *** PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared. *** In such case look for errors in NN_*.log files created during install. *** SQLT_VERSION ---------------------------------------- SQLT version number: 12.1.09 SQLT version date : 2014-06-13 Installation date : 2014-07-29/15:45:43 ... please wait ... adding: alert_RLZY.log (deflated 93%) NOTE: You used the XECUTE method connected as INSUR_CHANGDE. In case of a session disconnect please verify the following: 1. There are no errors in sqltxecute.log or sqltxecute2.log. 2. Your SQL contains token "^^unique_id" within a comment. 3. Your SQL ends with a semi-colon ";". 4. You connected as the application user that issued original SQL. 5. Script /oracle/sqlt/input/sample/mysql2.sql can execute stand-alone connected as INSUR_CHANGDE 6. User INSUR_CHANGDE has been granted SQLT_USER_ROLE. In case of errors ORA-03113, ORA-03114 or ORA-07445 please just re-try this SQLT method. This tool handles some of the errors behind a disconnect when executed a second time. To actually diagnose the problem behind the disconnect, read ALERT log and provide referenced traces to Support. After the root cause of the disconnect is fixed then reset SQLT corresponding parameter. ... executing /oracle/sqlt/input/sample/mysql2.sql ... In case of a disconnect review sqltxecute2.log and mysql2_output_s34879.txt To monitor progress, login into another session and execute: SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v; ... collecting diagnostics details, please wait ... In case of a disconnect review log file in current directory If running as SYS in 12c make sure to review sqlt_instructions.html first 09:30:47 17 sqlt$a: ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF' 09:30:47 0 sqlt$a: ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8' 09:30:47 0 sqlt$a: skip 10241, 10032, 10033, 10104, 10730, 46049 off as per parameter event_others 09:31:18 31 sqlt$d: collect_gv$sesstat 09:31:18 0 sqlt$d: 760 rows collected 09:31:18 0 sqlt$d: collect_gv$cell_state 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$session_event 09:31:18 0 sqlt$d: 19 rows collected 09:31:18 0 sqlt$d: collect_gv$segment_statistics 09:31:18 0 sqlt$d: 510 rows collected 09:31:18 0 sqlt$d: collect_gv$statname 09:31:18 0 sqlt$d: 380 rows collected 09:31:18 0 sqlt$d: collect_gv$pq_slave 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$pq_sysstat 09:31:18 0 sqlt$d: 32 rows collected 09:31:18 0 sqlt$d: collect_gv$px_process_sysstat 09:31:18 0 sqlt$d: 30 rows collected 09:31:18 0 sqlt$d: collect_gv$px_process 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$px_session 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$pq_sesstat 09:31:18 0 sqlt$d: 22 rows collected 09:31:18 0 sqlt$d: collect_gv$px_sesstat 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$px_instance_group 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: collect_gv$pq_tqstat 09:31:18 0 sqlt$d: 0 rows collected 09:31:18 0 sqlt$d: < = collect_perf_stats_post 09:31:18 0 sqlt$a: ALTER SESSION SET STATISTICS_LEVEL = 'TYPICAL' 09:31:18 0 sqlt$a: -> session_trace_filename 09:31:18 0 sqlt$a: session_trace_filename, current trace_filename 09:31:18 0 sqlt$a: session_trace_filename, current process_name ora 09:31:18 0 sqlt$a: < - session_trace_filename rlzy_ora_3081178_s34879_10046_10053.trc 09:31:18 0 sqlt$a: in udump: "rlzy_ora_3081178_s34879_10046_10053.trc" 09:31:18 0 sqlt$a: in local: "sqlt_s34879_10046_10053_execute.trc" 09:31:18 0 sqlt$a: ALTER SESSION SET TRACEFILE_IDENTIFIER = "S34879_SQLT_TRACE" 09:31:18 0 sqlt$a: timestamp_before: 26-AUG-14 09.30.30.240238 AM +08:00 09:31:18 0 sqlt$a: timestamp_after: 26-AUG-14 09.30.47.115196 AM +08:00 09:31:18 0 sqlt$a: statement_response_time: +00 00:00:16.874958 09:31:18 0 sqlt$a: <= event_10046_10053_off 09:31:18 0 sqlt$d: => capture_sqltext 09:31:18 0 sqlt$d: -> capture_statement 09:31:18 0 sqlt$a: -> find_sql_in_memory_or_awr 09:31:18 0 sqlt$a: -> get_sql_id_or_hash_value 09:31:18 0 sqlt$a: sql_id = "7fv05tum90t4j" 09:31:18 0 sqlt$a: < - get_sql_id_or_hash_value 09:31:18 0 sqlt$a: -> sql_in_memory_or_awr 09:31:18 0 sqlt$a: sql found in memory using sql_id = "7fv05tum90t4j" and string = "sqlt_s34879" (excludes PL/SQL EXECUTE) 09:31:18 0 sqlt$a: hash_value = "2794480785" 09:31:18 0 sqlt$a: SELECT /*+ gather_plan_statistics monitor bind_aware */ /* sqlt_s34879 */ nvl(sum(real_pay), 0) as dYearA131 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c where a.hospital_id = b.hospital_id and a.serial_no = b.seri al_no and a.treatment_type = '131' and a.indi_id = 5609194 and a.serial_apply = 135888 and a.valid_flag = '1' and b.valid_flag = '1' and a.serial_apply = c.serial_apply and c.valid_flag = '1' and b.POLICY_ITEM_CODE in ('C000', 'C00 1', 'C004') and exists (select 1 from mt_pay_record_fin b where b.fund_id not in ('003', '999') and a.serial_no = b.serial_no) 09:31:18 0 sqlt$a: sql not found in awr using sql_id = "7fv05tum90t4j" 09:31:18 0 sqlt$a: < - sql_in_memory_or_awr 09:31:18 0 sqlt$a: explain_plan_and_10053 09:31:19 0 sqlt$i: sql_length = "631" 09:31:19 0 sqlt$i: => remote_trace_begin 09:31:19 0 sqlt$i: no remote db_links were found/activated at this step 09:31:19 0 sqlt$i: < = remote_trace_begin 09:31:19 0 sqlt$a: -> event_10053_on 09:31:19 0 sqlt$a: ALTER SESSION SET MAX_DUMP_FILE_SIZE = '200M' 09:31:19 0 sqlt$a: ALTER SESSION SET TRACEFILE_IDENTIFIER = "s34879_10053" 09:31:19 0 sqlt$a: ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1' 09:31:19 0 sqlt$a: ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF' 09:31:19 0 sqlt$a: -> session_trace_filename 09:31:19 0 sqlt$a: session_trace_filename, current trace_filename 09:31:19 0 sqlt$a: session_trace_filename, current process_name ora 09:31:19 0 sqlt$a: < - session_trace_filename rlzy_ora_3081178_s34879_10053.trc 09:31:19 0 sqlt$a: in udump: "rlzy_ora_3081178_s34879_10053.trc" 09:31:19 0 sqlt$a: in local: "sqlt_s34879_10053_explain.trc" 09:31:19 0 sqlt$a: ALTER SESSION SET TRACEFILE_IDENTIFIER = "S34879_SQLT_TRACE" 09:31:19 0 sqlt$a: clean_sqlt$_sql_plan_table 09:31:19 0 sqlt$a: < - clean_sqlt$_sql_plan_table 09:31:19 0 sqlt$d: -> capture_xplain_plan_hash_value 09:31:19 0 sqlt$d: < - capture_xplain_plan_hash_value "2582817425" for xplain_sql_id "6kxjb1zyt8qdt" with cost of "83997" 09:31:19 0 sqlt$i: <= explain_plan_and_10053 09:31:19 0 sqlt$i: => remote_trace_end 09:31:19 0 sqlt$i: < = remote_trace_end 09:31:19 0 sqlt$d: collect_gv$parameter_cbo 09:31:19 0 sqlt$d: 190 rows collected 09:31:19 0 sqlt$a: -> reset_init_parameters 09:31:19 0 sqlt$a: optimizer_index_cost_adj was "20" 09:31:19 0 sqlt$a: ALTER SESSION SET optimizer_index_cost_adj = 100 09:31:19 0 sqlt$a: < - reset_init_parameters 09:31:19 0 sqlt$d: => diagnostics_data_collection_1 09:31:19 0 sqlt$d: -> collection from memory 09:31:19 0 sqlt$d: sql_id = "7fv05tum90t4j", hash_value = "2794480785" 09:31:19 0 sqlt$d: collect_gv$sql_shared_cursor 09:31:20 1 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$sql_bind_capture 09:31:20 0 sqlt$d: 0 rows collected 09:31:20 0 sqlt$d: collect_gv$sql_optimizer_env 09:31:20 0 sqlt$d: 1342040 KB -> 1374248960 09:31:20 0 sqlt$d: 5 rows collected 09:31:20 0 sqlt$d: collect_gv$sql_workarea 09:31:20 0 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$sql_plan_statistics 09:31:20 0 sqlt$d: 11 rows collected 09:31:20 0 sqlt$d: collect_gv$sql_plan 09:31:20 0 sqlt$d: include predicates on plan table (lock). 09:31:20 0 sqlt$d: included predicates on plan table (unlock). 09:31:20 0 sqlt$d: 12 rows collected 09:31:20 0 sqlt$d: collect_gv$sql 09:31:20 0 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$sqlarea_plan_hash 09:31:20 0 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$sqlarea 09:31:20 0 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$sqlstats 09:31:20 0 sqlt$d: 1 rows collected 09:31:20 0 sqlt$d: collect_gv$object_dependency 09:31:20 0 sqlt$d: sql_id="7fv05tum90t4j" 09:31:20 0 sqlt$d: hash_value="2794480785" 09:31:20 0 sqlt$d: xplain_sql_id="6kxjb1zyt8qdt" 09:31:20 0 sqlt$d: root inst_id="1", hash_value="2794480785", address="0700000438927940", depth="0" 09:31:20 0 sqlt$d: root inst_id="1", hash_value="4254357945", address="0700000671675E40", depth="0" 09:31:20 0 sqlt$d: 3 rows collected 09:31:20 0 sqlt$d: collect_gv$vpd_policy 09:31:21 1 sqlt$d: 0 rows collected 09:31:21 0 sqlt$d: collect_gv$active_session_hist 09:31:23 2 sqlt$d: 16 rows collected 09:31:23 0 sqlt$d: collect_dbms_xplan_cursor_last 09:31:24 1 sqlt$d: 0 rows collected 09:31:24 0 sqlt$d: collect_dbms_xplan_cursor_all 09:31:24 0 sqlt$d: 0 rows collected 09:31:24 0 sqlt$d: -> create_tuning_task_memory 09:31:24 0 sqlt$d: task name = "sqlt_s34879_mem" 09:31:24 0 sqlt$d: < - create_tuning_task_memory 09:31:24 0 sqlt$d: -> create_tuning_task_text 09:31:24 0 sqlt$d: parsing_schema_name:INSUR_CHANGDE 09:31:25 1 sqlt$d: task name = "sqlt_s34879_mem" 09:31:25 0 sqlt$d: < - create_tuning_task_text 09:31:25 0 sqlt$d: -> collect_tuning_sets_mem 09:31:25 0 sqlt$d: sqlt$_gv$sql_plan: 2582817425 09:31:25 0 sqlt$d: created sqlset: s34879_2582817425_mem 09:31:26 1 sqlt$d: loaded sqlset: s34879_2582817425_mem 09:31:26 0 sqlt$d: < - collect_tuning_sets_mem 09:31:26 0 sqlt$d: diagnostics_data_collection_2 09:31:26 0 sqlt$d: sql_id = "7fv05tum90t4j" 09:31:26 0 sqlt$d: in_awr = "N" 09:31:26 0 sqlt$d: s_db_link = "" 09:31:26 0 sqlt$d: collect_dba_hist_snapshot 09:31:26 0 sqlt$d: 179 rows collected 09:31:26 0 sqlt$d: collect_dba_hist_parameter 09:31:28 2 sqlt$d: 47793 rows collected 09:31:28 0 sqlt$d: collect_dba_hist_parameter_m 09:31:28 0 sqlt$d: 88 rows collected 09:31:28 0 sqlt$d: -> expanded collection 09:31:28 0 sqlt$d: collect_gv$parameter2 09:31:28 0 sqlt$d: 263 rows collected 09:31:28 0 sqlt$d: collect_gv$nls_parameters 09:31:28 0 sqlt$d: 19 rows collected 09:31:28 0 sqlt$d: collect_gv$system_parameter 09:31:28 0 sqlt$d: 261 rows collected 09:31:28 0 sqlt$d: collect_nls_database_params 09:31:28 0 sqlt$d: 20 rows collected 09:31:28 0 sqlt$d: collect_v$session_fix_control 09:31:28 0 sqlt$d: 115 rows collected 09:31:28 0 sqlt$d: collect_dba_dependencies 09:31:28 0 sqlt$d: 0 rows collected 09:31:28 0 sqlt$d: list_of_objects 09:31:28 0 sqlt$d: preliminary_list_of_tables 09:31:28 0 sqlt$d: preliminary_list_of_indexes 09:31:28 0 sqlt$d: final_list_of_tables 09:31:30 2 sqlt$d: list_of_fixed_objects 09:31:30 0 sqlt$d: final_list_of_indexes 09:31:30 0 sqlt$d: list_of_other_objects 09:31:30 0 sqlt$d: final_list_of_other_objects 09:31:35 5 sqlt$d: 20 rows collected 09:31:35 0 sqlt$d: collect_schema_object_stats 09:31:35 0 sqlt$d: export table stats 09:31:35 0 sqlt$d: 4762 rows collected 09:31:35 0 sqlt$d: export dictionary stats 09:31:35 0 sqlt$d: dictionary objects stats export skipped because of export_dict_stats param 09:31:35 0 sqlt$d: export fixed objects stats 09:31:35 0 sqlt$d: 0 rows collected 09:31:35 0 sqlt$d: collect_dba_tables 09:31:35 0 sqlt$d: 3 rows collected 09:31:35 0 sqlt$d: collect_dba_object_tables 09:31:35 0 sqlt$d: 0 rows collected 09:31:35 0 sqlt$d: collect_dba_nested_tables 09:31:41 6 sqlt$d: 0 rows collected 09:31:41 0 sqlt$d: collect_dba_tab_statistics 09:31:41 0 sqlt$d: 3 rows collected 09:31:41 0 sqlt$d: collect_dba_tab_modifications 09:31:41 0 sqlt$d: 3 rows collected 09:31:41 0 sqlt$d: collect_dba_tab_cols 09:31:41 0 sqlt$d: 190 rows collected 09:31:41 0 sqlt$d: collect_dba_nested_table_cols 09:31:41 0 sqlt$d: 0 rows collected 09:31:41 0 sqlt$d: collect_dba_indexes 09:31:41 0 sqlt$d: 17 rows collected 09:31:41 0 sqlt$d: collect_dba_ind_statistics 09:31:41 0 sqlt$d: 17 rows collected 09:31:41 0 sqlt$d: collect_dba_ind_columns 09:31:41 0 sqlt$d: 39 rows collected 09:31:41 0 sqlt$d: collect_dba_ind_expressions 09:31:41 0 sqlt$d: 0 rows collected 09:31:41 0 sqlt$d: collect_dba_tab_histograms 09:31:42 1 sqlt$d: 4825 rows collected 09:31:42 0 sqlt$d: collect_dba_constraints 09:31:42 0 sqlt$d: 50 rows collected 09:31:42 0 sqlt$d: Skipped gv$im_segments since version is older than 12.1.0.2 or parameter c_inmemory is set to N 09:31:42 0 sqlt$d: Skipped gv$im_column_level since version is older than 12.1.0.2 or parameter c_inmemory is set to N 09:31:42 0 sqlt$d: collect_dba_segments GLOBAL 09:31:51 9 sqlt$d: 20 rows collected 09:31:51 0 sqlt$d: collect_dba_tablespaces 09:31:52 1 sqlt$d: 8 rows collected 09:31:52 0 sqlt$d: collect_dba_objects GLOBAL 09:31:52 0 sqlt$d: 20 rows collected 09:31:52 0 sqlt$d: collect_dba_source 09:31:52 0 sqlt$d: 0 rows collected 09:31:52 0 sqlt$d: collect_dba_col_usage$ 09:31:52 0 sqlt$d: 93 rows collected 09:31:52 0 sqlt$d: collect_dba_policies 09:31:52 0 sqlt$d: 0 rows collected 09:31:52 0 sqlt$d: collect_dba_audit_policies 09:31:52 0 sqlt$d: 0 rows collected 09:31:52 0 sqlt$d: capture up to "31" days of CBO statistics versions as per parameter c_cbo_stats_vers_days 09:31:52 0 sqlt$d: collect_dba_optstat_operations 09:31:52 0 sqlt$d: 29 rows collected 09:31:52 0 sqlt$d: collect_dba_tab_stats_versn GLOBAL 09:31:52 0 sqlt$d: 1 rows collected 09:31:52 0 sqlt$d: collect_dba_ind_stats_versn GLOBAL 09:31:52 0 sqlt$d: 9 rows collected 09:31:52 0 sqlt$d: collect_dba_col_stats_versn GLOBAL 09:31:53 1 sqlt$d: 78 rows collected 09:31:53 0 sqlt$d: collect_dba_histgrm_stats_ver GLOBAL 09:31:59 6 sqlt$d: 2758 rows collected 09:31:59 0 sqlt$d: collect_wri$_optstat_aux_hist 09:31:59 0 sqlt$d: 0 rows collected 09:31:59 0 sqlt$d: collect_aux_stats$ 09:31:59 0 sqlt$d: 13 rows collected 09:31:59 0 sqlt$d: collect_dbms_xplan_display 09:32:00 1 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_dba_outlines 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_dba_outline_hints 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_wri$_adv_tasks 09:32:00 0 sqlt$d: 2 rows collected 09:32:00 0 sqlt$d: collect_wri$_adv_rationale 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_dba_sqltune_plans 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_dba_sql_profiles 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: -> collect_dbms_stats_glob_prefs 09:32:00 0 sqlt$d: < - collect_dbms_stats_glob_prefs 09:32:00 0 sqlt$d: collect_sqlprof$attr 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_sqlprof$ 09:32:00 0 sqlt$d: 0 rows collected 09:32:00 0 sqlt$d: collect_dba_scheduler_jobs 09:32:00 0 sqlt$d: 1 rows collected 09:32:00 0 sqlt$d: one_plan_per_hv_mem 09:32:00 0 sqlt$d: 1 selected plans on sqlt$_gv$sql 09:32:00 0 sqlt$d: one_plan_per_hv_sta 09:32:00 0 sqlt$d: 0 selected plans on sqlt$_dba_sqltune_plans 09:32:00 0 sqlt$d: collect_plan_extensions 09:32:00 0 sqlt$d: 24 rows collected 09:32:00 0 sqlt$d: sql_tuning_advisor 09:32:01 1 sqlt$i: max_plan_et_secs:5.404 sta_time_limit_secs:1800 09:32:01 0 sqlt$i: -> SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sqlt_s34879_mem') 09:36:56 295 sqlt$i: < - SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sqlt_s34879_mem') 09:36:56 0 sqlt$i: -> SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sqlt_s34879_txt') 09:38:05 69 sqlt$i: < - SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sqlt_s34879_txt') 09:38:05 0 sqlt$a: -> upload_sta_files 09:38:05 0 sqlt$a: "sqlt_s34879_sta_report_mem.txt" was uploaded to repository 09:38:05 0 sqlt$a: "sqlt_s34879_sta_script_mem.sql" was uploaded to repository 09:38:05 0 sqlt$a: "sqlt_s34879_sta_report_txt.txt" was uploaded to repository 09:38:05 0 sqlt$a: "sqlt_s34879_sta_script_txt.sql" was uploaded to repository 09:38:05 0 sqlt$a: < - upload_sta_files 09:38:05 0 sqlt$i: <= sql_tuning_advisor 09:38:05 0 sqlt$i: => test_case_builder 09:38:05 0 sqlt$i: skip test_case_builder since rdbms_release does not provide it 09:38:05 0 sqlt$r: -> tcb_driver 09:38:05 0 sqlt$r: generated sqlt_s34879_tcb_driver.sql 09:38:05 0 sqlt$r: < - tcb_driver 09:38:05 0 sqlt$i: <= test_case_builder 09:38:05 0 sqlt$i: => ebs_application_specific 09:38:05 0 sqlt$i: this is not an EBS application 09:38:05 0 sqlt$i: skip "bde_chk_cbo" since this is not an EBS application 09:38:05 0 sqlt$i: < = ebs_application_specific 09:38:05 0 sqlt$i: => siebel_application_specific 09:38:05 0 sqlt$i: < = siebel_application_specific 09:38:05 0 sqlt$i: => psft_application_specific 09:38:05 0 sqlt$i: < = psft_application_specific 09:38:05 0 sqlt$i: => collect_metadata 09:38:05 0 sqlt$i: -> collect_metadata_objects transformed = "N" 09:38:08 3 sqlt$i: < - collect_metadata_objects transformed = "N" 09:38:08 0 sqlt$i: -> collect_metadata_objects transformed = "Y" 09:38:09 1 sqlt$i: < - collect_metadata_objects transformed = "Y" 09:38:09 0 sqlt$i: -> collect_metadata_constraints transformed = "Y" 09:38:11 2 sqlt$i: < - collect_metadata_constraints transformed = "Y" 09:38:11 0 sqlt$i: <= collect_metadata 09:38:11 0 sqlt$i: => compute_full_table_scan_cost 09:38:11 0 sqlt$i: < = compute_full_table_scan_cost 09:38:11 0 sqlt$i: => perform_count_star 09:38:11 0 sqlt$i: num_rows=119951 sql=SELECT /*+ FULL(t) PARALLEL */ COUNT(*) * 1e2 FROM "INSUR_CHANGDE"."MT_APPLY" SAMPLE (1) t 09:38:11 0 sqlt$i: 121500 rows counted 09:38:11 0 sqlt$i: num_rows=14014679 sql=SELECT /*+ FULL(t) PARALLEL */ COUNT(*) * 1e4 FROM "INSUR_CHANGDE"."MT_BIZ_FIN" SAMPLE (.01) t 09:38:15 4 sqlt$i: 13860000 rows counted 09:38:15 0 sqlt$i: num_rows=35518818 sql=SELECT /*+ FULL(t) PARALLEL */ COUNT(*) * 1e4 FROM "INSUR_CHANGDE"."MT_PAY_RECORD_FIN" SAMPLE (.01) t 09:38:15 0 sqlt$i: 38670000 rows counted 09:38:15 0 sqlt$i: < = perform_count_star 09:38:15 0 sqlt$i: collect_dbms_space 09:38:15 0 sqlt$i: collect_dbms_space.tables 09:38:15 0 sqlt$i: DBMS_SPACE.CREATE_TABLE_COST tablespace_name:HYGEIA avg_row_size:291 row_count:121500 pct_free:10 09:38:16 1 sqlt$i: DBMS_SPACE.CREATE_TABLE_COST tablespace_name:HYGEIA avg_row_size:292 row_count:13860000 pct_free:10 09:38:16 0 sqlt$i: DBMS_SPACE.CREATE_TABLE_COST tablespace_name:HYGEIA avg_row_size:70 row_count:38670000 pct_free:10 09:38:16 0 sqlt$i: collect_dbms_space.indexes 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX$$_429C0002 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX$$_429C0001 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_END_DATE 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_INDI_ID 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_APPLY_INDI 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_FIN_DATE_01 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_APPLY_SERIAL 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:INDI_MT_BIZ_FIN_H_F 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_PAY_RECORD_FIN_2 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_PAY_RECORD_FIN_1 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:PK_MT_PAY_RECORD_FIN 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:PK_MT_BIZ_FIN 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:PK_MT_APPLY 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_FIN_DATE 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_BEGIN_DATE 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_BIZ_FIN_1 09:38:16 0 sqlt$i: SYS.DBMS_SPACE.CREATE_INDEX_COST index_name:IDX_MT_APPLY_INPUT 09:38:16 0 sqlt$t: => perm_transformation 09:38:16 0 sqlt$t: put_statid_into_plan_table 09:38:16 0 sqlt$t: 12 rows updated 09:38:16 0 sqlt$t: put_obj_id_into_tables 09:38:16 0 sqlt$t: 3 rows updated 09:38:16 0 sqlt$t: put_obj_id_into_indexes 09:38:16 0 sqlt$t: 17 rows updated 09:38:16 0 sqlt$t: record_cbo_system_stats 09:38:16 0 sqlt$t: remap_metadata 09:38:17 1 sqlt$t: 70 metadata rows remapped in sqlt$_metadata 09:38:17 0 sqlt$t: table_histograms 09:38:17 0 sqlt$t: partition_histograms 09:38:17 0 sqlt$t: subpartition_histograms 09:38:17 0 sqlt$t: cook_low_and_high_values 09:38:17 0 sqlt$t: 170 rows updated in sqlt$_dba_all_table_cols_v 09:38:17 0 sqlt$t: 0 rows updated in sqlt$_dba_part_col_statistics 09:38:17 0 sqlt$t: 0 rows updated in sqlt$_dba_subpart_col_stats 09:38:17 0 sqlt$t: 78 rows updated in sqlt$_dba_col_stats_versions 09:38:17 0 sqlt$t: compute_mutating_ndv 09:38:17 0 sqlt$t: 0 rows updated with mutating_ndv 09:38:17 0 sqlt$t: compute_endpoints_count 09:38:17 0 sqlt$t: 35 rows updated in sqlt$_dba_histgrm_stats_versn with endpoints_count > 0 09:38:17 0 sqlt$t: compute_mutating_endpoints 09:38:17 0 sqlt$t: 0 rows updated with mutating_endpoints 09:38:17 0 sqlt$t: compute_mutating_num_rows 09:38:17 0 sqlt$t: 0 rows updated with mutating_num_rows 09:38:17 0 sqlt$t: compute_mutating_blevel 09:38:17 0 sqlt$t: 0 rows updated with mutating_blevel 09:38:17 0 sqlt$t: index_in_plan 09:38:17 0 sqlt$t: 3 indexes in plan 09:38:17 0 sqlt$t: column_in_indexes 09:38:17 0 sqlt$t: 30 columns in indexes 09:38:17 0 sqlt$t: at_least_1_notnull_col 09:38:18 1 sqlt$t: 14 indexes updated 09:38:18 0 sqlt$t: skip add_column_default 09:38:18 0 sqlt$t: not_shared_cursors 09:38:18 0 sqlt$t: 0 rows inserted into sqlg$_sql_shared_cursor_n 09:38:18 0 sqlt$t: 0 rows inserted into sqlt$_sql_shared_cursor_d 09:38:18 0 sqlt$t: flag_dba_hist_sqlstat 09:38:18 0 sqlt$t: 0 rows flagged in in_plan_extension 09:38:18 0 sqlt$t: 0 plans in in_plan_summary_v 09:38:18 0 sqlt$t: best_and_worst_plans 09:38:18 0 sqlt$t: fix_cardinality_line_0 09:38:18 0 sqlt$t: 1 plans fixed 09:38:18 0 sqlt$t: execution_order 09:38:18 0 sqlt$t: real_depth 09:38:18 0 sqlt$t: real_depth_m 09:38:18 0 sqlt$t: process_other_xml 09:38:18 0 sqlt$t: 0 binds were processed out of other_xml columns 09:38:18 0 sqlt$t: 12 info rows were processed out of other_xml columns 09:38:18 0 sqlt$t: 68 hints were processed out of other_xml columns 09:38:18 0 sqlt$t: plan_operation 09:38:18 0 sqlt$t: top_cost 09:38:18 0 sqlt$t: top_last_cr_buffer_gets 09:38:18 0 sqlt$t: top_cr_buffer_gets 09:38:18 0 sqlt$t: top_last_cu_buffer_gets 09:38:18 0 sqlt$t: top_cu_buffer_gets 09:38:18 0 sqlt$t: top_last_disk_reads 09:38:18 0 sqlt$t: top_disk_reads 09:38:18 0 sqlt$t: top_last_disk_writes 09:38:18 0 sqlt$t: top_disk_writes 09:38:18 0 sqlt$t: top_last_elapsed_time 09:38:18 0 sqlt$t: top_elapsed_time 09:38:18 0 sqlt$t: build_plan_more_html_table 09:38:19 1 sqlt$t: 22 plan more html tables built 09:38:19 0 sqlt$t: build_workarea_html_table 09:38:19 0 sqlt$t: 1 work area html tables built 09:38:19 0 sqlt$t: build_workarea_html_table 09:38:19 0 sqlt$t: 14 go to html tables built 09:38:19 0 sqlt$t: sanitize_reason 09:38:19 0 sqlt$t: 0 reason columns have been sanitized 09:38:19 0 sqlt$t: sanitize_dir_notes 09:38:19 0 sqlt$t: 0 directives notes columns have been sanitized 09:38:19 0 sqlt$t: sanitize_other_xml 09:38:19 0 sqlt$t: 3 other_xml columns have been sanitized 09:38:19 0 sqlt$t: extend_peeked_binds 09:38:19 0 sqlt$t: 0 peeked_binds were extended 09:38:19 0 sqlt$t: extend_gv$sql_bind_capture 09:38:19 0 sqlt$t: 0 captured_binds were extended with phv 09:38:19 0 sqlt$t: 0 captured_binds were extended with value 09:38:19 0 sqlt$t: extend_gv$sql_optimizer_env 09:38:19 0 sqlt$t: 5 optimizer_env were extended 09:38:19 0 sqlt$t: extend_dba_hist_sqlbind 09:38:19 0 sqlt$t: 0 captured_binds were extended with phv 09:38:19 0 sqlt$t: 0 captured_binds were extended with value 09:38:19 0 sqlt$t: binds_in_predicates 09:38:19 0 sqlt$t: 0 binds in predicates 09:38:19 0 sqlt$t: column_in_predicates 09:38:19 0 sqlt$t: 18 columns in predicates 09:38:19 0 sqlt$t: column_in_projection 09:38:19 0 sqlt$t: 10 columns in projection 09:38:19 0 sqlt$t: extend_gv$sql_monitor 09:38:19 0 sqlt$t: 0 sql_monitor rows were extended 09:38:19 0 sqlt$t: extend_sqlt$_aux_stats$ 09:38:19 0 sqlt$t: compute_index_range_scan_cost 09:38:19 0 sqlt$t: compute_index_leaf_estimate 09:38:19 0 sqlt$t: new_11g_ndv_algorithm_used 09:38:19 0 sqlt$t: skip col_group_usage_report 09:38:19 0 sqlt$t: add_dv_censored 09:38:19 0 sqlt$t: < = perm_transformation 09:38:19 0 sqlt$r: -> sql_monitor_reports 09:38:19 0 sqlt$r: skip "sql_monitor_reports" since not available in "10.2.0.4.0" 09:38:19 0 sqlt$r: < - sql_monitor_reports 09:38:19 0 sqlt$r: -> sql_detail_report 09:38:19 0 sqlt$r: skip "sql_detail_report" since not available in "10.2.0.4.0" 09:38:19 0 sqlt$r: < - sql_detail_report 09:38:19 0 sqlt$r: -> awrrpt_driver 09:38:19 0 sqlt$r: generated sqlt_s34879_awrrpt_driver.sql 09:38:19 0 sqlt$r: < - awrrpt_driver 09:38:19 0 sqlt$r: -> addmrpt_driver 09:38:19 0 sqlt$r: generated sqlt_s34879_addmrpt_driver.sql 09:38:19 0 sqlt$r: < - addmrpt_driver 09:38:19 0 sqlt$r: -> ashrpt_driver 09:38:19 0 sqlt$r: generated sqlt_s34879_ashrpt_driver.sql 09:38:19 0 sqlt$r: < - ashrpt_driver 09:38:19 0 sqlt$r: -> xpand_sql_driver 09:38:19 0 sqlt$r: generated sqlt_s34879_xpand_sql_driver.sql 09:38:19 0 sqlt$r: < - xpand_sql_driver 09:38:19 0 sqlt$r: -> script_output_driver 09:38:19 0 sqlt$r: generated sqlt_s34879_script_output_driver.sql 09:38:19 0 sqlt$r: < - script_output_driver 09:38:19 0 sqlt$r: -> tkprof_px_driver 09:38:19 0 sqlt$a: -> session_trace_filename 09:38:19 0 sqlt$a: session_trace_filename, current trace_filename 09:38:19 0 sqlt$a: session_trace_filename, current process_name ora 09:38:19 0 sqlt$a: < - session_trace_filename rlzy_ora_3081178_s34879_10046_10053.trc 09:38:19 0 sqlt$r: trace rlzy_ora_3081178_s34879_10046_10053.trc exists in /oracle/admin/RLZY/udump/ 09:38:19 0 sqlt$r: px files were not found 09:38:19 0 sqlt$r: generated sqlt_s34879_tkprof_px_driver.sql 09:38:19 0 sqlt$r: call_trace_analyzer_px 09:38:19 0 sqlt$i: skip "trace_analyzer_px" since there are no accesible PX traces 09:38:19 0 sqlt$i: < = call_trace_analyzer_px 09:38:19 0 sqlt$r: -> export_parfile 09:38:20 1 sqlt$r: generated sqlt_s34879_export_parfile.txt 09:38:20 0 sqlt$r: < - export_parfile 09:38:20 0 sqlt$r: -> export_parfile2 09:38:20 0 sqlt$r: generated sqlt_s34879_export_parfile2.txt 09:38:20 0 sqlt$r: < - export_parfile2 09:38:20 0 sqlt$r: -> export_driver 09:38:20 0 sqlt$r: generated sqlt_s34879_export_driver.sql 09:38:20 0 sqlt$r: < - export_driver 09:38:20 0 sqlt$r: -> import_script 09:38:20 0 sqlt$r: generated sqlt_s34879_import.sh 09:38:20 0 sqlt$r: < - import_script 09:38:20 0 sqlt$r: -> metadata_script 09:38:20 0 sqlt$r: < - metadata_script 09:38:20 0 sqlt$r: -> metadata_script1 09:38:20 0 sqlt$r: < - metadata_script1 09:38:20 0 sqlt$r: -> metadata_script2 09:38:20 0 sqlt$r: < - metadata_script2 09:38:20 0 sqlt$r: -> system_stats_script 09:38:20 0 sqlt$r: < - system_stats_script 09:38:20 0 sqlt$r: -> schema_stats_script 09:38:20 0 sqlt$r: < - schema_stats_script 09:38:20 0 sqlt$r: -> set_cbo_env_script 09:38:20 0 sqlt$r: < - set_cbo_env_script 09:38:20 0 sqlt$r: -> custom_sql_profile 09:38:20 0 sqlt$r: ** skip "custom_sql_profile" as per "custom_sql_profile" parameter. this functionality is now disabled by default. 09:38:20 0 sqlt$r: ** to enable this functionality execute: SQL> EXEC SQLTXADMIN.sqlt$a.set_param('custom_sql_profile', 'Y'); 09:38:20 0 sqlt$r: < - custom_sql_profile 09:38:20 0 sqlt$r: -> plan 09:38:20 0 sqlt$r: < - plan 09:38:20 0 sqlt$r: -> s10053 09:38:20 0 sqlt$r: < - s10053 09:38:20 0 sqlt$r: -> flush 09:38:20 0 sqlt$r: < - flush 09:38:20 0 sqlt$r: -> purge 09:38:20 0 sqlt$r: < - purge 09:38:20 0 sqlt$r: -> restore 09:38:20 0 sqlt$r: < - restore 09:38:20 0 sqlt$r: -> del_hgrm 09:38:20 0 sqlt$r: < - del_hgrm 09:38:20 0 sqlt$r: -> tc_sql 09:38:20 0 sqlt$r: < - tc_sql 09:38:20 0 sqlt$r: -> xpress_sh 09:38:20 0 sqlt$r: < - xpress_sh 09:38:20 0 sqlt$r: -> xpress_sql 09:38:20 0 sqlt$r: < - xpress_sql 09:38:20 0 sqlt$r: -> setup 09:38:20 0 sqlt$r: < - setup 09:38:20 0 sqlt$r: -> readme 09:38:20 0 sqlt$r: < - readme 09:38:20 0 sqlt$r: -> tc_pkg 09:38:20 0 sqlt$r: < - tc_pkg 09:38:20 0 sqlt$r: -> sel 09:38:20 0 sqlt$r: < - sel 09:38:20 0 sqlt$r: -> sel_aux 09:38:20 0 sqlt$r: < - sel_aux 09:38:20 0 sqlt$r: -> install_sh 09:38:20 0 sqlt$r: < - install_sh 09:38:20 0 sqlt$r: -> install_sql 09:38:20 0 sqlt$r: < - install_sql 09:38:20 0 sqlt$r: -> tcx_pkg 09:38:20 0 sqlt$r: < - tcx_pkg 09:38:20 0 sqlt$r: -> lite_report 09:38:20 0 sqlt$r: -> lite_report.header 09:38:20 0 sqlt$r: -> lite_report.plans_summary 09:38:20 0 sqlt$r: -> lite_report.plans_list 09:38:20 0 sqlt$r: -> lite_report.plans 09:38:21 1 sqlt$r: -> lite_report.tables 09:38:21 0 sqlt$r: -> lite_report.table_columns 09:38:21 0 sqlt$r: -> lite_report.indexes 09:38:21 0 sqlt$r: -> lite_report.index_columns 09:38:22 1 sqlt$r: -> lite_report.footer 09:38:22 0 sqlt$r: < - lite_report 09:38:22 0 sqlt$a: -> upload_10053_trace 09:38:22 0 sqlt$a: -> upload_trace 09:38:22 0 sqlt$a: source = "rlzy_ora_3081178_s34879_10053.trc" 09:38:22 0 sqlt$a: target = "sqlt_s34879_10053_explain.trc" 09:38:22 0 sqlt$a: prepare file workspace 09:38:22 0 sqlt$a: open source trace (input) 09:38:22 0 sqlt$a: open target clob (output) 09:38:22 0 sqlt$a: source_offset:1 target_offset:1 09:38:22 0 sqlt$a: close trace and clob 09:38:22 0 sqlt$a: update file_size 09:38:22 0 sqlt$a: -> set_filesize for:sqlt_s34879_10053_explain.trc 09:38:22 0 sqlt$a: < - set_filesize for:sqlt_s34879_10053_explain.trc 09:38:22 0 sqlt$a: upload_10053_xtract 09:38:22 0 sqlt$a: -> upload_trace 09:38:22 0 sqlt$a: there is no trace to upload to repository 09:38:22 0 sqlt$a: < - upload_trace 09:38:22 0 sqlt$a: remote_trace_analyzer_and_copy 09:38:22 0 sqlt$i: < = remote_trace_analyzer_and_copy 09:38:22 0 sqlt$r: -> remote_driver 09:38:22 0 sqlt$r: generated sqlt_s34879_remote_driver.sql 09:38:22 0 sqlt$r: < - remote_driver 09:38:22 0 sqlt$a: input_filename "/oracle/sqlt/input/sample/mysql2.sql" was recorded 09:38:22 0 sqlt$m: => main_report_root 09:38:22 0 sqlt$a: -> common_initialization 09:38:22 0 sqlt$a: ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".," 09:38:22 0 sqlt$a: ALTER SESSION SET NLS_SORT = BINARY 09:38:22 0 sqlt$a: < - common_initialization 09:38:22 0 sqlt$t: => temp_transformation 09:38:22 0 sqlt$t: build_column_html_table_pred 09:38:22 0 sqlt$t: 8 column html tables built 09:38:22 0 sqlt$t: build_column_html_table_idx 09:38:23 1 sqlt$t: 27 column html tables built 09:38:23 0 sqlt$t: index_columns 09:38:23 0 sqlt$t: < = temp_transformation 09:38:23 0 sqlt$h: => health_check 09:38:23 0 sqlt$h: -> global_hc 09:38:23 0 sqlt$h: < - global_hc 09:38:23 0 sqlt$h: -> table_hc_MT_APPLY 09:38:23 0 sqlt$h: < - table_hc_MT_APPLY 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_APPLY_INDI 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_APPLY_INDI 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_APPLY_INPUT 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_APPLY_INPUT 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_APPLY_SERIAL 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_APPLY_SERIAL 09:38:23 0 sqlt$h: -> index_hc_PK_MT_APPLY 09:38:23 0 sqlt$h: < - index_hc_PK_MT_APPLY 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_HOSPITAL_ID 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_HOSPITAL_ID 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_INDI_ID 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_INDI_ID 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_SERIAL_APPLY 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_SERIAL_APPLY 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_SERIAL_NO 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_SERIAL_NO 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_TREATMENT_TYPE 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_TREATMENT_TYPE 09:38:23 0 sqlt$h: -> column_hc_MT_APPLY_VALID_FLAG 09:38:23 0 sqlt$h: < - column_hc_MT_APPLY_VALID_FLAG 09:38:23 0 sqlt$h: -> table_hc_MT_BIZ_FIN 09:38:23 0 sqlt$h: < - table_hc_MT_BIZ_FIN 09:38:23 0 sqlt$h: -> index_hc_IDX$$_429C0001 09:38:23 0 sqlt$h: < - index_hc_IDX$$_429C0001 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_1 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_1 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_BEGIN_DATE 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_BEGIN_DATE 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_END_DATE 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_END_DATE 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_FIN_DATE 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_FIN_DATE 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_FIN_DATE_01 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_FIN_DATE_01 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_BIZ_FIN_INDI_ID 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_BIZ_FIN_INDI_ID 09:38:23 0 sqlt$h: -> index_hc_INDI_MT_BIZ_FIN_H_F 09:38:23 0 sqlt$h: < - index_hc_INDI_MT_BIZ_FIN_H_F 09:38:23 0 sqlt$h: -> index_hc_PK_MT_BIZ_FIN 09:38:23 0 sqlt$h: < - index_hc_PK_MT_BIZ_FIN 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_HOSPITAL_ID 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_HOSPITAL_ID 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_INDI_ID 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_INDI_ID 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_SERIAL_APPLY 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_SERIAL_APPLY 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_SERIAL_NO 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_SERIAL_NO 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_TREATMENT_TYPE 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_TREATMENT_TYPE 09:38:23 0 sqlt$h: -> column_hc_MT_BIZ_FIN_VALID_FLAG 09:38:23 0 sqlt$h: < - column_hc_MT_BIZ_FIN_VALID_FLAG 09:38:23 0 sqlt$h: -> table_hc_MT_PAY_RECORD_FIN 09:38:23 0 sqlt$h: < - table_hc_MT_PAY_RECORD_FIN 09:38:23 0 sqlt$h: -> index_hc_IDX$$_429C0002 09:38:23 0 sqlt$h: < - index_hc_IDX$$_429C0002 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_PAY_RECORD_FIN_1 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_PAY_RECORD_FIN_1 09:38:23 0 sqlt$h: -> index_hc_IDX_MT_PAY_RECORD_FIN_2 09:38:23 0 sqlt$h: < - index_hc_IDX_MT_PAY_RECORD_FIN_2 09:38:23 0 sqlt$h: -> index_hc_PK_MT_PAY_RECORD_FIN 09:38:23 0 sqlt$h: < - index_hc_PK_MT_PAY_RECORD_FIN 09:38:23 0 sqlt$h: -> column_hc_MT_PAY_RECORD_FIN_FUND_ID 09:38:23 0 sqlt$h: < - column_hc_MT_PAY_RECORD_FIN_FUND_ID 09:38:23 0 sqlt$h: -> column_hc_MT_PAY_RECORD_FIN_HOSPITAL_ID 09:38:23 0 sqlt$h: < - column_hc_MT_PAY_RECORD_FIN_HOSPITAL_ID 09:38:23 0 sqlt$h: -> column_hc_MT_PAY_RECORD_FIN_POLICY_ITEM_CODE 09:38:23 0 sqlt$h: < - column_hc_MT_PAY_RECORD_FIN_POLICY_ITEM_CODE 09:38:23 0 sqlt$h: -> column_hc_MT_PAY_RECORD_FIN_SERIAL_NO 09:38:23 0 sqlt$h: < - column_hc_MT_PAY_RECORD_FIN_SERIAL_NO 09:38:23 0 sqlt$h: -> column_hc_MT_PAY_RECORD_FIN_VALID_FLAG 09:38:23 0 sqlt$h: < - column_hc_MT_PAY_RECORD_FIN_VALID_FLAG 09:38:23 0 sqlt$h: <= health_check 09:38:23 0 sqlt$m: => main_report 09:38:23 0 sqlt$m: -> flags 09:38:24 1 sqlt$m: < - flags 09:38:24 0 sqlt$m: observations_sec 09:38:24 0 sqlt$m: sql_text_sec 09:38:24 0 sqlt$m: sql_identification_sec 09:38:24 0 sqlt$m: environment_sec 09:38:24 0 sqlt$m: cbo_environment_sec 09:38:24 0 sqlt$m: cbo_environment_mod 09:38:24 0 sqlt$m: cbo_environment_unmod 09:38:24 0 sqlt$m: bug_fix_control_sec 09:38:24 0 sqlt$m: bug_fix_control_sec_0 09:38:24 0 sqlt$m: bug_fix_control_sec_1 09:38:24 0 sqlt$m: system_stats_sec 09:38:24 0 sqlt$m: dbms_stats_setup_sec 09:38:25 1 sqlt$m: init_parameters_sec 09:38:25 0 sqlt$m: init_parameters_sec_FALSE 09:38:25 0 sqlt$m: init_parameters_sec_TRUE 09:38:25 0 sqlt$m: init_parameters_sys_mod 09:38:25 0 sqlt$m: init_parameters_sys 09:38:25 0 sqlt$m: nls_parameters_sec 09:38:25 0 sqlt$m: io_calibration_sec 09:38:25 0 sqlt$m: tool_config_params_sec 09:38:25 0 sqlt$m: cursor_sharing_sec 09:38:26 1 sqlt$m: plan_sum_sec 09:38:26 0 sqlt$m: plan_stats_sec 09:38:26 0 sqlt$m: plan_stats_sec_2582817425_GV$SQLAREA_PLAN_HASH_1 09:38:26 0 sqlt$m: plan_exec_sec 09:38:26 0 sqlt$m: plan_exec_sec_2582817425_1_1_0_07000005C96BA558 09:38:26 0 sqlt$m: src = "MEM" 09:38:26 0 sqlt$m: source = "GV$SQL_PLAN" 09:38:26 0 sqlt$m: phv = "2582817425" 09:38:26 0 sqlt$m: plan_id = "-1" 09:38:26 0 sqlt$m: inst_id = "1" 09:38:26 0 sqlt$m: child_number = "0" 09:38:26 0 sqlt$m: child_address = "07000005C96BA558" 09:38:26 0 sqlt$m: gv$sql_plan_statistics = "1" 09:38:26 0 sqlt$m: last_starts = "7" 09:38:26 0 sqlt$m: starts = "21" 09:38:26 0 sqlt$m: last_output_rows = "1185999" 09:38:26 0 sqlt$m: output_rows = "3557995" 09:38:26 0 sqlt$m: last_cr_buffer_gets = "408609" 09:38:26 0 sqlt$m: cr_buffer_gets = "1225838" 09:38:26 0 sqlt$m: last_cu_buffer_gets = "0" 09:38:26 0 sqlt$m: cu_buffer_gets = "0" 09:38:26 0 sqlt$m: last_disk_reads = "0" 09:38:26 0 sqlt$m: disk_reads = "1798" 09:38:26 0 sqlt$m: last_disk_writes = "0" 09:38:26 0 sqlt$m: disk_writes = "0" 09:38:26 0 sqlt$m: last_elapsed_time = "5388427" 09:38:26 0 sqlt$m: elapsed_time = "16192336" 09:38:26 0 sqlt$m: executions = "3" 09:38:26 0 sqlt$m: gv$sql_monitor.sql_exec_start = "" 09:38:26 0 sqlt$m: gv$sql_monitor.sql_exec_id = "" 09:38:26 0 sqlt$m: gv$sql_monitor.key = "" 09:38:26 0 sqlt$m: gv$sql_workarea = "1" 09:38:26 0 sqlt$m: sqlt$_plan_extension.more = "1" 09:38:26 0 sqlt$m: sqlt$_plan_extension.binds_peek = "0" 09:38:26 0 sqlt$m: sqlt$_plan_extension.binds_capt = "0" 09:38:26 0 sqlt$m: plan_exec_sec_2582817425_3_542___ 09:38:26 0 sqlt$m: src = "XPL" 09:38:26 0 sqlt$m: source = "PLAN_TABLE" 09:38:26 0 sqlt$m: phv = "2582817425" 09:38:26 0 sqlt$m: plan_id = "542" 09:38:26 0 sqlt$m: inst_id = "" 09:38:26 0 sqlt$m: child_number = "" 09:38:26 0 sqlt$m: child_address = "" 09:38:26 0 sqlt$m: sqlt$_plan_extension.more = "1" 09:38:26 0 sqlt$m: sqlt$_plan_extension.binds_peek = "0" 09:38:26 0 sqlt$m: sqlt$_plan_extension.binds_capt = "0" 09:38:26 0 sqlt$m: act_sess_hist_sec 09:38:26 0 sqlt$m: sql_stats_sec 09:38:26 0 sqlt$m: sql_stats_sec_2582817425_1_0_07000005C96BA558_0700000438927940 09:38:26 0 sqlt$m: session_stats_sec 09:38:26 0 sqlt$m: session_event_sec 09:38:26 0 sqlt$m: parallel_processing_sec1 09:38:27 1 sqlt$m: table_sum_sec 09:38:27 0 sqlt$m: tab_stats_sec 09:38:27 0 sqlt$m: tab_stats_vers_sec 09:38:27 0 sqlt$m: tab_mod_sec 09:38:27 0 sqlt$m: tab_prop_sec 09:38:27 0 sqlt$m: tab_phy_prop_sec 09:38:27 0 sqlt$m: tab_cons_sec 09:38:27 0 sqlt$m: tab_cols_sec 09:38:29 2 sqlt$m: indexed_columns_sec 09:38:30 1 sqlt$m: tab_col_hgrm_sec 09:38:58 28 sqlt$m: index_sum_sec 09:39:00 2 sqlt$m: objects_sec 09:39:00 0 sqlt$m: dependencies_sec 09:39:00 0 sqlt$m: tablespaces_sec 09:39:00 0 sqlt$m: metadata_sec 09:39:00 0 sqlt$m: generated sqlt_s34879_main.html 09:39:00 0 sqlt$m: <= main_report 09:39:00 0 sqlt$e: -> get_file_attributes_from_repo 09:39:00 0 sqlt$e: < - get_file_attributes_from_repo 09:39:00 0 sqlt$m: main_report max_file_size: 104857600B (102400KB) (100MB) 09:39:00 0 sqlt$m: main_report current_size : 2786854B (2722KB) (3MB) 09:39:00 0 sqlt$m: no need to resize 1: scale down all lists by 0.5 09:39:00 0 sqlt$m: no need to resize 2: turn off go_to 09:39:00 0 sqlt$m: no need to resize 3: turn off overlib 09:39:00 0 sqlt$m: no need to resize 4: scale down all lists by 0.25 09:39:00 0 sqlt$m: no need to resize 5: turn off in_pred 09:39:00 0 sqlt$m: no need to resize 6: turn off sql_text 09:39:00 0 sqlt$m: no need to resize 7: scale down all lists by 0.125 09:39:00 0 sqlt$m: no need to resize 8: turn off metadata 09:39:00 0 sqlt$m: no need to resize 9: scale down all lists by 0.05 09:39:00 0 sqlt$m: <= main_report_root 09:39:00 0 sqlt$i: => call_trace_analyzer Execution ID: 38597 started at 2014-08-26 09:39:00 In case of premature termination, read trcanlzr_error.log located in SQL*Plus default directory /*************************************************************************************/ 09:39:00 => trcanlzr 09:39:00 file_name:"rlzy_ora_3081178_s34879_10046_10053.trc" 09:39:00 analyze:"YES" 09:39:00 split:"YES" 09:39:00 tool_execution_id:"38597" 09:39:00 directory_alias_in:"SQLT$UDUMP" 09:39:00 file_name_log:"sqlt_s34879_" 09:39:00 file_name_html:"sqlt_s34879_" 09:39:00 file_name_txt:"sqlt_s34879_" 09:39:00 file_name_10046:"" 09:39:00 file_name_10053:"" 09:39:00 out_file_identifier:"" 09:39:00 calling trca$p.parse_main 09:39:00 => parse_main 09:39:00 analyzing input file rlzy_ora_3081178_s34879_10046_10053.trc in /oracle/admin/RLZY/udump (SQLT$UDUMP) 09:39:00 -> parse_file 09:39:00 parsing file rlzy_ora_3081178_s34879_10046_10053.trc in /oracle/admin/RLZY/udump 09:39:01 invalid stat at line 233 "613" "" "123" 09:39:01 invalid stat at line 234 "613" "" "123" 09:39:01 invalid stat at line 235 "613" "" "123" 09:39:01 invalid stat at line 236 "613" "" "123" 09:39:01 invalid stat at line 237 "613" "" "123" 09:39:01 invalid stat at line 238 "613" "" "123" 09:39:01 invalid stat at line 239 "613" "" "123" 09:39:02 invalid stat at line 11296 "549" "" "105" 09:39:02 invalid stat at line 11297 "549" "" "105" 09:39:02 invalid stat at line 11298 "549" "" "105" 09:39:02 invalid stat at line 11299 "549" "" "105" 09:39:02 invalid stat at line 11300 "549" "" "105" 09:39:02 invalid stat at line 11301 "549" "" "105" 09:39:03 process_orphan_waits: creating some fake trace lines to aggregate waits into calls and cursors 09:39:03 ===================== 09:39:03 PARSING IN CURSOR #0 len=9 dep=0 uid=0 oct=0 lid=0 tim=3502350433402 hv=0 ad='0' 09:39:03 CURSOR #0 09:39:03 END OF STMT 09:39:03 EXEC #0:c=0,e=340,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=3502350433402 09:39:03 ===================== 09:39:03 + 09:39:03 | 161 BINDS sets. 09:39:03 | 327 binds. 09:39:03 | 239 PARSE CALLs. 09:39:03 | 886 EXEC CALLs. 09:39:03 | 910 FETCH CALLs. 09:39:03 | 0 UNMAP CALLs. 09:39:03 | 0 SORT UNMAP CALLs. 09:39:03 | 47427547 CALL elapsed. 09:39:03 | 30780000 CALL cpu. 09:39:03 | 1828 CALL disk. 09:39:03 | 6547018 CALL query. 09:39:03 | 623 CALL current. 09:39:03 | 116 CALL misses. 09:39:03 | 3603 CALL rows. 09:39:03 | 21 WAIT count idle event. 09:39:03 | 720 WAIT count non-idle event. 09:39:03 | 26656 WAIT ela idle event. 09:39:03 | 310992 WAIT ela non-idle event. 09:39:03 | 885 STAT lines. 09:39:03 | 0 ERROR lines. 09:39:03 + 09:39:03 | 0 sessions in tracefile. 09:39:03 | 72 transactions in tracefile. 09:39:03 | 4 gaps in tracefile. 09:39:03 | 84 user SQL statements in trace file. 09:39:03 | 358 internal SQL statements in trace file. 09:39:03 | 442 SQL statements in trace file. 09:39:03 | 77 user unique SQL statements in trace file. 09:39:03 | 38 internal unique SQL statements in trace file. 09:39:03 | 115 unique SQL statements in trace file. 09:39:03 | 12043 lines in trace file. 09:39:03 | 0 long lines in trace file. 09:39:03 | 47 elapsed seconds in trace file. 09:39:03 + 09:39:03 parsed rlzy_ora_3081178_s34879_10046_10053.trc (input 713737 bytes, parsed as 713737 bytes) 09:39:03 < - parse_file 09:39:03 first_transformation 09:39:03 refresh tool data dictionary? 09:39:03 finding same database_id and host_name, considering dictionary refresh... 09:39:03 -> refresh_trca$_dict_from_this before(20140813) 09:39:03 => refresh_trca$_dict_from_this 09:39:03 -> purge_trca$_dict 09:39:03 dict_state_before_purge 09:39:03 ----------------------- 09:39:03 -> print_dict_state 09:39:03 dict_refresh_days : 1 09:39:03 dict_refresh_date : 20140813 09:39:03 dict_database_id : 1589671076 09:39:03 dict_database_name: RLZY 09:39:03 dict_instance_id : 1 09:39:03 dict_instance_name: RLZY 09:39:03 dict_host_name : IBMP740-1 09:39:03 dict_platform : IBM/AIX RISC System/6000 09:39:03 dict_rdbms_version: 10.2.0.4.0 09:39:03 dict_db_files : 200 09:39:03 < - print_dict_state 09:39:03 -> call_purge_trca$_dict_gtt 09:39:03 < - call_purge_trca$_dict_gtt 09:39:25 dict_state_after_purge 09:39:25 ---------------------- 09:39:25 -> print_dict_state 09:39:25 dict_refresh_days : 09:39:25 dict_refresh_date : 09:39:25 dict_database_id : 09:39:25 dict_database_name: 09:39:25 dict_instance_id : 09:39:25 dict_instance_name: 09:39:25 dict_host_name : 09:39:25 dict_platform : 09:39:25 dict_rdbms_version: 09:39:25 dict_db_files : 09:39:25 < - print_dict_state 09:39:25 trca$_file$ 09:39:25 < - trca$_file$ (78 rows) 09:39:25 -> trca$_segments 09:39:28 < - trca$_segments (96922 rows) 09:39:28 -> trca$_extents_dm 09:39:28 < - trca$_extents_dm (0 rows) 09:39:28 -> trca$_extents_lm 09:39:28 < - trca$_extents_lm (0 rows) 09:39:28 -> trca$_users 09:39:28 < - gather_table_stats: sqltxplain.trca$_users skipped 09:39:28 trca$_extents 09:39:28 < - gather_table_stats: sqltxplain.trca$_extents skipped 09:39:28 call_purge_trca$_dict_gtt 09:39:28 < - call_purge_trca$_dict_gtt 09:39:28 -> trca$_tables$ 09:39:31 < - gather_table_stats: sqltxplain.trca$_tables$ skipped 09:39:31 trca$_indexes$ 09:39:33 < - gather_table_stats: sqltxplain.trca$_indexes$ skipped 09:39:33 trca$_ind_columns$ 09:39:43 < - gather_table_stats: sqltxplain.trca$_ind_columns$ skipped 09:39:43 trca$_tab_cols$ 09:40:12 < - gather_table_stats: sqltxplain.trca$_tab_cols$ skipped 09:40:12 trca$_objects$ 09:40:14 < - gather_table_stats: sqltxplain.trca$_objects$ skipped 09:40:14 trca$_parameter2$ 09:40:14 < - gather_table_stats: sqltxplain.trca$_parameter2$ skipped 09:40:14 print_dict_state 09:40:14 dict_refresh_days : 2 09:40:14 dict_refresh_date : 20140826 09:40:14 dict_database_id : 1589671076 09:40:14 dict_database_name: RLZY 09:40:14 dict_instance_id : 1 09:40:14 dict_instance_name: RLZY 09:40:14 dict_host_name : IBMP740-1 09:40:14 dict_platform : IBM/AIX RISC System/6000 09:40:14 dict_rdbms_version: 10.2.0.4.0 09:40:14 dict_db_files : 200 09:40:14 < - print_dict_state 09:40:14 <= refresh_trca$_dict_from_this 09:40:14 adjust_calls 09:40:14 adjusting tim=3502350433402 c: 0 -> 19970000 09:40:14 adjusting tim=3502350433402 e: 340 -> 30915713 09:40:14 adjusting tim=3502350433402 p: 0 -> 153 09:40:14 adjusting tim=3502350433402 cr: 0 -> 5319618 09:40:14 adjusting tim=3502350433402 cu: 0 -> 611 09:40:14 adjusted c count=1 adjustment=19970000 09:40:14 adjusted e count=1 adjustment=30915373 09:40:14 adjusted p count=1 adjustment=153 09:40:14 adjusted cr count=1 adjustment=5319618 09:40:14 adjusted cu count=1 adjustment=611 09:40:14 < - adjust_calls 09:40:14 -> min_and_max_tim 09:40:14 < - min_and_max_tim 09:40:14 -> call_tree 09:40:14 < - gather_table_stats: sqltxplain.trca$_call_tree skipped 09:40:14 exec_tree 09:40:14 < - gather_table_stats: sqltxplain.trca$_exec_tree skipped 09:40:14 -> exec_tree 09:40:14 < - genealogy_edge 09:40:14 genealogy 09:40:15 < - gather_table_stats: sqltxplain.trca$_genealogy skipped 09:40:15 tool_call_non_recursive 09:40:15 < - tool_call_non_recursive 09:40:15 -> tool_call_recursive 09:40:15 < - tool_call_recursive 09:40:15 -> tool_call 09:40:15 < - gather_table_stats: sqltxplain.trca$_tool_exec_call skipped 09:40:15 tool_call_total 09:40:15 < - gather_table_stats: sqltxplain.trca$_tool_exec_call skipped 09:40:15 group_call 09:40:15 < - gather_table_stats: sqltxplain.trca$_group_call skipped 09:40:15 group_call_total 09:40:15 < - gather_table_stats: sqltxplain.trca$_group_call skipped 09:40:15 compute_group_rank_rt 09:40:15 group_id:"647" tool_execution_id:"38597" 09:40:15 75 sqlt$i: ** ORA-01403: no data found ORA-01403: no data found 09:40:15 0 sqlt$i: skip "trace_analyzer" as per error above 09:40:15 0 sqlt$i: < = call_trace_analyzer 09:40:15 0 sqlt$r: -> readme_report_html 09:40:15 0 sqlt$r: < - readme_report_html 09:40:15 0 sqlt$r: -> readme_report_txt 09:40:15 0 sqlt$r: < - readme_report_txt 09:40:15 0 sqlt$a: -> upload_10046_10053_trace 09:40:15 0 sqlt$a: -> upload_trace 09:40:15 0 sqlt$a: source = "rlzy_ora_3081178_s34879_10046_10053.trc" 09:40:15 0 sqlt$a: target = "sqlt_s34879_10046_10053_execute.trc" 09:40:15 0 sqlt$a: prepare file workspace 09:40:15 0 sqlt$a: open source trace (input) 09:40:15 0 sqlt$a: open target clob (output) 09:40:15 0 sqlt$a: source_offset:1 target_offset:1 09:40:15 0 sqlt$a: close trace and clob 09:40:15 0 sqlt$a: update file_size 09:40:15 0 sqlt$a: -> set_filesize for:sqlt_s34879_10046_10053_execute.trc 09:40:15 0 sqlt$a: < - set_filesize for:sqlt_s34879_10046_10053_execute.trc 09:40:15 0 sqlt$a: sql_monitor_driver 09:40:15 0 sqlt$r: skip "sql_monitor_driver" since not available in "10.2.0.4.0" 09:40:15 0 sqlt$r: < - sql_monitor_driver 09:40:15 0 sqlt$i: <== xecute_end 09:40:15 0 sqlt$a: -> restore_init_parameters 09:40:15 0 sqlt$a: optimizer_index_cost_adj restored to "20" 09:40:15 0 sqlt$a: ALTER SESSION SET optimizer_index_cost_adj = 20 09:40:15 0 sqlt$a: < - restore_init_parameters 09:40:15 0 sqlt$r: -> process_log 09:40:15 0 sqlt$r: < - process_log 09:40:15 0 sqlt$i: SQLTXECUTE completed for "/oracle/sqlt/input/sample/mysql2.sql" 09:40:15 0 sqlt$i: ... please wait ... Elapsed: 00:09:29.00 sar: The file access permissions do not allow the specified action. ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s34879_remote_driver.sql out of sqlt repository ... adding: sqlt_s34879_remote_driver.sql (deflated 46%) ... getting sqlt_s34879_main.html out of sqlt repository ... ... getting sqlt_s34879_lite.html out of sqlt repository ... ... getting sqlt_s34879_readme.html out of sqlt repository ... ... getting sqlt_s34879_readme.txt out of sqlt repository ... ... getting sqlt_s34879_metadata.sql out of sqlt repository ... ... getting sqlt_s34879_metadata1.sql out of sqlt repository ... ... getting sqlt_s34879_metadata2.sql out of sqlt repository ... ... getting sqlt_s34879_system_stats.sql out of sqlt repository ... ... getting sqlt_s34879_schema_stats.sql out of sqlt repository ... ... getting sqlt_s34879_set_cbo_env.sql out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s34879_sta_report_mem.txt out of sqlt repository ... ... getting sqlt_s34879_sta_script_mem.sql out of sqlt repository ... ... getting sqlt_s34879_sta_report_txt.txt out of sqlt repository ... ... getting sqlt_s34879_sta_script_txt.sql out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s34879_10053_explain.trc out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s34879_import.sh out of sqlt repository ... ... getting sqlt_s34879_export_parfile.txt out of sqlt repository ... ... getting sqlt_s34879_export_parfile2.txt out of sqlt repository ... ... getting plan.sql out of sqlt repository ... ... getting 10053.sql out of sqlt repository ... ... getting flush.sql out of sqlt repository ... ... getting sqlt_s34879_purge.sql out of sqlt repository ... ... getting sqlt_s34879_restore.sql out of sqlt repository ... ... getting sqlt_s34879_del_hgrm.sql out of sqlt repository ... ... getting tc.sql out of sqlt repository ... ... getting xpress.sh out of sqlt repository ... ... getting xpress.sql out of sqlt repository ... ... getting setup.sql out of sqlt repository ... ... getting readme.txt out of sqlt repository ... ... getting tc_pkg.sql out of sqlt repository ... ... getting sel.sql out of sqlt repository ... ... getting sel_aux.sql out of sqlt repository ... ... getting install.sh out of sqlt repository ... ... getting install.sql out of sqlt repository ... ... getting pack_tcx.sql out of sqlt repository ... ... getting sqlt_s34879_awrrpt_driver.sql out of sqlt repository ... adding: sqlt_s34879_awrrpt_driver.sql (deflated 47%) ... getting sqlt_s34879_addmrpt_driver.sql out of sqlt repository ... adding: sqlt_s34879_addmrpt_driver.sql (deflated 46%) ... getting sqlt_s34879_ashrpt_driver.sql out of sqlt repository ... ... generating sqlt_s34879_ashrpt_0001_mem_1_0826_0930.html ... adding: sqlt_s34879_ashrpt_0001_mem_1_0826_0930.html (deflated 80%) adding: sqlt_s34879_ashrpt_driver.sql (deflated 60%) ... getting sqlt_s34879_tcb_driver.sql out of sqlt repository ... adding: sqlt_s34879_tcb_driver.sql (deflated 13%) ... getting sqlt_s34879_xpand_sql_driver.sql out of sqlt repository ... ... getting sqlt_s34879_export_driver.sql out of sqlt repository ... ******************************************************************* * Enter SQLTXPLAIN valid password to export SQLT repository * * Notes: * * 1. If you entered an incorrect password you will have to enter * * now both USER and PASSWORD. The latter is case sensitive * * 2. User is SQLTXPLAIN and not your application user. * ******************************************************************* Export: Release 10.2.0.4.0 - Production on Tue Aug 26 09:40:57 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported Note: constraints on tables will not be exported About to export specified tables via Conventional Path ... . . exporting table SQLT$_STATTAB 4762 rows exported Export terminated successfully without warnings. Export: Release 10.2.0.4.0 - Production on Tue Aug 26 09:40:58 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported Note: constraints on tables will not be exported About to export specified tables via Conventional Path ... . . exporting table SQLT$_SQL_STATEMENT 1 rows exported . . exporting table SQLT$_AUX_STATS$ 13 rows exported . . exporting table SQLT$_DBA_COL_STATS_VERSIONS 78 rows exported . . exporting table SQLT$_DBA_COL_USAGE$ 93 rows exported . . exporting table SQLT$_DBA_CONSTRAINTS 50 rows exported . . exporting table SQLT$_DBA_HIST_PARAMETER_M 88 rows exported . . exporting table SQLT$_DBA_HIST_SNAPSHOT 179 rows exported . . exporting table SQLT$_DBA_HISTGRM_STATS_VERSN 2758 rows exported . . exporting table SQLT$_DBA_IND_COLUMNS 39 rows exported . . exporting table SQLT$_DBA_IND_STATISTICS 17 rows exported . . exporting table SQLT$_DBA_IND_STATS_VERSIONS 9 rows exported . . exporting table SQLT$_DBA_INDEXES 17 rows exported . . exporting table SQLT$_DBA_OBJECTS 20 rows exported . . exporting table SQLT$_DBA_OPTSTAT_OPERATIONS 29 rows exported . . exporting table SQLT$_DBA_SCHEDULER_JOBS 1 rows exported . . exporting table SQLT$_DBA_SEGMENTS 20 rows exported . . exporting table SQLT$_DBA_TAB_COLS 190 rows exported . . exporting table SQLT$_DBA_TAB_HISTOGRAMS 4825 rows exported . . exporting table SQLT$_DBA_TAB_MODIFICATIONS 3 rows exported . . exporting table SQLT$_DBA_TAB_STATISTICS 3 rows exported . . exporting table SQLT$_DBA_TAB_STATS_VERSIONS 1 rows exported . . exporting table SQLT$_DBA_TABLES 3 rows exported . . exporting table SQLT$_DBA_TABLESPACES 8 rows exported . . exporting table SQLT$_DBMS_XPLAN 243 rows exported . . exporting table SQLT$_GV$ACTIVE_SESSION_HISTOR 16 rows exported . . exporting table SQLT$_GV$NLS_PARAMETERS 19 rows exported . . exporting table SQLT$_GV$OBJECT_DEPENDENCY 3 rows exported . . exporting table SQLT$_GV$PARAMETER2 263 rows exported . . exporting table SQLT$_GV$PARAMETER_CBO 190 rows exported . . exporting table SQLT$_GV$PQ_SESSTAT 22 rows exported . . exporting table SQLT$_GV$PQ_SYSSTAT 32 rows exported . . exporting table SQLT$_GV$PX_PROCESS_SYSSTAT 30 rows exported . . exporting table SQLT$_GV$SEGMENT_STATISTICS 510 rows exported . . exporting table SQLT$_GV$SESSION_EVENT 19 rows exported . . exporting table SQLT$_GV$SESSTAT 760 rows exported . . exporting table SQLT$_GV$SQL 1 rows exported . . exporting table SQLT$_GV$SQL_OPTIMIZER_ENV 5 rows exported . . exporting table SQLT$_GV$SQL_PLAN 12 rows exported . . exporting table SQLT$_GV$SQL_PLAN_STATISTICS 11 rows exported . . exporting table SQLT$_GV$SQL_SHARED_CURSOR 1 rows exported . . exporting table SQLT$_GV$SQL_WORKAREA 1 rows exported . . exporting table SQLT$_GV$SQLAREA 1 rows exported . . exporting table SQLT$_GV$SQLAREA_PLAN_HASH 1 rows exported . . exporting table SQLT$_GV$SQLSTATS 1 rows exported . . exporting table SQLT$_GV$SQLTEXT_WITH_NEWLINES 11 rows exported . . exporting table SQLT$_GV$STATNAME 380 rows exported . . exporting table SQLT$_GV$SYSTEM_PARAMETER 261 rows exported . . exporting table SQLT$_LOG 1475 rows exported . . exporting table SQLT$_METADATA 160 rows exported . . exporting table SQLT$_NLS_DATABASE_PARAMETERS 20 rows exported . . exporting table SQLT$_OUTLINE_DATA 68 rows exported . . exporting table SQLT$_PLAN_EXTENSION 24 rows exported . . exporting table SQLT$_PLAN_INFO 12 rows exported . . exporting table SQLT$_SQL_PLAN_TABLE 12 rows exported . . exporting table SQLT$_STATTAB 4762 rows exported . . exporting table SQLT$_V$SESSION_FIX_CONTROL 115 rows exported . . exporting table SQLT$_WRI$_ADV_TASKS 2 rows exported Export terminated successfully without warnings. adding: sqlt_s34879_exp.dmp (deflated 89%) adding: sqlt_s34879_import.sh (deflated 33%) adding: sqlt_s34879_exp2.dmp (deflated 93%) adding: sqlt_s34879_exp.log (deflated 78%) adding: sqlt_s34879_exp2.log (deflated 42%) adding: sqlt_s34879_export_driver.sql (deflated 67%) adding: sqlt_s34879_export_parfile.txt (deflated 73%) adding: sqlt_s34879_export_parfile2.txt (deflated 32%) ... getting sqlt_s34879_10046_10053_execute.trc out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting missing_file.txt out of sqlt repository ... ... getting sqlt_s34879_tkprof_px_driver.sql out of sqlt repository ... TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:02 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. adding: rlzy_ora_3081178_s34879_10046_10053.trc (deflated 87%) adding: rlzy_ora_3081178_s34879_10046_10053.tkprof (deflated 91%) adding: sqlt_s34879_tkprof_px_driver.sql (deflated 57%) ... getting sqlt_s34879_script_output_driver.sql out of sqlt repository ... adding: sqlt_s34879_script_output_driver.sql (deflated 38%) ### copy command below will error out on windows. disregard error. ### copy command below will error out on linux and unix. disregard error. /usr/bin/ksh: copy: not found. ### tkprof commands below may error out with "could not open trace file". disregard error. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:02 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:03 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. ### copy command below will error out on linux and unix. disregard error. /usr/bin/ksh: copy: not found. ### tkprof commands below may error out with "could not open trace file". disregard error. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:04 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file /oracle/admin/RLZY/udump/S34879_SQLT_TRACE.trc TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:04 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file /oracle/admin/RLZY/udump/S34879_SQLT_TRACE.trc ### tkprof commands below may error out with "could not open trace file". disregard error. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:04 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file *_ora_*_S34879_SQLT_TRACE.trc TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:04 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file *_ora_*_S34879_SQLT_TRACE.trc adding: sqlt_s34879_sqlt_tkprof_nosort.txt (deflated 93%) adding: sqlt_s34879_sqlt_tkprof_sort.txt (deflated 92%) updating: alert_RLZY.log (deflated 93%) adding: spfileRLZY.ora (deflated 79%) zip warning: name not matched: /oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch* zip error: Nothing to do! (sqlt_s34879_opatch.zip) ### chmod command below will error out on windows. disregard error. adding: sqlt_s34879_system_stats.sql (deflated 48%) adding: sqlt_s34879_set_cbo_env.sql (deflated 74%) adding: sqlt_s34879_metadata1.sql (deflated 83%) adding: sqlt_s34879_metadata2.sql (deflated 66%) adding: q.sql (deflated 52%) adding: plan.sql (deflated 27%) adding: 10053.sql (deflated 21%) adding: flush.sql (deflated 5%) adding: tc.sql (deflated 17%) adding: sel.sql (deflated 40%) adding: sel_aux.sql (deflated 34%) adding: install.sql (deflated 58%) adding: install.sh (deflated 10%) adding: pack_tcx.sql (deflated 64%) adding: sqlt_s34879_schema_stats.sql (deflated 52%) ### chmod command below will error out on windows. disregard error. adding: sqlt_s34879_system_stats.sql (deflated 48%) adding: sqlt_s34879_set_cbo_env.sql (deflated 74%) adding: sqlt_s34879_metadata.sql (deflated 82%) adding: sqlt_s34879_readme.txt (deflated 75%) adding: q.sql (deflated 52%) adding: plan.sql (deflated 27%) adding: 10053.sql (deflated 21%) adding: flush.sql (deflated 5%) adding: tc.sql (deflated 17%) adding: sel.sql (deflated 40%) adding: sel_aux.sql (deflated 34%) adding: xpress.sql (deflated 60%) adding: xpress.sh (deflated 11%) adding: setup.sql (deflated 43%) adding: readme.txt (stored 0%) adding: tc_pkg.sql (deflated 52%) adding: sqlt_s34879_purge.sql (deflated 30%) adding: sqlt_s34879_restore.sql (deflated 42%) adding: sqlt_s34879_del_hgrm.sql (deflated 26%) zip warning: name not matched: sqlt_s34879_opatch.zip zip error: Nothing to do! (sqlt_s34879_tc.zip) adding: rlzy_ora_3081178_s34879_10053.trc (deflated 84%) adding: sqlt_s34879_10046_10053_execute.trc (deflated 87%) adding: sqlt_s34879_10053_explain.trc (deflated 84%) ### tkprof commands below may error out with "could not open trace file". disregard error. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:05 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:05 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. ### tkprof commands below may error out with "could not open trace file". disregard error. TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:05 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file rlzy_ora_3081178_s34879_10046_10053.trc TKPROF: Release 10.2.0.4.0 - Production on Tue Aug 26 09:41:05 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. could not open trace file rlzy_ora_3081178_s34879_10046_10053.trc adding: mysql2.sql (deflated 52%) adding: sqltxecute.log (deflated 78%) adding: sqltxecute2.log (deflated 82%) adding: missing_file.txt (deflated 16%) deleting: sqltxecute.log deleting: sqltxecute2.log deleting: missing_file.txt ### ls commands below will error out on windows. disregard error. ### who command below will error out on windows. disregard error. adding: sqlt_s34879_xpand_sql_driver.sql (stored 0%) adding: sqlt_s34879_cell_state_begin.txt (stored 0%) adding: sqlt_s34879_cell_state_begin_and_end.txt (stored 0%) adding: sqlt_s34879_cell_state_end.txt (stored 0%) adding: sqlt_s34879_xecute.log (deflated 78%) adding: sqltxhost.log (deflated 51%) adding: sqlt_s34879_10046_10053_execute.trc (deflated 87%) adding: sqlt_s34879_10053_explain.trc (deflated 84%) adding: sqlt_s34879_ashrpt_0001.zip (stored 0%) adding: sqlt_s34879_cell_state.zip (stored 0%) adding: sqlt_s34879_driver.zip (stored 0%) adding: sqlt_s34879_lite.html (deflated 86%) adding: sqlt_s34879_log.zip (stored 0%) adding: sqlt_s34879_main.html (deflated 91%) adding: sqlt_s34879_readme.html (deflated 73%) adding: sqlt_s34879_sta_report_mem.txt (deflated 85%) adding: sqlt_s34879_sta_report_txt.txt (deflated 85%) adding: sqlt_s34879_sta_script_mem.sql (deflated 67%) adding: sqlt_s34879_sta_script_txt.sql (deflated 67%) adding: sqlt_s34879_tc.zip (stored 0%) adding: sqlt_s34879_tcx.zip (stored 0%) adding: sqlt_s34879_tkprof_nosort.txt (deflated 91%) adding: sqlt_s34879_tkprof_sort.txt (deflated 91%) adding: sqlt_s34879_trc.zip (stored 0%) Archive: sqlt_s34879_xecute.zip Length Date Time Name -------- ---- ---- ---- 1259 08-26-14 09:23 mysql2.sql 713442 08-26-14 09:41 sqlt_s34879_10046_10053_execute.trc 169497 08-26-14 09:40 sqlt_s34879_10053_explain.trc 3117 08-26-14 09:40 sqlt_s34879_ashrpt_0001.zip 556 08-26-14 09:41 sqlt_s34879_cell_state.zip 6486 08-26-14 09:41 sqlt_s34879_driver.zip 47872 08-26-14 09:40 sqlt_s34879_lite.html 1376973 08-26-14 09:41 sqlt_s34879_log.zip 2793361 08-26-14 09:40 sqlt_s34879_main.html 14101 08-26-14 09:40 sqlt_s34879_readme.html 18123 08-26-14 09:40 sqlt_s34879_sta_report_mem.txt 18087 08-26-14 09:40 sqlt_s34879_sta_report_txt.txt 1796 08-26-14 09:40 sqlt_s34879_sta_script_mem.sql 1796 08-26-14 09:40 sqlt_s34879_sta_script_txt.sql 382871 08-26-14 09:41 sqlt_s34879_tc.zip 83017 08-26-14 09:41 sqlt_s34879_tcx.zip 152642 08-26-14 09:41 sqlt_s34879_tkprof_nosort.txt 152674 08-26-14 09:41 sqlt_s34879_tkprof_sort.txt 257761 08-26-14 09:41 sqlt_s34879_trc.zip -------- ------- 6195431 19 files File sqlt_s34879_xecute.zip for /oracle/sqlt/input/sample/mysql2.sql has been created. sqlt_s34879_sqldx T CSV 7fv05tum90t4j Parameter 1: Oracle Pack License (Tuning or Diagnostics) [T|D] (required) Parameter 2: Output Type (HTML or CSV or Both) [H|C|B] (required) Parameter 3: SQL_ID of the SQL to be analyzed (required) Values passed: License: "T" Output : "CSV" SQL_ID : "7fv05tum90t4j" ### ... getting SQL text ... ### ... getting signature ... ### ... getting tables ... ### ... generating dynamic script, please wait ... sqlt_s34879_sqldx_7fv05tum90t4j_driver.sql file has been created. ### ### by sql_id ### 2014-08-26/09:41:29 DBA_HIST_SQLTEXT 2014-08-26/09:41:29 DBA_SQLSET_PLANS 2014-08-26/09:41:29 DBA_SQLSET_STATEMENTS 2014-08-26/09:41:29 GV$ACTIVE_SESSION_HISTORY 2014-08-26/09:41:31 GV$SQL 2014-08-26/09:41:31 GV$SQLAREA 2014-08-26/09:41:31 GV$SQLAREA_PLAN_HASH 2014-08-26/09:41:31 GV$SQLSTATS 2014-08-26/09:41:31 GV$SQLTEXT 2014-08-26/09:41:31 GV$SQLTEXT_WITH_NEWLINES 2014-08-26/09:41:31 GV$SQL_OPTIMIZER_ENV 2014-08-26/09:41:31 GV$SQL_PLAN 2014-08-26/09:41:32 GV$SQL_PLAN_STATISTICS 2014-08-26/09:41:32 GV$SQL_PLAN_STATISTICS_ALL 2014-08-26/09:41:32 GV$SQL_REDIRECTION 2014-08-26/09:41:36 GV$SQL_SHARED_CURSOR 2014-08-26/09:41:36 GV$SQL_SHARED_MEMORY 2014-08-26/09:41:37 GV$SQL_WORKAREA adding: sqlt_s34879_sqldx_7fv05tum90t4j_DBA_HIST_SQLTEXT.csv (deflated 99%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_DBA_SQLSET_PLANS.csv (deflated 99%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_DBA_SQLSET_STATEMENTS.csv (deflated 95%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsACTIVE_SESSION_HISTORY.csv (deflated 96%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL.csv (deflated 82%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLAREA.csv (deflated 98%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLAREA_PLAN_HASH.csv (deflated 82%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLSTATS.csv (deflated 77%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLTEXT.csv (deflated 89%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLTEXT_WITH_NEWLINES.csv (deflated 89%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_OPTIMIZER_ENV.csv (deflated 87%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN.csv (deflated 99%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN_STATISTICS.csv (deflated 91%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN_STATISTICS_ALL.csv (deflated 99%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_REDIRECTION.csv (deflated 94%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_SHARED_CURSOR.csv (deflated 70%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_SHARED_MEMORY.csv (deflated 95%) adding: sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_WORKAREA.csv (deflated 75%) Archive: sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip Length Date Time Name -------- ---- ---- ---- 80800 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_DBA_HIST_SQLTEXT.csv 342745 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_DBA_SQLSET_PLANS.csv 15441 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_DBA_SQLSET_STATEMENTS.csv 23832 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsACTIVE_SESSION_HISTORY.csv 6456 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL.csv 140280 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLAREA.csv 5898 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLAREA_PLAN_HASH.csv 1926 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLSTATS.csv 1495 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLTEXT.csv 1495 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQLTEXT_WITH_NEWLINES.csv 5323 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_OPTIMIZER_ENV.csv 299724 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN.csv 6084 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN_STATISTICS.csv 332892 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_PLAN_STATISTICS_ALL.csv 4712 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_REDIRECTION.csv 588 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_SHARED_CURSOR.csv 72891 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_SHARED_MEMORY.csv 1336 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_GVsSQL_WORKAREA.csv -------- ------- 1343918 18 files adding: sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip (stored 0%) Archive: sqlt_s34879_sqldx.zip Length Date Time Name -------- ---- ---- ---- 26770 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip -------- ------- 26770 1 file ### ### by exact signature ### ### ### by force signature ### 2014-08-26/09:41:37 DBA_SQLSET_PLANS 2014-08-26/09:41:37 DBA_SQLSET_STATEMENTS 2014-08-26/09:41:37 GV$ACTIVE_SESSION_HISTORY 2014-08-26/09:41:40 GV$SQL 2014-08-26/09:41:46 GV$SQLAREA 2014-08-26/09:41:52 GV$SQLAREA_PLAN_HASH adding: sqlt_s34879_sqldx_3644691823526457268_force_DBA_SQLSET_PLANS.csv (deflated 99%) adding: sqlt_s34879_sqldx_3644691823526457268_force_DBA_SQLSET_STATEMENTS.csv (deflated 95%) adding: sqlt_s34879_sqldx_3644691823526457268_force_GVsACTIVE_SESSION_HISTORY.csv (deflated 96%) adding: sqlt_s34879_sqldx_3644691823526457268_force_GVsSQL.csv (deflated 82%) adding: sqlt_s34879_sqldx_3644691823526457268_force_GVsSQLAREA.csv (deflated 82%) adding: sqlt_s34879_sqldx_3644691823526457268_force_GVsSQLAREA_PLAN_HASH.csv (deflated 82%) Archive: sqlt_s34879_sqldx_3644691823526457268_force_csv.zip Length Date Time Name -------- ---- ---- ---- 342745 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_DBA_SQLSET_PLANS.csv 15441 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_DBA_SQLSET_STATEMENTS.csv 23832 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_GVsACTIVE_SESSION_HISTORY.csv 6456 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_GVsSQL.csv 6087 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_GVsSQLAREA.csv 5898 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_GVsSQLAREA_PLAN_HASH.csv -------- ------- 400459 6 files adding: sqlt_s34879_sqldx_3644691823526457268_force_csv.zip (stored 0%) Archive: sqlt_s34879_sqldx.zip Length Date Time Name -------- ---- ---- ---- 26770 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip 9463 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_csv.zip -------- ------- 36233 2 files ### ### by table ### 2014-08-26/09:41:58 DBA_ALL_TABLES 2014-08-26/09:41:59 DBA_CACHEABLE_TABLES 2014-08-26/09:41:59 DBA_CATALOG 2014-08-26/09:41:59 DBA_COL_COMMENTS 2014-08-26/09:41:59 DBA_CONSTRAINTS 2014-08-26/09:42:00 DBA_CONS_COLUMNS 2014-08-26/09:42:01 DBA_INDEXES 2014-08-26/09:42:01 DBA_IND_COLUMNS 2014-08-26/09:42:01 DBA_IND_STATISTICS 2014-08-26/09:42:02 DBA_SYNONYMS 2014-08-26/09:42:02 DBA_TABLES 2014-08-26/09:42:02 DBA_TAB_COLS 2014-08-26/09:42:02 DBA_TAB_COLUMNS 2014-08-26/09:42:02 DBA_TAB_COL_STATISTICS 2014-08-26/09:42:03 DBA_TAB_COMMENTS 2014-08-26/09:42:03 DBA_TAB_HISTOGRAMS 2014-08-26/09:42:09 DBA_TAB_PRIVS 2014-08-26/09:42:09 DBA_TAB_STATISTICS 2014-08-26/09:42:09 DBA_TAB_STATS_HISTORY 2014-08-26/09:42:09 DBA_UPDATABLE_COLUMNS 2014-08-26/09:42:09 TABLE_PRIVILEGES adding: sqlt_s34879_sqldx_table_DBA_ALL_TABLES.csv (deflated 85%) adding: sqlt_s34879_sqldx_table_DBA_CACHEABLE_TABLES.csv (deflated 69%) adding: sqlt_s34879_sqldx_table_DBA_CATALOG.csv (deflated 75%) adding: sqlt_s34879_sqldx_table_DBA_COL_COMMENTS.csv (deflated 88%) adding: sqlt_s34879_sqldx_table_DBA_CONSTRAINTS.csv (deflated 99%) adding: sqlt_s34879_sqldx_table_DBA_CONS_COLUMNS.csv (deflated 99%) adding: sqlt_s34879_sqldx_table_DBA_INDEXES.csv (deflated 95%) adding: sqlt_s34879_sqldx_table_DBA_IND_COLUMNS.csv (deflated 99%) adding: sqlt_s34879_sqldx_table_DBA_IND_STATISTICS.csv (deflated 89%) adding: sqlt_s34879_sqldx_table_DBA_SYNONYMS.csv (deflated 89%) adding: sqlt_s34879_sqldx_table_DBA_TABLES.csv (deflated 84%) adding: sqlt_s34879_sqldx_table_DBA_TAB_COLS.csv (deflated 98%) adding: sqlt_s34879_sqldx_table_DBA_TAB_COLUMNS.csv (deflated 98%) adding: sqlt_s34879_sqldx_table_DBA_TAB_COL_STATISTICS.csv (deflated 91%) adding: sqlt_s34879_sqldx_table_DBA_TAB_COMMENTS.csv (deflated 96%) adding: sqlt_s34879_sqldx_table_DBA_TAB_HISTOGRAMS.csv (deflated 100%) adding: sqlt_s34879_sqldx_table_DBA_TAB_PRIVS.csv (deflated 92%) adding: sqlt_s34879_sqldx_table_DBA_TAB_STATISTICS.csv (deflated 82%) adding: sqlt_s34879_sqldx_table_DBA_TAB_STATS_HISTORY.csv (deflated 74%) adding: sqlt_s34879_sqldx_table_DBA_UPDATABLE_COLUMNS.csv (deflated 94%) adding: sqlt_s34879_sqldx_table_TABLE_PRIVILEGES.csv (deflated 86%) Archive: sqlt_s34879_sqldx_table_csv.zip Length Date Time Name -------- ---- ---- ---- 4437 08-26-14 09:41 sqlt_s34879_sqldx_table_DBA_ALL_TABLES.csv 235 08-26-14 09:41 sqlt_s34879_sqldx_table_DBA_CACHEABLE_TABLES.csv 351 08-26-14 09:41 sqlt_s34879_sqldx_table_DBA_CATALOG.csv 24772 08-26-14 09:41 sqlt_s34879_sqldx_table_DBA_COL_COMMENTS.csv 225238 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_CONSTRAINTS.csv 233615 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_CONS_COLUMNS.csv 36900 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_INDEXES.csv 171874 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_IND_COLUMNS.csv 9411 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_IND_STATISTICS.csv 1510 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_SYNONYMS.csv 4042 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TABLES.csv 925879 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_COLS.csv 908439 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_COLUMNS.csv 73815 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_COL_STATISTICS.csv 4450 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_COMMENTS.csv 19846498 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_HISTOGRAMS.csv 2410 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_PRIVS.csv 2267 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_STATISTICS.csv 500 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_TAB_STATS_HISTORY.csv 20160 08-26-14 09:42 sqlt_s34879_sqldx_table_DBA_UPDATABLE_COLUMNS.csv 1114 08-26-14 09:42 sqlt_s34879_sqldx_table_TABLE_PRIVILEGES.csv -------- ------- 22497917 21 files adding: sqlt_s34879_sqldx_table_csv.zip (stored 0%) Archive: sqlt_s34879_sqldx.zip Length Date Time Name -------- ---- ---- ---- 26770 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip 9463 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_csv.zip 150718 08-26-14 09:42 sqlt_s34879_sqldx_table_csv.zip -------- ------- 186951 3 files ### ### by global ### 2014-08-26/09:42:10 DBA_HIST_SNAPSHOT 2014-08-26/09:42:10 GV$PARAMETER2 adding: sqlt_s34879_sqldx_global_DBA_HIST_SNAPSHOT.csv (deflated 95%) adding: sqlt_s34879_sqldx_global_GVsPARAMETER2.csv (deflated 97%) Archive: sqlt_s34879_sqldx_global_csv.zip Length Date Time Name -------- ---- ---- ---- 74029 08-26-14 09:42 sqlt_s34879_sqldx_global_DBA_HIST_SNAPSHOT.csv 397832 08-26-14 09:42 sqlt_s34879_sqldx_global_GVsPARAMETER2.csv -------- ------- 471861 2 files adding: sqlt_s34879_sqldx_global_csv.zip (stored 0%) Archive: sqlt_s34879_sqldx.zip Length Date Time Name -------- ---- ---- ---- 26770 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip 9463 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_csv.zip 150718 08-26-14 09:42 sqlt_s34879_sqldx_table_csv.zip 16829 08-26-14 09:42 sqlt_s34879_sqldx_global_csv.zip -------- ------- 203780 4 files sqlt_s34879_sqldx_*.zip files have been created. adding: sqlt_s34879_sqldx_7fv05tum90t4j_driver.sql (deflated 93%) adding: sqldx.log (deflated 60%) Archive: sqlt_s34879_sqldx_7fv05tum90t4j_log.zip Length Date Time Name -------- ---- ---- ---- 54527 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_driver.sql 3917 08-26-14 09:41 sqldx.log -------- ------- 58444 2 files adding: sqlt_s34879_sqldx_7fv05tum90t4j_log.zip (stored 0%) SQLDX files have been created. Archive: sqlt_s34879_sqldx.zip Length Date Time Name -------- ---- ---- ---- 26770 08-26-14 09:41 sqlt_s34879_sqldx_7fv05tum90t4j_csv.zip 9463 08-26-14 09:41 sqlt_s34879_sqldx_3644691823526457268_force_csv.zip 150718 08-26-14 09:42 sqlt_s34879_sqldx_table_csv.zip 16829 08-26-14 09:42 sqlt_s34879_sqldx_global_csv.zip 5565 08-26-14 09:42 sqlt_s34879_sqldx_7fv05tum90t4j_log.zip -------- ------- 209345 5 files adding: sqlt_s34879_sqldx.zip (stored 0%) SQLTXECUTE completed.
從上面顯示的資訊可知生成了sqlt_s34879_sqldx.zip檔案,將這個sqlt_s34879_sqldx.zip檔案複製到測試資料庫所在主機上,
這裡顯示了sqlt_s34879_sqldx.zip檔案中的目錄資訊。
[oracle@jyrac1 sqlt_s34879]$ ls -lrt total 9036 -rw-r--r-- 1 root root 1259 Aug 26 09:23 mysql2.sql -rw-r--r-- 1 root root 14101 Aug 26 09:40 sqlt_s34879_readme.html -rw-r--r-- 1 root root 2793361 Aug 26 09:40 sqlt_s34879_main.html -rw-r--r-- 1 root root 47872 Aug 26 09:40 sqlt_s34879_lite.html -rw-r--r-- 1 root root 1796 Aug 26 09:40 sqlt_s34879_sta_script_txt.sql -rw-r--r-- 1 root root 1796 Aug 26 09:40 sqlt_s34879_sta_script_mem.sql -rw-r--r-- 1 root root 18087 Aug 26 09:40 sqlt_s34879_sta_report_txt.txt -rw-r--r-- 1 root root 18123 Aug 26 09:40 sqlt_s34879_sta_report_mem.txt -rw-r--r-- 1 root root 169497 Aug 26 09:40 sqlt_s34879_10053_explain.trc -rw-r--r-- 1 root root 3117 Aug 26 09:40 sqlt_s34879_ashrpt_0001.zip -rw-r--r-- 1 root root 713442 Aug 26 09:41 sqlt_s34879_10046_10053_execute.trc -rw-r--r-- 1 root root 257761 Aug 26 09:41 sqlt_s34879_trc.zip -rw-r--r-- 1 root root 152674 Aug 26 09:41 sqlt_s34879_tkprof_sort.txt -rw-r--r-- 1 root root 152642 Aug 26 09:41 sqlt_s34879_tkprof_nosort.txt -rw-r--r-- 1 root root 382871 Aug 26 09:41 sqlt_s34879_tc.zip -rw-r--r-- 1 root root 83017 Aug 26 09:41 sqlt_s34879_tcx.zip -rw-r--r-- 1 root root 1376973 Aug 26 09:41 sqlt_s34879_log.zip -rw-r--r-- 1 root root 6486 Aug 26 09:41 sqlt_s34879_driver.zip -rw-r--r-- 1 root root 556 Aug 26 09:41 sqlt_s34879_cell_state.zip -rw-r--r-- 1 root root 210301 Aug 26 09:42 sqlt_s34879_sqldx.zip -rw-r--r-- 1 root root 2750929 Aug 26 10:05 sqlt_s34879_xecute.zip
這個目錄列表中有兩個檔案要注意。第一個sqlt_s34879_tc.zip是一個標準的測試用例檔案,第二個sqlt_s34879_tcx.zip是最近才增加的(2012.12)。
1.構建測試用例的步驟
建立測試用例的第一步是在主SQLT區域建立一個子目錄。然後複製sqlt_s34879_tc.zip檔案到這個子目錄中並進行解壓。
這樣會建立一些檔案並且我們將對每一個進行描述。這裡是一個示例列表。
[oracle@jyrac1 sqlt_s34879_tc]$ ls -lrt total 3732 -rwxrwxrwx 1 oracle dba 11964 Aug 26 09:40 sqlt_s34879_readme.txt -rwxrwxrwx 1 oracle dba 137 Aug 26 09:40 tc.sql -rwxrwxrwx 1 oracle dba 1240 Aug 26 09:40 sqlt_s34879_system_stats.sql -rwxrwxrwx 1 oracle dba 36572 Aug 26 09:40 sqlt_s34879_set_cbo_env.sql -rwxrwxrwx 1 oracle dba 747 Aug 26 09:40 sqlt_s34879_restore.sql -rwxrwxrwx 1 oracle dba 238 Aug 26 09:40 sqlt_s34879_purge.sql -rwxrwxrwx 1 oracle dba 22402 Aug 26 09:40 sqlt_s34879_metadata.sql -rwxrwxrwx 1 oracle dba 663 Aug 26 09:40 sqlt_s34879_import.sh -rwxrwxrwx 1 oracle dba 261 Aug 26 09:40 sqlt_s34879_del_hgrm.sql -rwxrwxrwx 1 oracle dba 273 Aug 26 09:40 plan.sql -rwxrwxrwx 1 oracle dba 101 Aug 26 09:40 flush.sql -rwxrwxrwx 1 oracle dba 187 Aug 26 09:40 10053.sql -rwxrwxrwx 1 oracle dba 1049 Aug 26 09:40 xpress.sql -rwxrwxrwx 1 oracle dba 114 Aug 26 09:40 xpress.sh -rwxrwxrwx 1 oracle dba 935 Aug 26 09:40 tc_pkg.sql -rwxrwxrwx 1 oracle dba 438 Aug 26 09:40 setup.sql -rwxrwxrwx 1 oracle dba 412 Aug 26 09:40 sel.sql -rwxrwxrwx 1 oracle dba 370 Aug 26 09:40 sel_aux.sql -rwxrwxrwx 1 oracle dba 37 Aug 26 09:40 readme.txt -rwxrwxrwx 1 oracle dba 3137536 Aug 26 09:41 sqlt_s34879_exp.dmp -rwxrwxrwx 1 oracle dba 1259 Aug 26 09:41 q.sql -rw-r--r-- 1 oracle oinstall 55276 Aug 26 15:12 sqlt_s34879_metadata.log -rw-r--r-- 1 oracle oinstall 9663 Aug 26 15:12 sqlt_s34879_purge.log -rw-r--r-- 1 oracle oinstall 4790 Aug 26 15:12 sqlt_s34879_imp.log -rw-r--r-- 1 oracle oinstall 1798 Aug 26 15:12 sqlt_s34879_restore.log -rw-r--r-- 1 oracle oinstall 1602 Aug 26 15:12 sqlt_s34879_system_stats.log -rw-r--r-- 1 oracle oinstall 47794 Aug 26 15:12 sqlt_s34879_set_cbo_env.log -rw-r--r-- 1 oracle oinstall 3167 Aug 26 15:58 plan.log -rw-r--r-- 1 oracle oinstall 682 Aug 26 18:24 sel.log
在這個目錄中的檔案都是必要的(使用SQLT來在你的資料庫中建立一個測試用例)。在下一節我們將檢視這些檔案的詳細資訊。
2.測試用例檔案
目錄中的指令碼和其它的檔案被用來簡單和快速地構建一個測試用例。我列出了每一個檔案和它們的描述。後面我們將看到怎樣使用這些檔案來構建一個真實的測試環境。
. 10053.sql—設定10053的跟蹤級別為1
. flush.sql—清空共享池
. plan.sql—顯示最近執行SQL的執行計劃並將其輸出到plan.log中,它是呼叫的dbms_xplan.display_cursor過程來顯示執行計劃
. q.sql—被調查的SQL
. readme.txt—指令。它們非常簡單:例如”connect as sys and execute setup.sql”
. sel.sql—計算謂詞選擇性。這個指令碼依賴於sel_aux.sql並且會列印出表名和謂詞然後給你顯示出謂詞基數和選擇性。下面有一個使用sel.sql的示例
. sel_aux.sql—使用sel.sql基於不同的謂詞來生成預期的基數和選擇性
. setup.sql—設定系統統計資訊,建立測試使用者和後設資料,匯入物件統計資訊和最佳化器環境,執行測試查詢並顯示執行計劃
. sqlt_snnnnn_del_hgrm.sql—刪除測試使用者方案的直方圖資訊
. sqlt_snnnnn_exp.dmp—包含統計資訊的轉儲檔案
. sqlt_snnnnn_import.sh—Unix版本的匯入SQLT物件指令碼
. sqlt_snnnnn_metadat.sql—setup.sql呼叫該指令碼;建立測試使用者和使用者物件
. sqlt_snnnnn_purge.sql—從SQL檔案庫中刪除對測試用例SQL的引用
. sqlt_snnnnn_readmet.txt—記錄特定的測試用例,包含匯出和匯入SQLT檔案庫資訊的簡單命令,SQLTCOMPARE的使用,還原CBO統計資訊和在快捷模式和客戶模式下完成測試用例
. sqlt_snnnnn_restore.sql—匯入CBO的統計資訊到測試用例中
. sqlt_snnnnn_set_cbo_env.sql—設定測試用例的CBO環境
. sqlt_snnnnn_system_stats.sql—設定測試系統的系統統計資訊
. tc.sql—執行測試sql並顯示執行計劃
. tc_pkg.sql—對一些小的測試用例檔案進行打包並生成tc.zip檔案
. xpress.sh—xpress.sql的Unix版本。用來構建整個測試用例
. xpress.sql—這個指令碼以快捷方式來構建整個測試用例
正如你所看到的一旦解壓後在sqlt_s34879_tc目錄中會生成許多檔案。它們中的大部分會被xpress.sql和setup.sql指令碼呼叫,因此我們不會詳細討論它們,
但有一些有趣的單獨的功能在我們構建測試用例後來檢視。
3.快速構建一個測試用例(XPRESS.sql)
首先我們建立一個目錄來儲存SQLTXTRACT報告檔案。在這個目錄中我建立一個TC目錄並將測試用例zip檔案放入TC目錄。現在我們有了測試用例目錄,解壓測試用例檔案來快速和簡單的構建一個測試使用者。記住你需要用SYS使用者進行登入並執行這個操作。這是因為xpress.sql中的有些操作將會改變資料庫環境。這種環境是不能與其它使用者進行共享的。因此使用有警告的方式來構建一個測試用例。
首先我們將執行xpress.sql。這將在各個部分暫停並給你機會來檢查步驟和任何錯誤。如果沒有錯誤,那麼正常情況你只需要按下Enter鍵就會執行下一步操作。指令碼中的這些步驟每一個都被突出的顯示出來例如:
SQL> @xpress.sql 1/7 Press ENTER to create TC user and schema objects for statement_id 34879.
下面按級別列出了檢視指令碼的步驟。下面我們將檢視這七步操作的詳細資訊:
1. 建立測試使用者,使用者物件(包含表和索引),使用者名稱的形式為TCnnnnn。在步驟1中你被要求輸入測試使用者使用者名稱的字首。你在這時可以按Enter鍵或者輸入像DEV這樣的字首。在這一步的最後你應該檢查是否存在任何無效物件。有效物件會被列出來
2. 任何SQL語句以前版本的SQL檔案庫資訊會被清除
3. 使用匯入工具SQL語句將被匯入到SQL檔案庫中並且會還原系統環境。你將被要求輸入SQLTXPLAIN使用者的密碼。執行這一步的就是因為你不應該在系統中執行快捷方式不能避免丟失的原因
4. 測試使用者方案物件統計資訊會被還原
5. 系統統計資訊會被還原
6. 你被連線到測試使用者方案並設定CBO環境
7. 測試使用者方案環境被設定,SQL被執行並顯示其執行計劃
一旦你到達這個階段,假設沒有任何錯誤,你就可以自由的修改測試環境(記住在測試完成後這個系統就會被丟棄)。你可以根據提高你測試用例效能的要求進行修改,或者有時為了更好的理解發生的操作和執行計劃你可能會對測試用例進行一些修改。下面我們將檢視七步操作每一步的詳細資訊。我們將根據看到的示例輸出並解釋正在執行的操作。我們將透過這所有的步驟來構建一個能進行測試的測試環境。
1. 在步驟1中你被要求確認你想要建立的測試使用者(在我的例子中是TC64661)和方案物件
如果你輸入Enter鍵來完成步驟1。在這個步驟中執行了sqlt_metadata.sql指令碼。你被要求為測試使用者輸入一個字首。通常的值為”_1”,但你可以輸入Enter鍵接受預設值,不使用字首。然後就會建立後設資料物件,比如表和索引,還有其它約束,函式和包,檢視或者任何其它的後設資料。在這個步驟的最後將會顯示物件的狀態。它就座都為valid有效。這裡是我的示例的一個截圖。
SQL> SQL> SQL> /**********************************************************************/ SQL> SQL> REM PACKAGE SQL> SQL> SQL> /**********************************************************************/ SQL> SQL> REM VIEW SQL> SQL> SQL> /**********************************************************************/ SQL> REM FUNCTION, PROCEDURE, LIBRARY and PACKAGE BODY SQL> SQL> SQL> /**********************************************************************/ SQL> SQL> REM OTHERS SQL> SQL> SQL> SQL> /**********************************************************************/ SQL> SQL> SET ECHO OFF VER OFF PAGES 1000 LIN 80 LONG 8000000 LONGC 800000; PL/SQL procedure successfully completed. :VALID_OBJECTS -------------------------------------------------------------------------------- VALID TABLE TC34879 MT_APPLY VALID TABLE TC34879 MT_BIZ_FIN VALID TABLE TC34879 MT_PAY_RECORD_FIN VALID INDEX TC34879 IDX$$_429C0001 VALID INDEX TC34879 IDX$$_429C0002 VALID INDEX TC34879 IDX_MT_APPLY_INDI VALID INDEX TC34879 IDX_MT_APPLY_INPUT VALID INDEX TC34879 IDX_MT_APPLY_SERIAL VALID INDEX TC34879 IDX_MT_BIZ_FIN_1 VALID INDEX TC34879 IDX_MT_BIZ_FIN_BEGIN_DATE VALID INDEX TC34879 IDX_MT_BIZ_FIN_END_DATE VALID INDEX TC34879 IDX_MT_BIZ_FIN_FIN_DATE VALID INDEX TC34879 IDX_MT_BIZ_FIN_FIN_DATE_01 VALID INDEX TC34879 IDX_MT_BIZ_FIN_INDI_ID VALID INDEX TC34879 IDX_MT_PAY_RECORD_FIN_1 VALID INDEX TC34879 IDX_MT_PAY_RECORD_FIN_2 VALID INDEX TC34879 INDI_MT_BIZ_FIN_H_F VALID INDEX TC34879 PK_MT_APPLY VALID INDEX TC34879 PK_MT_BIZ_FIN VALID INDEX TC34879 PK_MT_PAY_RECORD_FIN :INVALID_OBJECTS -------------------------------------------------------------------------------- SQL> REM In case of INVALID OBJECTS: review log, fix errors and execute again. SQL> SPO OFF; SQL> SET ECHO OFF; 2/7 Press ENTER to purge statement_id 34879 from SQLT repository.
在我們例子中所有後設資料物件都是有效的,並且我這裡沒有包,檢視,函式,或過程。因此這裡沒有無效物件,我輸入Enter鍵開始執行步驟2.
2. 在步驟2中將執行sqlt_snnnnn_purge.sql指令碼。它將清空任何與被分析SQL語句相關的SQLT檔案庫資訊。在需要的時候重新載入一個測試用例是很常見的。這個指令碼的輸出類似如:
SQL> @@sqlt_s34879_purge.sql SQL> REM Purges statement_id 34879 from local SQLT repository. Just execute "@sqlt_s34879_purge.sql" from sqlplus. SQL> SPO sqlt_s34879_purge.log; SQL> SET SERVEROUT ON; SQL> EXEC SQLTXADMIN.sqlt$a.purge_repository(34879, 34879); 15:12:11 0 sqlt$a: purging statement_id = "34879" 15:12:12 1 sqlt$a: 0 rows deleted from SQLI$_DBA_HIST_PARAMETER 15:12:12 0 sqlt$a: 0 rows deleted from SQLI$_DB_LINK 15:12:12 0 sqlt$a: 0 rows deleted from SQLI$_FILE 15:12:12 0 sqlt$a: 13 rows deleted from SQLT$_AUX_STATS$ 15:12:12 0 sqlt$a: 0 rows deleted from SQLT$_DBA_AUDIT_POLICIES 15:12:12 0 sqlt$a: 0 rows deleted from SQLT$_DBA_AUTOTASK_CLIENT 15:12:12 0 sqlt$a: 0 rows deleted from SQLT$_DBA_AUTOTASK_CLIENT_HST 15:12:12 0 sqlt$a: 78 rows deleted from SQLT$_DBA_COL_STATS_VERSIONS 15:12:12 0 sqlt$a: 93 rows deleted from SQLT$_DBA_COL_USAGE$ 15:12:12 0 sqlt$a: 50 rows deleted from SQLT$_DBA_CONSTRAINTS 15:12:12 0 sqlt$a: 0 rows deleted from SQLT$_DBA_DEPENDENCIES 15:12:13 1 sqlt$a: 2758 rows deleted from SQLT$_DBA_HISTGRM_STATS_VERSN 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_ACTIVE_SESS_HIS 15:12:13 0 sqlt$a: 88 rows deleted from SQLT$_DBA_HIST_PARAMETER_M 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_SEG_STAT_OBJ 15:12:13 0 sqlt$a: 179 rows deleted from SQLT$_DBA_HIST_SNAPSHOT 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_SQLBIND 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_SQLSTAT 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_SQLTEXT 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_HIST_SQL_PLAN 15:12:13 0 sqlt$a: 17 rows deleted from SQLT$_DBA_INDEXES 15:12:13 0 sqlt$a: 39 rows deleted from SQLT$_DBA_IND_COLUMNS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_IND_EXPRESSIONS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_IND_PARTITIONS 15:12:13 0 sqlt$a: 17 rows deleted from SQLT$_DBA_IND_STATISTICS 15:12:13 0 sqlt$a: 9 rows deleted from SQLT$_DBA_IND_STATS_VERSIONS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_IND_SUBPARTITIONS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_NESTED_TABLES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_NESTED_TABLE_COLS 15:12:13 0 sqlt$a: 20 rows deleted from SQLT$_DBA_OBJECTS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_OBJECT_TABLES 15:12:13 0 sqlt$a: 29 rows deleted from SQLT$_DBA_OPTSTAT_OPERATIONS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_OUTLINES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_OUTLINE_HINTS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_PART_COL_STATISTICS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_PART_HISTOGRAMS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_PART_KEY_COLUMNS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_POLICIES 15:12:13 0 sqlt$a: 1 rows deleted from SQLT$_DBA_SCHEDULER_JOBS 15:12:13 0 sqlt$a: 20 rows deleted from SQLT$_DBA_SEGMENTS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SOURCE 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQLTUNE_PLANS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQL_PATCHES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQL_PLAN_BASELINES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQL_PLAN_DIRECTIVES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQL_PLAN_DIR_OBJS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SQL_PROFILES 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_STAT_EXTENSIONS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SUBPART_COL_STATS 15:12:13 0 sqlt$a: 0 rows deleted from SQLT$_DBA_SUBPART_HISTOGRAMS 15:12:13 0 sqlt$a: 3 rows deleted from SQLT$_DBA_TABLES 15:12:13 0 sqlt$a: 8 rows deleted from SQLT$_DBA_TABLESPACES 15:12:13 0 sqlt$a: 190 rows deleted from SQLT$_DBA_TAB_COLS 15:12:14 1 sqlt$a: 0 rows deleted from SQLT$_DBA_TAB_COL_STATISTICS 15:12:14 0 sqlt$a: 4825 rows deleted from SQLT$_DBA_TAB_HISTOGRAMS 15:12:14 0 sqlt$a: 3 rows deleted from SQLT$_DBA_TAB_MODIFICATIONS 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_DBA_TAB_PARTITIONS 15:12:14 0 sqlt$a: 3 rows deleted from SQLT$_DBA_TAB_STATISTICS 15:12:14 0 sqlt$a: 1 rows deleted from SQLT$_DBA_TAB_STATS_VERSIONS 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_DBA_TAB_SUBPARTITIONS 15:12:14 0 sqlt$a: 243 rows deleted from SQLT$_DBMS_XPLAN 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_DISPLAY_MAP 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_FND_HISTOGRAM_COLS 15:12:14 0 sqlt$a: 16 rows deleted from SQLT$_GV$ACTIVE_SESSION_HISTOR 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$CELL_STATE 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$IM_COLUMN_LEVEL 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$IM_SEGMENTS 15:12:14 0 sqlt$a: 19 rows deleted from SQLT$_GV$NLS_PARAMETERS 15:12:14 0 sqlt$a: 3 rows deleted from SQLT$_GV$OBJECT_DEPENDENCY 15:12:14 0 sqlt$a: 263 rows deleted from SQLT$_GV$PARAMETER2 15:12:14 0 sqlt$a: 190 rows deleted from SQLT$_GV$PARAMETER_CBO 15:12:14 0 sqlt$a: 22 rows deleted from SQLT$_GV$PQ_SESSTAT 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PQ_SLAVE 15:12:14 0 sqlt$a: 32 rows deleted from SQLT$_GV$PQ_SYSSTAT 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PQ_TQSTAT 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PX_INSTANCE_GROUP 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PX_PROCESS 15:12:14 0 sqlt$a: 30 rows deleted from SQLT$_GV$PX_PROCESS_SYSSTAT 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PX_SESSION 15:12:14 0 sqlt$a: 0 rows deleted from SQLT$_GV$PX_SESSTAT 15:12:14 0 sqlt$a: 510 rows deleted from SQLT$_GV$SEGMENT_STATISTICS 15:12:14 0 sqlt$a: 19 rows deleted from SQLT$_GV$SESSION_EVENT 15:12:14 0 sqlt$a: 760 rows deleted from SQLT$_GV$SESSTAT 15:12:15 1 sqlt$a: 1 rows deleted from SQLT$_GV$SQL 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_GV$SQLAREA 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_GV$SQLAREA_PLAN_HASH 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_GV$SQLSTATS 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQLSTATS_PLAN_HASH 15:12:15 0 sqlt$a: 11 rows deleted from SQLT$_GV$SQLTEXT_WITH_NEWLINES 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_BIND_CAPTURE 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_CS_HISTOGRAM 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_CS_SELECTIVITY 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_CS_STATISTICS 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_MONITOR 15:12:15 0 sqlt$a: 5 rows deleted from SQLT$_GV$SQL_OPTIMIZER_ENV 15:12:15 0 sqlt$a: 12 rows deleted from SQLT$_GV$SQL_PLAN 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$SQL_PLAN_MONITOR 15:12:15 0 sqlt$a: 11 rows deleted from SQLT$_GV$SQL_PLAN_STATISTICS 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_GV$SQL_SHARED_CURSOR 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_GV$SQL_WORKAREA 15:12:15 0 sqlt$a: 380 rows deleted from SQLT$_GV$STATNAME 15:12:15 0 sqlt$a: 261 rows deleted from SQLT$_GV$SYSTEM_PARAMETER 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_GV$VPD_POLICY 15:12:15 0 sqlt$a: 1475 rows deleted from SQLT$_LOG 15:12:15 0 sqlt$a: 160 rows deleted from SQLT$_METADATA 15:12:15 0 sqlt$a: 20 rows deleted from SQLT$_NLS_DATABASE_PARAMETERS 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_OPTSTAT_USER_PREFS$ 15:12:15 0 sqlt$a: 68 rows deleted from SQLT$_OUTLINE_DATA 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_PEEKED_BINDS 15:12:15 0 sqlt$a: 24 rows deleted from SQLT$_PLAN_EXTENSION 15:12:15 0 sqlt$a: 12 rows deleted from SQLT$_PLAN_INFO 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_SQLOBJ$ 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_SQLOBJ$DATA 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_SQLPROF$ 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_SQLPROF$ATTR 15:12:15 0 sqlt$a: 12 rows deleted from SQLT$_SQL_PLAN_TABLE 15:12:15 0 sqlt$a: 0 rows deleted from SQLT$_SQL_SHARED_CURSOR_D 15:12:15 0 sqlt$a: 1 rows deleted from SQLT$_SQL_STATEMENT 15:12:16 1 sqlt$a: 115 rows deleted from SQLT$_V$SESSION_FIX_CONTROL 15:12:16 0 sqlt$a: 0 rows deleted from SQLT$_WRI$_ADV_RATIONALE 15:12:16 0 sqlt$a: 2 rows deleted from SQLT$_WRI$_ADV_TASKS 15:12:16 0 sqlt$a: 0 rows deleted from SQLT$_WRI$_OPTSTAT_AUX_HISTORY 15:12:19 3 sqlt$a: 4762 rows deleted from SQLI$_STATTAB_TEMP 15:12:19 0 sqlt$a: 4762 rows deleted from SQLT$_STATTAB 15:12:19 0 sqlt$a: 0 rows deleted from SQLT$_STGTAB_BASELINE 15:12:19 0 sqlt$a: 0 rows deleted from SQLT$_STGTAB_DIRECTIVE 15:12:19 0 sqlt$a: 0 rows deleted from SQLT$_STGTAB_SQLPROF 15:12:19 0 sqlt$a: 0 rows deleted from SQLT$_STGTAB_SQLSET 15:12:19 0 sqlt$a: 0 rows deleted from SQLI$_STGTAB_SQLPROF 15:12:19 0 sqlt$a: 7 rows deleted from SQLI$_STGTAB_SQLSET 15:12:19 0 sqlt$a: 130 tables were purged for statement_id = "34879" PL/SQL procedure successfully completed. SQL> SET SERVEROUT OFF; SQL> SPO OFF; SQL> SET ECHO OFF; 3/7 Press ENTER to import SQLT repository for statement_id 34879.
3. 步驟3將從目標系統收集到的資料匯入到SQLT檔案庫中。這裡顯示輸出資訊:
SQL> HOS imp SQLTXPLAIN FILE=sqlt_s34879_exp.dmp LOG=sqlt_s34879_imp.log TABLES=sqlt% IGNORE=Y Import: Release 11.2.0.1.0 - Production on Tue Aug 26 15:12:22 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password:
你需要輸入SQLTXPLAIN使用者的密碼。當你輸入密碼並按下Enter鍵時就會開始進行匯入。下面是示例輸出資訊。
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SQLTXPLAIN's objects into SQLTXPLAIN . importing SQLTXPLAIN's objects into SQLTXPLAIN . . importing table "SQLT$_SQL_STATEMENT" 1 rows imported . . importing table "SQLT$_AUX_STATS$" 13 rows imported . . importing table "SQLT$_DBA_COL_STATS_VERSIONS" 78 rows imported . . importing table "SQLT$_DBA_COL_USAGE$" 93 rows imported . . importing table "SQLT$_DBA_CONSTRAINTS" 50 rows imported . . importing table "SQLT$_DBA_HIST_PARAMETER_M" 88 rows imported . . importing table "SQLT$_DBA_HIST_SNAPSHOT" 179 rows imported . . importing table "SQLT$_DBA_HISTGRM_STATS_VERSN" 2758 rows imported . . importing table "SQLT$_DBA_IND_COLUMNS" 39 rows imported . . importing table "SQLT$_DBA_IND_STATISTICS" 17 rows imported . . importing table "SQLT$_DBA_IND_STATS_VERSIONS" 9 rows imported . . importing table "SQLT$_DBA_INDEXES" 17 rows imported . . importing table "SQLT$_DBA_OBJECTS" 20 rows imported . . importing table "SQLT$_DBA_OPTSTAT_OPERATIONS" 29 rows imported . . importing table "SQLT$_DBA_SCHEDULER_JOBS" 1 rows imported . . importing table "SQLT$_DBA_SEGMENTS" 20 rows imported . . importing table "SQLT$_DBA_TAB_COLS" 190 rows imported . . importing table "SQLT$_DBA_TAB_HISTOGRAMS" 4825 rows imported . . importing table "SQLT$_DBA_TAB_MODIFICATIONS" 3 rows imported . . importing table "SQLT$_DBA_TAB_STATISTICS" 3 rows imported . . importing table "SQLT$_DBA_TAB_STATS_VERSIONS" 1 rows imported . . importing table "SQLT$_DBA_TABLES" 3 rows imported . . importing table "SQLT$_DBA_TABLESPACES" 8 rows imported . . importing table "SQLT$_DBMS_XPLAN" 243 rows imported . . importing table "SQLT$_GV$ACTIVE_SESSION_HISTOR" 16 rows imported . . importing table "SQLT$_GV$NLS_PARAMETERS" 19 rows imported . . importing table "SQLT$_GV$OBJECT_DEPENDENCY" 3 rows imported . . importing table "SQLT$_GV$PARAMETER2" 263 rows imported . . importing table "SQLT$_GV$PARAMETER_CBO" 190 rows imported . . importing table "SQLT$_GV$PQ_SESSTAT" 22 rows imported . . importing table "SQLT$_GV$PQ_SYSSTAT" 32 rows imported . . importing table "SQLT$_GV$PX_PROCESS_SYSSTAT" 30 rows imported . . importing table "SQLT$_GV$SEGMENT_STATISTICS" 510 rows imported . . importing table "SQLT$_GV$SESSION_EVENT" 19 rows imported . . importing table "SQLT$_GV$SESSTAT" 760 rows imported . . importing table "SQLT$_GV$SQL" 1 rows imported . . importing table "SQLT$_GV$SQL_OPTIMIZER_ENV" 5 rows imported . . importing table "SQLT$_GV$SQL_PLAN" 12 rows imported . . importing table "SQLT$_GV$SQL_PLAN_STATISTICS" 11 rows imported . . importing table "SQLT$_GV$SQL_SHARED_CURSOR" 1 rows imported . . importing table "SQLT$_GV$SQL_WORKAREA" 1 rows imported . . importing table "SQLT$_GV$SQLAREA" 1 rows imported . . importing table "SQLT$_GV$SQLAREA_PLAN_HASH" 1 rows imported . . importing table "SQLT$_GV$SQLSTATS" 1 rows imported . . importing table "SQLT$_GV$SQLTEXT_WITH_NEWLINES" 11 rows imported . . importing table "SQLT$_GV$STATNAME" 380 rows imported . . importing table "SQLT$_GV$SYSTEM_PARAMETER" 261 rows imported . . importing table "SQLT$_LOG" 1475 rows imported . . importing table "SQLT$_METADATA" 160 rows imported . . importing table "SQLT$_NLS_DATABASE_PARAMETERS" 20 rows imported . . importing table "SQLT$_OUTLINE_DATA" 68 rows imported . . importing table "SQLT$_PLAN_EXTENSION" 24 rows imported . . importing table "SQLT$_PLAN_INFO" 12 rows imported . . importing table "SQLT$_SQL_PLAN_TABLE" 12 rows imported . . importing table "SQLT$_STATTAB" 4762 rows imported . . importing table "SQLT$_V$SESSION_FIX_CONTROL" 115 rows imported . . importing table "SQLT$_WRI$_ADV_TASKS" 2 rows imported Import terminated successfully without warnings. SQL> SET ECHO OFF; 4/7 Press ENTER to restore schema object stats for TC34879.
正如你所看到的匯入物件列表,步驟3已經將SQLT在執行SQLTXTRACT時所捕獲到的資訊匯入在SQLT檔案庫中。然後你被要求執行步驟4,它將還原測試用例物件的統計資訊。
4. 按下Enter鍵開始處理步驟4。在步驟4中將使用SQLT檔案庫中的資訊來替換資料字典的資訊。這就是為什麼你能對系統重建的原因。步驟4的輸出資訊如下:
SQL> @@sqlt_s34879_restore.sql SQL> REM Restores schema object stats for statement_id 34879 from local SQLT repository into data dictionary. Just execute "@sqlt_s34879_restore.sql" from sqlplus. SQL> SPO sqlt_s34879_restore.log; SQL> SET SERVEROUT ON; SQL> TRUNCATE TABLE SQLTXPLAIN.SQLI$_STATTAB_TEMP; Table truncated. SQL> ALTER SESSION SET optimizer_dynamic_sampling = 0; Session altered. SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; Session altered. SQL> -- if you need to upload stats history so you can use SQLT XHUME you need to pass p_load_hist as Y SQL> EXEC SQLTXADMIN.sqlt$a.import_cbo_stats(p_statement_id => 's34879', p_schema_owner => '&&tc_user.', p_include_bk => 'N', p_make_bk => 'N', p_load_hist => 'N'); remapping stats into user TC34879(99) obtain statistics staging table version for this system statistics version for this system: 5 +-----+ upgrade/downgrade of sqli$_stattab_temp to version 5 as per this system restoring cbo stats for table TC34879.MT_APPLY restoring cbo stats for table TC34879.MT_BIZ_FIN restoring cbo stats for table TC34879.MT_PAY_RECORD_FIN + | | Stats from id "s34879_rlzy_ibmp7401" | have been restored into data dict | | METRIC IN STATTAB RESTORED OK | ------------- ---------- -------- -- | STATS ROWS: 4762 4762 OK | TABLES: 3 3 OK | TABLE PART: 0 0 OK | TABLE SUBPART: 0 0 OK | INDEXES: 17 17 OK | INDEX PART: 0 0 OK | INDEX SUBPART: 0 0 OK | COLUMNS: 4742 4742 OK | COLUMN PART: 0 0 OK | COLUMN SUBPART: 0 0 OK | AVG AGE DAYS: 42.4 42.4 OK | + PL/SQL procedure successfully completed. SQL> ALTER SESSION SET SQL_TRACE = FALSE; Session altered. SQL> ALTER SESSION SET optimizer_dynamic_sampling = 2; Session altered. SQL> SET SERVEROUT OFF; SQL> SPO OFF; SQL> SET ECHO OFF; 5/7 Press ENTER to restore system statistics.
我們只是將物件統計資訊匯入到系統中的TC64661使用者中因此它們是測試使用者方案的統計資訊。在這個過程的最後我們看到每一個物件的統計資訊被成功匯入並且要求我們處理步驟5。
5. 在步驟5中我們要刪除已經存在的系統統計資訊(我已經說了只有在系統中沒有生產資料和其它使用者時你才這麼做)。然後用對系統統計資訊設定新值。然後要求我們處理步驟6。
SQL> @@sqlt_s34879_system_stats.sql SQL> SPO sqlt_s34879_system_stats.log; SQL> SET ECHO ON TERM ON; SQL> REM SQL> REM $Header: 215187.1 sqlt_s34879_system_stats.sql 12.1.09 2014/08/26 mauro.pagano $ SQL> REM SQL> REM Copyright (c) 2000-2014, Oracle Corporation. All rights reserved. SQL> REM SQL> REM AUTHOR SQL> REM mauro.pagano@oracle.com SQL> REM SQL> REM SCRIPT SQL> REM sqlt_s34879_system_stats.sql SQL> REM SQL> REM SOURCE SQL> REM Host : IBMP740-1 SQL> REM DB Name : RLZY SQL> REM Platform: IBM/AIX RISC System/6000 SQL> REM Product : Oracle Database 10g Enterprise Edition (64bi) SQL> REM Version : 10.2.0.4.0 SQL> REM Language: US:AMERICAN_AMERICA.ZHS16GBK SQL> REM EBS : NO SQL> REM Siebel : NO SQL> REM PSFT : NO SQL> REM SQL> REM DESCRIPTION SQL> REM This script is generated automatically by the SQLT tool. SQL> REM It contains the SQL*Plus commands to set the CBO System SQL> REM Statistics as found on IBMP740-1 SQL> REM at the time SQL 7fv05tum90t4j was analyzed by SQLT. SQL> REM SQL> REM PARAMETERS SQL> REM None. SQL> REM SQL> REM EXAMPLE SQL> REM SQL> START sqlt_s34879_system_stats.sql; SQL> REM SQL> REM NOTES SQL> REM 1. Should be run as SYSTEM or SYSDBA. SQL> REM SQL> SQL> EXEC SYS.DBMS_STATS.DELETE_SYSTEM_STATS; PL/SQL procedure successfully completed. SQL> EXEC SYS.DBMS_STATS.SET_SYSTEM_STATS('CPUSPEEDNW', 855.040470934511); PL/SQL procedure successfully completed. SQL> EXEC SYS.DBMS_STATS.SET_SYSTEM_STATS('IOSEEKTIM', 10); PL/SQL procedure successfully completed. SQL> EXEC SYS.DBMS_STATS.SET_SYSTEM_STATS('IOTFRSPEED', 4096); PL/SQL procedure successfully completed. SQL> SQL> SPO OFF; SQL> SET ECHO OFF; 6/7 Press ENTER to connect as TC34879 and set CBO env.
6. 在步驟6中我們將使用測試使用者來登入資料庫。輸出資訊如下:
SQL> CONN &&tc_user./&&tc_user. Connected. SQL> @@sqlt_s34879_set_cbo_env.sql
指令碼sqlt_s34879_set_cbo_env.sql將設定CBO環境。它很重要你應該在執行它前檢視其內容。
SQL> ALTER SESSION SET optimizer_features_enable = '10.2.0.4'; Session altered. SQL> SQL> SET ECHO OFF; Press ENTER to execute ALTER SYSTEM/SESSION commands to set CBO env.
當你輸入Enter鍵時,所有的CBO環境設定將在系統級別進行。在我的例子中日誌檔案包含以下資訊:
/*************************************************************************************/ SQL> SQL> REM Non-Default or Modified Parameters SQL> SQL> -- enable modification monitoring. isdefault="TRUE" ismodified="SYSTEM_MOD" issys_modifiable="IMMEDIATE" SQL> ALTER SYSTEM SET "_dml_monitoring_enabled" = TRUE SCOPE=MEMORY; System altered. SQL> SQL> -- optimizer secure view merging and predicate pushdown/movearound. isdefault="TRUE" ismodified="SYSTEM_MOD" issys_modifiable="IMMEDIATE" SQL> ALTER SYSTEM SET optimizer_secure_view_merging = TRUE SCOPE=MEMORY; System altered. SQL> SQL> -- number of CPUs for this instance. isdefault="TRUE" ismodified="SYSTEM_MOD" issys_modifiable="IMMEDIATE" SQL> ALTER SYSTEM SET cpu_count = 2 SCOPE=MEMORY; System altered. SQL> -- number of parallel execution threads per CPU. isdefault="TRUE" ismodified="SYSTEM_MOD" issys_modifiable="IMMEDIATE" SQL> ALTER SYSTEM SET parallel_threads_per_cpu = 2 SCOPE=MEMORY; System altered. SQL> SQL> -- Maximum size of the PGA memory for one process. isdefault="TRUE" ismodified="SYSTEM_MOD" issys_modifiable="IMMEDIATE" SQL> ALTER SYSTEM SET "_pga_max_size" = 209715200 SCOPE=MEMORY; System altered. SQL> SQL> -- optimizer use feedback. isdefault="TRUE" ismodified="SYSTEM_MOD" SQL> ALTER SESSION SET "_optimizer_use_feedback" = TRUE; Session altered. SQL> SQL> -- optimizer dynamic sampling. isdefault="TRUE" ismodified="SYSTEM_MOD" SQL> ALTER SESSION SET optimizer_dynamic_sampling = 0; Session altered.
注意我們是如何修改系統引數的。例如optimizer_dynamic_sampling被設定為0。這是不預設值,這裡日誌檔案中所記錄的對隱含會話引數的設定:
SQL> SQL> -- compute join cardinality using non-rounded input values SQL> ALTER SESSION SET "_optimizer_new_join_card_computation" = TRUE; Session altered. SQL> SQL> -- null-aware antijoin parameter SQL> ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE; Session altered. SQL> -- Use subheap for optimizer or-expansion SQL> ALTER SESSION SET "_optimizer_or_expansion_subheap" = TRUE; Session altered. SQL> SQL> -- Eliminates order bys from views before query transformation SQL> ALTER SESSION SET "_optimizer_order_by_elimination_enabled" = TRUE; Session altered.
注意前面例子中的隱含引數。我們甚至設定了_fix_control引數,在下面的例子中,fix_control引數控制著特定的bug修復(包含資料庫的)是否啟用還是禁用。這裡給出了日誌檔案中fix control部分的資訊(我們將在下一章中討論fix control)。
SQL> SQL> -- remove distribution method optimization for insert/update qbc (ofe 11.2.0.1) (event 0) SQL> ALTER SESSION SET "_fix_control" = '6376551:1'; Session altered. SQL> SQL> -- Convert outer-join to inner-join if single set aggregate functio (ofe 11.1.0.7) (event 0) SQL> ALTER SESSION SET "_fix_control" = '6377505:1'; Session altered. At the end of step 6 we are prompted to execute the test case. SQL> /********************************* SQL> SQL> SPO OFF; SQL> SET ECHO OFF; 7/7 Press ENTER to execute test case.
7. 在步驟7中我們最後從我們的測試環境中執行SQL。用測試使用者來執行測試用例並將輸出查詢結果和執行計劃。在我們的示例中輸出結果如下所示:
SQL> @@tc.sql SQL> REM Executes SQL on TC then produces execution plan. Just execute "@tc.sql" from sqlplus. SQL> SET APPI OFF SERVEROUT OFF; SQL> @@q.sql SQL> REM SQL> SQL> -- execute sqlt xecute as sh passing script name SQL> -- cd sqlt SQL> -- #sqlplus sh SQL> -- SQL> start run/sqltxecute.sql input/sample/script1.sql SQL> SQL> REM Optional ALTER SESSION commands SQL> REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL> SQL> --ALTER SESSION SET statistics_level = ALL; SQL> SQL> REM Optional Binds SQL> REM ~~~~~~~~~~~~~~ SQL> SQL> VAR b1 NUMBER; SQL> EXEC :b1 := 10; PL/SQL procedure successfully completed. SQL> SQL> REM SQL statement to be executed SQL> REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL> SQL> SELECT /*+ gather_plan_statistics monitor bind_aware */ 2 /* ^^unique_id */ 3 nvl(sum(real_pay), 0) as dYearA131 4 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c 5 where a.hospital_id = b.hospital_id 6 and a.serial_no = b.serial_no 7 and a.treatment_type = '131' 8 and a.indi_id = 5609194 9 and a.serial_apply = 135888 10 and a.valid_flag = '1' 11 and b.valid_flag = '1' 12 and a.serial_apply = c.serial_apply 13 and c.valid_flag = '1' 14 and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') 15 and exists (select 1 16 from mt_pay_record_fin b 17 where b.fund_id not in ('003', '999') 18 and a.serial_no = b.serial_no); DYEARA131 ---------- 0 SQL> / DYEARA131 ---------- 0 SQL> / DYEARA131 ---------- 0 SQL> SQL> REM Notes: SQL> REM 1. SQL must contain token: /* ^^unique_id */ SQL> REM 2. Do not replace ^^unique_id with your own tag. SQL> REM 3. SQL may contain CBO Hints, like: SQL> REM /*+ gather_plan_statistics monitor bind_aware */ SQL> SQL> @@plan.sql SQL> REM Displays plan for most recently executed SQL. Just execute "@plan.sql" from sqlplus. SQL> SET PAGES 2000 LIN 180; SQL> SPO plan.log; SQL> --SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC ROWS COST PREDICATE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ SELECT /*+ gather_plan_statistics monitor bind_aware */ /* ^^unique_id */ nvl(sum(real_pay), 0) as dYearA131 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c where a.hospital_id = b.hospital_id and a.serial_no = b.serial_no and a.treatment_type = '131' and a.indi_id = 5609194 and a.serial_apply = 135888 and a.valid_flag = '1' and b.valid_flag = '1' and a.serial_apply = c.serial_apply and c.valid_flag = '1' and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') and exists (select 1 from mt_pay_record_fin b where b.fund_id not in ('003', '999') and a.serial_no = b.serial_no) Plan hash value: 3265746456 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 83997 (100)| | 1 | SORT AGGREGATE | | 1 | | |* 2 | HASH JOIN SEMI | | 1 | 83997 (2)| |* 3 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 1 (0)| | 4 | NESTED LOOPS | | 1 | 6 (0)| | 5 | NESTED LOOPS | | 1 | 5 (0)| |* 6 | TABLE ACCESS BY INDEX ROWID| MT_APPLY | 1 | 1 (0)| |* 7 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | 1 (0)| |* 8 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 1 | 4 (0)| |* 9 | INDEX RANGE SCAN | IDX_MT_BIZ_FIN_INDI_ID | 23 | 1 (0)| |* 10 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | 1 (0)| |* 11 | TABLE ACCESS FULL | MT_PAY_RECORD_FIN | 9218K| 83900 (2)| ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."SERIAL_NO"="B"."SERIAL_NO") 3 - filter((INTERNAL_FUNCTION("B"."POLICY_ITEM_CODE") AND "B"."VALID_FLAG"='1')) 6 - filter("C"."VALID_FLAG"='1') 7 - access("C"."SERIAL_APPLY"=135888) 8 - filter(("A"."SERIAL_APPLY"=135888 AND "A"."TREATMENT_TYPE"='131' AND "A"."VALID_FLAG"='1')) 9 - access("A"."INDI_ID"=5609194) 10 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO") 11 - filter(("B"."FUND_ID"<>'999' AND "B"."FUND_ID"<>'003')) 47 rows selected.
上面是在linux Oracle 11g中測試的執行計劃
而在原系統aix Oracle 10g中的執行計劃如下:
SQL> select nvl(sum(real_pay), 0) as dYearA131 2 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c 3 where a.hospital_id = b.hospital_id 4 and a.serial_no = b.serial_no 5 and a.treatment_type = '131' 6 and a.indi_id = 5609194 7 and a.serial_apply = 135888 8 and a.valid_flag = '1' 9 and b.valid_flag = '1' 10 and a.serial_apply = c.serial_apply 11 and c.valid_flag = '1' 12 and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') 13 and exists (select 1 14 from mt_pay_record_fin b 15 where b.fund_id not in ('003', '999') 16 and a.serial_no = b.serial_no) 17 ; DYEARA131 ---------- 1466.4 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC ROWS COST PREDICATE')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select nvl(sum(real_pay), 0) as dYearA131 from mt_biz_fin a, mt_pay_record_fin b, mt_apply c where a.hospital_id = b.hospital_id and a.serial_no = b.serial_no and a.treatment_type = '131' and a.indi_id = 5609194 and a.serial_apply = 135888 and a.valid_flag = '1' and b.valid_flag = '1' and a.serial_apply = c.serial_apply and c.valid_flag = '1' and b.POLICY_ITEM_CODE in ('C000', 'C001', 'C004') and exists (select 1 from mt_pay_record_fin b where b.fund_id not in ('003', '999') and a.serial_no = b.serial_no) Plan hash value: 2582817425 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 83997 (100)| | 1 | SORT AGGREGATE | | 1 | | |* 2 | HASH JOIN SEMI | | 1 | 83997 (2)| |* 3 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 1 (0)| | 4 | NESTED LOOPS | | 1 | 6 (0)| | 5 | NESTED LOOPS | | 1 | 5 (0)| |* 6 | TABLE ACCESS BY INDEX ROWID| MT_APPLY | 1 | 1 (0)| |* 7 | INDEX UNIQUE SCAN | PK_MT_APPLY | 1 | 1 (0)| |* 8 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 1 | 4 (0)| |* 9 | INDEX RANGE SCAN | IDX_MT_BIZ_FIN_INDI_ID | 23 | 1 (0)| |* 10 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | 1 (0)| |* 11 | TABLE ACCESS FULL | MT_PAY_RECORD_FIN | 9218K| 83900 (2)| ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."SERIAL_NO"="B"."SERIAL_NO") 3 - filter((INTERNAL_FUNCTION("B"."POLICY_ITEM_CODE") AND "B"."VALID_FLAG"='1')) 6 - filter("C"."VALID_FLAG"='1') 7 - access("C"."SERIAL_APPLY"=135888) 8 - filter(("A"."SERIAL_APPLY"=135888 AND "A"."TREATMENT_TYPE"='131' AND "A"."VALID_FLAG"='1')) 9 - access("A"."INDI_ID"=5609194) 10 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO") 11 - filter(("B"."FUND_ID"<>'999' AND "B"."FUND_ID"<>'003')) 45 rows selected.
從上面的結果來看我們已經在linux Oracle 11g中成功構建了一個測試環境。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-1258437/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用TypeScript改造構建工具及測試用例TypeScript
- 使用 TDD 測試驅動開發來構建 Laravel REST APILaravelRESTAPI
- 測試面試-測試用例面試
- 通過構建自己的JavaScript測試框架來了解JS測試JavaScript框架JS
- 測試用例
- 針對httptest4net構建elasticsearch叢集壓力測試用例HTTPElasticsearch
- oracle 死鎖發生的測試用例Oracle
- 【5】測試用例
- C# 使用SpecFlow建立BDD測試用例C#
- 手工測試用例與自動化測試用例的區別
- postman寫測試用例Postman
- IOS測試用例例子iOS
- 測試用例的方法
- 黑盒測試用例二
- 面經-測試用例
- 自動化測試|Eolink Apikit 如何儲存、使用測試用例API
- 大型專案裡Flutter測試應用例項整合測試深度使用Flutter
- 【轉】測試用例編寫(功能測試框架)框架
- Oracle工具(Oracle Tools) – SQLT(SQLTXPLAIN)OracleSQLAI
- 程式碼測試用例指南
- 測試用例設計指南
- 測試用例最佳實踐
- 前端測試套件構建實踐前端套件
- 使用allpairs自動設計組合測試用例AI
- APP測試設計測試用例的要點APP
- 建立測試用例以及測試結果儲存
- httprunner 4.x學習 - 12 測試用例引用另一個測試用例HTTP
- 使用 Snapcraft 構建、測試併發布 Snap 軟體包Raft
- SQLT 使用指南SQL
- 軟體測試用例設計中的結構設計
- 介面測試用例編寫和測試關注點
- web安全性測試用例Web
- 使用Gradle構建Android應用內測版本GradleAndroid
- TestLink測試用例管理工具使用說明
- iOS itunes-connect使用文件(app 構建 刪除 測試)iOSAPP
- python TestCase測試用例怎麼用Python
- 使用 Oracle XML Publisher 構建線上報表應用程式OracleXML
- oracle sqlt(sqltxplain) 診斷工具OracleSQLAI