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 want to add two columns to a table with not null and default as 0 for both columns Can i write the whole in one statement or do i have to split statement I tried this, but didn't work alter table DWSODS01.DWT00301_ORD_DTL_OMS add ( COMB_ORD_FLG NUM [More]
I just had what I think was a power supply failure on my HP desktop running Win 8.1.   My issue is, while the machine is away getting fixed, I need to be able to read content from the HDD, to use my HP Laptop to do work in the meantime. I have a USB [More]
Hello, I have a Macbook Pro early 2011. And I am wondering if I can install a sata 600 SSD in my mac. I would like to place the SATA 600 SSD in my optical bay and keep my existing HDD in the normal bay. So, is this possible? RoyOGELTHORPE wrote: Csou [More]
Got my 5s about 3 weeks ago and have been happy with it but it started acting up tonight. Anyone have any idea what might be wrong? It started out of nowhere, and was not dropped or wet... it starts with a line on the bottom of the screen at start up [More]
The error msg states the site may be tempo unavailable or that I have a network problem; even trying to open a new tab will give me the same error. When I attempt to load my own website I get the "unsafe site" msg but switching to http from http [More]
The thread under title "The Microsoft Office cache encountered a problem (with Office 2013 ProPlus Preview/Beta)" described exactly my problem, but discussion on that thread has been locked without any solution to the problem. I'd like to add on [More]
Below is a proposal for organising my photo management system. I'm a hobbiest photographer with over 30 years of pictures to file. I have read extensively, including this forum and sort best practice to developed a plan to do achieve an organised sca [More]
The problem with the charger used to be infrequent and easily fixed by turning the phone off and on, or by taking out the battery. Now it happens about 50% of the time I plug the phone in. After it's been plugged in, when i go to press the send icon, [More]
How can I use webview to display a local html file?Here is a link that will help: "How To Read a File From Your Application Bundle" http://iphoneincubator.com/blog/data-management/how-to-read-a-file-from-your-app lication-bundle but basically yo [More]
Hi I am new to this, and i want to know any java api that supports Touch Chip finger print reader a default that came with TouchChip TCKDU02 sdk does not support java, help me regards B.Rameshwarithat supports Touch Chip finger print reader a default [More]