Home » RDBMS Server » Performance Tuning » What is causing the lock despite such an decent update state (Oracle 10gr2, windows 32)
What is causing the lock despite such an decent update state [message #504755] Wed, 27 April 2011 10:50 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi,

this is a test database

When I read the Performance Finding Details

It shown the following
Quote:

Action Investigate the SQL statement with SQL_ID "8ttwfmnbh22xj" for possible performance improvements.

SQL TextUPDATE WFP_PROJECT SET COSTSHEET_MIGRATE = 0 WHERE COSTSHEET_MIGRATE = ...
SQL ID8ttwfmnbh22xj
Rationale Waiting for event "enq: TX - row lock contention" in wait class "Application" accounted for 100% of the database time spent in processing the SQL statement with SQL_ID "8ttwfmnbh22xj".


Quote:

Action Significant row contention was detected in the TABLE "FLOWLOGIC.WFP_PROJECT" with object id 503930. Trace the cause of row contention in the application logic using the given blocked SQL.
Database ObjectFLOWLOGIC.WFP_PROJECT
Rationale The SQL statement with SQL_ID "8ttwfmnbh22xj" was blocked on row locks.

SQL Text UPDATE WFP_PROJECT SET COSTSHEET_MIGRATE = 0 WHERE COSTSHEET_MIGRATE = ...
SQL ID8ttwfmnbh22xj


The statement in question is

UPDATE WFP_PROJECT SET COSTSHEET_MIGRATE = 0 WHERE COSTSHEET_MIGRATE = .


I simply don't understand what is causing this.

I even created a table in the same schema, and test the following

FLOWLOGIC@ngistst-ora> 
FLOWLOGIC@ngistst-ora> declare
  2  v_begin_date date;
  3  v_end_date date;
  4  v_performance_test PERFORMANCE_TEST.performance_test%type;
  5  v_max PERFORMANCE_TEST.performance_test%type default 1000;
  6  v_performance_string PERFORMANCE_TEST.performance_string%type;
  7  CURSOR v_performace_test_cursor is SELECT * from PERFORMANCE_TEST;
  8  begin
  9  		     v_begin_date :=sysdate;
 10  	     for v_performace_test_rs in v_performace_test_cursor loop
 11  				     v_performance_test :=v_performace_test_rs.performance_test;
 12  				     v_performance_string := to_char(v_performance_test+1);
 13  
 14  		     update PERFORMANCE_TEST set performance_string =
                     v_performance_string where performance_test=v_performance_test;
 15  	     end loop;
 16  
 17  		     v_end_date :=sysdate;
 18  
 19  		     dbms_output.put_line ('v_begin_date ' || v_begin_date);
 20  		     dbms_output.put_line ('v_end_date ' || v_end_date);
 21  end;
 22  /
v_begin_date 27-APR-2011 16:43:54
v_end_date 27-APR-2011 16:43:55


Could any one tell us what is wrong?

The only difference I see is that WFP_PROJECT is a very large table


CREATE TABLE "FLOWLOGIC"."WFP_PROJ_HDR_MIGRATE"
   (	"PP_WO" VARCHAR2(30),
	"PROJECT_NO" VARCHAR2(100),
	"EXCH_CODE" VARCHAR2(5),
	"DONOR_EXCH_CODE" VARCHAR2(5),
	"PROJ_TITLE" VARCHAR2(2000),
	"PROJ_LOCATION" VARCHAR2(2000),
	"SCHEME_TYP_CODE" VARCHAR2(100),
	"SCHEME_CAT_CODE" VARCHAR2(100),
	"SCHEME_OBJ_CODE" VARCHAR2(100),
	"PROJECT_TYP_CODE" VARCHAR2(100),
	"SERVICE_TYP_CODE" VARCHAR2(100),
	"CUSTOMER_CODE" VARCHAR2(100),
	"CONTRACTOR_CODE" VARCHAR2(100),
	"EXTERNAL_PTY_CODE" VARCHAR2(100),
	"PROJECT_CODE" VARCHAR2(20),
	"JOB_NO" VARCHAR2(30),
	"OLD_PP_WO" VARCHAR2(30),
	"CHARGEABLE_FLG" VARCHAR2(2),
	"TENDER_FLG" VARCHAR2(20),
	"WAYLEAVE_FLG" VARCHAR2(2),
	"WORK_FLG" VARCHAR2(2),
	"PROGRESS" NUMBER(5,2),
	"PLN_EST_AMT" NUMBER(20,2),
	"PLN_ACT_AMT" NUMBER(20,2),
	"FIN_ACT_COST" NUMBER(20,2),
	"FIN_EXT_COST" NUMBER(20,2),
	"DMG_CLM_NO" VARCHAR2(30),
	"EST_SRT_DATE" DATE,
	"EST_END_DATE" DATE,
	"ACT_SRT_DATE" DATE,
	"ACT_END_DATE" DATE,
	"REMARKS" VARCHAR2(500),
	"SP_INSTR" VARCHAR2(500),
	"MANDAYS" NUMBER,
	"SOURCE_CODE" VARCHAR2(20),
	"SOURCE_NO" VARCHAR2(30),
	"ISACTIVE" NUMBER(1,0),
	"CREATE_DATE" DATE,
	"CREATE_BY" VARCHAR2(100),
	"LPT" VARCHAR2(20),
	"FIN_YEAR" VARCHAR2(20),
	"IND_PROJ" VARCHAR2(20),
	"DMG_EST_AMT" NUMBER(10,2),
	"ATTENTION_TO" VARCHAR2(100),
	"TARGET_DATE" DATE,
	"PHASE_BLOCK" VARCHAR2(50),
	"DS_CAT" VARCHAR2(20),
	"DUE_DATE" DATE,
	"BLDG_COMP_DATE" DATE,
	"ASSIGNED_BY" VARCHAR2(100),
	"ASSIGNED_TO" VARCHAR2(100),
	"COST_VALUE" NUMBER(20,2),
	"PROJ_DEF_CODE" VARCHAR2(50),
	"PROJ_NATURE_CODE" VARCHAR2(50),
	"PROJ_TYPE_CODE" VARCHAR2(50),
	"SUB_PROJ_CODE" VARCHAR2(20),
	"APP_CODE" VARCHAR2(50),
	"FIXED_CHG_TYPE" NUMBER(*,0),
	"CERTIFIED_BY" VARCHAR2(100),
	"CERTIFIED_DATE" DATE,
	"FINALPOST_FLAG" NUMBER(1,0),
	"CO_FLAG" NUMBER(1,0),
	"CO_STATUS" VARCHAR2(20),
	"CO_DATE" DATE,
	"CONTRACT_CREATION_FLAG" NUMBER(1,0),
	"IMPACT_FLG" NUMBER(1,0),
	"CONTRACT_LOCK_FLAG" NUMBER(1,0),
	"REV_FLG" NUMBER(20,0),
	"AMD_FLG" NUMBER(20,0),
	"ACCNO" VARCHAR2(50),
	"DUMMY_PROJECT_FLAG" NUMBER(1,0),
	"SUPERVISOR" VARCHAR2(500),
	"PARENT_PPWONO" VARCHAR2(20),
	"LOB" VARCHAR2(100),
	"MANAGER" VARCHAR2(200),
	"DIRECTOR" VARCHAR2(200),
	"DESIGNER_DATE" DATE,
	"MANAGER_DATE" DATE,
	"PLANNER_DATE" DATE,
	"DIRECTOR_DATE" DATE,
	"FIXED_CHARGE_TYPE" NUMBER(1,0),
	"PLANNER_FN" VARCHAR2(200),
	"DESIGNER_FN" VARCHAR2(200),
	"MANAGER_FN" VARCHAR2(200),
	"DIRECTOR_FN" VARCHAR2(200),
	"ASSIGNED_BY_FN" VARCHAR2(200),
	"TQN" VARCHAR2(2),
	"WAYLEAVE" VARCHAR2(100),
	"INSTALL_DATE" DATE,
	"PLANNED_DESIGN_END_DATE" DATE,
	"WORKPRINT_MIGRATE" CHAR(1),
	"COSTSHEET_MIGRATE" CHAR(1),
	"FOLDER_CREATION" CHAR(1),
	"COSTSHEET_MIGRATE_START" CHAR(1),
	"ERRORMSG" VARCHAR2(200),
	"SO_CREATION" CHAR(1),
	"SHAREPOINT_MIGRATE" NUMBER,
	"PDF_CREATED" CHAR(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "FLOWLOGIC"


the update does not involved a large number of rows at all. can any one kindly advise me on how to resolve this issue? thanks a lot!

[Updated on: Fri, 29 April 2011 01:46] by Moderator

Report message to a moderator

Re: What is causing the lock despite such an decent update state [message #504757 is a reply to message #504755] Wed, 27 April 2011 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://arjudba.blogspot.com/2008/05/what-is-enq-tx-row-lock-contention.html

Report message to a moderator

Re: What is causing the lock despite such an decent update state [message #505059 is a reply to message #504757] Fri, 29 April 2011 01:42 Go to previous message
kytemanaic
Messages: 55
Registered: February 2009
Member
I suspend that it could be application using app servers that manage persistence for them, so how do I check for application usage? probably from oracle enterprise manager, but where do I start from?

I actually did a trace for the single update statement which causes lock, I could not detect any problem, is there a way to detect the session causing the lock before the update statement?

thanks

Report message to a moderator

Previous Topic: tkprof trace analyze for recommendataion
Next Topic: Adding +0 and -0 to a number predicate produces differents plans.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 13:39:13 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.