231. What
is the name of the default stats gathering job on 11g?
The automatic statistics
gathering job on 11g is called "auto optimizer stats collection".
232. What
are the default windows for the automated maintenance task to run?
In 11g daily maintenance
windows are provided. by default these are defined as :
Weeknights: Starts at 10
p.m. and ends at 2 a.m.
Weekends: Starts at 6 a.m.
is 20 hours long.
743507.1 How to Benefit from
Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in
11g?
233. How
do you change the default windows for the automated maintenance task to run?
Maintenance windows can be
modified using the DBMS_SCHEDULER PL/SQL package.
234. Whats
new/changed in 11g with respect to automatic statistics collection?
The GATHER_STATS_JOB does
not exist in 11g (the name does not exist). Instead it has been included in
Automatic Maintenance Tasks. The following are the tasks that AutoTask
automatically schedules in these maintenance windows:
select CLIENT_NAME from
DBA_AUTOTASK_CLIENT
CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats
collection
auto space advisor
sql tuning advisor
235. How
to enable auto stats collection?
If for some reason automatic
optimizer statistics collection is disabled, you can enable it using the ENABLE
procedure in the
DBMS_AUTO_TASK_ADMIN
package:
exec
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto
optimizer stats collection',
operation => NULL,
window_name => NULL);
236. How
to disable the auto stats collection?
In situations when you want
to disable automatic optimizer statistics collection, you can disable it using
the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
exec
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto
optimizer stats collection',
operation => NULL,
window_name => NULL);
237. How
can I check the status of the 'auto optimizer stats collection'?
The status of the automatic
statistics collection can be checked using:
select client_name,
JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB
where client_name='auto
optimizer stats collection';
The possible Job status:
DISABLED
RETRY SCHEDULED
SCHEDULED
RUNNING
COMPLETED
BROKEN
FAILED
REMOTE
SUCCEEDED
CHAIN_STALLED
238. How
can I check whether or not the database has the 'auto optimizer stats
collection' job enabled to run during the next maintenance window?
SELECT CLIENT_NAME, STATUS FROM
DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection';
239. How
can I see the history of the automatic stats job for each day?
SELECT client_name,
window_name, jobs_created, jobs_started, jobs_completed FROM
dba_autotask_client_history
WHERE client_name like
'%stats%';
JOBS JOBS JOBS
CLIENT_NAME WINDOW_NAME
CREATED STARTED COMPLETED
-------------------------------
---------------- ------- -------- ----------
auto optimizer stats
collection THURSDAY_WINDOW 1 1 1
auto optimizer stats
collection SUNDAY_WINDOW 3 3 3
auto optimizer stats
collection MONDAY_WINDOW 1 1 1
auto optimizer stats
collection SATURDAY_WINDOW 2 2 2
240. How
to manually execute the Optimizer Statistics Auto Task?
In 11g the Auto-Task
infrastructure replaced the need for the gather_stats_job and you can execute
the following command to accomplish manual statistics collection:
SQL> exec
DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
This will prompt the
Automated Maintenance Tasks subsystem into starting a job that will gather
optimizer statistics, unless such a job is already running (for example if a
maintenance window is currently open). If an immediate job is created it will
be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike
regular Automated Maintenance jobs, the "MANUAL" job is not tied to a
specific maintenance window.
241. How
do to check values of parameter( estimate percent, type of histograms etc) used
by the job?
DBMS_STATS.GET_PARAM (pname
IN VARCHAR2) RETURN VARCHAR2;
If there were any
non-default preferences set for the job:
DBMS_STATS.GET_PREFS (pname
IN VARCHAR2,ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
Possible preferences: -
AUTOSTATS_TARGET
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT
242. How
to set preference for the next maintenance job run to pick?
The automatic
statistics-gathering job uses the default parameter values for the DBMS_STATS
procedures. If you wish to change these default values you can use the
DBMS_STATS.SET_GLOBAL_PREFS procedure. Remember these values will be used for
all schemas including ' SYS' . There are 2 different procedures that you can
use to set parameters depending on whether you have existing preferences or
not:
SET_GLOBAL_PREFS - This
procedure enables you to change the default values of the parameters used by
the
DBMS_STATS.GATHER_*_STATS
procedures for any object in the database that does not have an existing table
preference or for any new objects created after this.
SET_DATABASE_PREFS - This
procedure enables you to change the default values of the parameters used by
the
DBMS_STATS.GATHER_*_STATS
procedures for all user-defined schemas in the database.
243. How to I change the "STALE
PERCENT", for example?
If the stale percentage does
not collect statistics frequently enough to reflect changes in the data
accurately, then you can use the
"DBMS_STATS.SET_GLOBAL_PREFS"
procedure to change this (and other parameters) so that statistics are
collected more frequently in future. For Example: To change the 'STALE_PERCENT'
you can use:
exec DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
Note that if you only want
to do this for a small subset of the objects and not all of them, you may be
better creating a custom statistics gathering scheme
244. Does
the auto stats gathering job gather statistics on all schemas in the instance?
By default 'auto optimizer
stats collection' is controlled by the Global preference AUTOSTATS_TARGET which
defaults to AUTO collecting all schemas including SYS. On 12c this includes
fixed object statistics, however, on 11g it does not.
Document 457926.1 How to
Gather Statistics on SYS Objects and 'Fixed' Objects?
245. What
is the AUTOSTATS_TARGET of SET_GLOBAL_PREFS?
This additional parameter
controls which objects the automatic statistic gathering job (that runs in the
nightly maintenance window) will monitor. The possible values for this
parameter are:
ALL -- This setting means that the automatic statistics gathering job
will gather statistics on all objects in the database. From 12c this includes
statistics on fixed objects. On 11g and
below this was APART FROM statistics on fixed objects. Since Fixed objects
record current database activity, the representative workload you want to
capture may not be active at the time of automatic statistics collection. You
should gather statistics when the database has representative activity. You can
manually collect statistics on fixed objects, such as the dynamic performance
tables, using GATHER_FIXED_OBJECTS_STATS procedure.
ORACLE -- ORACLE means that the automatic statistics gathering job will
only gather statistics for Oracle owned schemas (sys, sytem, etc)
AUTO (default) -- means that Oracle will decide what objects to gather
statistics on. Currently AUTO and ALL behave the same.
246. How does auto optimizer stats
collection prioritize which tables are analyzed first?
Accurate statistics are
important on all objects. The GATHER_DATABASE_STATS_JOB_PROC
procedure called by the 'auto optimizer stats collection' job prioritizes
database objects that have no statistics. This means that objects that most
need statistics are processed first. Once these are done then objects with
stale statistics are addressed. For these, there is no particular
prioritization. The statistics may be ordered in some way but it is cursory,
ordering by owner,object_name,part_name just to be consistent.
247. How do you disable automatic
statistics gathering for a specific schema?
You can do this by using DBMS_STATS.LOCK_SCHEMA_STATS
to lock the statistics. See:
Document 283890.1 Preserving
Statistics using DBMS_STATS.LOCK_TABLE_STATS
248. Is
there be any reason to gather full schema stats regularly in addition to the
automatic stats gathering job?
If your data changes very
frequently then it the automatic job may not collect the statistics frequently
enough (for example, it may be collecting other tables on occasion) and so you
may find that you get better statistics by manually collecting on that table to
keep up with the changes that are happening. There may also be cases where the
default sample size does not pick representative data and you need to select a
different sample.
249. What
is the name of the default stats gathering job on 10g?
The automatic statistics
gathering job on 10g is called "GATHER_STATS_JOB"
250. What
are the default windows for the GATHER_STATS_JOB ?
In 10g, Two Scheduler
windows are predefined upon installation of Oracle Database:
WEEKNIGHT_WINDOW starts at
10 p.m. and ends at 6 a.m. every Monday through Friday.
WEEKEND_WINDOW covers whole
days Saturday and Sunday.
Together these windows
constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks
are scheduled.
251. How
do you disable the GATHER_STATS_JOB?
The most direct approach is
to disable the GATHER_STATS_JOB as follows:
SQL> exec sys.dbms_scheduler.disable ('GATHER_STATS_JOB');
252. How
do you enable the GATHER_STATS_JOB?
This is enabled by default.
If you have disabled it, then you can re-enable it as
SQL> exec
sys.dbms_scheduler.enable ( "SYS"."GATHER_STATS_JOB");
253. How
do you Determine That the GATHER_STATS_JOB Completed
SELECT job_name, state FROM
dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';
There are four types of jobs
that are not running:
FAILED
BROKEN
DISABLED
COMPLETED
Note that if a job has
recently completed successfully, but is scheduled to run again, the job state
is set to 'SCHEDULED'. A job is marked as ' COMPLETED ' if 'end_date' or
'max_runs' (in dba_scheduler_jobs) is reached.
254. How
to Change the NEXT_RUN_DATE on the GATHER_STATS_JOB ?
You can adjust the
predefined maintenance windows to a time suitable to your database environment
using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure For Example:
begin
dbms_scheduler.disable('gather_stats_job');
dbms_scheduler.set_attribute_null('gather_stats_job','schedule_name');
dbms_scheduler.set_attribute(-
'gather_stats_job','repeat_interval',-
'freq=minutely;byminute=1,11,21,31,41,51;byhour=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,22,23;bysecond=0'-
);
dbms_scheduler.enable('gather_stats_job');
end;
to run job on weekends only:
SQL> exec
sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );
SQL> exec
sys.dbms_scheduler.set_attribute( name =>
'"SYS"."GATHER_STATS_JOB"', attribute =>'schedule_name',
value => 'SYS.WEEKEND_WINDOW');
SQL> exec sys.dbms_scheduler.enable(
'"SYS"."GATHER_STATS_JOB"' );
255. Are the ACS buckets based on
actual row processes OR estimated row process ?
ACS
is based on actual rows not estimated rows.
256. Should STATISTIC_LEVEL be ALL with ACS?
Statistics
level set to all is not required, it is only there so we can see the actual
rows in the execution plan.
257. What is the impact of ACS on the Shared pool?
ACS
will increase the shared pool requirements but it is difficult to say by how
much since it is dependent on the amount of variability in the plans in the
application
258. Is there any impact/or consideration when using ACS with
RAC database ?
No
259. In RAC, when one instance gets bounced, will it get back
the ACS cache from any other instance?
No.
The ACS information is unique to each node and is not shared from instance to
instance.
260. Does ACS work for the SQL from PL/SQL?
Yes.
ACS will work on the SQL started within PL/SQL
261. What is the suggestion to have the CURSOR_SHARING parameter
for ACS to be effective?
SIMILAR
is deprecated in 12c, so that is not recommended. 1169017.1 Deprecating the cursor_sharing =
'SIMILAR' setting
If
the application already uses bind variables throughout then EXACT is fine.
If
any literals are being used (either all literals or a combination of both
literals and binds) and you want to use ACS, then set the CURSOR_SHARING
parameter to FORCE. This will convert literal values into binds and allow the
cursors to be shared. ACS can then be used to make it possible for the same
query to generate and use different execution plans for different set of binds
values based upon the selectivity of those values.
262. Is there a plan to have persistent ACS cursors?
Development
are considering this and it may happen in the future.
263. Is ACS a separately licensed feature?
No.No
separate license is required Document 1361401.1 Where to Find Information About
Performance Related Features
264. Does ACS work with Standard Edition ?
Yes.
ACS is a standard feature of Personal, Standard and Enterprise Editions.
265. How does CURSOR_SHARING=FORCE/EXACT affect ACS?
CURSOR_SHARING
= FORCE converts all SQL statements that do not use bind variables to contain
bind variables so that they can be shared.
CURSOR_SHARING
= EXACT performs no literal replacement so ACS will work only on SQL that was
originally written to include bind variables.
ACS
works on all SQL containing bind variables regardless of whether they were in
the original SQL or generated by cursor_sharing = FORCE.
Adaptive
cursor sharing is independent of the CURSOR_SHARING initialization parameter
and is equally applicable to statements that contain user-defined and
system-generated bind variables.
266. When cursor_sharing=EXACT, will ACS still work?
Yes,
as long as the queries contain bind variables. Queries with literals are not
candidates for ACS.
267. Are there drawbacks or negative effects to using the
cursor_sharing=FORCE initialisation parameter for Oracle 11g database? Does
this have effects on ACS?
CURSOR_SHARING
= FORCE is a workaround to make an application scalable that otherwise would
not be because of literals. It is not however the optimal solution (which is to
write the application with bind variables in the first place), it is a
workaround - we are trying to fix a problem with a parameter. CURSOR_SHARING =
FORCE replaces literals with binds.
Pre-11g
(i.e. with no ACS) queries with bind variables were constrained to a single
execution plan per SQL. With 11g and ACS we can have multiple execution plans.
So there are no specific negative effects of CURSOR_SHARING = FORCE . With ACS
you may get more cursors than without ACS but these should be for different
plans based on the different values and their selectivities.
268. If an application uses literals and CURSOR_SHARING = EXACT,
will adding a "bind aware" hint help?
No.
ACS will not be used because there are no binds in the query.
269. Are there any recommendations with regard to the
SESSION_CACHED_CURSORS initialization parameter relate to ACS?
The
SESSION_CACHED_CURSORS parameter allows you to specify the number of cursors to
cache within an individual session. This can help performance where queries are
repeated within that session. Whether or not you need a larger session cache
with ACS depends on your application and how many extra cursors are being
stored with ACS as compared to without. It is difficult to
generalise
and while ACS is likely to increase the number of cursors for bind aware
cursors, it does not necessarily affect all queries and it may not increase the
number of cursors significantly overall. If it does, then increasing the cache
may help but only if individual sessions re-use the same cursors.
If,
for example, you have 100 different child cursors created by ACS and an
individual session only use one of these within a particular session then
additional session caching would be unlikely to help. If however the session
used bind variable values such that all 100 were used then additional session
caching may prove beneficial.
Generally,
the default value should be sufficient, but if you want to check your usage
then we would suggest using a script similar to the following to measure the
usage for a representative set of session and use that as a basis for any
decision:
Document
208857.1 SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS'
Parameters Based on Usage
If
you find that you are using more cursors than you have cached and that, upon
testing, a higher value is beneficial for your application, then a higher value
can be set.
270. How does the OPEN_CURSORS initialization parameter relate
to ACS?
The OPEN_CURSORS parameter
has nothing to do with ACS. It limits the number of cursor that can be kept
open in a session at the same time. Its purpose is to prevent a session from opening
an excessive number of cursors.
271. Does
bind peeking play a role in ACS?
Yes, it is one of the
pre-requisites. ACS needs to know the bind variable value so that it can use
column histograms to determine the selectivity of the bind variables that is
uses to determine if extra plans are required.
272. What
is the difference between bind aware and bind sensitive?
Bind sensitive means that a
query contains bind variables that are sensitive to different plans when the
bind values are changed. For example, for a particular bind variable, if data
is skewed then the number of rows returned by ValueA might be 10 but for ValueB
is 10,000. In this case a different plan may be appropriate for each value.
This query would be bind sensitive in that the plan chosen is sensitive to the
values of the bind variables supplied when the query is executed.
Bind aware means that the statement
has moved into ACS and is a candidate for multiple plans according to different
bind variables. This status is achieved by some checks to ensure that cursors
are not moved into ACS unless they are deserving of it so as to avoid using
extra space in the shared pool unnecessarily.
273. Why
is ACS restricted to 14 bind variables?
Development are wary of ACS
taking up too much space with large numbers of large cursors so they chose a
limit of 14 which is appropriate for the vast majority of queries. If this
limit is exceeded then the SQL will never become bind sensitive.
274. Why
are there hints to enable/disable bind aware features?
ACS is conservative in
making statements bind aware. If ACS made all queries move from bind sensitive
to bind aware in every case the shared pool would be flooded with ACS cursors
and there would be no benefit over creating a cursor for every different bind
value. There can be cases where the order that SQL is executed can mean that
the statement sometimes becomes bind aware and other times does not. If we want
it to be bind aware we can use the hints.
275. Can
we enable BIND_AWARE hint in any way for all connections from a user or
service?
No. It would be unlikely
that you would want to do that because it could impact too many sql statements.
It is better to use on only a few sql statements for where it provides a
benefit than globally where space and CPU would be wasted.
276. Should
ACS be disabled if the Bind Variable Selectivity Changes too frequently?
No. ACS is designed to
handle changes in bind variable values.
Lets consider that we have
SQL with a bind variable in a predicate against a column with a histogram and
the bind value is changed many times. In this case the selectivity may be
different for all or many (or none even) of thes values. When each value is
evaluated, if the selectivity is not within any of the ranges of the existing
selectivities already recorded against a bind aware query there will be some
overhead because it will have to make the child cursor non-shareable and create
a new child cursor copy the selectivity profile and increase the range of it.
This overhead occurs primarily in the ramp up phase. Once the system is stable
then the selectivities are less likely to be out of range it should just pick
plans according to the selectivity of the predicate.
277. What
does it mean for ACS when bind aware is true, but bind sensitive is false?
This is not a normal
situation. For a SQL to be bind aware it must first have been bind sensitive.
If you have this particular case then we suggest you open a SR with the
details.
278. Are
histograms always needed for ACS?
Histograms are required for
ACS to work upon SQL with equality predicates. Without histograms the
selectivity for an equality predicate against a column is 1 / Number of
distinct values in the column. This is the same for all values and thus ACS
could not differentiate between plans based on bind value. SQL with range
predicates (and LIKE) can work regardless of histogram because different
selectivities can be estimated for different values provided compared to the
position of the values relative to the high and low values in the column.
Obviously with histograms this information will be more accurate.
279. If
there is are histograms, how does Oracle determine that a SQL statement is bind
sensitive?
With Equality predicates it
cannot. However a SQL statement with range predicates ( > < >= etc)
has different selectivity regardless of the presence of histograms since the
optimizer generates selectivity estimates from the low/high column values
280. Can
ACS choose a Bad Plan when there is a better one available?
ACS creates plans based upon
selectivity ranges. It is still possible that a bad plan could be chosen for a
particular selectivity range but it is likely to be better overall than relying
on a single plan for all ranges.
281. Can
ACS make the CBO choose a bad plan?
No. ACS simply triggers a
new hard parse based upon certain trigger conditions. It is still up to the
optimizer to determine the plan based on the statistics it is provided.
282. Does
ACS impact SQLT output?
SQLT will report information
regardless of whether ACS used or not. SQL contains an Adaptive Cursor Sharing
section:
which will be populated with
various ACS related information if ACS is used.
283. Once
ACS has made a SQL statement non-shareable, will that plan never be used?
The child cursor will not be
used but the plan may be re-generated in other child cursor if it is re-parsed.
A Child Cursor becomes non-shareable because now that ACS is enabled and active
for this cursor, the new ACS Bind Aware plans need to be used.
284. Is
there a danger of generating a high number of version counts with ACS enabled?
High Version Counts can be
an issue regardless of ACS. That said however ACS does have potential to
generate more cursors than simply sharing 1 cursor for all different bind
values without it. It is considerably better than having a different cursor for
every single values like you would with literals. The number of cursors with
ACS should stay under a controlled number (you would not expect hundreds of
cursors with ACS) _ and is going to be somewhere between 1 cursor (bind
variables) and 1 cursor for every value (literals) but tending towards the
lower end of that due to the selectivity range model and the likelihood that
there are probably a small number of good plans for each SQL statement.
285. How
do SQL profiles play a role in ACS ?
SQL Profiles adjust CBO
calculations and impact the final plan. ACS doesn't impact the final plan
generated by the optimizer. It manages whether different plans are required to
handle different bind variable values. The goal of ACS is to make sure that a
new hard parse is triggered when a new plan is required.
286. What
is the relationship between ACS and Sql Plan Management (SPM) ?
ACS helps with in plan
flexibility. SPM helps with plan stability.
ACS allows there to be
different plans for the same cursor with bind variables, but limits this based
upon the selectivity of those bind variables rather than having a different
plan for every bind (as you would with literals).
SPM allows you to fix and
manage access paths for queries so that only a limited number of verified and
accepted plans are available for queries so as to avoid 'nasty suprises' such
as the generation of new bad plan.
287. If I
have some plan baselines that I am happy with, then I enable ACS which creates
some plans which look promising, how does SPM and ACS interract?
If you are happy with a new
plan then you need to determine whether to use them or not and accept the new
plans. the new plans will not be executed unless they are accepted.
288. What
would be the impact of ACS we're NOT using SPM and have Statistics locked
ACS can work regardless of
SPM or locked statistics. Different set of bind values could get still get
different plans.
With SPM, plans that have
not been accepted are not so ACS will simply generate plans that are waiting to
be accepted or rejected. The standard SPM plan will be used until that happens.
With locked statistics you
could still get different plans with different bind variable values until the
stored ACS plans have reached a certain level of maturity depending on the
relative selectivities of the bind variable values.
289. Using
SPM we can pin a good plan, can this be done in case of a situation demanding
better plan (among the child cursors) arises with ACS....
Yes. The cursors generated
by ACS will not have been accepted by SPM so SPM still applies.
290. What
is the SQL Tuning Health-Check Script?
The SQL Tuning Health-Check
Script is a tool developed by the Oracle Server Technologies Center of
Expertise. The tool,
also known as SQLHC, is used
to check the environment in which a single SQL Statement runs, checking checks
Costbased
Optimizer (CBO) statistics,
schema object metadata, configuration parameters and other elements that may influence
the performance of the one SQL being analyzed.
Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video
The intention of SQLHC is to
allow users to avoid SQL Performance from being affected by avoidable problems
by ensuring that the environment that an individual SQL runs in is sound.
It does this while leaving
"no database footprint" ensuring it can be run on all systems.
291. What
is the origin of SQLHC?
SQLHC is a subset of the SQL
used by the SQLTXPLAIN script:
Document 215187.1 SQLT
(SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly
but unlike that tool, has no
database footprint. It is a relatively
short SQL script that generates a HTML output. It extracts database statistic
information using selects or standard APIs (such as DBMS_XPLAN) from the
database and combines that with Automatic Workload Repository (AWR) output if
the correct licenses are available.
292. Is
SQLHC an 'advanced' version of SQLTXPLAIN?
Quite the opposite. SQLT is
far more advanced than SQLHC; while it incorporates similar checks to those
done in SQLHC
it is far more extensive
generally. SQLHC is simply a quick health check for a statement.
293. Are
the underlying selects used by SQLHC available?
SQLHC is simply a SQL
script. The source code is clear in the sqlhc.sql script
294. Where
can SQLHC be downloaded from?
See the following article
for details: Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)
295. How
can I identify the SQL I want to Healthcheck?
SQLHC uses the SQL_ID of the
desired statement to generate the Health check report. You can take the SQL_ID
from an AWR or ASH report or you can select it from the database. If you are
able to identify the SQL with a particular identifiable string or by some kind
of unique comment such as: /* TARGET SQL */ then this will make it easier to
locate. For example:
SELECT /* TARGET SQL */ *
FROM dual;
SELECT sql_id,
substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET
SQL */%'
SQL_ID SQL_TEXT
-------------
----------------------------------------
0xzhrtn5gkpjs SELECT /*
TARGET SQL */ * FROM dual
Document 1627387.1 How to Determine the SQL_ID for a SQL Statement
296. Does
SQLHC have to execute the SQL to generate a report?
No. SQLHC uses the SQL_ID of
a statement that has already been executed and is in memory to generate the
report. It can
also retrieve information on
the statement from the Automatic Workload Repository (AWR) if the correct
licenses are available.
297. What
if the SQL_ID in question has been aged out from memory?
In that case the SQL would
have to be re-executed because this tool is dependent upon having a current
SQL_ID.
Historical information could
then be extracted from AWR.
298. Can
SQLHC retrieve an explain plan from memory or the AWR?
Yes. SQLHC will access both
using the SQL_ID provided, assuming you have indicated that you have the
appropriate licenses.
299. Is
SQLHC safe to use in a production environment?
Yes. It has a very small
resource requirement and performs a rollback at the end of the script so that
no changes are made.
300. Is
SQLHC practical to use for complex sql statements?
Yes. SQLHC is a good, fast
way to check your SQL independent of the size of the SQL or the Application you
are using.
No comments:
Post a Comment