How To Indentify The Row Which is Locked By an Other User's Session

Subject: How To Indentify The Row Which is Locked By an Other User's Session
Doc ID: Note:198150.1 Type: TROUBLESHOOTING
Last Revision Date: 18-MAY-2004 Status: PUBLISHED



goal: Identify the row which is locked by an other user's session
fact: Oracle Server - Enterprise Edition



fix:

To determine which row is locked in a certain table can only be queried when an
other user is waiting for the row involved.

To find the row, 3 queries must be executed:

1) This query will display some information about the object and user involved.
The OBJECT_ID value returned must be used in the second query. The
OBJECT_NAME is used in the last query. The queries must be executed as a DBA
user.

sql> select a.os_user_name,
2 a.oracle_username,
3 a.object_id,
4 c.object_name,
5 c.object_type
6 from v$locked_object a, dba_objects c
7 where a.object_id=c.object_id
8 /

2) This query will return the ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#
values for the OBJECT_ID involved. The ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and
ROW_WAIT_ROW# will be needed in the last query. If this query returns no
rows, no user is waiting for this row.

sql> select sid,
2 row_wait_obj# objn,
3 row_wait_file# fn,
4 row_wait_block# bn,
5 row_wait_row# rn
6 from v$session
7 where row_wait_obj#=
8 /

3) The final query will return the row which is locked an the user is waiting
for. The OBJN, FN, BN and RN values from the second query and OBJECT_NAME
from the first query need to be subsituted in this query.

sql> select * from
2 where rowid = dbms_rowid.rowid_create(1, , , , )
3 /

bind variables

var mybind number;
execute :mybind:=100
print :mybind

bat, batch job

@echo off
set /p seq=sequence#:
echo spool D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.txt>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo.>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
echo spool off;>>D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
start notepad D:\work\%date:~0,4%%date:~5,2%%date:~8,2%%seq%.sql
@echo on

DBMS_CRYPTO encryption 加密 AES

目前主流的加密方法大致有MD5、SHA-1、DES、Triple DES、AES，目前MD5、SHA-1、DES都已经有破解方法，Triple DES、AES还没有被破解的记载，其中AES是最新最安全的加密方法，最多可提供256位的加密。

Oracle 9i提供MD5、DES、Triple DES,而Oracle 10g和10g R2提供了上面提到的所有加密方法供选择。



鉴于以上情况，针对Oracle 10g，我的想法是使用DBMS_CRYPTO包提供的Triple DES或者AES进行加密（Oracle 9i中没有这个包）。

与其他方法相比，这样做的优点是：



1. Triple DES、AES是最安全的加密方法，没有被破解的记载

2. 有可逆和不可逆两种加密选择

3. 支持对BLOB和CLOB加密




1.脚本

-----------------------------------------------------------------------------------------

加密函数

create or replace function encrypt256(v_input varchar2) return raw

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.ENCRYPT(

UTL_I18N.STRING_TO_RAW(v_input, 'AL32UTF8'),

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return v_result;

end;

/



解密函数

create or replace function decrypt256(v_input raw) return varchar2

is

v_result raw(256);

c_key char(32) := '1q2wa3es4rz5tx6ycD*UF8tif9ogjen2';

begin

v_result := DBMS_CRYPTO.DECRYPT(

v_input,

DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO.PAD_PKCS5,

UTL_I18N.STRING_TO_RAW(c_key, 'AL32UTF8'));

return UTL_I18N.RAW_TO_CHAR(v_result, 'AL32UTF8');

end;

/

-----------------------------------------------------------------------------------------



2.功能演示



SQL> insert into test values(1,encrypt256('一次成功')); <--用户插入或者修改数据时这样使用



已创建 1 行。



SQL> commit;



提交完成。



SQL> select * from test;



ID RAWS

---------- --------------------------------------------------

1 597B660AFD6CC3A90D064B00DF7EB80D <--数据已经被密文保存



SQL> select id,raws from test where raws=encrypt256('一次成功'); <--核对或者查找时这样使用



ID RAWS

---------- --------------------------------------------------

1 597B660AFD6CC3A90D064B00DF7EB80D



SQL> select id,decrypt256(raws) raws from test; <--解密方法和结果



ID RAWS

---------- --------------------------------------------------

1 一次成功





3. 加密对性能的影响

在我的测试中，逻辑读的量基本不变，扫描索引的耗时变为不加密时的10.8倍（66us --> 717us）。

INDEX RANGE SCAN YB_IND_TESTT_1 (cr=3 pr=0 pw=0 time=66 us)(object id 81639)

INDEX RANGE SCAN YB_IND_TESTT_2 (cr=3 pr=0 pw=0 time=717 us)(object id 81640)

这种耗时的增加与使用何种加密方式无关。



4. 对于脚本中密钥的保护

虽然密钥仍然是写死在脚本中，但是不用担心开发人员或者数据库管理人员会得到密钥，这里使用的方法是Oracle提供的wrap程序包，使用这个包可以将脚本以不可逆的方式加密。



例如：创建encrypt256()这个函数的脚本经过加密以后，会变成下面这样。

这样的脚本仍然可以创建函数，但没有人知道这个函数内容更没有人知道密钥是什么。

----------------------------------------------------------------------------

create or replace function encrypt256 wrapped

a000000

354

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

18b 15c

cSBfE65CYUS4abCCuiwqeQWAEZEwg/DIDK5qfC9Grfjqzqc2UfIyWpTjI7h2sFSyE4XQwwA7

LyfVV6mp8no/ANyNnp4A9SCHcrSaWUNnVD2mjcrSwg1WkEakxRGQY6SxBkD2b8xiO+tnKg8v

AVoHo6vqBfl2I4SJtjfHvhrte1k0+SPy1giZEp0MKyCcZA8pMwubQg9BmMUFIthkQXjQCklv

XRrIgkQkckLqk2iraDchjDcWFNJyV7qMqxd8ZSvVdWt5S0mISwurpXs4/yuTL5W1R4A/Z9Hw

0FII+JeAzKlRL1sFp5y0/wKhhl5rn5V2yjphQHv+r8dma0dyvPumJQEJ



/

----------------------------------------------------------------------------



5. 几点提示和注意事项

(1) 示例脚本中被加密的字段最大255字节因此需要开发人员在程序中判断长度；

这个长度限制根据需要可以调整到最大1999字节。

(2) BLOB和CLOB类型的数据也可以加密

(3) 由于使用了wrap方式保护密钥且这个方法不可逆，所以密钥必须妥善保存。

os authentication

In order for a database account to accept connections without a password the
database logon must be created with an 'IDENTIFIED EXTERNALLY' clause.

Eg: CREATE USER OSUSER_SCOTT IDENTIFIED EXTERNALLY;
This will allow SCOTT to 'CONNECT /'.
This will NOT allow SCOTT to connect as OSUSER_SCOTT in any other manner

CREATE USER OSUSER_SCOTT IDENTIFIED BY XYZ;
The user must supply the username AND password to connect as the
account has actually been created as requiring database authentication.
Ie: 'CONNECT /' will *NOT* work.
'CONNECT OSUSER_SCOTT/XYZ' will connect.

If the OS_AUTHENT_PREFIX is set to OPS$ a user can connect in either
manner if created with a password. This is more of a security risk
as there are 2 ways to access the database account.

Eg: CREATE USER OPS$SCOTT IDENTIFIED BY XYZ;
Allows the user to 'CONNECT /' or 'CONNECT OPS$SCOTT/XYZ'.

NOTE: on unix platforms, you need use sqlplus "OPS\$SCOTT/XYZ" to connect to oracle server.

The Priority of NLS Parameters Explained

Subject: The Priority of NLS Parameters Explained
Doc ID: Note:241047.1 Type: BULLETIN
Last Revision Date: 21-AUG-2007 Status: PUBLISHED


This note explains the order in which NLS parameters are taken into account
in the database client/server model using a standard client connection.
(This does NOT cover JDBC connections, please see Note 115001.1
NLS_LANG Client Settings and JDBC Drivers for more info on jdbc and NLS.)

There are 3 levels at which you can set NLS parameters: Database, Instance and
Session. If a parameter is defined at more than one level then the rules on
which one takes precedence are quite straighforward:
1. NLS database settings are overwritten by NLS instance settings
2. NLS database & NLS instance settings are overwritten by NLS session settings

In the remainder of this note we shall explain all the different settings in
detail. The categories A to C shown below indicate the order of precedence with
A being the highest and C the lowest.
For example, if you set NLS_NUMERIC_CHARACTERS in the init.ora (point B) and
in the environment (point A 6), then for a session the value defined in the
environment will take priority because point A 6 comes before point B.


A) The Session Parameters.
--------------------------

select * from NLS_SESSION_PARAMETERS;

These are the settings used for the current sql session.

These reflect (in this order):

1) The values of NLS parameters set by "alter session .... "

alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';

* this can also been done with an after logon trigger(!).
select OWNER, TRIGGER_NAME, TRIGGER_BODY from DBA_TRIGGERS where
trim(TRIGGERING_EVENT) = 'LOGON';
-> Note 251044.1 How to set a NLS session parameter at database
level for all sessions ?

2) If there are no explicit "alter sessions ..." statements done then it
reflects the setting of the corresponding NLS parameter on the client derived
from the NLS_LANG variable.

NLS_LANG consist of: NLS_LANG=_.
for example:
NLS_LANG=DUTCH_BELGIUM.WE8MSWIN1252

For information on how to find the NLS_LANG your sqlplus session is using
see point "4.2 How can I Check the Client's NLS_LANG Setting?" in Note 158577.1

3) If NLS_LANG is specified with only the part
then AMERICAN is used as default .

So if you set NLS_LANG=_BELGIUM.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY BELGIUM
NLS_CURRENCY
NLS_ISO_CURRENCY BELGIUM
....

Note the difference between NLS_LANG=_BELGIUM.WE8PC850 (correct) and
NLS_LANG=BELGIUM.WE8PC850 (incorrect), you need to set the "_" as separator.

4) If NLS_LANG is specified with only the part then the
defaults to a setting based on .

So if you set NLS_LANG=ITALIAN_.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE ITALIAN
NLS_TERRITORY ITALY
NLS_CURRENCY
NLS_ISO_CURRENCY ITALY
.....

Note the difference between NLS_LANG=ITALIAN_.WE8PC850 (correct) and
NLS_LANG=ITALIAN.WE8PC850 (incorrect), you need to set the "_" as separator.

5) If NLS_LANG is specified without the _ part
then the _ part defaults to AMERICAN_AMERICA.

So if you set NLS_LANG=.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ ----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
....

Note the difference between NLS_LANG=.WE8PC850 (correct) and
NLS_LANG=WE8PC850 (incorrect), you need to set the "." as separator.

6) If the NLS_LANG is set (either like in point 3,4 or 5) then parameters like
NLS_SORT, NLS_DATE_FORMAT,... can be set as a "standalone" setting and will
overrule the defaults derived from NLS_LANG _ part.

So if you set NLS_LANG=AMERICAN_AMERICA.WE8PC850 and NLS_ISO_CURRENCY=FRANCE
then you get this:

PARAMETER VALUE
------------------------------ -----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY FRANCE
...

* Make sure that you set "NLS_ISO_CURRENCY=FRANCE", NLS_ISO_CURRENCY= FRANCE
(note the space) will not give an error but the parameter is just ignored
and the default based on NLS_TERRITORY will be used.


Defaults:
---------
* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from
NLS_LANGUAGE.

* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT,
NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are
not set then they are derived from NLS_TERRITORY


7) If the NLS_LANG is not set at all, then it defaults to
_.US7ASCII and the values for the
_ part used are the ones found in
NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on
the client side are ignored.

* Oracle does NOT recommend to have the NLS_LANG UNSET, please always define
at least the proper part for the NLS_LANG like shown
in point 5)
See Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
and Note 179133.1 The correct NLS_LANG in a Windows Environment

Note that:

* If set, client parameters (NLS_SESSION_PARAMETERS) take always precedence
above NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.

* This behavior can not be disabled on/from the server, so a parameter set
on the client always has precedence above an instance or database parameter.

* NLS_LANG cannot be changed by alter session, NLS_LANGUAGE and NLS_TERRITORY
can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as a "standalone" parameters
in the enviroment or registry on the client.

* NLS_SESSION_PARAMETERS is NOT visible for other sessions. If you need to trace
this then you have to use a logon trigger to create your own logging table
(based on session_parameters)

* The part of NLS_LANG is *NOT* shown in any system table
or view. (see section 4.2 How can I Check the Client's NLS_LANG Setting? in
Note 158577.1)

* On Windows you have two possible options, normally the NLS_LANG is set in
the registry, but it can also be set in the environment, however this is not
often done and genrally not recommended to do so.
The value in the environment takes precedence over the value in
the registry and is used for ALL Oracle_Homes on the server(!) if defined as
a system environment variable.
See Note 179133.1 The correct NLS_LANG in a Windows Environment

* NLS_COMP *cannot* be set as enviroment variable (unlike documented in the
manual). All Oracle8i and Oracle9 versions use NLS_COMP from INIT.ORA or
from explicit ALTER SESSION.
Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT

* NLS_LENGTH_SEMANTICS *cannot* be set as enviroment variable in 9i,
from 10g onwards it can be, please note that it needs to be set as UPPERCASE.
It is however possible to do a ALTER SESSION.
If not set explicit in a session it will use the NLS_INSTANCE_PARAMETER setting.

* NLS_NCHAR_CONV_EXCP *cannot* be set as enviroment variable.
It is however possible to do a ALTER SESSION.

* NLS_LANGUAGE in the session parameters also declares the language for the
client error messages, see also Note 132090.1.

* you cannot "set" a NLS parameter in an SQL script, you need to use alter
session.

B) The Instance Parameters.
---------------------------

select * from NLS_INSTANCE_PARAMETERS;

These are the settings in the init.ora of the database at the moment that
the database was started or set trough ALTER SYSTEM.

If the parameter is not explicitly set in the init.ora / defined by
ALTER SYSTEM then it's value is NOT derived from a "higher" parameter

(=we are talking about parameters like ex. NLS_SORT who
derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS,
this is NOT the case for NLS_INSTANCE_PARAMETERS )

Note that:

* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
so you need to set NLS_LANGUAGE and NLS_TERRITORY separated.

* you cannot define the or NLS_LANG in the init.ora
the clients characterset is defined by the NLS_LANG on client side (see above).

* you cannot define the database characterset in the init.ora.
The database characterset is defined by the "Create Database" command (see point c)).

* These settings take precedence above the NLS_DATABASE_PARAMETERS.

* These values are used for the NLS_SESSION_PARAMETERS if on the client the
NLS_LANG is NOT set (we strongly advice to set the NLS_LANG).

* Oracle *strongly* recommends that you set the NLS_LANG on the client at least to
NLS_LANG=.

* ALTER SYSTEM SET NLS_LENGTH_SEMANTICS does not change the SESSION parameters
(who take precedence) due to bug number 1488174 until the database is restarted.
however it can be set in the init.ora or Spfile -> see Note 144808.1

* ALTER SYSTEM SET NLS_NCHAR_CONV_EXCP does not change the SESSION parameters
(who take precedence).. workaround: use a init.ora parameter.

C) The Database Parameters.
---------------------------

select * from NLS_DATABASE_PARAMETERS;

These are always defaulting to american america if there were no parameters
explicitly set in the init.ora during database creation time (!).
If there were parameters set in the init.ora during database creation you see
them here. There is no way to change these after the database creation.
Do NOT update systemtables!
These settings are used to give the database a default if the INSTANCE and
SESSION parameters are not set.

Note that:

* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
So you need to set NLS_LANGUAGE and NLS_TERRITORY separatly.

* These parameters are overruled by NLS_INSTANCE_PARAMETERS and
NLS_SESSION_PARAMETERS.

* you cannot define the or NLS_LANG in the init.ora
the clients characterset is defined by the NLS_LANG on client side (see above).

* you cannot define the database characterset in the init.ora.
The database (national) characterset (NLS_(NCHAR)_CHARACTERSET)
is defined by the "Create Database ..." command.

* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be
overruled by instance or session parameters.

They are defined by the value specified in "create database ..." and are not
intended to be changed afterwards dynamically.
Do NOT update systemtables to change the characterset.
This will corrupt your database and potentialy it will by impossible to open
the database again.
See Note 225912.1 Changing the Database Character Set - an Overview
If you want to change the database characaterset. Don't mess with this if you
don't know what you are doing. Log a tar if any doubt.

* Setting the NLS_LANG during the creation of the database dous not influence
the NLS_DATABASE_PARAMETERS.

* The NLS_LANG set during the database creation has NO impact on the database
(national) Characterset.

* These settings are used in evaluation of CHECK constraints if TO_CHAR/TO_DATE
without a date format is used in the CHECK condition. Writing CHECK
constraints without explicit date formats is a bad habit, you should use
explicit formats and this setting becomes irrelevant.

Additional selects:
-------------------

A) select name,value$ from sys.props$ where name like '%NLS%';

This gives the same info as NLS_DATABASE_PARAMETERS.
You should use NLS_DATABASE_PARAMETERS instead of props$.
Note the UPPERCASE '%NLS%'

B) select * from v$nls_parameters;

A view that shows the current session parameters and
the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

C) select name,value from v$parameter where name like '%nls%';

This gives the same info as NLS_INSTANCE_PARAMETERS.
Note the LOWERCASE '%nls%'

D) select userenv ('language') from dual;
and
select sys_context('userenv','language') from dual;

Both these select statements give the session's _
and the *DATABASE* character set. The database character set is not the
same as the character set of the NLS_LANG that you started this connection
with! So don't be fooled, although the output of this query looks like the
value of a NLS_LANG variable - it is NOT.

For more info on SYS_CONTEXT please see Note 120797.1

E) select userenv ('lang') from dual;

This select gives the short code that Oracle uses for the Language defined
by NLS_LANGUAGE setting for this session.
If NLS_LANGUAGE is set to French then this will return "F",
if NLS_LANGUAGE is set to English then this will return "GB"
If NLS_LANGUAGE is set to American then this will return "US", and so on...

F) show parameter NLS%

This will give the same as the NLS_INSTANCE_PARAMETERS

Remark:
-------

The part of NLS_LANG is *NOT* shown in any systemtable
or view (it is not known in the database). If you require to know the current
setting of a clients NLS_LANG then please see section 4.2 (How can I Check the
Client's NLS_LANG Setting?) of Note 158577.1



Related Documents:
------------------

Note 179133.1 The correct NLS_LANG in a Windows Environment
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 227331.1 Setting NLS Parameters - Frequently Asked Questions

Note 13978.1 NLS Sort Characteristics
Note 13882.1 Linguistic Sorting of Data in Oracle7 and Oracle8
Note 30557.1 NLS_DATE_FORMAT and a Default Century

Note 225912.1 Changing the Database Character Set - an Overview

Note 132090.1 How to get messages in your own language on MS Windows platform?
Note 120797.1 How to Determine Client IP-address,Language & Territory and Username for Current Session

Note 251044.1 How to set a NLS session parameter at database level for all sessions ?
Note 144808.1 Examples and limits of BYTE and CHAR semantics usage

Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT

For further NLS / Globalization information you may start here:
Note 267942.1 Globalization Technology (NLS) Knowledge Browser
Note 60134.1 NLS Frequently Asked Questions

AIX / redhat linux: Percentage of memory currently used by the file cache

Percentage of memory currently used by the file cache
AIX
vmstat -vs |grep "numperm percentage"

redhat
vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 29772 37820 45952 3729772 0 0 9 127 3 51 1 0 95 5

cache: the amount of memory used as cache.

linux环境sqlplus中使用backspace键出现乱码的解决方法

linux环境sqlplus中使用backspace键出现乱码的解决方法

1. 要使用回删键（backspace）时，同时按住ctrl键

2. 设定环境变量

在bash下：$ stty erase ^H
或者把 stty erase ^H 添加到.bash_profile中。

HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK

Subject: HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
Doc ID: Note:122793.1 Type: TROUBLESHOOTING
Last Revision Date: 25-JUL-2005 Status: PUBLISHED


PURPOSE
-------

In some situations it may happen your session is 'hanging' and is awaiting for
a 'Library cache lock'. This document describes how to find the session that
in fact has the lock you are waiting for.


SCOPE & APPLICATION
-------------------

Support analysts, dba's, ..


HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
------------------------------------------------------

Common situations:

* a DML operation that is hanging because the table which is accessed is currently
undergoing changes (ALTER TABLE). This may take quite a long time depending on
the size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).

* The compilation of package will hang on Library Cache Lock and Library Cache Pin
if some users are executing any Procedure/Function defined in the same package.

In the first situation the V$LOCK view will show that the session doing the
'ALTER TABLE' has an exclusive DML enqueue lock on the table object (LMODE=6,
TYPE=TM and ID1 is the OBJECT_ID of the table). The waiting session however does
not show up in V$LOCK yet so in an environment with a lot of concurrent sessions
the V$LOCK information is insufficient to track down the culprit blocking your
operation.

METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------

One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the resources
held & requested by a specific process.

Whilst an operation is hanging, open a new session and launch the following
statement:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION with ADDR from V$PROCESS:

SELECT PID FROM V$PROCESS WHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);

The systemstate dump contains a separate section with information for each
process. Open the tracefile and do a search for 'PROCESS pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.

Example output:

PROCESS 8:
----------------------------------------
SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 82 0 4
last post received-location: kslpsr
last process to post me: 5004ff08 1 2
last post sent: 0 0 13
last post sent-location: ksasnd
last process posted by me: 5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 50058ac4
O/S info: user: daemon, term: pts/1, ospid: 15161
OSD pid info: 15161
----------------------------------------
SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 41/LC
O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.

Example output:

PROCESS 9:
----------------------------------------
SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0

....

----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

From the output we can see that the Oracle process with PID 9 has an exclusive
lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
retrieve the sid,user,terminal,program,... for this process. The actual statement
that was launched by this session is also listed in the tracefile (statements and
other library cache objects are preceded by 'name=').


METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------

The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.

You can examine the locks requested (and held) by the waiting session
by looking up the session address (SADDR) in V$SESSION and doing the
following select:

select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'

This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.

You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
If we now match the KGLLKHDL with the handles of other sessions in
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.

SELECT * FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);

If we look a bit further we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

In the same way we can also find all the blocked sessions:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);


RELATED DOCUMENTS
-----------------

Note 1020008.6 SCRIPT FULLY DECODED LOCKING SCRIPT
Note 1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK

a fga + vpd example

DROP PACKAGE MAINKEY;
begin
DBMS_RLS.DROP_POLICY (
'TEST','UTIPOSMAINKEY','PROTECTMAINKEY');
end;
/
begin
DBMS_FGA.DROP_POLICY(
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'AUDITMAINKEY');
end;
/

=========================

CREATE OR REPLACE PACKAGE mainkey AS
FUNCTION usercontrol (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY mainkey AS
FUNCTION usercontrol (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
IS
D_predicate VARCHAR2 (2000);
BEGIN
D_predicate := 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''TEST''';
RETURN D_predicate;
END usercontrol;
END mainkey;
/


BEGIN
DBMS_RLS.ADD_POLICY (
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'PROTECTMAINKEY',
function_schema=>'TEST',
policy_function=>'MAINKEY.USERCONTROL',
statement_types=>'SELECT, UPDATE, DELETE, INSERT, INDEX',
update_check=>TRUE,
policy_type=>DBMS_RLS.STATIC);
END;
/

begin
DBMS_FGA.ADD_POLICY(
object_schema=>'TEST',
object_name=>'UTIPOSMAINKEY',
policy_name=>'AUDITMAINKEY',
audit_condition=>'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') <> ''TEST''',
statement_types=>'INSERT, UPDATE, DELETE, SELECT',
audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED);
end;
/

范式, NF, normal form

4.4 关系模式的范式


4.4.1 第一范式
考核要求：达到“领会”层次
知识点：1NF的定义


--------------------------------------------------------------------------------

1NF：第一范式—— 即关系模式中的属性的值域中每一个值都是不可再分解的值。
如果某个数据库模式都是第一范式的，则称该数据库模式是属于第一范式的数据库模式。

比如有一个关系 study={学号，课程}，若有这样几行记录： 学号
课程

99001
C语言

99002
数据结构

99003
C语言，数据结构

　

这时的第三条记录就表示本关系模式不是1NF的，因为课程中的值域还是可以分解的，它包括了两门课程。

如果改为：

学号
课程

99001
C语言

99002
数据结构

99003
C语言

99003
数据结构

　
则成为1NF的关系。


4.4.2 第二范式
考核要求：达到“领会”层次
知识点：2NF的定义


--------------------------------------------------------------------------------

如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。

首先温习、理解“非主属性”、“完全函数依赖”、“候选键”这三个名词的含义。
（1）候选键：可以唯一决定关系模式R中某元组值且不含有多余属性的属性集。
（2）非主属性：即非键属性，指关系模式R中不包含在任何建中的属性。
（3）完全函数依赖：设有函数依赖W→A，若存在XW，有X→A成立，那么称W→A是局部依赖，否则就称W→A是完全函数依赖。

在分析是否为第2范式时，应首先确定候选键，然后把关系模式中的非主属性与键的依赖关系进行考察， 是否都为完全函数依赖，如是，则此关系模式为2NF。
如果数据库模式中每个关系模式都是2NF的，则此数据库模式属于2NF的数据库模式。

比如有一个关系 study={学号，学生姓名，课程,成绩} 学号
姓名
课程
成绩

99001
Lily
C语言
91

99002
Rose
数据结构
82

99003
Keven
C语言
77

99003
keven
数据结构
86



其中，（学号，课程）为候选键；“成绩”对键的函数依赖为完全函数依赖，而“姓名”只依赖于“学号”， 对键的依赖为部分函数依赖。所以，该关系模式不符合2NF。如果将该 模式分解为以下两个关系：
　　　　　student={学号，姓名｝
　　　　　study={学号，课程，成绩}
则分解后的两个关系模式均为2NF


4.4.3 第三范式
考核要求：达到“领会”层次
知识点：3NF的含义


--------------------------------------------------------------------------------


如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。
传递依赖的含义： 在关系模式中，如果Y→X，X→A，且XY(X不决定Y)和AX(A不属于X)，那么Y→A是传递依赖。 Notice:要求非主属性都不传递依赖于候选键。

上一小节例子中student={学号，姓名｝，study={学号，课程，成绩}都是3NF




4.4.4 BCNF
考核要求：达到“领会”层次
知识点：BCNF的含义


--------------------------------------------------------------------------------

这个范式和第三范式有联系，它是3NF的改进形式。
若关系模式R是第一范式，且每个属性都不传递依赖于R的候选键。这种关系模式就是BCNF模式。
四种范式，可以发现它们之间存在如下关系：
BCNF3NF2NF1NF



1NF
↓ 消去非主属性对键的部分函数依赖
2NF
↓ 消去非主属性对键的传递函数依赖
3NF
↓ 消去主属性对键的传递函数依赖
BCNF





4.4.5 分解成BCNF模式集的算法
考核要求：达到“识记”层次
知识点：分解成BCNF模式集的算法


--------------------------------------------------------------------------------

对于任一关系模式，
(1)可找到一个分解达到3NF，且具有无损联接和保持函数依赖性。
(2)对于BCNF分解，则可以保证无损联接但不一定能保证保持函数依赖集。
（定理4.9）
（算法4.3）
无损联接分解成BCNF模式集的算法：
(1)置初值ρ={R};
(2)如果ρ中所有关系模式都是BCNF，则转(4);
(3)如果ρ中有一个关系模式S不是BCNF，则S中必能找到一个函数依赖集X→A有X不是S的键，且A不属于X，设S1=XA，S2=S-A，用分解S1,S2代替S，转(2)；
(4)分解结束。输出ρ。
Notice:重点在于(3)步，判断哪个关系不是BCNF，并找到X和A。
（以上内容可结合习题内容加以理解）

4.4.6 分解成3NF模式集
考核要求：达到“识记”层次
知识点：算法4.4


--------------------------------------------------------------------------------

(算法4.4)
(1)如果R中的某些属性在F的所有依赖的左边和右边都不出现，那么这些属性可以从R中分出去，单独构成一个关系模式。
(2)如果F中有一个依赖X→A有XA=R，则ρ={R}，转(4)
(3)对于F中每一个X→A，构成一个关系模式XA，如果F有有X→A1，X→A2...X→An,则可以用模式XA1A2...An代替n个模式XA1,XA2...XAn;
(4)w分解结束，输入ρ。
（以上内容可结合习题内容加以理解）

4.4.7 模式设计方法的原则
考核要求：达到“识记”层次
知识点：四项特性和三条原则


--------------------------------------------------------------------------------

关系模式R相对于函数依赖集F分解成数据库模式ρ={R1,R2...Rk}，一般具有下面四项特性：
(1)ρ中每个关系模式Ri上应具有某种范式性质(3NF或BCNF)
(2)无损联接性。
(3)保持函数依赖集。
(4)最小性，即ρ中模式个数应最少且模式中属性总数应最少。

一个好的模式设计方法应符合下列三条原则：
(1)表达性
(2)分离性
(3)最小冗余性


4.4.8 多值依赖
考核要求：达到“识记”层次
知识点：多值依赖的概念及其和函数依赖的区别


--------------------------------------------------------------------------------

函数依赖有效地表达了属性值之间多对一的联系，是最重要的一种数据依赖；而多值依赖是为了刻划属性值之间一对多的联系.


4.4.9 第四范式
考核要求：达到“识记”层次
知识点：第四范式的概念


--------------------------------------------------------------------------------

设R是一个关系模式，D是R上的多值依赖集合。如果D中成立非平凡多值依赖X→→Y时，X必是R的超键， 那么称R是第四范式（4NF）。
（定理4.13) 一个关系模式若属于4NF，则必然属于BCNF。
（算法4.5) ：了解一下。

Which indexes are used and how many times they are used?

Which indexes are used and how many times they are used?

col c1 heading object|Owner format a15
col c2 heading object|Name format a25
col c3 heading operation format a10
col c4 heading option format a15
col c5 heading index|Usage|Count format 999,999

select
p.object_owner c1,
p.object_name c2,
p.operation c3,
p.options c4,
count(1) c5
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like 'INDEX%' and
p.sql_id = s.sql_id
group by
p.object_owner,
p.object_name,
p.operation,
p.options
order by
1,2,3,4;

Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns

Subject: Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns
Doc ID: Note:264003.1 Type: PROBLEM
Last Revision Date: 02-JUL-2007 Status: MODERATED


The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.

Errors
ORA 2437 cannot enable - primary key violated


Symptoms
Have a table with duplicate data in the PK columns.
Trying to create the PK with the novalidate option fails with an ora-02437.

Test Case to exhibit the problem:

CREATE TABLE chris (col1 INTEGER)
/
INSERT INTO chris VALUES (1)
/
INSERT INTO chris VALUES (2)
/
INSERT INTO chris VALUES (3)
/
INSERT INTO chris VALUES (4)
/
INSERT INTO chris VALUES (4)
/
ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/
-- fails with error ORA-02437
ALTER TABLE chris ADD PRIMARY KEY (col1) DISABLE
/
-- ok
ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/
-- again fails with error ORA-02437

Cause
This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.
Fix
- First, create a NON-UNIQUE index on the same fields that you want to
include in the constraint.
- Then add the unique or primary key constraint.

Thats is:

create index idx_chris_col1 on chris(col1);

After this give:

ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/

OR

ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/

References
Bug 544502 - Enabling Novalidate Not Deferrable Unique Constraint Creates Unique Index

index rebuild

The index is valid during rebuild or rebuild online if it is valid before rebuild.
Query started during rebuild or rebuild online can use this index.

How to find which indexes are used and which indexes are NOT used?

In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used.

select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%' and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;

advanced SQL tuning

Subject: TROUBLESHOOTING: Advanced Query Tuning
Doc ID: Note:163563.1 Type: TROUBLESHOOTING
Last Revision Date: 18-JUL-2006 Status: PUBLISHED

Purpose

The purpose of this article is to assist with the handling of query tuning issues. This article provides guidelines for dealing with tuning problems. It assumes that there really is a query tuning problem as opposed to some other issue that makes queries perform in a sub-optimal way. There are already plenty of articles on this topic. These will be referenced from this article where appropriate rather than trying to duplicate existing material. Obviously an article such as this cannot cover all possibilities but rather attempts to indicate what to look for.

Scope and Application

All

Resolving Query Tuning Issues

Queries can run slowly for any number of reasons. In order to try to determine where the problem lies and thus how to deal with it, this article has been divided into a number of sections containing pertinent questions to help the reader . Remember that initially you should be thinking towards providing at least a satisfactory workaround to the issue if the absolute root cause cannot be determined. Once a Workaround has been put in place and the pressure is off (to some degree at least), then the root cause can be determined (if necessary).

The points in this article are applicable to both the Rule (RBO) and Cost Based Optimizers (CBO)

Investigation

The first step must be to collect information about the problem itself:

Do you know which query is causing the problems?
If not, then Note 33089.1 contains information that may help identify problem queries.
The Tuning Pack in Oracle Enterprise Manager is also very useful for identifying problematic statements. There is also useful information in Chapter 6 of the Oracle9i Database Performance Guide and Reference.

Tools such as STATSPACK can also assist in determining which query is causing problems. See Note 94224.1

Information on this particular query
At this stage it is not recommended to get exhaustive information regarding this query. With experience, it is often possible to resolve issues with only small amounts of data, such as the query itself and the explain plan. Note 68735.1 Information required to diagnose a Query Performance Problem is an exhaustive list of the type of information required.



Diagnostics

Once the statement (or statements) have been determined then the next stage is to find the reason for the query running slow.

Has this query always run slowly? See Note 179668.1
Investigate the following areas
Explain plan for the query
In a perfect world, you would wish to gather the explain plan for both the 'slow' and 'not so slow' queries. Obviously this may not be possible but if this can be achieved it makes generation of a workaround much quicker because there is immediately a goal to aim at that has known and acceptable performance.

The best (standard) way of getting an explain plan is to use autotrace.
See Note 68735.1 regarding how to do this.

Once you have the explain plan, review it alongside the query text looking for anything unusual. The goal here is to identify any problem areas or anything that could be modified to run more efficiently.

Identify the optimizer in use (See Note 66484.1)
By far the most common issue encountered will be that the 'wrong' optimizer has been chosen and the system has not been set up to use it. This results in choices being made based on incorrect information.

Look for cost figures in the explain output to indicate that the CBO has been used
Missing cost figures do not necessarily indicate that the RBO has been used.
Look for CBO specific features (Hash Join, Index Fast Full Scan etc) as these confirm CBO usage
Once the optimizer has been determined, establish that the system is setup to work effectively with this optimizer. For example if CBO is in use then ensure that statistics are upto date and reflect the dataset.

Once the optimizer has been determined, establish that the system is setup to work effectively with this optimizer. For example if CBO is in use then ensure that statistics are up to date and reflect the dataset. See Statistics and analysis strategy below

Identify operations that may cause problems on your system:
Remember that these can only be general suggestions and many systems will work fine that do not match this structure.

Online Transaction Processing (OLTP) System

Full Table Scan
Hash or Sort merge joins
Index fast full scans or (large) index range scans
Parallel query
Nested loop joins with high cardinality outer table
Batch/Warehouse system

Nested loop joins
Index lookups
See the Query text section below for more general suggestions of things to look for.

Identify expensive operations:
There are 2 main categories of potentially expensive operations: High cardinality (large number of rows) and High cost. If the explain plan has steps that show large cardinality or cost figures then these may be areas where the biggest benefits of tuning can be reaped.

Statistics and analysis strategy
The CBO requires accurate statistics to enable it to work efficiently. By default no statistics are gathered on any objects. If the CBO is to be used effectively, statistics must be gathered. See Note 44961.1 for Analysis recommendations. A quick way to see if statistics are present is to select the NUM_ROWS column for your base table from dba_tables. If the column value is non-NULL then statistics have been gathered at some point. If it is suspected that the statistics may be old, then the LAST_ANALYZED column in DBA_TABLES can be used to determine when the table was last analyzed.

Query text
Review the query looking for any constructs which may cause you problems.
These come from experience but you are looking for:

Large INlists / OR statements Note 62153.1
Outer joins - There are a number of restrictions on the use of outer joins - see Oracle9i Database Performance Guide and Reference
Set operators (UNION etc) - Incorrect usage of set operations can be very inefficient
Partitioned Tables (or Views Oracle 7.3) - ensure that elimination is taking place as expected
No where clause or missing join predicates (potential for Cartesian Products)
Aggregate functions (cpu intensive functions applied to large rows sources can cause problems in some cases)
Sorting issues (These will typically be identified in combination with Explain plan) Note 67409.1 and Note 10577.1
Implicit type conversion - ensure that the datatypes of compared objects are the same or conversion overheads can occur
Any other 'strange' or unexpected constructs
Views, Inline views or Subqueries - is view merging or subquery unnesting taking place or not? Are predicates being passed in?
Finding an acceptable plan

Examination of the explain plan for the query can only give information on the query as it is running currently. If the query is running poorly then this may not help determine how the query could run more efficiently. Also this may be a new query where no acceptable plan has been determined yet so there is nothing to aim for. The following section gives suggestions for how a better plan may be found

Trace statistics
I/O and timing statistics (elapsed and cpu) can be very helpful in determining which part of a query is the root cause of the problem. Especially useful are actual row counts as these can be compared against the predicted row counts (expected row counts (cardinalities e.g. Card=12345) can be found in explain plans for CBO queries, actual row counts can be found in tkprof output or in the STAT lines from raw sqltrace/10046 output ). Any anomalies can then be investigated. For example, if the actual number of rows returned by a particular explain plan step differs significantly from the CBO cardinality estimates, then it is possible that this is a source of a bad plan choice. The cause of the incorrect statistics can be investigated and corrected. A fully detailed analysis is beyond the scope of this document.

Where excessive I/O has been identified, some potential causes are:

Full Table Scans and Index Fast Full Scans - this would be characterised by multi block i/o and likely waits for 'db file scattered read'. Note that Full table scans on the Right hand side (inner) of a nested loops join is unlikley to be performant since the table will be scanned once for every row on the Left hand side (outer).
Index range or Full Scan - If a large proportion (or indeed all) of an index is scanned then this can lead to excessive I/O (and CPU). This would be characterised by high single block i/o activity and likely waits for 'db file sequential read'. Join order can play a very significant part in this
Join order - It is advisable to choose a join order that eliminates as many rows as possible early in the query execution plan. If this does not occur then large volumes of data may be scanned that is later eliminated by the application of join predicates. If the join predicates can be applied earlier, then this volume of data may not need to be scanned and may reduce I/O (and CPU) requirements.
Excessive sort/hash areas - If sort/hash areas are excessive, the optimizer may start to choose plans which take advantage of these which may result in large amounts of I/O. For example, hash or sort merge joins may be chosen in preference to otjher methods. If parallel execution is also being used then this is even more likely.

Where excessive CPU usage has been identified, some potential causes are:

Index range or Full Scan - If a large proportion (or indeed all) of an index is scanned then this can lead to excessive CPU since much of the index may be cached. Join order can play a very significant part in this
Join order - As with I/O, it is advisable to choose a join order that eliminates as many rows as possible early in the query execution plan. If this does not occur then large volumes of data may be scanned that is later eliminated by the application of join predicates. If the join predicates can be applied earlier, then this volume of data may not need to be scanned and may reduce CPU (and I/O) requirements.
Excessive sort/hash areas - Sorting is very CPU intensive.
Nested Loops joins with high number of rows in the left hand side (outer table) will result in a large number of lookups on the Right hand side (inner table). The repeated lookup process is CPU intensive.

Break Query down into component parts
Most complex queries are a combination of many simpler queries. If a query is broken into its constituent parts and there are then optimized a good indication of the overall (desired) plan can be built up by combining each tuned section. Remember that in some cases significant benefits can be gained from the combination of steps and so even better performance can be obtained from a combined query than a number of standalone queries. Generally it is much easier to tune a simple query than a complex one.

Application knowledge
It may be that the application designer has information about how a particular query should run based on the application design. If that is the case then that can be used that to compare the plan that is generated with the expected (designed) access path. For example, if it is felt that a query should read table A with a full table scan and then use the information to drive an index lookup on table B with a nested loop join, then check this against what it was designed to do. If it does not match up then it may perform more acceptably if it is forced to use the designed access path, perhaps by using hints..

If specific application knowledge is not available then similar results may be attainable by considering the table sizes and organizing them so that the most rows are eliminated as early as possible in the query. If rows are eliminated early, then they do not have to be considered later and therefore may improve the overall query performance.

Trial and Error
Often trying a plan can give you a pointer as to a potential better plan. For example if a query performs badly under the RBO, analyze the tables and use CBO. It may be that the plan produced is a good one and can then be used in your environment. Even if the method used to create the plan is not feasible for use in the real environment, the fact that it actually produces the required output in a timely manner may be useful in as much as it proves that the operations are possible.

Summary: Compare real figures with optimizer estimates, break the query down into its constituent pieces and tune these individually, utilise application knowledge to suggest improvements in join orders and utilise trial and error to test potential plan choices.

Solutions

By looking at the diagnostics above, hopefully the root cause of the problem can be determined. The articles below cover most common solution areas.

Query does not use an index See Note 67522.1 Why is my index not used?
My hints are being ignored See Note 69992.1 Why is my hint ignored?
Changed oracle version and now query(ies) are slow See Note 160089.1 Why are my queries slow after upgrading my database?
Query produces wrong results See Note 150895.1 Handling Wrong Results Issues
Workarounds

Often the provision of a workaround is enough to diffuse a serious situation. It is worth determining if a workaround is applicable early on so that you can formulate your strategy around that. Even the knowledge that a workaround in not applicable can be useful as it can concentrate the mind on other solutions to the problem. Also creating a workaround can actually confirm that as solution is viable.

Is a Workaround going to be a valid solution to the problem?
Usually the most effective workaround is to use a hint. Can the query be hinted to force the desired plan?
See Note 29236.1 Hints Quick Reference or
Note 69992.1 Why hints are ignored.

What to do if the query cannot be modified?
For more suggestions and what to do if the query itself cannot be modified because it is generated or fixed code, or for any other reason then see Note 122812.1



RELATED DOCUMENTS

Note 233112.1 START HERE> Diagnosing Query Tuning Problems Using a Decision Tree

Note 372431.1 TROUBLESHOOTING: Tuning a New Query
Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Note 122812.1 Tuning Suggestions When Query Cannot be Modified
Note 67522.1 Diagnosing Why a Query is Not Using an Index

Note 214106.1 Using TKProf to compare actual and predicted row counts

photo

I forgot my root password, how can I get into my system?

Issue:
I forgot my root password, how can I get into my system? Resolution:You can change your root password from single user mode or rescue mode. Getting into single use mode depends on your bootloader:

GRUB
Booting into single user mode using GRUB is accomplished by editing the kernel line of the boot configuration. This assumes that either the GRUB boot menu is not password protected or that you have access to the password if it is.

If the GRUB boot menu is password protected and you do not have access to the password, then you will need to use a rescue disk to boot the system. Follow the instructions given by the rescue disk boot process to recover your installation and then chroot to your system image (usually accomplished by issuing the command chroot /mnt/sysimage). From this point you should be able to use the passwd to change the root password of the system.

At the boot prompt, select the kernel that you wish to boot with and press 'e' (for edit). You will now be taken to a screen where you can edit the boot parameters. Move the cursor to the kernel line and press 'e' again. Now append an 'S' to the end of the line, press Return, and then 'b' (for boot). The system will now start in single user mode and you can change the root password using the passwd command.

LILO
When the system comes to the LILO: prompt, type linux single . When you get the # prompt you will need to type passwd root. This will update the password to a newer one. At this point you can type exit and your system should return to the boot sequence. Alternatively, you can reboot your system with the shutdown -r now or reboot commands. The system should boot up normally. You can now use your new root password to gain root access.

If LILO is configured to not wait at the boot menu (timeout value in /etc/lilo.conf set to 0) you can still halt the boot process by pressing any key in the split second before LILO boots the kernel.

Database is Shown with Status 'Pending' in Grid Control

Subject: Problem: Database is Shown with Status 'Pending' in Grid Control
Doc ID: Note:312797.1 Type: PROBLEM
Last Revision Date: 26-MAR-2007 Status: PUBLISHED

In this Document
Symptoms
Cause
Solution



--------------------------------------------------------------------------------



Applies to: Enterprise Manager Grid Control - Version: 10.1.0.2 to 10.1.0.4
This problem can occur on any platform.
SymptomsDatabase is shown with status pending in Grid Control.
CauseThe DBSNMP user on the databases is locked and the dbsnmp password has not been configured
for the database. SolutionTo implement the solution, please execute the following steps:

1. Connect to database using sqlplus as sysdba and run the following command:



alter user DBSNMP account unlock;

2. Configure DBSNMP passwords for each database:


In Grid Control go to Management System > Agents and click on the link for the agent of the database
Select the database from the list of targets and click on the 'Configure' button
Specify the correct dbsnmp password in the 'Monitor Password' field
Click on the 'Test Connection' button.
You should see a 'Success' indication at the top of the page. Click Next and complete the Configure Database dialog appropriate to your needs.


3. Wait for 4 minutes before checking the status of the newly configured database (It should take a maximum of 4 minutes to determine the status of the database)

Keywords'GRID~CONTROL' 'CONFIGURE~DATABASE' 'LOCK' 'DBSNMP'
--------------------------------------------------------------------------------

Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace

Subject: Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace
Doc ID: Note:329375.1 Type: PROBLEM
Last Revision Date: 27-JUL-2005 Status: MODERATED

In this Document
Symptoms
Cause
Solution



--------------------------------------------------------------------------------


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Enterprise Manager Grid Control - Version: 10.1.0.3.0
This problem can occur on any platform.

Symptoms
Getting error ORA-30041: Cannot grant quota on the tablespace when installing Grid Control 10.1.0.3.0 using an existing 10.2 database.
Cause
10gR2 databases are not supported to be used as the Oracle Management Repository for Grid Control release 1.
Solution
To implement the solution, please execute the following steps:

1. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using a New database' option

OR

2. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using an Existing Database' option with a 9.2.0.5+ or 10.1.0.3+ database but not a 10gR2 database.

Errors
ORA-30041 Cannot grant quota on the tablespace

If you grid is 10g Release 3 (10.2.0.3.0), ensure the database release should be 9.2.0.6 and later, or 10.1.0.4 and later.