171. In
the SQLT report, why is the "Last rows" not "Actual rows"?
"Last rows" is
used because those numbers are related to the last execution. If this varies
for some reason then this information may be important. The data is the same
you can get from RUNSTATS_LAST in DBMS_XPLAN.DISPLAY_CURSOR,
Document 215187.1 SQLT
(SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly
172. What
is new in this SQLT release?
A summary of recent SQLT
changes can be found in file: "sqlt/doc/sqlt_changes.html"
173. How
do I make a SQL statement use a plan that I can only achieve by using CBO Hints?
The question assumes you
cannot modify the original SQL for whatever reason. You want to
"capture" the plan from a modified version of your SQL (the one with
CBO Hints) and associate this "good" plan to the original un-modified
SQL.
If on 11g consider a SQL
Plan Baseline by using "sqlt/utl/coe_load_sql_baseline.sql".
If on 10g consider a custom
SQL Profile by using "sqlt/utl/coe_load_sql_profile.sql"
174. ORA-00942:
table or view does not exist: Error at line 1: SELECT prev_sql_id,
prev_child_number FROM sys.my_v$session
Using XECUTE method and
getting error above. It means the "script.sql" passed to this method
is bogus. Your script must be able to execute stand-alone without errors.
175. "ORA-07445"
or "ORA-03114: not connected to ORACLE".
Review
alert.log and the trace referenced by this error. If failed SQL uses
V$SQL_PLAN, then there is a database bug in your system. You can pursue a fix
logging a ticket and providing alert.log and the referenced trace. SQLT can
workaround this bug with some loss of functionality:
EXEC
sqltxadmin.sqlt$d.set_param('predicates_in_plan', 'N');
If alert.log includes error :
BEGIN
CTXSYS.CTX_REPORT.CREATE_INDEX_SCRIPT(index_name => :name, report =>
:report); END;
then
try SQLT for a 2nd time. If problem persists, then apply following workaround
with minimal loss of functionality:
EXEC
sqltxadmin.sqlt$d.set_param('domain_index_metadata', 'Y');
176. How
do I analyze a distributed query?
Install this SQLT tool in
your local and remote nodes. Then on your local node make SQLT aware of the
remote nodes by registering the database links your SQL or the dependent objects
referenced:
EXEC
sqltxadmin.sqlt$i.register_db_link('db_link_name');
177. XTRACT
or XECUTE methods are taking too long.
Review corresponding log
file. The culprit could be the 11g Test Case Builder TCB and/or the SQL Tuning
Advisor STA. These two utilities are usually time consuming. You can sacrifice
their functionality and improve the performance of SQLT by disabling SQLT
access to the TCB and reducing the threshold time for the STA:
EXEC
sqltxadmin.sqlt$a.set_param('test_case_builder', 'N');
EXEC
sqltxadmin.sqlt$a.set_param('sta_time_limit_secs', '30');
If SQLT still takes a long
time, and the schema objects behind your SQL contain a large number of sub-partitions,
you can reduce the granularity of the data collected:
EXEC
sqltxadmin.sqlt$a.set_param('c_gran_segm', 'PARTITION');
EXEC
sqltxadmin.sqlt$a.set_param('c_gran_cols', 'PARTITION');
EXEC
sqltxadmin.sqlt$a.set_param('c_gran_hgrm', 'PARTITION');
178. How
do I register additional SQLT users?
SQLT users must 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;
179. Can
I change the SQLT staging directory?
Yes, use
"sqlt/utl/sqltcdirs.sql" passing the full directory path. This path
cannot contain the "?" or "*" symbols and it is case
sensitive in most systems. This directory must exist in the server and ORACLE
must have read/write access to it. The recommended directory is UDUMP. Aliases
are not allowed.
180. How
to suppress the export of the SQLT repository?
SQLT repository is needed to
generate a Test Case TC and to troubleshoot a query performing poorly. If you
are sure the exported repository is not needed, use this command to disable its
automatic export:
EXEC
sqltxadmin.sqlt$a.set_param('export_repository', 'N');
181. What
is my SQLT tool version?
SELECT
sqltxadmin.sqlt$a.get_param('tool_version') FROM DUAL;
182. How
to keep the SQLT repository small?
You can purge the SQLT
repository periodically by using provided script:
"sqlt/utl/sqlthistpurge.sql
183. How
can I expedite "SELECT COUNT(*)" on application Tables?
If you look at the SQLT log
file and identify that most time is spent when SQLT is performing a COUNT(*) in
all related application tables, then you can suppress this operation with minimum
functionality impact:
EXEC
sqltxadmin.sqlt$a.set_param('count_star_threshold', '0');
184. How
can I expedite "Taking a snapshot of some Data Dictionary objects"?
A snapshot of some Data
Dictionary objects takes between a couple of minutes and up to several hours,
depending on the size of the data dictionary. This is the last step of the installation.
If killed, it will be automatically executed when XECUTE is first used.
To expedite the snapshot
time, and with some loss of functionality, you can disable the snapshot of
extents by executing these commands connecting as SQLTXPLAIN:
SET SERVEROUT ON;
EXEC
trca$g.set_param('capture_extents', 'N');
EXEC trca$t.refresh_trca$_dict_from_this;
If still "Taking a
snapshot of some Data Dictionary objects" takes longer than several hours,
it can be disabled altogether with significant loss of functionality, by
executing these commands connecting as SQLTXPLAIN:
SET SERVEROUT ON;
EXEC
trca$g.set_param('refresh_dict_repository', 'N');
EXEC trca$t.refresh_trca$_dict_from_this;
185. What is the difference between a SPM
Baseline, a SQL Profile and a SQL_PATCH?
SPM baselines :
Enforce specific plans and
Guarantee plan stability
Contain multiple plans
Can record new plans for
potential improvement
Only matches a whole SQL
statement in its entirety
Only accepted plans are used
A SQL Profile is
generated by SQL Tuning Advisor and:
Does not enforce a specific
plan
Does not guarantee plan
stability
Does not contains a plan at
all, whereas SPM can contain multiple plans
Is a collection of
directives (generated by STA based on empirical information) which can be
applied across many SQL statements to influence the optimizer to hopefully produce
a better plan.
Because no plans are
recorded, if there is a change that results in a new, better, plan, then that
plan would be lost if the change was backed out.
Can be configured to match
part of multiple SQL statement (ie can apply to SQL strings which contain all
the same characters)
If there is already a SQL
plan baseline in place when a SQL Profile is created, the new plan is added to
the baseline
SQL_PATCH:
A SQL patch circumvents a
failure by causing the query optimizer to choose an alternate execution plan
for future executions.
Does not contain a plan -
essentially it generates a hint to avoid a problem (in the short term)
Generated as a workaround
via the SQL Repair Advisor
With a baseline, a new plan
that is better than the one in the baseline will not be applied but with be
stored in the history and not lost. You can then manually check and apply the
new plan or schedule the API to check to see if the plan in the history is
better. This provides the flexibility to evolve if necessary while providing
stability in the meantime.
Additionally, with SPM, the
plan is applied as a whole or not at all. For example if an index is dropped
with a profile, the hint pertaining to the index will just be ignored and all
the other hints applied. With a SPM baseline there is verification of whether
the plan you want to force has been reproduced or not. If not a new plan will
be calculated and no remnants of the previous plan
are used (so you are less
likely to get a situation where a nested loops join is used to drive a full
table scan like you might get if a partial hint is applied).
You can find more on the
differences in the following blog post: What is the difference between SQL
Profiles and SQL Plan Baselines?
If a baseline is present for
a particular SQL ID, then a profile or a SQL_PATCH will only have an effect if
the plan generated by the profile or the SQL_PATCH is Enabled and Accepted
within the baseline.
186. Can there be Multiple baselines?
Yes. SPM baselines allow
multiple plans to be associated with a particular SQL statement. A baseline is
a set of all the plans for a particular query. There can be multiple queries
with baselines each with multiple plans.
187. Is there a feature which allows
partitioning a baseline for multiple user profiles, for example one for
application, one default and another for selected end users?
No, but you can use Adaptive
Cursor Sharing (ACS) to differentiate between the different plans for different
bind variable values.
188. I
ran my SQL with optimizer_capture_sql_plan_baselines = true but I do not see a
baseline in dba_sql_plan_baselines?
In order for a query to be a
candidate for capture, it needs to be repeatable. The statement needs to
executed more than once in order to be captured. Additionally, it will not
capture dictionary SQL. See: Document 788853.1 Sql Plan
Baseline Not always created
189. If I
have a SPM baseline, how do I find out what SQL_ID it relates to?
There are a few different ways
of doing this including: Using the SQL_HANDLE as the identifier for the
baseline, join DBA_SQL_PLAN_BASELINES.PLAN_NAME with V$SQL.SQL_PLAN_BASELINE:
SELECT b.plan_name,
b.enabled, b.accepted, s.sql_id FROM DBA_SQL_PLAN_BASELINES b, V$SQL s
WHERE b.plan_name=s.sql_plan_baseline
AND sql_handle='&SQL_HANDLE' ORDER
BY 1;
190. How
can I display the plans for a SQL statement recorded in a SPM baseline?
You can see the execution
plan using the same API that you use to look at the plan in memory -
dbms_xplan.display. For various different ways of doing this see: Document
235530.1 How to Obtain a Formatted Explain Plan - Recommended Methods
191. Do I
need to change my statistics gathering routine having implemented SPM
Baselines?
SPM Baselines are not
designed to avoid gathering statistics. If you stored baselines for every
statement and never added any new statements, then technically you would not
need to regather statistics on your application objects. However, this is
unlikely to be the case.
192. What is the order of precedence for
Baselines, SQL profiles, Outlines, Hints etc?
The first recommendation is
to "keep it simple". In other words,try not to mix up different
methods if possible.
If you do have to use
different methods, then:
Adding a hint changes the
SQL and so you will have a new cursor that won't match up with an existing
baseline. Once you add a baseline to a query containing a hint then that
baseline can override whatever that hint is intended to do.
If you have a stored outline
and a baseline then the Stored outline will take precedence. If you have
Outlines and you want to use SPM Baselines, then migrate the outlines into the
baseline.
When they are migrated they
are no longer considered an outline any more so they won't take precedence over
the baseline. If you have migrated the stored outline into a baseline then it
is just another plan in that baseline.
If a SQL Profile is
generated, initially it will not be one of the accepted plans in the baseline
so the SQL Profile will be added as as an unaccepted plan to the baseline. The
DBA then will have a choice between which different plans to enable and accept.
193. How
does SPM decide between the same SQL statement called by two different database
users, referencing two different tables with the same name?
All SPM Baselines from
different users are created for the same statement, and Oracle will choose the
best one for each user based upon its cost. See: Document 1469099.1 SQL Plan
Baselines Clarified from a Security Perspective.
Additionally, in terms of
baselines, queries using different case and whitespace are also deemed to match
as long as the actual SQL is the same.
194. How
can I Monitor Plan Evolution?
From 12cR1, it is possible
to automatically evolve plans and modify the plan evolution using the SPM
Evolve Advisor Task.
Prior to 12cR1 ,there is no
monitoring that automatically evolves plans. Evolution is an active action by
the DBA that moves the plan to be accepted and enabled. If you want to monitor
what has and has not yet been evolved, then there are various views such as
dba_sql_plan_baselines that allow the dba to monitor various pieces of
information about SPM. For example you could use a select similar to the
following to identify whether a particular plan has been enabled / accepted or
not within a baseline:
col sql_text format a42
col parsing_schema_name
format a14
col sql_handle format a20
SELECT sql_handle, sql_text,
parsing_schema_name, plan_name, enabled, accepted
FROM dba_sql_plan_baselines WHERE sql_text LIKE
'&Query_Text%';
195. How
does a DBA decide which plans to accept and enable?
The choice between which
plan to use is that same with or without SPM. SPM does not make the choice for
you, it just provides a mechanism for enforcing the choice that has been made.
The usual criteria a DBA would use to determine the relative performance
of different plans would be
elapsed time or buffer gets.
196. What
is the recommended method for plan capture?
Plans can be captured in
numerous different ways and the 'best' depends on your application and the
access to it. If all the plans you want are already executed and recorded in
the cache, then create a baseline from there. If you can run a session which
contains all the SQL you want to capture then set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
to true, capture the plans
and then return it to the
default of false when you are done. The cost-based optimizer will continue to
capture any new execution plan which it identifies as having a lower cost than
the lowest-costed, applicable execution plan already present within
the SQL plan baseline, even
after you have turned off OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. However, the
execution plan is unaccepted until you choose to verify and evolve it. You may
prevent this from taking place by marking at least one of the execution plans
within the SQL plan baseline as FIXED. This is explained further in Document
1520337.1.
197. Is
it good to set auto capture to true right after an upgrade of the Database?
No.If you have done no
pre-testing then you have no guarantee that the plans will be 'good' right out
the box. If you have used Real Application Testing (RAT) or you have stored
plans in the previous release then you would likely already have baseline
information that you could use or migrate.
198. What
if the first captured plan is not the most optimal?
If the first captured plan
is non-optimal then the situation is the same as without SPM. You need to
investigate why a bad plan is generated.
If the first plan is bad,
then that implies that there is something deficient in the statistics or
environment that produces a bad plan.
If different bind variable
values perform differently with the same plan, then that implies that there is
data skew, and column statistics (histograms) may be necessary to reflect that.
Assuming that you are using
bind variables, without ACS, you will get a single plan based on the first bind
value and all subsequent identical query will use that irrespective of the bind
value.
If different bind values
give different good plans for each value then that implies that ACS would be
helpful.
With ACS and SPM, the first
plan will be automatically accepted and enabled, subsequent plans will need to
be manually accepted. Once you have generated a good plan, you can then manage
subsequent plans picking the good plan and make sure that is accepted and
enabled so that the bad plan is not used
199. How
can plans be migrated from Oracle 9i?
In Oracle 9i you can create
stored outlines and export/import them from 9i to 11, then migrate the outlines
in to SPM.
In Oracle 10g you can create
a SQL Tuning Set (STS), transport it to 11g and then load it into a baseline.
So the additional steps in
9i over 10g is to create stored outlines and export/import them.
200. Is
it possible to load plans from the AWR into SPM?
Yes. You can load a SQL
Tuning Set from AWR and load the baselines from there.
201. Is it possible to upload an existing
baseline or manually create a baseline?
Baselines can be created
manually by
1. Loading plans from the shared
SQL area or from a SQL tuning set Using routines in DBMS_SPM
2. Using an Alternative Plan
found by SQL Tuning Advisor
While tuning a SQL
statement, SQL Tuning Advisor searches real-time and historical performance
data for alternative execution plans for the statement. If plans other than the
original plan exist, then SQL Tuning Advisor reports an alternative plan
finding.
SQL Tuning Advisor validates
the alternative execution plans and notes any plans that are not reproducible.
When reproducible alternative
plans are found, a SQL plan baseline can be created using
DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE
When a baseline is created
manually, it is automatically accepted.
202. If a baseline is imported from a
"test" database into a "production" database do environment
variables that must be set to the same values (e.g. optimizer_mode)?
No. The Goal of SPM
baselines is to guarantee the plan regardless to the environment. The structure
of the objects must be the same, but the parameters do not have to be.
203. What
is the performance impact of setting optimizer_capture_sql_plan_baselines on a
production system?
If you are using the latest
patchset the impact is negligible since all you are doing is recording
generated plans .
204. How
can you calculate additional disk space in the sysaux tablespace for spm?
This is not easy to
calculate. It depends on how many queries are being captured and how many plans
each generate. The best advice is not to enable the feature systemwide, but for
specific SQLs that are critical to the business only. Then monitor and adjust
the space as required.
205. How
can you verify the performance of different accepted plans stored in a baseline
verses non accepted plans?
The evolve API
(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE) has a verify option which allows Oracle to
compare the performance inside or outside the accepted.
206. Does
capturing and use of SPM baselines degrade the database performance?
No. There is a very small
overhead, but it is negligable.
207. What is Concurrent statistics
gathering?
Concurrent statistics
gathering is a new statistics gathering mode introduced in Oracle Database 11g
Release 2
(11.2.0.2). This works on
both partitioned and non-partioned tables; the database running as many
concurrent jobs as
possible, bounded by the job_queue_processes
initialization parameter and the available system resources.
If Concurrent Statistics is
controlled via the job_queue_processes parameter, the default in 11.2.0.3.0 is
1000.
208. Would a stats gather job
really create 1000 concurrent stats gather processes?
Potentially
yes, but this will also be constrained by the available system resources.
Concurrent statistics gathering does not use any automated way to determine the
maximum number of jobs that can concurrently run based on the system load and
resources. Concurrent statistics gathering solely relies on the Job Scheduler
and the value of the job_queue_processes parameter.
209. How
do I control how many jobs get created using Concurrent Statistics with auto optimizer
stats collection?
The number of jobs is
constrained by the job_queue_processes initialization parameter. See the
following blog entry:
https://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one
210. How
is gathering concurrently different from parallel stats gathering ?
Concurrent Statistics
Gathering uses the job queue mechanism to schedule multiple activities at once
within the resources available. Parallel statistics gathering splits up a
single gathering activity across multiple parallel slave processes. Concurrent
jobs can still execute in parallel. If you plan to execute the concurrent
statistics gathering jobs in parallel you should disable the parallel adaptive
multi-user initialization parameter ("PARALLEL_ADAPTIVE_MULTI_USER")
and enable parallel statement queuing.
211. Does
gathering statistics concurrently interfere with parallel stats gathering ?
No. They work alongside one
another.
212. Is
concurrent object stats gathering automatic or is it parameter controlled ?
Concurrent statistics gathering is controlled
by a global preference, CONCURRENT, in the DBMS_STATS package
213. How
does Oracle handle locking?
Oracle use enqueues as
locking mechanism for managing access to shared resources. A shared resource
can be a table definition, a transaction or any type of structure that
represent something sharable between sessions Each type of actions performed by
Oracle sessions on those shared resources will require a certain type of lock
or lock mode (e.g. a 'select on a table' action will require that the executing
session has a shared lock on the resource 'table definition' of the selected
table). When conflicting actions are occuring, Oracle will serialize the
processing by putting a number of sessions in waiting mode until the work of
the blocking session has been completed.
Each enqueue represent a
sharable resource. Sessions may be acquiring, converting and releasing locks on
resources
in function of the work they
need to perform.
Releasing locks are
performed by the sessions when they issue a commit or a DDL statement (i.e.
implicit commit), or by PMON if the sessions have been killed. (Remember this
only pertains to row locks. Only TX, TM, and DX enqueues are held until commit.
Other locks such as RT enqueues will be held until the instance is shutdown.)
Conversion is the process of
changing a lock from the mode we currently hold to a different mode. We are
allowed to convert a lock if the mode we require, is a subset of the mode we
hold or is compatible with the modes already held by other sessions. Otherwise,
we wait on the converters queue of the resource.
Acquiring a lock is the
process of getting a lock on a resource on which we currently do not have a
lock. We are allowed to acquire a lock, if there are no converters or waiters
ahead of us and the mode we require is compatible with the modes already held
by others. Otherwise, we wait on the waiters queue of the resource.When a
session has a lock on a resource, then it stands in the owner queue of the
resource. When a lock is released or converted, the converters and waiters are
re-checked to see if they can be acquired. The converters are processed first,
then the waiters.
Row locking in Oracle is
based on the TX enqueues and is known as transactional locking. When two or
more sessions are changing data on one row of a table (DML statements on the
same record), the first session will lock the row by putting his transaction
reference in the block containing the row header. The other sessions will look
at this lock information and will wait on the transaction (i.e. the TX enqueue
of the blocking session) of the first session before proceeding. When the first
session performs a commit, the TX resource will be released and the waiters
will start their own locking. The waiting sessions are thus waiting on an
exclusive TX resource, but their TM resources they are holding give the objects
they are in fact waiting on.
If a lock has not been
acquired or converted, a deadlock check is made by the waiting session after a
timeout. For example, following situation generates a deadlock:
User A gets an S lock on
resource 1
User B gets an S lock on
resource 2
User A request an X lock on
resource 2 and waits
User B requests an X lock on
resource 1 and waits
A is waiting for B to
release resource 2, which is waiting for A to release resource 1
A is indirectly waiting for
A. This is a deadlock, generating a tracefile in the user_dump_dest and and ORA-60
in
the detecting session which
results in the session being killed.
The lock and resource
information stands within the SGA to allow PMON to recover in the event of
process failure. The
PMON is responsible for
releasing the locks of the crashed/killed process.
214. How
to find the resource definitions?
Each resource is represented
by an enqueue. An enqueue is identified by a unique name, also known as the
resource name. The name has the form: . Type has two characters and represent a
resource type (e.g. TM for the table definition type). ID1 and ID2 are positive
numbers and identify the resource fully (e.g. ID1 is the object_id of the table
if the resource type is "TM"). ID1 and ID2 have different meanings
depending on the type of enqueue. The description of most enqueue/resource
types can be found in the appendixes of the Oracle Reference Guide. See also: Document
29787.1 VIEW: "V$LOCK" Reference Note
The most commonly known
resource types are the TM, TX and UL resources:
1. The TM resource, known as
the DML enqueue, is acquired during the execution of a statement when referencing
a table so that the table is not dropped or altered during the execution of it.
2. The TX resource, known as
the transaction enqueue, is acquired exclusive when a transaction initiates its
first change and is held until the transaction does a COMMIT or ROLLBACK. Row
locking is based on TX enqueues.
PMON will mark a transaction
status as dead in the undo segment header. At that point the TX enqueue associated
with that transaction is also released. PMON will then attempt to rollback some
of the changes associated with the "dead" transaction, but will then
pass it to SMON to apply the remainder of the associated undo records.
3. The UL resource represent
the user-defined locks defined by the DBMS_LOCK package.
215. Which
lock modes are required for which table action?
The following table
describes what lock modes on DML enqueues are actually gotten for which table
operations in a standard Oracle installation.
Operation Lock Mode LMODE
Lock Description
-------------------------
--------- ----- ----------------
Select NULL 1 null
Select for update SS 2 sub
share
Insert SX 3 sub exclusive
Update SX 3 sub exclusive
Delete SX 3 sub exclusive
Lock For Update SS 2 sub
share
Lock Share S 4 share
Lock Exclusive X 6 exclusive
Lock Row Share SS 2 sub
share
Lock Row Exclusive SX 3 sub
exclusive
Lock Share Row Exclusive SSX
5 share/sub exclusive
Alter table X 6 exclusive
Drop table X 6 exclusive
Create Index S 4 share
Drop Index X 6 exclusive
Truncate table X 6 exclusive
216. How
compatibility of locks works
The compatibility of lock
modes are normally represented by following matrix:
LMODE Description Name NULL
SS SX S SSX X
0,1 No Lock NULL YES YES YES
YES YES YES
2 Row-Share SS YES YES YES
YES YES no
3 Row-
Exclusive
SX YES YES YES no no no
4 Share S YES YES no YES no
no
5 Share Row-
Exc
SSX YES YES no no no no
6 Exclusive X YES no no no no no
217. How
to detect locking situations?
Different tools can be used
to detect locking issues and the identity of a blocking session or sessions.
The following
article contains some
selects to find blocking locks: Document 729727.1 Detecting blocking Locks in
10.2 and above
A methodology using OEM Top
Sessions report is explained in: Document 164760.1 Detecting and Resolving
Locking Conflicts using TopSessions
218. Which views can be used to detect
locking problems?
A number of Oracle views
permits to detect locking problems.
V$SESSION_WAIT -- When a session is waiting on a resource,
it can be found waiting on the enqueue wait event
SELECT * FROM V$SESSION_WAIT
WHERE EVENT = 'enqueue';
SID identifier of session
holding the lock
P1, P2, P3 determine the
resource when event = 'enqueue'
SECONDS_IN_WAIT gives how
long the wait did occurs
V$SESSION -- Session
information and row locking information
SID, SERIAL# identifier of
the session
EVENT event waited on
P1, P2, P3 determine the
resource when event = 'enqueue'
# SECONDS_IN_WAIT gives how
long the wait did occurs
LOCKWAIT address of the lock
waiting, otherwise null
ROW_WAIT_OBJ# object
identified of the object we are waiting on (object_id of dba_objects)
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
file_id , block_id and row
location within block of the locked row
V$LOCK -- List of all
the locks in the system
SID identifier of session
holding the lock
TYPE, ID1 and ID2 determine
the resource
LMODE and REQUEST indicate
which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0
owner
LMODE = 0, REQUEST > 0
acquirer
LMODE > 0, REQUEST > 0
converter
CTIME time since current
mode was converted
BLOCK are we blocking
another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others
DBA_LOCK or DBA_LOCKS -- Formatted
view on V$LOCK (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
SESSION_ID == SID in V$LOCK
LOCK_TYPE, LOCK_ID1,
LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
MODE_HELD and MODE_REQUESTED
formatted value of LMODE and REQUEST from V$LOCK
LAST_CONVERT == CTIME of
V$LOCK
BLOCKING_OTHERS formatted
value of BLOCK from V$LOCK
V$TRANSACTION_ENQUEUE -- Subset of
V$LOCK for the blocking TX resources only (same description as for the V$LOCK
view)
V$ENQUEUE_LOCK -- Subset of
V$LOCK for the system resources only and blocked TX resources only. (same
description as for the V$LOCK view)
DBA_DML_LOCKS -- Subset of
the V$LOCK for the DML (TM) locks only . Created via
$ORACLE_HOME/rdbms/admin/catblock.sql
Same description as the
DBA_LOCK view
V$LOCKED_OBJECT -- Same info
as DBA_DML_LOCKS, but linked with the rollback and session information
XIDUSN, XIDSLOT and XIDSQN
rollback information to be linked with V$TRANSACTION
OBJECT_ID object being
locked
SESSION_ID session id
ORACLE_USERNAME oracle user
name
OS_USER_NAME OS user name
PROCESS OS process id
LOCKED_MODE lock mode
V$RESOURCE -- List of all
the currently locked resources in the system. Each row can be associated with
one or more rows in V$LOCK
TYPE, ID1 and ID2 determine
the resource
DBA_DDL_LOCKS -- Has a row
for each DDL lock that is being held, and one row for each outstanding request
for a DDL lock.
It is subset of DBA_LOCKS Same description as the DBA_LOCK view
DBA_WAITERS --View that
retrieve information for each session waiting on a lock (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
WAITING_SESSION waiting
session
HOLDING_SESSION holding
session
LOCK_TYPE, LOCK_ID1,
LOCK_ID2 resource locked
MODE_HELD lock type held
MODE_REQUESTED lock type
requested
DBA_BLOCKERS -- View that
gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
HOLDING_SESSION holding
session
219. Which tools are there to diagnostic
locking issues?
1. OEM Top Session
OEM Top Session permits to
see all the session information. The sessions that are in status ACTIVE can be waiting
on locks. By right clicking on those sessions and choosing the 'detail' screen,
you can go to the 'lock' leaflet and find blocking sessions.
For more information see Document
164760.1 Detecting and Resolving Locking Conflicts using TopSessions
2. Lock Charts in Performance
Manager of Oracle Enterprise Manager
OEM contains a lock graph
facility named the Lock Charts in the Performance Manager that is part of the
Oracle Diagnostics Pack of OEM. The Performance Manager requires the Oracle
Data Gatherer to run. You should select the database from the OEM Console
Navigator window. Then launch Performance Manager from the Applications menu
bar. When the application comes up, select the 'Lock Charts'. You can use the
Performance Manager to kill the session. You just need to right-mouse click on the
session you want to kill. Since the Lock Charts are automatically refreshed,
you can watch the charts and instantly see locks being released.
3. Scripts for detecting locking
A number of sample scripts
exist to retrieve locking information.
Document 1020012.6 TFTS
SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO
Document 1020008.6 TFTS
FULLY DECODED LOCKING SCRIPT
Document 1020007.6 SCRIPT:
DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
Document 1020010.6 SCRIPT:
DISPLAY SQL TEXT FROM LOCKS
Document 1020047.6 SCRIPT:
SCRIPT TO DISPLAY USER LOCK INFORMATION
Document 1020088.6 SCRIPT:
REPORT SESSIONS WAITING FOR LOCKS ()
4. Using the logminer (Oracle8i
onwards)
All locking statements are
logged in the redologs. Document 198828.1 Analyzing them permits to understand
locking situations
220. How to resolve locking situations?
Most locking issues are
application specifics. To resolve locking contention, one needs to free the
resource by:
1. Asking the HOLDER to
commit or rollback
2. Killing the session which
holds the lock, for example:
ALTER SESSION KILL SESSION
'sid, serial#';
3. Killing the
"shadow" process directly.
Although this may free the
resource it is not recommended as it may prevent proper cleanup of a session. Especially
avoid killing shared server or dispatcher processes at the OS level in a shared
server environment since this may effect more than one session, not only the
deadlocked one..
4. If the operation is a 2pc pending
transaction: ROLLBACK FORCE or COMMIT FORCE
221. Deadlock
Situations
If you are encountering a
deadlock situation (which may be accompanied by an "ORA-60 Deadlock
Detected" error) this
should generate a deadlock
report trace which should enable you to diagnose the cause. ORA-60 is an
application error
which usually occurs because
a consistent locking strategy has not been followed throughout an application.
Please refer to the
following articles for more information:
Document 18251.1 OERR: ORA
60 "deadlock detected while waiting for resource"
Document 62365.1 What to do
with "ORA-60 Deadlock Detected" Errors
Document 62354.1 TX
Transaction locks - Example wait scenarios
222. Unusual
locking problems
1. Some common locking
scenarios are explained in:
Document 62354.1TX
Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios
2. When your application has
referential integrity and attempts to modify the child/parent table, Oracle
will get additional locking on the parent/child table when there is NO index on
the foreign key. To bypass this problem, the most efficient way is to create
indexes for all foreign key defined in the database. See the following notes:
Document 1019527.6 'TFTS
CHECK FOR FOREIGN KEY LOCKING'
Document 33453.1 REFERENTIAL
INTEGRITY AND LOCKING
3. When your application is
using DBMS_PIPE extensively, your session can wait for CI locks. You should
increase your shared pool.
4. When statements like
'CREATE INDEX' and 'ALTER INDEX REBUILD' are issued, Oracle behave differently
in Oracle7 compared to Oracle8i. To understand the benefit of the ONLINE option,
you can find more information in
Document 70120.1 Locking
Behavior During Index Creation or Index Rebuild
5. When a table's INITRANS
is set too low , the block is full with data, and there are many concurrent
DML's occurring on rows within the block, one may see a Share Lock being
requested when doing a DML. To my knowledge, this is only time we grab the
SHARED lock. Instead of waiting for a lock, this process is waiting for some
extra space or a release of an INITRANS within the transaction layer of the
block. See: Document 62354.1 TX Transaction locks - Example wait scenarios
6. There are other
systemwide locks that can be held at any given time. See: Document 102925.1
Tracing sessions: waiting on an enqueue
7. Distributed transactions
also use locks.See: Document 118219.1 Detecting and Resolving Distributed
Locking Conflicts
223. What
are 'cursor: ' waits?
Any operation that
manipulates or accesses cursors may be subject to waits for access to the
structures that support them in the shared pool. In cases of extreme contention,
these waits can become a significant bottleneck and can can constrain normal
activity. From 10.2 some shared cursor operations started to be implemented by
Oracle's Mutex feature and in 11g Librarycache and rowcache components were
also implemented with Mutexes.
224. What
are the most common waits?
The most commonly seen waits
are :
cursor: mutex X
cursor: mutex S
cursor: pin X
cursor: pin S
cursor: pin S wait on X
library cache: mutex X
library cache: mutex S
Note that all these waits
are very similar and may all be waited for in the course of an operation.
eXclusive operations are those that need to change a particular structure while
Share operations can make do without changes but need to briefly lock them for
the duration of a change to stop them being changed by something else. The
differentiation is not really relevant for diagnosis of problems other than
certain waits may be more prevalent for certain problems.
225. What are the most common causes of
waits?
Contention on these events
is usually a symptom of another problem - an indicator that there is a problem
elsewhere as opposed to a problem with the structure or the mechanism itself.
To resolve the symptom, the root cause needs to be identified and addressed.
Waits for cursors are parse
time waits and involve the loading of cursors into the shared pool or searching
for those cursors. Most problems occur either because:
The number of versions of
cursors in the shared pool becomes excessive
Excessive hard/soft parsing
Excessive
Invalidations/reloads
Enormous objects are loaded
Inappropriately sized shared
pool
The holder of the resource
being scheduled Off CPU by the OS/Resource Manager
Operating system management
of memory (e.g. Very large SGA on Linux x86-64 platform without the
implementation of Hugepages)
Code defects
If the sharing of cursors is
actively used and child cursors and versions are low in number, then it is
unlikely that contention will occur.
226. How
can I avoid these waits?
Generally, by adopting a
sensible sharing strategy for cursors, using bind variables appropriately and
making sure that there an not large numbers of versions, you should be able to
avoid most issues of this nature. Useful articles include:
Note:62143.1 Understanding
and Tuning the Shared Pool
Note:94036.1 Init.ora
Parameter "CURSOR_SHARING" Reference Note
If you find that you have a
large number of versions of cursors then the following articles can help:
Note:296377.1
Troubleshooting: High Version Count Issues
Note:438755.1 Formated
V$SQL_SHARED_CURSOR Report by SQLID or Hash Value
227. Where can I find cause diagnostics
and more information about these waits?
Common Diagnostics
Because these waits have
similar causes, there are a number of common diagnostics that can be collected
:
History of the Issue -- Being
aware of and focusing on changes that appear to trigger contention affects can
help narrow down areas for examination and where deeper investigation may be
merited.
What has recently changed?
Application, database,
middle tier changes
OS change
Increased load
Common Patterns
Does something seem to
trigger the problem?
Is there a specific period
of time the issue is seen?
Diagnostic Evidence
Often a substantial quantity
of diagnostics is required to pin-point an issue. This is because the waits are
a symptom and the holding session may have nothing to do with the waiting
session other than it happens to be blocking it.
AWR (or Statspack) and ASH
reports (for the waiting sessions)
These provide a system
overview and also a focus on individual sessions where appropriate Stack
trace(s)
Stack traces identify the
code area where holders are working.
If the holder cannot be
identified: Collect Systemstate with short stack dumps
(Collecting information
about every session should capture the holder)
If the holder cannot be
identified: Collect Errorstacks of the waiter as this may also provide some
useful information
Operating System statistics
(e.g. OSWatcher)
Operating system statistics are
useful for various reasons such as identification of high CPU users, picking
out peaks of activity and
identifying 'build up' signs
to help trigger early capture of data.
Note that acquiring a
systemstate on a busy system with lots of sessions can be expensive.
If this is the case then
different dumps can be produced but these will provide reduced information
which may not be enough to make progress. So, from the most useful (and
expensive) to the least useful please try to acquire these:
Systemstate(s) at level 266
(Systemstate with stacks)
Systemstate(s) at level 258
(less detailed Systemstate with stacks)
Hanganalyze output with
stack traces
As an example, if a level
266 systemstate is too expensive then you could try to use level 258. You could
also combine the above. E.g., get one level 266 systemstate and multiple
hanganalyze dumps. (The hanganalyze dumps should include short stack dumps).
Data Collection Reference:
Note:1363422.1 AWR Reports -
Information Center
Note:748642.1 How to
Generate an AWR Report and Create Baselines [ID 748642.1]
Note:1364257.1 How to
Collect Errorstacks for use in Diagnosing Performance Issues.
Note:452358.1 Database
Hangs: What to collect for support.
Note:301137.1 OS Watcher
User Guide
Note:1360119.1 FAQ: Database
Performance Frequently Asked Questions
cursor: mutex X
Here a cursor is being
parsed and is trying to get the cursor mutex in eXclusive mode
cursor: mutex S
Here a cursor is being
parsed and is trying to get the cursor mutex in Share mode
Note:9591812.8 Bug 9591812 -
Wrong wait events in 11.2 ("cursor: mutex S" instead of "cursor:
mutex X")
cursor: pin X
Here a cursor is being parsed and is trying to
get the cursor pin in eXclusive mode
cursor:
pin S
Here a cursor is being
parsed and is trying to get the cursor pin in Share mode. See:
Note:1310764.1 WAITEVENT:
"cursor: pin S" Reference Note
If contention is seen on
this event, it is better to look for other related waits and investigate them
first, as it is likely that this is a symptom of those.
cursor: pin S wait on X
Here a cursor is being
parsed and has the cursor pin and is trying to get it in eXclusive mode. If you
see 'cursor: pin S wait on X' prominent in the 'Top
Waits' section of AWR, like
here for example:
then it would be sensible to
initially look at the number of versions of cursors that you have on the
system:
and if they are anywhere
remotely close to the exceptionally high numbers shown here, reduce them as a
matter of priority. See:
Document 296377.1
Troubleshooting: High Version Count Issues
If this is not the case then
you can use the following articles to help you debug issues:
Note:1349387.1
Troubleshooting 'cursor: pin S wait on X' waits
Note:1298015.1 WAITEVENT:
"cursor: pin S wait on X" Reference Note
Note:786507.1 How to
Determine the Blocking Session for Event: 'cursor: pin S wait on X'
Note:742599.1 High 'cursor:
pin S wait on X' and/or 'library cache lock' Waits Generated by Frequent Shared
Pool/Buffer Cache Resize Activity
Note:1268724.1 "Cursor:
Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce
[KKSCHLPIN2]'
Note:402027.1 Bug:5653007;
5485914: SELF DEADLOCK PROCESS WAITS ON ''Cursor: Pin S Wait On X'' with
SQL_TRACE enabled.
Note:9472669.8 Bug 9472669 -
'cursor: pin S wait on X' waits for invalid SQL over DB link
library cache: mutex X
Here a library cache
operation is being performed and is trying to get the library cache mutex in
eXclusive mode.
The "library cache:
mutex X" symptom is very common and can be caused by a number of issues,
so it is very important to fully understand the root cause in order to
determine the right action. In numerous cases, the problem can be alleviated by
a change to the application (preventing logon or logoff storms is an example).
Also it is possible that a known Oracle bug is being encountered (this includes
both mutex-related issues and non-mutex issues that had mutex waits as a
symptom).
Note:1357946.1
Troubleshooting 'library cache: mutex X' waits.
Note:727400.1 WAITEVENT:
"library cache: mutex X"
Note:758674.1 " Library
Cache: Mutex X " On Koka Cursors (LOBs) Non-Shared :
Note:9530750.8 Bug 9530750 -
High waits for 'library cache: mutex X' for cursor Build lock library cache:
mutex S
Here a library cache operation is being
performed and is trying to get the library cache mutex in Share mode
228. What
kind of statistics do the Automated tasks collect
The statistics jobs
automatically gather Missing and Stale statistics for the Cost Based Optimizer
(CBO). The Automatic Statistics Gathering Job was built to assist with the
collection of statistics from the start with newly created databases so that
there are accurate statistics to use rather than relying on defaults. The
statistics collected are generic one and not customized; however since they use
"AUTO" by default, a number of decisions are automatically tailored
to the data.
For many database these
automatic statistics are adequate, however, since these provide basic and
general statistics it may be possible to collect better statistics by
customizing the statistics collection. The flexibility is there to allow you to
gathers stats daily for some tables, collect hourly statistics for others and
just once for static data. You can decide to collect more accurate statistics
depending on the data volatility and the performance of the current statistics.
229. How do I revert to a previous set of
statistics?
From Oracle 10g, Oracle
retains collected statistics for 31 days after they are changed.
Refer to: Document 452011.1
* Restoring Table Statistics (Oracle 10G Onwards)
230. Does
the automatic statistic collection jobs populate CHAIN_CNT?
No. Chain count is not a
statistic used as by the Optimizer and as such the job does not populate the
chained rows information. Historically the legacy analyze command could be used
to capture chain count but this functionality was not included in the
DBMS_STATS package and is not collected by the automated jobs.
You can use the Automatic
segment advisor for the purpose; it provides Chained row analysis .
No comments:
Post a Comment