I got a call from the client that an application was not function due to unique constraint violation. The client insisted on finding the cause of the problem. The challenge was that the database was a mission critical database and too many users were connected to the database which system wide tracing could not be an option. Also disabling unique constraint was not an option as the client did not know the impact of it on the application and also how to clean up skewed data.
For this, I chose to trace ORA-0001 using errorstack :
1. Enable tracing for unique constraint.
This only dumps the first occurance of unique constraint violation for each session,It has minimum side affect on mission critical database.stack level 2 and above captures bind variables.
alter system set events '1 trace name errorstack level 3';
2. Check udump for the generated trace when it occurs, you should be able to find SQL statement and the value for its bind variables if it has.The following shows a sample :
----- Error Stack Dump -----
ORA-00001: unique constraint (AVAIL.SYS_C00269375) violated
----- Current SQL Statement for this session (sql_id=d0p6uv6pamwk5) -----
.................... <---- SQL statement
.
.
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=ffffffff7a42c3f8 bln=22 avl=03 flg=09
value=2555 <--- Bind variable value ...
Frames pfr 0xffffffff7a478328 siz=3192 efr 0xffffffff7a478260 siz=3136
Cursor frame dump
enxt: 3.0x00000460 enxt: 2.0x00000020 enxt: 1.0x000007c0
pnxt: 1.0x00000038
kxscphp=0xffffffff7a450d58 siz=984 inu=464 nps=336
kxscbhp=0xffffffff7a4515c8 siz=984 inu=120 nps=0
kxscwhp=0xffffffff7a4512f8 siz=4056 inu=368 nps=0
Starting SQL statement dump
SQL Information
user_id=95 user_name=AVAIL module=SQL*Plus action=
sql_id=d0p6uv6pamwk5 plan_hash_value=0 problem_type=0
3. Turn off tracing when the issue is resolved.
alter system set events '1 trace name errorstack off';
TIP 89# : CPU cost is not shown in the execution plan
You may notice that sometimes that you get the execution plan, the CPU column is not shown. Also you may get the following messge : cpu costing is off (consider enabling it)
The followings could be the main reasons :
- old plan_table.
[@?/rdbms/admin/utlxpls.sql]
- optimizer_features_enable is still 9i.
[Bump up the parameter to DB version]
- System stats does not exist.
[Gather system stats]
Subscribe to:
Posts (Atom)