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
Dear All, I just joined the lenovo world by buying a yoga 3 pro. Unfortunately, I am having an unusual problem. I plugged in the device and tried to turn it on. It did not work. There is no picture on the screen, and there is the light getting on and [More]
Here's the scenario: Customer sends me an illustrator file with an embedded image. I need to check the resolution of the image to see see if it is high resolution enough for print. I delete all art but the image in illustrator and then save that file [More]
Hi, How can I display a message when creating or changing sales orders? I need the configuration steps for 4.6. Thanks, Sai.Hi, First you need to make an entry of your pop up message in the sales text view of the material master and when the user ent [More]
Hi Experts, Very much hopeful that I will get solutuion of below: How or where to see changes done in production / process order. e.g Suppose I have created one production order. Then release it in CO02. Then I changed order quantity from 100 to 200 [More]
Hi Experts I am implementing e-Recruitment 3.0 at one of client site. Here we are looking out for integration with the third party internet service provider i.e. job sites. Wherein we will able to surf and search external job  site for the suitable r [More]
Hello, I downloaded a set of brushes as an ABR file, pasted it into the 'brush' folder in the 'presets' folder, but when I try to load my brushes I can't see any ABR files in my brushes folder. Does anyone have any ideas? I'd be so grateful if anyone [More]
I saw this discussion that took place in 2010 (iTunes U in Russia). But there is still no Russian Federation in the country field here. We really want to add russian universities to iTunes U and grant them an opportunity to create and distribute cour [More]
I have a new 13" MBP with Mountain Lion Installed.  I am using a display that has a 1600x1200 resolution, however, when I go into the preferences to change the resolution, it will not allow me to set my display resolution to these settings.  I have u [More]
Hello. We have two system: development and production. Same report display different results. In development time 00:00:00 and 24:00:00 displayed in output of report. In production system same report in output instead time 00:00:00 and 24:00:00 displ [More]
hello, I have a linux machine, when i do a top command , i can see a oracle process that consumming a high CPU (PID=12958) I want to find a sql request that correspond to this PID I want to know , what is the view that contain the PID column. PID USE [More]