101. When
database is upgraded from 10.2 to 11.2, what is SQLT upgrade path?
If the database is upgraded
SQLT should work without any actions being performed. To be completely safe
re-execute spcreate.sql
102. If I
install SQLT on a production Db and it is later cloned, will SQLT work
"out-of-the-" box on the clone?
In the past this would not
work because there was a dependency on the bdump/udump directories. But today
it will check and refresh the pointers so it should work.
103. Is
any special setup needed to install SQLT on a server with multiple database
instances?
SQLT install is primarily about
installing SQLT into a particular database. If you have multiple databases you
have to install SQLT into each one where you have SQL statements you want to
analyze. You can, however, install SQLT into multiple databases from the same
install directory.
104. How
Frequently should SQLT be run?
SQLT is not designed to
check all SQL statements, rather, when you have concerns about the performance
of a single SQL statement then run it for that SQL.
SQLT takes a snapshot of the
SQL and the environment around it, so use SQLT when you want to analyze the
environment at a particular time. If SQL has intermittent performance issues
then run in SQLT to collect information from the good and the bad times. If it
works on one system and not another then run SQLT on both. Then compare the
information and use the differences to identify where the problem lies. When
used with AWR (with appropriate licenses), SQLT can also look at historical
data.
105. Can
install be scripted/automated?
The SQLT install process
itself is scripted in a script called sqlt/install/sqcreate.sql If you
have dozens of installations to do on multiple machines with the same answers
to the questions, then you may want to automate the install process. In the instructions
file, after it describes the installation steps it has options for an automatic
install with no user input giving you 2 options that you can follow to do this.
This can enable you to automate multiple installs.
106. Do
we need to worry about maintenance for SQLEXPLAIN tablespace?
The tool has a repository
which can grow over time. There is a purge script in the utl (utilities)
directory called "sqlthistpurge.sql" which can
purges a range of statements from the SQLT repository based on SQL_ID. But in
most cases the repository will not get large because we only store metadata NOT
application data.
107. I am
receiving unexpected SQLT Installation errors, what should I do?
If, during installation, you
receive unexpected errors then, as a first step you should re-download the
"sqlt.zip" file from a different browser and re-try the install as
sometimes this has resolved issues.
If that does not work then
most common issues are someting to do with the setup of the database unrelated
to SQLT itself. For example: if you have a password policy that enforces passwords must be at least 8 characters, then
do not give SQLT a password that is <8 characters because this will fail and
can cause all sorts of errors! Ensure that you examine all the install logs to
diagnose issues. There are some common questions and resolutions in the section
below.
Failing that, you can post
questions in our dedicated threads in the Database Tuning Forum:
SQLTXPLAIN: SQLT
Installation Issues
SQLTXPLAIN: Dealing with
Errors reported
SQLTXPLAIN: Dealing with
Errors reported in SQLT MAIN report
108. I am
receiving unexpected SQLT Runtime errors, what should I do?
The vast majority of errors
received when executing SQLT are nothing to do with SQLT itself, rather they
are problems with the environment itself that SQLT has detected. When you receive
an error, be sure check the logs that a produced (typically called sqltMODE.log)
to read the error message itself since this will often give vital clues as to
potential solutions.
For example, if you are
running a SQLT XECUTE on an insert statement and you receive the following:
SQL> START
run/sqltxecute.sql input/sample/script1.sql
SQL> INSERT INTO my_table
SELECT * FROM another_table;
INSERT INTO my_table SELECT
* FROM another_table;
*
ERROR at line 1:
ORA-00001: unique constraint
(MY_PRIMARY_KEY) violated
ORA-00001 indicates that a
unique constraint has been violated. If the insert in question has been
executed more than once, it is possible that the data has already been inserted
and so violates the primary key conditions. This error is nothing to do with
SQLT, you would get the same error running the SQL standalone in a completely
separate session. The solution in this case is to insert unique data. If you
encounter errors you cannot resolve, please log a SR with support, add a
comment to this document or contact the author.
I receive "ORA-01924: role 'SQLT_USER_ROLE' not
granted or does not exist"
When running various SQLT
methods (XPLAIN, XECUTE etc.) for the first time by a user, you may receive:
SET ROLE sqlt_user_role
* ERROR at line 1:
ORA-01924: role
'SQLT_USER_ROLE' not granted or does not exist
Typically, this error occurs
because you have not granted the 'SQLT_USER_ROLE' role to the user you are
trying to run the SQLT as. Certain methods (XTRACT, XECUTE Method etc) require
the application user executing SQLT to be granted the 'SQLT_USER_ROLE' role. To
grant the SQLT_USER_ROLE, execute the following connected as SYS:
GRANT SQLT_USER_ROLE TO
@Application_User;
connected as SYS.
During SQLT Installation,
the sqguser.sql is executed against the "application user" you have
specified. If you are using SQLT with another user this error will occur if you
have not executed the script.
I receive "ORA-00942: table or view does not
exist" during SQLT install
When ORA-942 errors are
encountered, such as:
ORA-00942: table or view
does not exist
These indicate that a table
or view that SQLT is expecting to exist at this point does not. There are a
number of reasons this can occur so to diagnose and resolve the cause of the problem
you need to review the NN_*.log files created during installation and fix the
errors reported.
Being that ORA-942 indicates
that a "table or view does not exist", look for a reason in the log
why the object in question may not have created such as insufficient space or
quota (or similar) to create all the required objects in the original
tablespace .
I see PLS-00201 and ORA-00904 errors in the Log
The SQLT log actually
contains 2 error messages as an example:
***
*** 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.
***
The example simply points
you towards a potential resolution IF you actually see these errors elsewhere
in the log. If you do not receive the actual errors then you can ignore this
advisory message.
If you do see a
"real" PLS-201 in the log, then you need to review the NN_*.log files
created during installation.
PLS-201 means that :
"You tried to reference either: an undeclared variable, exception,
procedure, or other item, or an item to which no privilege was granted
or an item to which
privilege was granted only through a role."
This usually means that
there are missing privileges on the object in question for a particular user. You
should be able to determine which user from the log and fix the errors
reported. For example, the following shows a PLS-0021 being raised against
'SYS.DBMS_METADATA':
SQL> SELECT
owner,name,type,text FROM dba_errors WHERE name = 'SQLT$I' AND type ='PACKAGE
BODY';
OWNER NAME TYPE TEXT
---------- ------
------------ -----------------------------------------------------------
SQLTXADMIN SQLT$I PACKAGE
BODY PLS-00201: identifier 'SYS.DBMS_METADATA' must be declared
SQLTXADMIN SQLT$I PACKAGE
BODY PL/SQL: Statement ignored
SQLTXADMIN SQLT$I PACKAGE
BODY PLS-00201: identifier 'SYS.DBMS_METADATA' must be declared
SQLTXADMIN SQLT$I PACKAGE
BODY PL/SQL: Statement ignored
SQLTXADMIN SQLT$I PACKAGE
BODY PLS-00201: identifier 'SYS.DBMS_METADATA' must be declared
SQLTXADMIN SQLT$I PACKAGE
BODY PL/SQL: Statement ignored
Assuming that
SYS.DBMS_METADATA is present and compiled on your system then it is likely that
it is missing permissions or a synonym. To resolve this (and similar errors on
other packages), GRANT EXECUTE on the package to PUBLIC and create a public
synonym with the same name. If not installed or is invalid, then install and
compile it first.
During a 10g install, I receive "PLS-00920:
parameter plsql_native_library_dir is not set" during SQLT install
During a 10g install, if you
receive the following:
Errors for PROCEDURE
SYS.SQLT$_TRCA$_DIR_SET:
LINE/COL ERROR
--------
-----------------------------------------------------------------
318/3 PLS-00920: parameter
plsql_native_library_dir is not set
Then this means that the
"plsql_native_library_dir" parameter value is unset on the instance.
This parameter is needed to allow the SQLT code to work.
To resolve the issue, define
a directory path for the PLSQL_NATIVE_LIBRARY_DIR parameter with ALTER SYSTEM,
and then restart the installation. You can find details of the parameter in the
Oracle Documentation: PLSQL_NATIVE_LIBRARY_DIR. The directory path that you
specify should be readable and writable by the account used to install the
Oracle software installer.
This error should not appear
on 11g or higher.
During SQLT Installation, I receive ora-3113/3114 errors,
what should I do?
ORA-03113 and ORA-03114
errors are connection type errors that indicate that the client is not able to
communicate with the server:
ORA-03113: end-of-file on
communication channel
ORA-03114: not connected to
ORACLE
If you receive either of
these errors during installation or runtime SQLT, then this indicates that your
process has been disconnected from the server for one of a huge number of reasons.
To determine the cause you need to follow the normal steps for diagnosis of
these errors, which is to examine the alert log for errors and traces around the
time of the error and resolve them. These is comprehensive coverage of these
errors on MOS, a good starting point is:
Document 1506805.1 Master
Note: Troubleshooting ORA-03113
109. Is
the SQLT tool the same for all versions?
No. SQLT has been developed
over time and is currently on its 3rd revision which was designed for Oracle
10g Release and above.
For 9.2 and 10.1 instances,
a separate version is available from the Central SQLT article.
110. Can
SQLT be used on Oracle 9i and 10gR1?
Yes. For 9.2 and 10.1
instances, a separate version is available from the Central SQLT article.
111. What
is the licensing requirement for SQLT?
SQLT requires no license and
is FREE. SQLT can use data from the Oracle Diagnostic and/or the Oracle Tuning
Packs if your site has a license for them. These two packs provide enhanced
functionality to the SQLT tool.
During SQLT installation you
can specify if one of these two packages is licensed by your site. You can
specify "T" for Oracle Tuning Pack, "D" for Oracle Diagnostics
Pack or "N" for none (note that the Tuning pack license includes the
Diagnostics pack).
If "T" or
"D" is selected, SQLT may include licensed content within the
diagnostics files it produces. The Default is "T".
If "N" is
selected, SQLT installs with limited functionality but still provides some
basic information that can be used for initial SQL performance diagnostics.
With no packs SQLT will not use anything from AWR nor the tuning pack; so no
ADDM, AWR, ASH and no SQL Tuning Advisor output will be included. You should
check with your site license manager if you have doubts regarding the licensing
arrangements with regard to Oracle packs. SQLT does NOT check for a valid
license.
112. How
do I check in the DB whether or not tuning and diagnostic packs are installed /
available based on licensing
Most packs are installed by
default because the database itself uses the AWR repository. The license allows
you to use the AWR repository. There is no way of checking if you have such a
license in the database. The Diagnostic Pack is a Subset of the Tuning pack so
if you have Tuning you have the diagnostic pack.
113. What
is missing from the reports if the Tuning Pack is not installed?
SQLT uses packs to give it
access to AWR (Automatic Workload Repository) based information (AWR
information is accessible via the Diagnostic pack; the Tuning pack is a
superset of the Diagnostic pack so either will provide access to this
information). The AWR infrastructure is installed by default (because the
database uses AWR information for internal purposes), the real question is
whether you are licensed to use it or not. SQLT uses AWR to provide historical
information regarding the execution of SQL. So, for example, if a plan had
changed over time, it would use AWR information to report on that. If AWR
information is not made available by the presence of a license for one of the
aforementioned packs, then the AWR related information will not be reported.
In addition the Tuning Pack provides access to
the SQL Tuning Advisor. Without pack licenses, SQLT still provides some basic
information that can be used for initial SQL performance diagnostics.
114.When using SQLT, is it more beneficial to have AWR than not
having AWR?
Yes,
it is more beneficial to use AWR alongside SQLT. AWR information is a component
of the diagnostic pack and is fundamental for performance diagnostics and
interpretation. Generally, if you do not have AWR then you would need to use
statspack but this is of limited use on later versions. If you have enterprise
editions it is very useful to have AWR to diagnose performance issues.
Specifically
for SQLT, having AWR information provides more usable diagnostic output than
without, so we would recommend it but it is not a pre-requisite.
115.What
platforms does SQLT work on?
SQLT is platform
independent. SQLT uses SQL Scripts to generate output files. The SQLT report is
in html format so any browser capable of displaying html will be able to
display it.
116. Does
SQLT capture All the SQL from a Database or capture specific schemas only?
SQLT works on a single SQL
statement at a time and captures only ONE specific SQL_ID or HASH_VALUE
117. Is
SQLT able to analyze many SQLs simultaneously (e.g. from a SQL Tuning Set)?
No. SQLT works on a single
SQL statement at a time and captures only ONE specific SQL_ID or HASH_VALUE. If
you want to perform comparative baseline analysis you should look at SQL Plan
Management in conjunction with the SQL Performance Analyzer and Real
Application testing (dependent on your requirements).
118. If
we have multiple SQLs in a session, how do we use SQLT one by one?
SQLT works on a single SQL
statement at a time. You would need to supply the SQL_ID or HASH_VALUE for the
SQL you are interested in from the session. Once you have analyzed one SQL_ID
you can move it on to the next.
120. If
we have multiple reports for the same query where the plan has changed, can
these be compared?
In this case use the COMPARE
method. This allows you to take two similar sources but the same SQL statement
performs fine in one of them but not in the other. This method helps to
pin-point the differences between the two SOURCES in terms of plans, metadata,
CBO statistics, initialization parameters and bug fix-control. See the COMPARE
Section in the sqlt/sqlt_instructions.html file included in the zip file.
121. Can
we use this tool for PL/SQL code?
You can use SQLT on PL/SQL
but if the focus is on a specific SQL statement, if you don't know where
exactly the time is spent then 10046 or PL/SQL profiling may work better. Instructions
on the use of the profiler can be found in the following article:
Document 243755.1
Implementing and Using the PL/SQL Profiler
122. Where
can I find the SQL_ID or HASH_VALUE?
The SQL_ID can be found on
an AWR report, and the HASH_VALUE on any SQL Trace (above the SQL text and
identified by the "hv=" token). You can also find these columns in
the V$SQL view using a select similar to:
select SQL_ID, HASH_VALUE
,substr(SQL_TEXT,1,30) Text from V$SQL where SQL_TEXT like
'%&Some_Identifiable_String%';
123.Can
SQLT take the PLAN_HASH_VALUE as input?
No, it uses the SQL_ID or
HASH_VALUE (not PLAN HASH VALUE)
124. Can
it be used on Queries containing Multiple Tables, Views etc?
Yes. SQLT supports all valid
SQL statements.
125. Can
it be used where SQL references objects from multiple schemas?
Yes. Note that when creating
a SQLT testcase, SQLT will condense the objects from different schemas into a
single remapped schema
126. Does
SQLT work across multiple databases via a dblink?
Yes, SQLT works in a
distributed environment
127. Does
SQLT handle literal values and bind variables?
Yes. Because bind peeking
can affect plans, SQLT will report bind variable history and the different
plans that different binds have generated.
128. How
does SQLT handle bind variables?
Bind variable values are
stored in metadata and are associated with any different plans that they
generate.
129. Does
SQLT provide formatted 10053 output?
SQLT collects 10053 trace
but it does not re-format it. 10053 is an internal trace and it is not
documented. It was created by development for development to analyse issues
with the cost based optimizer. It is included in SQLT output for completeness
and so that we have the trace in the event that there is a need to engage
Oracle support or development.
130. Can
I use previously generated trace file(s) such as 10046 or 10053 trace file(s)
as an input to SQLT?
No SQLT works from a SQL_ID
and it generated the information from the database at the time the SQLT is
executed. It does not re-format 'old' 10046 or 10053 trace
131. How
do you execute the different SQLT Methods?
The different methods are
initiated with individual scripts, one for each method. For example:
XTRACT -
sqlt/run/sqltxtract.sql
XECUTE -
sqlt/run/sqltxtract.sql
XTRXEC -
sqlt/run/sqltxtrxec.sql
XPLAIN -
sqlt/run/sqltxplain.sql
A detailed description of
the difference is in the "sqlt_instructions.html" document included
in sqlt.zip
132. What
is the difference between XTRACT, XECUTE, XTRXREC and XPLAIN?
These are the different
methods by which you may execute the SQLT tool.
A detailed description of
the difference is in the "sqlt_instructions.html"
document included in sqlt.zip
133. What
should you run first XTRACT or XECUTE?
Since the XECUTE method
provides runtime information, that is going to have more information that
XTRACT, however it depends on the circumstances. If the SQL does not complete in
a timely fashion then XTRACT is more suitable. If the query does not take too
long to run then the Execute option is going to give you more information.
Additionally, EXECUTE mode contains various segment and session information
that cannot be delivered with XTRACT so we would suggest you try to get Execute
if possible.
134. If
the SQL in question does not complete, is SQLT still useful?
If the problem SQL statement
does not finish, then SQLT can still give you useful information. What it can
provide depends on whether the cause of the delay is generating the access path
or executing the query.
If the problem is at
execution time then SQLT can still produce information about the access path
that will be used. Additionally the SQLT calls to SQL monitor can provide you
with partial execution information which can prove useful even though it is not
complete. 11g XTRACT will give you the statistics for the execution even though
it is not complete.
If the query does not
complete parsing, then the optimizer has not finished choosing an access path
for the query so SQLT will only get limited information. Since SQLT is
primarily a tool for displaying the results of generating an access path
alongside the supporting information used to do that, you would probably be
better off using something else to trace why the generation of the plan took
too long (this will likely require interaction with support who will likely
request traces such as 10046 and errorstack to start diagnosis of the issue).
135. Does
sqlt actually execute the SQL statement or will collect info about executed
statements only?
This depends on the mode
used. For example, XTRACT will pick up previously executed statements, XECUTE
will execute the statement
136. Is a
hard parse of the analyzed SQL mandatory for the XECUTE method? Is this the
purpose of the "/* ^^unique_id */" comment?
Yes. The hard parse is
forced so as to guarantee the generation of the 10053 report.
137. Can
SQLT be used in a distributed environment?
Yes, we can use SQLT in a
distributed environment. To do this, store SQLT on the remote node or nodes and
the main node. When run on the main node, SQLT can then connect to the remote
nodes and produce SQLT extracts from the remote nodes and package everything
together.
138. Is
the functionality provided in SQLTXPLAIN also available in RAT (Real
Application Testing)?
No. SQLT functionality is
not available in RAT, they are two separate entities. SQLTXPLAIN is tool to
enable users to analyze and tune the performance of a single SQL statement. The
Real Application Testing option is a licensed product that allows potential issues
with system changes to be identified before they are deployed in a production
environment.
139. How
does SQLT get historical information regarding SQL statements?
If you are licensed to use
AWR then SQLT can access AWR to access historical information.
140. Does
SQLT contain any graphical output?
As its primary output, SQLT
generates an HTML report (along with a number of supporting files in various
formats). There are no 'graphics' or 'graphs' as such. SQLT in general presents
text output related to the SQL in question grouped and formatted for ease of
use.
141. Can
we use SQLT to identify different output for SQL on different clients?
Yes. SQLT can help in
diagnose this kind of issue. If a different plan is generated then the
information related to that plan is recorded and can be compared to determine
what is making the difference.
142. Can
you set a time for when to run sqlt. For example, for the case where the query
only runs slowly in the middle of the night?
There isn't a facility
directly withing SQLT that allows this automation. You could obviously automate
it with external tools. However, since SQLT is able to look at the execution
history, as long as the execution details are still in the history, then there
is no need to execute SQLT at the time of the issue. In this example, you
should be able to come in during the morning after the issue and use the SQL_ID
to get the run details.
143. How
do I analyze the report?
If a SQL statement takes a
long time, then collect information via SQLT. You then need to analyze the
information which requires SQL Tuning expertise. If you do not have that expertise
then consider getting someone else to help you such Advanced Customer Services
or other consulting options or use the SQL Tuning Advisor (assuming that you
have the required licenses).
144. What
are the main things to look for in the observations section?
The key findings in the
observations section are different from case to case. Something that is
unimportant in one report may be crucial for another. For example, an index may
have been dropped on one of the tables in a query. This might change the access
path in some queries but have no affect on others. Because of this there is no
way to mark something one finding as as more critical than another. The
observations are simply presented as a "heads up" for evaluation as
otherwise there is a possibility of supplying misleading information.
145. Is
information from memory 'better' than the information from the DBA_HIST_*
views?
Both the information from
memory and from AWR that SQLT uses may be aggregated. This means that if a
single execution performs differently but uses the same plan then it may be
difficult to identify that. In that case the AWR SQL Report is useful. See:
awrsqrpt.sql from: Document 748642.1 How to Generate an AWR Report and Create
Baselines
146. If
the database structure has not changed, why is there more than one explain plan
in SQLT?
There are many reasons for
the same SQL to generate different plans. One of these of course is that the
underlying structures have changed but others include using different bind variable
values and histograms to drive different execution plans, multiple sessions
using slightly different optimizer 'versions' by changing parameters and data
types or data lengths differing within one application as compared to another
application. Document 296377.1 Troubleshooting: High Version Count Issues
147. How
can I implement an explain plan suggested by SQLT?
SQLT does not suggest
execution plans - SQL Tuning Advisor does that. If you have the Oracle Tuning
Pack license then SQLT can execute SQL Tuning Advisor and this may suggest a better
plan for the SQL. You can implement that via a SQL_profile.
If you want to fix a
particular plan that has already been detected then see review the dynamic
readme generated by any of the main methods and look for "Create SQL Plan
Baseline from SQL Set". This documents using SQL Plan Management SPM
through a SQL Set. See: Document 271196.1 Automatic SQL Tuning - SQL Profiles.
148. How
do I delete an existing SQL Profile created by SQLT?
As mentioned, SQLT does not
create profiles - SQL Tuning Advisor does that. If you have the Oracle Tuning
Pack license then SQLT can execute SQL Tuning Advisor and this may suggest a
better plan for the SQL. You can implement that via a SQL_profile. To delete a
profile you use the standard procedure for deleting any SQL profile.
149. Does
SQLT take a lot of Database resources to execute?
The amount of resource used
depends on the method used. Some methods just extract plans from the repository
and so are relatively light. Other methods (such as the XECUTE method) may need
to run the SQL which means the resource taken is at least the resource needed
to execute that statement. Usually the benefit of executing and capturing the execution
of the statement is much higher than the few minutes it takes to run when
compared to not having that information.
150. How
much memory does SQLT use?
SQLT is not resource
intensive. It is just a script. If it runs for 10 mins then its the equivalent
of running any other script for 10 mins.
151. Does
SQLT work with --Insert application here--
SQLT is a series of SQL
scripts and a repository that uses SQL commands run against the data dictionary
and the repository to produce a report. It can work against any SELECT generated
by any application software.
152. Are
there any limitations on using SQLT in a Database running EBusiness Suite?
No.
153. Does
SQLT work with RAC and Exadata?
Yes. SQLT is RAC and Exadata
aware
154. Is
any Exadata specific information captured in report for the SQL?
Yes some parameters and IO
calibration is captured but not much else. From the SQL Tuning point of view
Exadata is not very different to a standard database.
155. What
is the difference between SQLT and the SQL Performance Analyzer.
The tools were designed for
totally different reasons. SQL Performance Analyzer is part of Real Application
Testing (RAT) suite and enables you to assess the impact of system changes on
the response time of SQL statements. SQLT is to help you understand what the
root cause of an issue with a particular statement is.
156. Is
SQLT integrated into Oracle Enterprise Manager (OEM)/Grid Control?
No. SQLT is a standalone
tool but will work alongside these tools.
157. Why
Would you want to use SQLT when you have OEM?
OEM and SQLT performs two
different job and are not mutually exclusive Oracle Enterprise Manager Database
Control is the primary tool for managing your Oracle database. SQLT is a tool
specifically for facilitating the tuning of individual SQL statements.
158. What
is the SQLT Testcase Generator?
When SQLT gathers
information about a candidate SQL statement, it automatically builds a testcase
to enable the current plan in the SQL to be reproduced. It does this by storing
the metadata of all the objects involved (including views and materialized
views) in the query in a testcase schema so that it can be recreated on a test
schema. No user data is stored, but the optimizer statistics are recorded so
that the plan can be recreated. Most of the time data is not necessary to
reproduce the plan (typically,it is only necessary to reproduce performance or
incorrect results). See the following for more details:
Document 1470811.1 How to
Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data
Document 1465741.1 How to
Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data
159. Does
SQLT Testcase generate user data?
No. It records the structure
and the statistics only. These are all we need to reproduce the access path.
160. In
order to reproduce a SQLT Testcase, is the same hardware required on each
server?
Usually no. SQLT records
system statistics and these are usually sufficient to make the target system
"think" that it is the same as the source
161. Can
I have multiple system stats on a test system?
SQLT does not contain a
specific facility to load up multiple sets of system statistics, nor is there a
history for system statistics. You can however save and restore different
system statistics manually but there is only one set of system stats available
at any time. See: Document 149560.1 How to Collect and Display System
Statistics (CPU and IO) for CBO use
Document 470316.1 Using
Actual System Statistics (Collected CPU and IO information)
Document 153761.1 System
Statistics: Scaling the System to Improve CBO optimizer
Document 427939.1 System
Statistics: How to gather system stats for a particular batch of work
162. Do
we need to take a backup before running SQLT?
It is generally prudent to
backup before installing any software on a production system.
With a Testcase generated
from SQLT, since this is usually going to be installed in a test system, there
is not usually any specific need to backup any data. Note that if the system statistics
of the target are going to be changed to replicate a plan, then you may wish to
save these before modifying them.
163. Can
you use SQLT to analyze a SQL statement just with a source system?
It is best to analyze SQLT
data on a source system since that is where the runtime information for the
statement is recorded.
In terms of a testcase you
would probably be better running that on a test system as opposed to
production.
164. Can
we use SQLT testcase to reproduce Oracle materialized view issue on a Test
system?
Yes. All metadata is
transferred to a special schema for the testcase together with the associated
statistics and environment etc to allows us to reproduce the plan. No Data is transferred.
165. Are
there any special considerations when working with dictionary and fixed
objects? Do they map to one schema too?
Dictionary/fixed objects are
handled just the same as any other objects; if you have queries containing
dictionary/fixed object then they are extracted into a special schema for the testcase
together with the associated statistics and environment etc to allows us to
reproduce the plan. No Data is transferred.
166. If
we use Real Application Cluster (RAC) for our Production database, and no RAC
for our Development database, then does the testcase still reproduce the SQL
Plan in the Development database ?
Yes. You can reproduce the
same plan in DEV regardless of RAC or not.
167. Is
the Objective of SQLT Testcase to only reproduce the same execution plan, not
help to retrieve the best execution plan?
SQLT Testcase reproduces the
same execution plan so that you can then use tools to determine a better plan
if that is possible. Tools such SQL Tuning Advisor enable that and SQLT integrates
that if you have the relevant licenses.
168. In
SQLT Version 11.4.4.8 and below "!=" is converted into "=",
potentially giving wrong information?
This issue is fixed in SQLT
Version 11.4.4.9 and above. As a workaround convert instances of "!="
in the input SQL into "<>". To find your current SQLT Version,
run the following SQL as the SYS or SQLTXPLAIN user:
COL sqlt_version FOR A40;
SELECT
'SQLT version number:
'||sqltxadmin.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date :
'||sqltxadmin.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date :
'||sqltxadmin.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL
/
SQLT_VERSION
----------------------------------------
SQLT version number:
11.4.4.8
SQLT version date :
2012-09-27
Installation date :
2012-10-24/09:56:12
169. What
is the CBO?
CBO is the Cost-Based
Optimizer. The Cost-Based Optimizer is built-in software that determines the
most efficient way to execute a SQL statement using statistics to determine the
most optimal access path for queries.
170. What
is AWR?
AWR is the Automatic
Workload Repository. Document 1363422.1 Automatic Workload Repository (AWR)
Reports - Start Point
No comments:
Post a Comment