Sitemap

Amicuk Programming Answers

Query Hangs after adding new Indexes

-0001-11-30   Views:0

Advertisement

Hi Gurus, In order to fix some performance issues in the integration and UI, in our implementation we have added some new indexes like FST_NAME,LAST_NAME for S_CONTACT table. We did not make any new configurations or SRF compilations. No new data is

Hi Gurus,
In order to fix some performance issues in the integration and UI, in our implementation we have added some new indexes like FST_NAME,LAST_NAME for S_CONTACT table.
We did not make any new configurations or SRF compilations. No new data is loaded into database.
But since today morning when we query in the application (Contact List Applet) the application simply hangs. We tried it with many sessions from different machines.
We thought of, this could be because of some broken earlier data source connections within the task/session. But the query in other entities is working totally fine.We tried Accounts and Assets, they are working fine.
We did a spool of SQL and executed the same statement in TOAD.It is strange, the statement is executed in just few milli seconds, as it was earlier.
We could not find root cause.
Could somebody give some ideas.
Regards

The replay answer
Advertisement
Hi Robert,
In the Object Manager session log file I found the following trace after the SQL statement.
T2.PR_EMP_ID = T11.PAR_ROW_ID (+) AND
T25.PR_DEPT_OU_ID = T3.ROW_ID (+) AND
T25.PR_DEPT_OU_ID = T21.PAR_ROW_ID (+) AND
T25.PR_DEPT_OU_ID = T24.PAR_ROW_ID (+) AND
T25.PR_SYNC_USER_ID = T10.ROW_ID (+) AND
T25.PR_SYNC_USER_ID = T15.PAR_ROW_ID (+) AND
(T25.EMP_FLG = 'N') AND
(T25.PERSON_UID LIKE :3)
ObjMgrSqlLog     Detail     4     0003f7324a41481b:0     2009-07-27 15:03:09     Bind variable 1: 0-5220
ObjMgrSqlLog     Detail     4     0003f7324a41481b:0     2009-07-27 15:03:09     Bind variable 2: 0-5220
ObjMgrSqlLog     Detail     4     0003f7324a41481b:0     2009-07-27 15:03:09     Bind variable 3: S1028056768*
ObjMgrSqlLog     Debug     5     0003f7324a41481b:0     2009-07-27 15:03:09     User search spec: S1028056768*
ObjMgrSqlLog     Debug     5     0003f7324a41481b:0     2009-07-27 15:03:09     Named search [Applet Search Spec Named Search]: [Employee Flag]='N'
ObjMgrSqlLog     Debug     5     0003f7324a41481b:0     2009-07-27 15:03:09     User sort spec:
ObjMgrSqlLog     Debug     5     0003f7324a41481b:0     2009-07-27 15:03:09     System sort spec:
ObjMgrSqlLog     Debug     5     0003f7324a41481b:0     2009-07-27 15:03:09     0xf1fcf0f8: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWEFrame::ExecuteQuery()() +0xf8
0xf1fcb62c: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWEFrame::DoInvokeMethod(const unsigned short*,CSSStringArray&,SSstring&)() +0x1ec
0xf20b6c80: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWEFrameList::DoInvokeMethod(const unsigned short*,CSSStringArray&,SSstring&)() +0x280
0xebcf0fbc: /siebel/siebsrvr/lib/libswcacmfr.so!unsigned CSSSWEFrameListBase::DoInvokeMethod(const unsigned short*,CSSStringArray&,SSstring&)() +0x77c
0xe67e17e4: /siebel/siebsrvr/lib/libswfafcfr.so!unsigned CSSSWEFrameListFINGenericButton::DoInvokeMethod(const unsigned short*,CSSStringArray&,SSstring&)() +0x164
0xf1fcb158: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWEFrame::InvokeMethod(const unsigned short*,CSSStringArray&,SSstring&)() +0x118
0xf20e7750: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWEFrameMgrInternal::InvokeAppletMethod(CSSSWEHtmlStream*,CSSSWEArgs*,WWEReqModInfo*,WWECbInfo*&,CSSStringArray&)() +0xd90
0xf20ca6cc: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWECmdProcessor::InvokeMethod(CSSSWEHtmlStream*,CSSSWEArgs*,WWEReqModInfo*,WWECbInfo*&)() +0x88c
0xf20c8c7c: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWECmdProcessor::_ProcessCommand(CSSSWEHtmlStream*,WWEReqModInfo*,WWECbInfo*&)() +0x87c
0xf20cea1c: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWECmdProcessor::ProcessCommand(CSSSWEGenericRequest*,CSSSWEGenericResponse*,WWEReqModInfo*&,WWECbInfo*&)() +0x9bc
0xf20cddb8: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSSWECmdProcessor::ProcessCommand(CSSSWEHttpRequest*,CSSSWEHttpResponse*,WWECbInfo*&)() +0xd8
0xf2321724: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSServiceSWEIface::Request(CSSSWEReqRec*,CSSSWEResponseRec*)() +0x404
0xf231e3e0: /siebel/siebsrvr/lib/libsscaswbc.so!unsigned CSSServiceSWEIface::DoInvokeMethod(const unsigned short*,const CCFPropertySet&,CCFPropertySet&)() +0xa80
0xf969b1a4: /siebel/siebsrvr/lib/libsscfom.so!unsigned CSSService::InvokeMethod(const unsigned short*,const CCFPropertySet&,CCFPropertySet&)() +0x244
0xf9b616e4: /siebel/siebsrvr/lib/libsstcsiom.so!int CSSSIOMSession::ModInvokeSrvcMethod(const unsigned short*,const unsigned short*,const unsigned short*,SSstring&)() +0x124
0xf9b6881c: /siebel/siebsrvr/lib/libsstcsiom.so!int CSSSIOMSession::RPCMiscModel(SISOMRPCCode,SISOMArgType,unsigned long,CSSSISOMRPCArgList*,CSSSISOMRPCArgList*,int&,void*)() +0x5bc
0xf9b5efb8: /siebel/siebsrvr/lib/libsstcsiom.so!int CSSSIOMSession::HandleRPC(SISOMRPCCode,SISOMArgType,unsigned long,CSSSISOMRPCArgList*,CSSSISOMRPCArgList*,int&,void*)() +0xb98
0xfa5144f8: /siebel/siebsrvr/lib/libsssasos.so!unsigned CSSClient::HandleOMRPC(CSSClientReq*)() +0x78
0xfa511698: /siebel/siebsrvr/lib/libsssasos.so!unsigned CSSClient::HandleRequest(CSSClientReq*)() +0x2f8
0xfa50c3e4: /siebel/siebsrvr/lib/libsssasos.so!unsigned _IsThreadPoolEnabled(const bool,const void*,bool&)() +0x8c4
0xfa50d45c: /siebel/siebsrvr/lib/libsssasos.so!int SOMMTServer::SessionHandleMsg(smiSisReq*)() +0x1bc
0x1ff7ac: /siebel/siebsrvr/bin/siebmtshmw!int smiMainThread::CompSessionHandleMsg(smiSisReq*)() +0x16c
0x2237fc: /siebel/siebsrvr/bin/siebmtshmw!int smiMessageQ::ProcessMessage(smiMsgQItem*,long,int)() +0x93c
0x2220c4: /siebel/siebsrvr/bin/siebmtshmw!int _smiMessageQ::ProcessRequest(void*,void*,void*&)() +0x244
0x216774: /siebel/siebsrvr/bin/siebmtshmw!int _smiWorkQueue::ProcessWorkItem(void*,void*,void*&)() +0xd4
0x2161e0: /siebel/siebsrvr/bin/siebmtshmw!int _smiWorkQueue::WorkerTask(void*)() +0x300
0x20428c: /siebel/siebsrvr/bin/siebmtshmw!int
It is stopped at the above line since a long time.
And one more observation I made is.
In the same applet for a normal other query, the working trace is as follows.
0x2161e0: /siebel/siebsrvr/bin/siebmtshmw!int _smiWorkQueue::WorkerTask(void*)() +0x300
0x20428c: /siebel/siebsrvr/bin/siebmtshmw!int SmiThrdEntryFunc(void*)() +0x46c
0xfe75be88: /siebel/siebsrvr/lib/libsslcosd.so!void*OSDInstallHook(OSDHK,void*)() +0x2a8
0xfe195730: /siebel/siebsrvr/mw/lib/libmfc400su.so!unsigned _AfxThreadEntry(void*)() +0x100
0xfcdf1288: /siebel/siebsrvr/mw/lib/libkernel32.so!void MwThread(void*)() +0x23c
0xfc8c4990: /lib/libc.so.1!_thr_slot_offset() +0x468
ObjMgrSqlCursorLog     Prepare     5     00045fbb4a414cc9:0     2009-07-27 15:03:01     Begin: PrepareStmt for Sql Cursor at 9c30618
ObjMgrSqlCursorLog     Prepare     5     00045fbb4a414cc9:0     2009-07-27 15:03:01     End: PrepareStatement for Sql Cursor at 9c30618
ObjMgrSqlLog     Detail     4     00045fbb4a414cc9:0     2009-07-27 15:03:01     
***** SQL Statement Prepare Time for SQL Cursor with ID 9C30618: 0.002 seconds *****
This statement is executed in within a second from UI applet.
Please look into this.
Edited by: user4619223 on Jul 27, 2009 3:05 PM

Go to See the other 12 answers

Query Hangs after adding new Indexes

Category:DefaultRelease time:-0001-11-30Views:130

Hi Gurus, In order to fix some performance issues in the integration and UI, in our implementation we have added some new indexes like FST_NAME,LAST_NAME for S_CONTACT table. We did not make any new configurations or SRF compilations. No new data is[More]

Performance drop around 20% after adding an index

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have an xml file (around 300 Kb, 3500 Nodes), the application internal works with dbxml handles but for display purposes I need xpaths for each node. First I was calculating them on request, but for requests with a lot of nodes it just takes to[More]

Query time lengthened after additional indexes added

Category:DefaultRelease time:2015-10-11Views:130

I had been frequently getting results from my query in about .5 - 1.5 seconds. However, I added additional indexes to my container on fields that I am not even using in my query at the moment. When I did this, my query went to about 35 seconds. Can a[More]

Sql query hangs close to the end

Category:DefaultRelease time:-0001-11-30Views:130

Hi: I am on 10.2.0.3. My rather simple query hangs for a few minutes close to the end. It's supposed to return 288 rows and it immediately returns 285, then hangs for 4-5 min, than returns the remaining 3 rows. Kind of similar behavior to using "Firs[More]

Query hangs on Value Help

Category:DefaultRelease time:-0001-11-30Views:130

Hi, We have created a query in which we are using quantity conversion ( defined in RSUOM ) , the target quantity is to be taken from a variable. The quantity conversions is running fine but when we do a value help for the target quantity variable in[More]

Query Hangs while Parsing

Category:DefaultRelease time:-0001-11-30Views:130

In one of our databases, one of the Select query hangs at the parsing stage. The query was working fine few days back. How and what can be traced to see the cause of this hang. Database is in 10.2.0.3 and statistics has been gathered for all the affe[More]

Query hangs with outer query

Category:DefaultRelease time:-0001-11-30Views:130

Hi, OS: SPARC 64 bit Oracle Version: 10.2.0.3.0 64 bit My query hangs when I include the outer query. When I execute without outer query It comes out in a fraction of second but when I includes outer query it starts hang Below is my original query wh[More]

Query not considering function based index in oracle 11g

Category:DefaultRelease time:2015-10-11Views:130

I have a query which used Function Based Index when run in oracle 9i but when I run the same query without any changes, it does not consider index. Below is the query: SELECT distinct patient_role.domain_key, patient_role.patient_role_key, patient_ro[More]

Acrobat 9 hangs when adding a text box

Category:DefaultRelease time:2015-10-11Views:130

It seems that any installation of Acrobat 9 I have come across, Acrobat hangs when adding a text box from "Forms>Add or Edit Fields". I may need to apply an update or two but this problem seems very consistent and reproducable. I plan on tryi[More]

Oracle 9.2.0.6; query hangs if partition doesn't exist

Category:DefaultRelease time:-0001-11-30Views:130

Hallo all, this is the first time I post on this forum. I work in a group of PL\SQL developers in Italy. Few day ago we had a problem with a query and like to find out what the problem is. We have an Oracle DB version 9.2.0.6 (old I know) in a produc[More]

ORA-00604 ORA-00904 When query partitioned table with partitioned indexes

Category:DefaultRelease time:-0001-11-30Views:130

Got ORA-00604 ORA-00904 When query partitioned table with partitioned indexes in the data warehouse environment. Query runs fine when query the partitioned table without partitioned indexes. Here is the query. SELECT al2.vdc_name, al7.model_series_na[More]

Hot
I would like to use on your iphone 4s ICloud, but when I try to run ICloud appears in this iPhone is activated limiting the number of free accounts. What do I do? p.s( Sorry for the broken English)Make the accounts on a computer. I believe the iPhone [More]
I've bought video clips from iTunes Mudisc Stores. Some have been downloaded as bonus tracks when I've bought some albums, some have been bought recently as stand-alone videoclips. The issue is that the first kind are "tagged" as movies, the sec [More]
I've created a beveled box in FW that contains a simple input form.   I know how to build a form in DW, but I don't know how to bring the beveled box into DW so that the form elements sit on top of the beveled box.  Help, please....and thanks in adva [More]
Hello Folks! Perhaps another one have the same problem as me: After synching my iPad2 under 10.6.8 with a MacPro 99 portrait orientated fotos from 100 are with huge white areas sometimes at the top, at the bottom or even both sides. Non of the horizo [More]
I have a WRT160n wireless router, hooked up to 2 laptops(wireless) 1 disktop (My computers do not lose thier connection), and I use VOIP.  The phone adapter is a Linksys spa2102 and the modem is a ADSL2x Bridge/Router, I believe its made by Zone. My [More]
Hi Everyone, In PI system When i am entering into integration directory.I am getting NO authorization for this action.I have given all the PI specified roles. Regards, ChanduHi chandu Have you added following roles for the user? SAP_XI_ADMINISTRATOR_ [More]
An iBook purchased text book no longer displays diagrams so I have tried to restore a previous backup but when I open my iBook library and then enter Time Machine it opens a Finder window for the Desktop, if I then navigate to iBooks app I just get i [More]
Hi Frnds, When i receive a partial receipt (of an invoice) from a customer using f-28, i am getting the link between invoice number and accounting document number from a table (BSEG), but when i am receiving the final settlement amount from the custo [More]
I have iPhone4 with updated software 6.0.1. Voice nevigation for map is not working. I could not see the voice options in setting/mapp.Voice navigation is not available for the iPhone 4Read other 2 answers [More]
 Pleeeeeease, Can anyone help? each time I open outlook express on my PC to check e-mails it deletes all the emails from the corresponding email account on my blackberry, does anybody have any ideas??IF you are using Outlook to download your email to [More]