Home > Oracle, Siebel > Siebel SQL cursor crashes

Siebel SQL cursor crashes

This typically occurs when DB goes through a series of transactions very quickly, typically involving opening and closing of connections/cursors.We had two situations when this happened. When we raised log levels in production environment with a large number of users, Siebel invariably crashed.
Siebel also crashed sometimes when users tried to load a lot of data using VB com object in a high load production environment.

To find out about this, we should first look at the call stack file. Let’s start with the one involving high log levels in a high load environment.
The callstack file would be like this

/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlCursor::Close()+0x27a)[0x4448336a]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbConn::CacheSqlCursor(CSSSqlCursor*)+0x383)[0x44477cb3]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbConn::CachePendingCursors()+0x150)[0x44478ec0]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSLockDbConn::Unlock(CSSModelPhysDef*)+0x8d)[0x44472d3d]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbLock::Unlock()+0x43)[0x44472313]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbLock::~CSSDbLock()+0x1f)[0x4447222f]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSLockSqlCursor::DoExecuteStmt(CSSBindVarArray*)+0x111)[0x444822d1]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlCursor::Execute(CSSBindVarArray*)+0xbd)[0x4448356d]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlObj::Execute(int, int, int)+0x1832)[0x44508732]
/app/siebel/siebsrvr/lib/libsscfom.so(CSSBusComp::SqlExecute(int, int, int)+0x4e)[0x440e842e]
This shows that 1) The sql cursor was closed
2) Db connection was unlocked
3) DB Connection was locked for the cursor transaction
4) SQL was executed
5) Then the logs stopped when this was done and Siebel crashed.
The last 5 points were analyzed from these words

CSSSqlCursor::Close()
CSSLockDbConn::Unlock
CSSLockSqlCursor::DoExecuteStmt
CSSBusComp::SqlExecute
For the com object manager, we did not generate a callstack file. Instead we had to generate an FDR from the corestack.

We can find out the process ID from the core file name.
e.g. if the core file name is core_a12345_siebmtshmw_69318_80758_1336649
Then the crashing process ID will be the last set of numbers, in this case – 1336649
If you run pflags against the core dump, you can get the thread id.
If you type pflags core_a12345_siebmtshmw_69318_80758_1336649

You will get an output with several threads. E.g.

/1045: flags = DETACH|STOPPED pollsys(0x4,0x1,0xd7a7d860,0x0)
why = PR_SUSPENDED

/1046: flags = DETACH
sigmask = 0xfffffeff,0x0000ffff cursig = SIGSEGV
Here the thread ID will be 1046. It is the number before the SIGSEGV or SIGBUS or SIGUSR instance.
After this run this SQL – select srvr_comp_name, srvr_logfile_name:
srvr_name,srvr_user_name from s_srm_task_hist
where srvr_thread_id_val= ‘1046’ and srvr_proc_id_val = ‘1336649’;
srvr_user_name will give you the userid that caused the crash, srvr_logfile_name is the object manager logfile name.
This table exists from Siebel 8x. If you are working with Siebel 7x, to get the object manager log file,
please go to the enterprise log folder where the crash happened and type (for bash)
Grep “1336649” *.log | grep “1046”
Getting the userid is more complex in 7x. You need to convert the FDR file to CSV first.

select all data (Control + A) and then select Filter in the data tab in excel.
After this select all data from the first column – FdrID
Then select Data-> AZ and order id like that.

After that in the 3rd column (ThreadID), filter data so that only this thread shows.Now we need to find out the name of our Application in Tools. It is normally something like Siebel eFinancials or Siebel Power Communication depending on the module.
If your application is named Siebel eFinancials, search for Siebel+eFinancials in this excel sheet.

You should see something like 100J+Siebel+eFinancials.100J will be the row id of the user that caused this crash. Run this SQL
Select login from s_user where row_id = ‘100J’;
This will give you the user login.
After that ask the user what he was doing, the easiest way.
If the user cannot explain, it is more painful to analyze.This is what Oracle says about this

The crash has been reported on 8.1.1.3, and has been reproduced internally in 8.1.1.0 and 8.1.1.4 fix pack, using standard SRF. Hence, this crash may happen with any 8.1.1.x fix pack.

There are two possible workarounds as follows:
1. Disable Database Connection Pooling
The three parameters that control Siebel Database Connection Pooling and Multiplexing are described below:
o Min Number of Dedicated database (DB) Connections (alias is MinTrxDbConns)
o Min Number of Shared DB Connections (alias is MinSharedDbConns)
o Max Number of Shared DB Connections (alias is MaxSharedDbConns)

Setting the above parameters to -1 will disable DB Connection Pooling and Multiplexing. When DB Connection Pooling and Multiplexing are disabled, each user creates a database connection at session creation and releases it at session termination.

Note: Make sure to have enough resources/licenses on the database server to accommodate the number of concurrent database sessions as the database my encounter severe performance impact due to the number of simultaneous sessions with disabled database connection pooling.
2. Reduce the ObjMgrSqlCursorLog event log level to default setting (1)

Keyword – avoid crash keep ObjMgrSqlCursorLog=1

Engineering has found on their investigation that a higher ObjMgrSqlCursorLog event logging level makes the crash happen more frequently. In internal tests, it was not possible to reproduce the crash using the above scenario with ObjMgrSqlCursorLog event log level set to 1.
Long Term Solution:
Below are the fixes currently available at the time of this document edition:

8.1.1.1 QF01CP Patch: 12804472
8.1.1.3 QF03AY Patch: 12764211
8.2.2 (no longer reproducible)

 

Advertisements
Categories: Oracle, Siebel Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: