Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the use of SQLT". Friends who are interested may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the use of SQLT?"

Main methods

Before using the main methods provided by SQLT, make sure that SQLT is installed correctly and that users using SQLT are given the SQLT_USER_ROLE role.

If the SQLT installation bypasses SQL*Net (meaning that you did not specify a connector during installation), you will need to set the connector parameters manually before executing any of the SQLT main methods from the remote client. That is, if you connect using sqlplus scott/tiger@myprod, then you need to execute: EXEC sqltxadmin.sqlt$a.set_sess_param ('connect_identifier',' @ myprod')

SQLT provides the following seven main methods for a SQL statement to generate diagnostic details XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT, and XPREXC. XTRACT, XECUTE, XTRXEC, XTRSBY, XPREXT, and XPREXC handle binding variables and do bind peeking (bind variable peeping), but XPLAIN does not. This is because XPLAIN is executed based on the EXPLAIN PLAN FOR command, which does not do bind peeking. Therefore, if possible, please avoid using XPLAIN.

In addition to the bind peeking limitations of XPLAIN, all seven major methods provide sufficient diagnostic details to make a preliminary assessment of SQL with poor performance or incorrect result sets. If the SQL is still in memory or in Automatic Workload Repository (AWR), use XTRACT or XTRXEC, and use XECUTE otherwise. For Data Guard or standby read-only databases, use XTRSBY. Consider using XPLAIN only if none of the other methods are feasible. XPREXT and XPREXC are similar to XTRACT and XECUTE, but they disable some SQLT features in order to improve the performance of SQLT.

XTRACT method

Use this method if you know the SQL_ID or HASH_VALUE of the SQL to be analyzed, otherwise use XECUTE. You can find SQL_ID in AWR report and HASH_VALUE in SQL trace (identified by the "hv=" tag on top of the SQL text).

If the SQL is still in memory or has been captured by AWR, use XTRACT to discover the SQL and provide a set of diagnostic files, otherwise XTRACT will output errors.

If you set the parameter STATISTICS_LEVEL to ALL when hard parsing SQL, you can get important performance statistics (such as the actual number of rows per step). You can also generate equally useful performance statistics by including the following CBO prompt in SQL: / * + GATHER_PLAN_STATISTICS * /. In 11g, you can include the following CBO prompt in SQL for enhanced diagnostic information: / * + GATHER_PLAN_STATISTICS MONITOR * /

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XTRACT execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XTRACT method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user who has executed the SQL to be analyzed, and execute the sqlt/run/sqltxtract.sql script, passing SQL_ID or HASH_VALUE.

# cd sqlt/run# sqlplus appsSQL > START sqltxtract.sql [SQL_ID] | [HASH_VALUE] [sqltxplain_password] SQL > START sqltxtract.sql 0w6uydn50g8cx sqltxplain_passwordSQL > START sqltxtract.sql 2524255098 sqltxplain_passwordXECUTE method

Compared with the XTRACT method, this method provides more detailed information. As the name XECUTE indicates, it executes the SQL being analyzed and then generates a set of diagnostic files. Its main disadvantage is that if the SQL being analyzed takes a long time to execute, the method also takes a long time.

According to the rule of thumb, consider using this method only if the SQL is executed for less than 1 hour, otherwise use XTRACT.

Before you can use this XECUTE method, you must create a text file that contains SQL text. If SQL includes binding variables, your file must contain binding variable declarations and assignments. Take sqlt/input/sample/script1.sql as an example. Your SQL should contain the tag / * ^ ^ unique_id * / (highly recommended).

If your SQL needs to be bound to a data type that SQL*Plus cannot use, or if it uses collections, you may need to embed SQL in an anonymous PL/SQL block. In this case, use sqlt/input/sample/plsql1.sql as an input example for this method.

For statements that modify data, such as INSERT/UPDATE/DELETE, the tool creates a SavePoint before the statement executes, so that the transaction can fall back to that SavePoint at the end of the session. For more information about SAVEPOINT (SavePoint), see the "Oracle Concepts" reference manual.

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XECUTE execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XECUT method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user who has executed the SQL to be parsed, and execute the sqlt/run/sqltxecute.sql script passing the name of the text file containing the SQL text and its bound variables. You need to place the file in the sqlt/input directory and run XECUTE when it is in the sqlt home directory, as follows:

# cd sqlt# sqlplus appsSQL > START [path] sqltxecute.sql [path] scriptname [sqltxplain_password] SQL > START run/sqltxecute.sql input/sample/script1.sql sqltxplain_passwordXTRXEC method

This method combines the functions of XTRACT and XECUTE. In fact, XTRXEC executes both methods successively. For the expensive plan found by the requested SQL statement, the XTRACT phase generates a script containing the extracted SQL and the binding declaration and assignment. XTRXEC then executes the XECUTE phase using the script created in the first phase.

SQLT creates a binding variable of the script for use by XTRACT based on the values it snoops when generating the most expensive execution plan in memory. The criterion for judging the cost of a plan is based on the average execution time of the plan.

If XTRXEC outputs errors after only the first phase (XTRACT), you may need to check the script used in the second phase (XECUTE) and adjust the binding variables accordingly. Adjustments are especially needed when using infrequently used data types.

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XTRXEC execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XTRXEC method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user executing the SQL to be analyzed, and execute the sqlt/run/sqltxtrxec.sql script, passing SQL_ID or HASH_VALUE.

# cd sqlt/run# sqlplus appsSQL > START sqltxtrxec.sql [SQL_ID] | [HASH_VALUE] [sqltxplain_password] SQL > START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_passwordSQL > START sqltxtrxec.sql 2524255098 sqltxplain_passwordXTRSBY method

Use this method if you need to analyze the SQL executed on Data Guard or a standby read-only database. You need to know the SQL_ID or HASH_VALUE of the SQL you want to analyze.

Create a database link to the slave database on the main library, and the user you connect to needs to have access to the data dictionary, usually using a user with DBA permission.

CREATE PUBLIC DATABASE LINK V1123 CONNECT TO mydba IDENTIFIED by mydba_password

USING'(DESCRIPTION = (ADDRESS= (PROTOCOL=TCP))

(HOST=coesrv14.us.oracle.com) (PORT=1521)) (CONNECT_DATA= (SID = V1123)'

If the SQL is still in memory in a read-only database, use XTRSBY to discover the SQL and provide a set of diagnostic files, otherwise XTRSBY will output errors.

If you set the parameter STATISTICS_LEVEL to ALL when hard parsing SQL in a read-only database, you can get important performance statistics (such as the actual number of rows per scheduled operation). You can also generate equally useful performance statistics by including the following CBO prompt in SQL: / * + GATHER_PLAN_STATISTICS * /. In 11g, you can include the following CBO hint in SQL for improved diagnostic information: / * + GATHER_PLAN_STATISTICS MONITOR * /

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XTRSBY execution.

XTRSBY requires three parameters: the ID of the SQL id,DB_LINK and the password of the SQLTXPLAIN.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XTRSBY method, first make sure that SQLT is installed on the primary database and replicated to the read-only database. Then connect to the SQL*Plus in the main database and execute the sqlt/run/sqltxtrsby.sql script, passing SQL_ID or HASH_VALUE, then DB_LINK.

# cd sqlt/run# sqlplus appsSQL > START sqltxtrsby.sql [SQL_ID] | [HASH_VALUE] [sqltxplain_password] [DB_LINK] SQL > START sqltxtrsby.sql 0w6uydn50g8cx sqltxplain_password V1123SQL > START sqltxtrsby.sql 2524255098 sqltxplain_password v1123

In addition to XTRSBY, you can also execute sqlt/utl/sqlhc.sql or sqlt/utl/sqlhcxec.sql directly from a read-only database. These two read-only scripts do not install anything on the database, nor do they execute DML commands. They provide additional information that is not available in XTRSBY.

XPLAIN method

This method is based on the EXPLAIN PLAN FOR command, so it ignores the binding variables referenced by your SQL statement. Use this method only if you cannot use XTRACT or XECUTE.

Before you can use this XPLAIN method, you must create a text file that contains SQL text. If SQL includes binding variables, you have two choices: leave the SQL text "unchanged" or carefully replace the binding with literals of the same data type. Take sqlt/input/sample/sql1.sql as an example.

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XPLAIN execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XPLAIN method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user who has executed the SQL to be parsed, and execute the sqlt/run/sqltxplain.sql script passing the name of the text file containing the SQL text. You need to place the file in the sqlt/input directory and run XPLAIN when it is in the sqlt home directory, as follows:

# cd sqlt# sqlplus appsSQL > START [path] sqltxplain.sql [path] filename [sqltxplain_password] SQL > START run/sqltxplain.sql input/sample/sql1.sql sqltxplain_passwordXPREXT method

Use this method if you want to use XTRACT and want to disable some SQLT features to make it execute faster. The script sqlt/run/sqltcommon11.sql shows which features are disabled.

If you know the SQL_ID or HASH_VALUE of the SQL to be analyzed, use this method, otherwise use XPREXC. You can find SQL_ID in AWR report and HASH_VALUE in SQL trace (identified by the "hv=" tag on top of the SQL text).

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XPREXT execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XPREXT method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user who has executed the SQL to be analyzed, and execute the sqlt/run/sqltxprext.sql script, passing SQL_ID or HASH_VALUE.

# cd sqlt/run# sqlplus appsSQL > START sqltxprext.sql [SQL_ID] | [HASH_VALUE] [sqltxplain_password] SQL > START sqltxprext.sql 0w6uydn50g8cx sqltxplain_passwordSQL > START sqltxprext.sql 2524255098 sqltxplain_passwordXPREXC method

Use this method if you want to use XECUTE and want to disable some SQLT features to make it execute faster. The script sqlt/run/sqltcommon11.sql shows which features are disabled

According to the rule of thumb, consider using this method only if the SQL is executed for less than 1 hour, otherwise use XPREXT.

Before you can use this XPREXC method, you must create a text file that contains SQL text. If SQL includes binding variables, your file must contain binding variable declarations and assignments. Take sqlt/input/sample/script1.sql as an example. Your SQL should contain the tag / * ^ ^ unique_id * /, which should be spelled accurately, in other words, please don't change it.

If your SQL needs to be bound to a data type that SQL*Plus cannot use, or if it uses collections, you may need to embed SQL in an anonymous PL/SQL block. In this case, use sqlt/input/sample/plsql1.sql as an input example for this method.

For statements that modify data, such as INSERT/UPDATE/DELETE, the tool creates a SavePoint before the statement executes, so that the transaction can fall back to that SavePoint at the end of the session. For more information about SAVEPOINT (SavePoint), see the "Oracle Concepts" reference manual.

When using this method, it asks for the SQLTXPLAIN password, which is used when exporting the SQLT Repository corresponding to the XPREXC execution.

This approach requires that the application user performing SQLT be granted the SQLT_USER_ROLE role.

To use this XPREXC method, first make sure that SQLT is installed, then connect to SQL*Plus as the application user who has executed the SQL to be parsed, and execute the sqlt/run/sqltxprexc.sql script passing the name of the text file containing the SQL text and its bound variables. You need to place the file in the sqlt/input directory and run XPREXC when it is in the sqlt home directory, as follows:

# cd sqlt# sqlplus appsSQL > START [path] sqltxprexc.sql [path] scriptname [sqltxplain_password] SQL > START run/sqltxprexc.sql input/sample/script1.sql sqltxplain_password Special method

In addition to the main methods, SQLT provides some special methods.

The most commonly used special method is COMPARE. This method takes the previous two executions of SQLT (either of the main methods) as input and generates a report of the difference analysis.

Other special methods include: TRCANLZR, TRCAXTR, TRCASPLIT and XTRSET. The first three methods perform operations based on a single SQL trace, and the last one performs operations on a set of SQL trace.

COMPARE method

Use this COMPARE method when you have two similar systems (SOURCES), but the same SQL statement executes normally on one system and not on the other. This method can help determine the differences between the two SOURCES in terms of planning, metadata, CBO statistics, initialization parameters, and problem repair control. The premise is that SQLT must be installed on both systems, and either of the main methods must be used on the same SQL on both systems.

This comparison can be performed on one or the third SOURCES database of any two COMPARE databases. The latter should contain two SQLT Repoitory of SOURCES. To import SQLT Repository, use the syntax provided in the sqlt_99999_readme.html file generated by any major method.

Once the COMPARE system contains repositories from both SOURCES, execute sqlt/run/sqltcompare.sql to connect as SYS or application user. A list of STATEMENT_ID is displayed, from which you can select the executors of the two SQLT stores to compare. After entering two STATEMENT_ID, you are asked to provide a specific PLAN_HASH_VALUE from both SOURCES.

# cd sqlt# sqlplus sqltxplainSQL > START [path] sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2] SQL > START run/sqltcompare.sql 92263 72597SQL > START run/sqltcompare.sqlTRCANLZR method

This method takes the SQL trace file name as input and parses the file. The actual trace must be in the TRCA$INPUT1 directory, which defaults to the USER_DUMP_DEST directory during installation.

The TRCANLZR method can also analyze multiple related trace as a group at the same time. You will need to use this feature when analyzing PX trace. In this case, you need to create a control.txt file that contains a list of trace (one file name per line, excluding the path assignment), and place the control.txt in the TRCA$INPUT1 or TRCA$INPUT2 directory. These two directories are being installed. Period defaults to USER_DUMP_DEST and BACKGROUND_DUMP_DEST, respectively. TRCANLZR then reads the control.txt file from one of the two input directories and looks for the trace set in either of the two directories.

TRCANLZR is similar to TKPROF, but it has extended functionality. When it analyzes a trace (or trace set), it also includes Schema object characteristics similar to CBO statistics and some other important performance metrics.

To use this TRCANLZR method, make sure that SQLT is installed first. Then, launch SQL*Plus as the application user that generated the trace, and execute the sqlt/run/sqltrcanlzr.sql script, passing the name of the trace to be parsed or the name of the control.txt file populated with the file name. Do not include any path assignments.

# cd sqlt# sqlplus [application_user] SQL > START [path] sqltrcanlzr.sql [SQL Trace filename | control.txt] SQL > START run/sqltrcanlzr.sql V1122_ora_24292.trcSQL > START run/sqltrcanlzr.sql control.txtTRCAXTR method

This method performs the same operation as TRCANLZR, but when the trace analysis is complete, it continues to execute XTRACT for the top-level SQL found in trace. This method basically automatically invokes and merges all reports generated by TRCANLZR and XTRACT.

To use this TRCAXTR method, make sure that SQLT is installed first. Then, go to the sqlt/run directory and start SQL*Plus to generate an application user connection for trace. Next, execute the sqlt/run/sqltrcaxtr.sql script, passing the name of the trace to be parsed or the name of the control.txt file populated with the file name. Do not include any path assignments.

# cd sqlt/run# sqlplus [application_user] SQL > START sqltrcaxtr.sql [SQL Trace filename | control.txt] SQL > START sqltrcaxtr.sql V1122_ora_24292.trcSQL > START sqltrcaxtr.sql control.txtTRCASPLIT method

This method takes the name of the SQL trace file created by EVENT 10046 and other EVENT (usually 10053) as input. It then continues to divide the input trace file into two output files. One contains the trace line corresponding to EVENT 10046, and the other contains its supplementary information. In other words, the second file contains those trace lines that are not part of the syntax of EVENT 10046. So, if the input trace is created using both EVENT 10046 and EVENT 10053, the resulting output file will be 10046 trace and 10053 trace. The actual input trace must be in the TRCA$INPUT1 directory, which defaults to the USER_DUMP_DEST directory during installation.

To use this TRCASPLIT method, make sure that SQLT is installed first. Then, start SQL*Plus (connect as any SQLT user) and execute the sqlt/run/sqltrcasplit.sql script, passing the name of the trace to be split. Do not include any path assignments.

# cd sqlt# sqlplus [sqlt_user] SQL > START [path] sqltrcasplit.sql [SQL Trace filename] SQL > START run/sqltrcasplit.sql V1122_ora_24292.trcXTRSET method

XTRSET extracts a list of SQL statements identified by SQL_ID or HASH_VALUE from memory or AWR, and then performs XTRACT on each of these SQL statements. Finally, it merges all the SQLT files into a single compressed file. This XTRSET method is required when benchmarking the same set of SQL statements through a series of tests.

When using this method, it requires only one SQLTXPLAIN password, which will be required when exporting the SQLT Repository for each XTRACT in the list of SQL statements.

To use this XTRSET method, you must first have SQLT installed. Navigate to the sqlt/run directory and start SQL*Plus to connect as the application user that issues all or most of the SQL statements. Then, execute the sqlt/run/sqltxtrset.sql script. When prompted, pass a comma-separated list of SQL statements identified by SQL_ID or HASH_VALUE, as well as the password for SQLTXPLAIN.

# cd sqlt/run# sqlplus [application_user] SQL > START sqltxtrset.sqlList of SQL_IDs or HASH_VALUEs: 2yas208zgt5cv, 6rczmqdtg99mu, 8w8tjgac6tv12 advanced methods and modules

In addition to the main and special methods, SQLT provides some other features. Use these advanced methods and modules only when required by Oracle Support: PROFILE, XGRAM, XPLORE, and XHUME. The last one is for Oracle Support only and is used only in the internal test environment.

PROFILE method

Using this PROFILE method at 10g provides a quick fix when poor-performing SQL statements happen to have better known plans. This better plan can be in memory on the same or different systems, or in the AWR of the same or different systems. In other words, this approach allows you to "pin" the plan using a custom SQL Profile if you have a better plan. Before using this method, you must use any of the main methods for the SQL for which you want to extract and pin this plan. You can use SQL Plan Management (SPM) instead of this method in 11g or higher.

Note that PROFILE uses DBMS_SQLTUNE API, which is part of "SQL Tuning Advisor", so permission to use Oracle Tuning Pack is required. Use this PROFILE method only if your database has permission for Oracle Tuning Pack.

To use this PROFILE method, make sure that SQLT is installed and used on the SOURCE system, then connect to SQL*Plus as SYS or SQLTXPLAIN, and execute the sqlt/utl/sqltprofile.sql script. It will ask for the STATEMENT_ID from the list previously executed by SQLT. When you select STATEMENT_ID, it will ask for the PLAN_HASH_VALUE from the list of available plans. These execution plans are captured and stored when using XTRACT or XECUTE for SQL.

On TARGET systems where you need to implement custom SQL Profile, you do not need to install SQLT.

The PROFILE method basically consists of four steps.

Use XTRACT or XECUTE on SOURCE systems.

Execute sqlt/utl/sqltprofile.sql in SOURCE to generate a script for a custom SQL Profile.

Review the generated script and adjust the SQL text as needed. For example, when using XECUTE, you want to remove comments generated by / * ^ ^ unique_id * /.

Execute the generated script in the TARGET system where you want to fix the schedule.

# cd sqlt/utl# sqlplus sqltxplainSQL > START sqltprofile.sql [statement id] [plan hash value]; SQL > START sqltprofile.sql 32263 923669362

The custom SQL Profile created by this method is based on the plan outline data, so it is more stable. If you want to delete this custom SQLProfile later, you can find the delete command in the script generated by PROFILE.

If you have not installed SQLT on the SOURCE system or cannot perform XTRACT or XECUTE for the SQL concerned, you can use sqlt/utl/coe_xfr_sql_profile.sql to implement the same functionality provided by the PROFILE method. The script also uses DBMS_SQLTUNE;, so Oracle Tuning Pack's permission is required.

If your system is 11g and you are considering using this PROFILE method, look at the dynamic Readme file generated by any major method and look for the "Create SQL Plan Baseline from SQL Set" section. As described in the dynamic Readme file, you can consider using "SQL Plan Management SPM" through SQL Set.

XGRAM module

The XGRAM module provides the ability to modify the CBO histogram to enhance CBO statistics for certain columns or as part of a test case. Through this module, you can insert, update, or delete a histogram or a single bucket.

Alphabetical list of scripts that implement the XGRAM module:

Sqlt/utl/xgram/sqlt_delete_column_hgrm.sql

Sqlt/utl/xgram/sqlt_delete_hgrm_bucket.sql

Sqlt/utl/xgram/sqlt_delete_schema_hgrm.sql

Sqlt/utl/xgram/sqlt_delete_table_hgrm.sql

Sqlt/utl/xgram/sqlt_display_column_stats.sql

Sqlt/utl/xgram/sqlt_insert_hgrm_bucket.sql

Sqlt/utl/xgram/sqlt_set_bucket_size.sql

Sqlt/utl/xgram/sqlt_set_column_hgrm.sql

Sqlt/utl/xgram/sqlt_set_min_max_values.sql

XGRAM is installed automatically during SQLT installation. If you want to use this module outside of SQLT, you only need to install a separate package and use the above script (independent of SQLTXADMIN).

XPLORE module

If the performance of SQL starts to deteriorate after a database upgrade, or if it may produce obvious error results, then using the XPLORE module will help. If you switch optimizer_features_enable OFE to the pre-upgrade database version and SQL reperforms normally or produces different results, you can use this XPLORE module to try to identify which particular Optimizer feature or fix introduces unexpected behavior. Identifying a specific fault is useful for further troubleshooting or performing a more detailed study of this particular feature and / or fix.

This module searches for plans by switching initialization parameters and fix control parameters.

Use XPLORE only if all of the following conditions are met:

When using a "poor" plan, SQL performs poorly or returns incorrect results.

Bad plans can be regenerated on the test system (preferably without real data).

You can regenerate a "good" plan on the test system by switching OFE.

You need to narrow the cause to specific parameters or bug fix control.

You have full access to the test system, including SYS access.

Do not use XPLORE when any of the following conditions are met:

SQL statements can cause data corruption or update.

There is a large amount of data in the tables referenced by SQL.

It may take more than a few seconds to execute the SQL.

To install and use the XPLORE module, read the appropriate sqlt/utl/xplore/readme.txt.

XHUME module

This module is for Oracle Support use only and can only be used in Oracle internal systems. Because it updates the data dictionary, and Oracle does not support this operation.

XHUME can be used to search for plans that can only be generated by older versions of Schema object statistics (related to a SQL). After creating a test case (TC) using SQLT, the XHUME module systematically restores the statistics from the previous version and generates the plan by executing the SQL under study. It will capture the plans that can be generated by each version of the statistics. Then generate a report that can be used to understand the reasons for the instability of the execution plan, or to find an execution plan that can be used to create a SQL Profile or SQL Plan Baseline.

This module can never be applied to a production system because it modifies the data dictionary. Can only be used in the Oracle internal test environment.

As an alternative to modifying the creation date of the test case (TC) Schema object, you can change the date on the server before the TC implementation and reset to the current date after the TC is created. This temporary previous date is at least one month earlier, so that all historical records of Schema object statistics will be saved at a new time than when the TC object was created.

Use XHUME only if all of the following conditions are met:

SQL is known to have multiple plans and one or more of them have poor performance.

Bind peeking has been eliminated for problems that do not lead to planning instability.

Changes to the CBO parameters have been eliminated that do not cause planning instability.

You have a SQLT TC ("good" or "bad" plan) that produces a known plan.

You need to be aware of the instability of the plan or are looking for a dedicated known "good" plan.

You have full access to the Oracle internal test system, including SYS access.

Do not use XHUME when any of the following conditions are met:

SQL statements can cause data corruption or update.

You can only run TC in one production environment.

You did not use SQLT to create a TC.

You do not have SYS access to the Oracle internal test system that contains your TC.

The Bind peeking or CBO parameters have not been excluded that do not cause planning instability.

To install and use this XHUME module, read the appropriate sqlt/utl/xhume/readme.txt.

Upload SQLT files to Oracle technical support

After SQLT runs, it produces an output file in zip format (this file contains various outputs related to SQLT). The file name of this file is generally:

Sqlt_s__.zip

If you have already executed a SQLT using a specific method, you can find the output file based on the name of the method. For example, the following file is generated after executing SQLT using the XTRACT method:

Sqlt_s45774_xtract_fp48hh6dkm529.zip

If the file name of the output file is similar to the following format:

Sqlt_s50605_log.zip

Then SQLT is likely not executed successfully, please check the error log in the log file.

A normal SQLT zip file typically contains the following files (for reference only):

$unzip-v sqlt_s45774_xtract_fp48hh6dkm529.zipArchive: sqlt_s45774_xtract_fp48hh6dkm529.zipLength Method Size Cmpr Date Time CRC-32 Name--0 Stored 0 040-09-2015 18:34 00000000 sqlt_s45774_10053_explain.trc0 Stored 0 004- 09-2015 18:34 00000000 sqlt_s45774_10053_i1_c0_extract.trc610 Stored 6100% 04-09-2015 18:35 51c54175 sqlt_s45774_cell_state.zip4187 Stored 4187 04-09-2015 18:35 ded1bbdd sqlt_s45774_driver.zip12814 Defl:N 2983 77% 04-09-2015 18:34 d359b6e7 sqlt_s45774_lite.html18879 Stored 18879 0409-2015 18:35 8270f836 sqlt_s45774_log.zip862169 Defl:N 121759 86% 04-09-2015 18:34 9c99af83 sqlt _ s45774_main.html3991 Stored 3991 04-09-2015 18:35 d42245f2 sqlt_s45774_opatch.zip12572 Defl:N 3645 71% 04-09-2015 18:34 2c6b2c0a sqlt_s45774_readme.html199784 Stored 199784 04-09-2015 18:35 93a85ee1 sqlt_s45774_tc.zip394 Defl:N 293 26% 04-09-2015 18:35 a405bd8b sqlt_s45774_tc_script.sql35 Stored 35 04-09-2015 18:35 4e1901f1 sqlt_s45774_tc_sql.sql52511 Stored 52511 04- 09-2015 18:35 2aee61c6 sqlt_s45774_tcx.zip406 Stored 406 0409-2015 18:35 6df4be4d sqlt_s45774_trc.zip216 Defl:N 11348% 04-09-2015 18:35 213361e5 sqlt_s45774_xpand.sql2319 Stored 2319 0409-2015 18:35 6f79e5b8 sqlt_s45774_sqldx.zip--1170887 411515 65% 16 files I believe that you have a deeper understanding of "what is the use of SQLT?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.