在进行性能优化尤其是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。这将在各个部分暂停并给你机会来检查步骤和任何错误。如果没有错误，那么正常情况你只需要按下回车键就会执行下一步操作。脚本中的这些步骤每一个都被突出的显示出来例如：
SQL> @xpress.sql 1/7 Press ENTER to create TC user and schema objects for statement_id 34879.
下面按级别列出了查看脚本的步骤。下面我们将查看这七步操作的详细信息：
1. 创建测试用户，用户对象（包含表和索引），用户名的形式为TCnnnnn。在步骤1中你被要求输入测试用户用户名的前缀。你在这时可以按回车键或者输入像DEV这样的前缀。在这一步的最后你应该检查是否存在任何无效对象。有效对象会被列出来
2. 任何SQL语句以前版本的SQL档案库信息会被清除
3. 使用导入工具SQL语句将被导入到SQL档案库中并且会还原系统环境。你将被要求输入SQLTXPLAIN用户的密码。执行这一步的就是因为你不应该在系统中运行快捷方式不能避免丢失的原因
4. 测试用户方案对象统计信息会被还原
5. 系统统计信息会被还原
6. 你被连接到测试用户方案并设置CBO环境
7. 测试用户方案环境被设置，SQL被执行并显示其执行计划
一旦你到达这个阶段，假设没有任何错误，你就可以自由的修改测试环境（记住在测试完成后这个系统就会被丢弃）。你可以根据提高你测试用例性能的要求进行修改，或者有时为了更好的理解发生的操作和执行计划你可能会对测试用例进行一些修改。下面我们将查看七步操作每一步的详细信息。我们将根据看到的示例输出并解释正在执行的操作。我们将通过这所有的步骤来构建一个能进行测试的测试环境。
1. 在步骤1中你被要求确认你想要创建的测试用户（在我的例子中是TC64661）和方案对象
如果你输入回车键来完成步骤1。在这个步骤中运行了sqlt_metadata.sql脚本。你被要求为测试用户输入一个前缀。通常的值为”_1”，但你可以输入回车键接受缺省值，不使用前缀。然后就会创建元数据对象，比如表和索引，还有其它约束，函数和包，视图或者任何其它的元数据。在这个步骤的最后将会显示对象的状态。它就座都为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.
在我们例子中所有元数据对象都是有效的，并且我这里没有包，视图，函数，或过程。因此这里没有无效对象，我输入回车键开始执行步骤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用户的密码。当你输入密码并按下回车键时就会开始进行导入。下面是示例输出信息。
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. 按下回车键开始处理步骤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.
当你输入回车键时，所有的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中成功构建了一个测试环境。
