Solved

Perfomance problem of JDBC connection

Posted on 2006-06-29
8
2,570 Views
Last Modified: 2013-12-10
Database: Oracle 10g
App Server: Weblogic 8.1

I am using following JDBC driver.

Manifest-Version: 1.0
Specification-Title: Oracle JDBC driver classes for use with JDK14
Created-By: 1.4.2_08 (Sun Microsystems Inc.)
Implementation-Title: ojdbc14_g.jar
Specification-Vendor: Oracle Corporation
Specification-Version: Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Version: Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Vendor: Oracle Corporation
Implementation-Time: Wed Jun 22 19:13:16 2005

I have two problems for perfomance.

First: I execute following query from JDBC. It takes 500 msecs but from toad it takes only 80 msecs.

SELECT /*+ FIRST_ROWS(1) */
       wo.workorder_id workorder_id, wo.invoice_id invoice_id,
       wo.contractor_id contractor_id, wo.tech tech, wo.corp corp,
       wo.status_code status_code,
       TO_CHAR (wo.workorder_date, 'MM/DD/YYYY') workorder_date,
       wo.house house, wo.cust cust,
       wo.parent_workorder_id parent_workorder_id,
       TO_CHAR (wo.created, 'MM/DD/YYYY') created, wo.created_by,
       TO_CHAR (wo.updated, 'MM/DD/YYYY') updated, wo.updated_by updated_by,
       wo.comments comments, wo.process_type process_type,
       wo.cnt_submit_count cnt_submit_count,
       wo.wo_exception_type wo_exception_type,
       TO_CHAR (wo.fin_date, 'MM/DD/YYYY') fin_date, wo.alert_flag alert_flag,
       wo.zip, TO_CHAR (wo.approved_date, 'MM/DD/YYYY') approved_date,
       wo.approved_by, wo.weekending_id weekending_id,
       wo.request_weekending_id request_weekending_id,
       wo.corp || '-' || wo.house || '-' || wo.cust ACCOUNT,
       li.lineitem_id lineitemid, li.reason_code reasoncode,
       NVL (reason.VALUE, '') reasonvalue, li.price price,
       li.adj_price adjprice, li.qty, li.price * li.qty EXTEND,
       li.job_cat_id jobcatid, jt.NAME jobtypename, cat.NAME CATEGORY,
       r.NAME regionname, t.NAME tiername, a.entity entity, a.dept dept,
       a.ACCOUNT ACCOUNT, a.event event, a.PERFORMANCE PERFORMANCE,
       a.program program, a.interco interco, a.paygroup paygroup
  FROM invoice i,
       workorder wo,
       lineitem li,
       job_category jc,
       jobtype jt,
       CATEGORY cat,
       region r,
       tier t,
       region_tier rt,
       weekending we,
       reason reason,
       area a
 WHERE i.invoice_id = wo.invoice_id
   AND wo.workorder_id = li.workorder_id
   AND li.job_cat_id = jc.job_cat_id
   AND jc.category_id = cat.category_id
   AND jc.jobtype_id = jt.jobtype_id
   AND wo.zip = rt.zip
   AND wo.corp = rt.corp
   AND rt.region_id = r.region_id
   AND rt.tier_id = t.tier_id
   AND wo.weekending_id = we.weekending_id
   AND li.reason_code = reason.reason_code(+)
   AND wo.corp = a.corp
   AND wo.zip = a.zip
   AND wo.invoice_id = 318
   AND rt.region_id IN (1, 2, 3, 4, 5, 6)

Second:
When I set rowprefetch value to 500 then also it fetches not more then 20 rows. Only iteration takes 200 msecs for 450 records.

pStmt = (OraclePreparedStatement) conn.prepareStatement(sSql);
logger.info("pStmt.getFetchSize(): " + pStmt.getFetchSize());
logger.info("pStmt.setRowPrefetch(): " + pStmt.getRowPrefetch());
pStmt.setRowPrefetch(500);

rs = pStmt.executeQuery();

while (rs.next()) {
count++;
logger.info("count: " + count);
}

Thanks in Advance.

Manoj.
0
Comment
Question by:mviramgama
  • 5
  • 3
8 Comments
 
LVL 3

Expert Comment

by:yuseungkim
Comment Utility
Did you check connection pool such as pool size, Inactive Connection Timeout and also make sure that cache prepare statements
It might be application is not releasing JDBC resource after finishes using them.

Are those response time reported after you ran same SQL with bind variable several times ?
If somehow Oracle believes it is different SQL statement and try to parse it every time, it will have such overhead.

How about network latency ? Did you run TOAD in the same machine where WLS is running ? If not, can you please check network response time by either ping or traceroute / tracert

If you already check out usual bottleneck of performance such as machine's CPU, Memory, Network, DISK I/O, then you can focus on WebLogic.

Good Luck

Andy
0
 

Author Comment

by:mviramgama
Comment Utility
This 500 msecs is for executing of only 1 statement.
rs = pStmt.executeQuery();

So connection issue doesn't come into picture. But still pool size, timeout is ok.
How I can check cache prepare statements?
Same query I ran several times thru application. But still no effect on 500 msecs.
I am running this application on my local computer. So toad and localhost are on the same machine. So no network issue.
I have 2GB RAM. No CPU or memory issues.
0
 
LVL 3

Expert Comment

by:yuseungkim
Comment Utility
By default, statement caching is enabled for any connection pool. In order to configure the statement cache for the pool, select the connection pool from the left frame of the Administration console, and then navigate to the Configuration/Connections tab in the right frame.

You will see following configuration settings

Statement cache size. Default is 10 statements per pool connection
Statement cache type. Default is LRU cache.

I believe you are using Datasource object as BEA recommends.

And also can you please make sure that WLS is not busy for Garbage collection
0
 

Author Comment

by:mviramgama
Comment Utility
Following config
--------------------------
Name: focisPool
Initial Capacity:      1
Maximum Capacity: 15
Capacity Increment: 1
Statement Cache Type:       LRU
Statement Cache Size:      10

I created datasource manually from admin console.
Name: focisDS
JndiName: focisDS
PoolName: focisPool

How to check WLS is not busy for Garbage collection ??
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Expert Comment

by:yuseungkim
Comment Utility
If you're using BEA JRockit as JVM, check it from Administration console, Server > Monitoring > JRockit and it will have heap information and GC information

One more place you can check whether there is something happen in Weblogic or not is checking Java Stack Trace as you know.

I believe you're running on Windows platform.

Let's try to get Java Stack Trace first to find out what is going on with WebLogic Server.

As you know, you can generate Java Stack Trace by entering the key sequence <ctrl><break> in the window where the java program is running.

If your're running on either Linux or UNIX, please send signal 3 using "kill -3 <PID of WebLogic JVM>"

Please refer "http://java.sun.com/developer/technicalArticles/Programming/Stacktrace/"

Good Luck

Andy
0
 
LVL 3

Expert Comment

by:yuseungkim
Comment Utility
If you specify -verbose:gc for JVM, then garbage collection information will be recorded in the server log file.
0
 

Author Comment

by:mviramgama
Comment Utility
Under Server > Monitoring there is no JRockit option
Only General, Performance, Security, JMS, JTA option.

Here is Java Stack Trace


"ExecuteThread: '2' for queue: 'weblogic.socket.Muxer'" daemon prio=5 tid=0x03f63c60 nid=0x1c90 runnable [4bbf000..4bbfd8c]
        at weblogic.socket.NTSocketMuxer.getIoCompletionResult(Native Method)
        at weblogic.socket.NTSocketMuxer.processSockets(NTSocketMuxer.java:85)
        at weblogic.socket.SocketReaderRequest.execute(SocketReaderRequest.java:32)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

"ExecuteThread: '1' for queue: 'weblogic.socket.Muxer'" daemon prio=5 tid=0x03364df8 nid=0x15e8 runnable [4b7f000..4b7fd8c]
        at weblogic.socket.NTSocketMuxer.getIoCompletionResult(Native Method)
        at weblogic.socket.NTSocketMuxer.processSockets(NTSocketMuxer.java:85)
        at weblogic.socket.SocketReaderRequest.execute(SocketReaderRequest.java:32)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

"ExecuteThread: '0' for queue: 'weblogic.socket.Muxer'" daemon prio=5 tid=0x0355d408 nid=0x1d08 runnable [4b3f000..4b3fd8c]
        at weblogic.socket.NTSocketMuxer.getIoCompletionResult(Native Method)
        at weblogic.socket.NTSocketMuxer.processSockets(NTSocketMuxer.java:85)
        at weblogic.socket.SocketReaderRequest.execute(SocketReaderRequest.java:32)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

"ListenThread.Default" prio=5 tid=0x02fe6c50 nid=0x130c runnable [46df000..46dfd8c]
        at java.net.PlainSocketImpl.socketAccept(Native Method)
        at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:353)
        - locked <0x11d9a5c0> (a java.net.PlainSocketImpl)
        at java.net.ServerSocket.implAccept(ServerSocket.java:448)
        at java.net.ServerSocket.accept(ServerSocket.java:419)
        at weblogic.socket.WeblogicServerSocket.accept(WeblogicServerSocket.java:26)
        at weblogic.t3.srvr.ListenThread.accept(ListenThread.java:735)
        at weblogic.t3.srvr.ListenThread.run(ListenThread.java:301)

"ExecuteThread: '3' for queue: 'JMS.TimerClientPool'" daemon prio=5 tid=0x0333cb50 nid=0x1d34 in Object.wait() [3e9f000..3e9fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11cdcad8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11cdcad8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '2' for queue: 'JMS.TimerClientPool'" daemon prio=5 tid=0x0333c1f0 nid=0x1c5c in Object.wait() [3e5f000..3e5fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11cdcb58> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11cdcb58> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '1' for queue: 'JMS.TimerClientPool'" daemon prio=5 tid=0x0304d988 nid=0x1a18 in Object.wait() [3e1f000..3e1fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11cdcbd8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11cdcbd8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '0' for queue: 'JMS.TimerClientPool'" daemon prio=5 tid=0x0304d828 nid=0x197c in Object.wait() [3ddf000..3ddfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11cdcc58> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11cdcc58> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"Thread-7" daemon prio=5 tid=0x0336e518 nid=0x1dc0 in Object.wait() [3d9f000..3d9fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11cb5dc8> (a java.util.TaskQueue)
        at java.util.TimerThread.mainLoop(Timer.java:429)
        - locked <0x11cb5dc8> (a java.util.TaskQueue)
        at java.util.TimerThread.run(Timer.java:382)

"ExecuteThread: '0' for queue: 'JMS.TimerTreePool'" daemon prio=5 tid=0x03030d20 nid=0x1f5c in Object.wait() [3d5f000..3d5fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11c79598> (a weblogic.jms.backend.BETimerTree)
        at weblogic.jms.backend.BETimerTree.execute(BETimerTree.java:146)
        - locked <0x11c79598> (a weblogic.jms.backend.BETimerTree)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)

"Thread-6" prio=5 tid=0x030a0980 nid=0x1848 in Object.wait() [3d1f000..3d1fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11c36410> (a java.util.TaskQueue)
        at java.util.TimerThread.mainLoop(Timer.java:429)
        - locked <0x11c36410> (a java.util.TaskQueue)
        at java.util.TimerThread.run(Timer.java:382)

"weblogic.health.CoreHealthMonitor" daemon prio=5 tid=0x032b7d00 nid=0x11a4 waiting on condition [3cdf000..3cdfd8c]
        at java.lang.Thread.sleep(Native Method)
        at weblogic.t3.srvr.CoreHealthMonitorThread.run(CoreHealthMonitorThread.java:163)

"Thread-5" prio=5 tid=0x032dc330 nid=0x194c in Object.wait() [3c9f000..3c9fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x118979e8> (a java.util.TaskQueue)
        at java.util.TimerThread.mainLoop(Timer.java:429)
        - locked <0x118979e8> (a java.util.TaskQueue)
        at java.util.TimerThread.run(Timer.java:382)

"VDE Transaction Processor Thread" prio=2 tid=0x032459d0 nid=0x1e1c in Object.wait() [3c5f000..3c5fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117ee350> (a com.octetstring.vde.backend.standard.TransactionProcessor)
        at java.lang.Object.wait(Object.java:429)
        at com.octetstring.vde.backend.standard.TransactionProcessor.waitChange(TransactionProcessor.java:365)
        - locked <0x117ee350> (a com.octetstring.vde.backend.standard.TransactionProcessor)
        at com.octetstring.vde.backend.standard.TransactionProcessor.run(TransactionProcessor.java:212)

"ExecuteThread: '2' for queue: 'weblogic.admin.RMI'" daemon prio=5 tid=0x03230428 nid=0x1664 in Object.wait() [3c1f000..3c1fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11773fd8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11773fd8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '1' for queue: 'weblogic.admin.RMI'" daemon prio=5 tid=0x0321db68 nid=0x2e0 in Object.wait() [3bdf000..3bdfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11774058> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11774058> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '0' for queue: 'weblogic.admin.RMI'" daemon prio=5 tid=0x0321d108 nid=0x1aec in Object.wait() [3b9f000..3b9fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117740d8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117740d8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '1' for queue: 'weblogic.admin.HTTP'" daemon prio=5 tid=0x0321c718 nid=0x1b04 in Object.wait() [3b5f000..3b5fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11774158> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11774158> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '0' for queue: 'weblogic.admin.HTTP'" daemon prio=5 tid=0x03232180 nid=0x184c in Object.wait() [3b1f000..3b1fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117741d8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117741d8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"weblogic.security.SpinnerRandomSource" daemon prio=5 tid=0x0322e928 nid=0x1b84 in Object.wait() [3adf000..3adfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711068> (a java.lang.Object)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.security.SpinnerRandomBitsSource.run(SpinnerRandomBitsSource.java:60)
        - locked <0x11711068> (a java.lang.Object)
        at java.lang.Thread.run(Thread.java:534)

"weblogic.time.TimeEventGenerator" daemon prio=9 tid=0x0321b940 nid=0x1ae8 runnable [3a9f000..3a9fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117110d8> (a weblogic.time.common.internal.TimeTable)
        at weblogic.time.common.internal.TimeTable.snooze(TimeTable.java:285)
        - locked <0x117110d8> (a weblogic.time.common.internal.TimeTable)
        at weblogic.time.common.internal.TimeEventGenerator.run(TimeEventGenerator.java:118)
        at java.lang.Thread.run(Thread.java:534)

"ExecuteThread: '4' for queue: 'weblogic.kernel.System'" daemon prio=5 tid=0x0322d0b8 nid=0x108 in Object.wait() [3a5f000..3a5fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711148> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711148> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '3' for queue: 'weblogic.kernel.System'" daemon prio=5 tid=0x0322c498 nid=0x14e0 in Object.wait() [3a1f000..3a1fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117111c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117111c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '2' for queue: 'weblogic.kernel.System'" daemon prio=5 tid=0x0322b878 nid=0x11ac in Object.wait() [39df000..39dfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711248> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711248> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '1' for queue: 'weblogic.kernel.System'" daemon prio=5 tid=0x0322ac58 nid=0x1194 in Object.wait() [399f000..399fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117112c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117112c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '0' for queue: 'weblogic.kernel.System'" daemon prio=5 tid=0x0322a850 nid=0x1888 in Object.wait() [395f000..395fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711348> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711348> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '14' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03229258 nid=0x199c in Object.wait() [391f000..391fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117113c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117113c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '13' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03228638 nid=0x1970 in Object.wait() [38df000..38dfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711448> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711448> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '12' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03227a18 nid=0x1898 in Object.wait() [389f000..389fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117114c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117114c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '11' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03226df8 nid=0x1934 in Object.wait() [385f000..385fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711548> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711548> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '10' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03226210 nid=0x1d10 in Object.wait() [381f000..381fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117115c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117115c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '9' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03225590 nid=0x1300 in Object.wait() [37df000..37dfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711648> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711648> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '8' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03224970 nid=0x19cc in Object.wait() [379f000..379fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117116c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117116c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '7' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03223d50 nid=0x19a4 in Object.wait() [375f000..375fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711748> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711748> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '6' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03223130 nid=0x1e58 in Object.wait() [371f000..371fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117117c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117117c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '5' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03222510 nid=0x1ac4 in Object.wait() [36df000..36dfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711848> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711848> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '4' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03221928 nid=0x1b7c in Object.wait() [369f000..369fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117118c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117118c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '3' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03219d80 nid=0x16e8 in Object.wait() [365f000..365fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711948> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711948> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '2' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x032192d0 nid=0x1e68 in Object.wait() [361f000..361fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x117119c8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x117119c8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '1' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x03218870 nid=0x1c1c in Object.wait() [35df000..35dfd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711a48> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711a48> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"ExecuteThread: '0' for queue: 'weblogic.kernel.Default'" daemon prio=5 tid=0x0317d200 nid=0x1968 in Object.wait() [359f000..359fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11711ac8> (a weblogic.kernel.ExecuteThread)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.kernel.ExecuteThread.waitForRequest(ExecuteThread.java:154)
        - locked <0x11711ac8> (a weblogic.kernel.ExecuteThread)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:174)

"Thread-1" daemon prio=5 tid=0x0327b968 nid=0x1820 in Object.wait() [2d7f000..2d7fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x11566410> (a java.util.TaskQueue)
        at java.lang.Object.wait(Object.java:429)
        at java.util.TimerThread.mainLoop(Timer.java:403)
        - locked <0x11566410> (a java.util.TaskQueue)
        at java.util.TimerThread.run(Timer.java:382)

"Signal Dispatcher" daemon prio=10 tid=0x009d0630 nid=0x17f8 waiting on condition [0..0]

"Finalizer" daemon prio=9 tid=0x009cdc38 nid=0xfb4 in Object.wait() [2c3f000..2c3fd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x10f70138> (a java.lang.ref.ReferenceQueue$Lock)
        at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:111)
        - locked <0x10f70138> (a java.lang.ref.ReferenceQueue$Lock)
        at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:127)
        at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:159)

"Reference Handler" daemon prio=10 tid=0x009cc8b8 nid=0x198c in Object.wait() [2bff000..2bffd8c]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x10f701a0> (a java.lang.ref.Reference$Lock)
        at java.lang.Object.wait(Object.java:429)
        at java.lang.ref.Reference$ReferenceHandler.run(Reference.java:115)
        - locked <0x10f701a0> (a java.lang.ref.Reference$Lock)

"main" prio=5 tid=0x00036e68 nid=0x1bc8 in Object.wait() [7f000..7fc38]
        at java.lang.Object.wait(Native Method)
        - waiting on <0x114ab5f0> (a weblogic.t3.srvr.T3Srvr)
        at java.lang.Object.wait(Object.java:429)
        at weblogic.t3.srvr.T3Srvr.waitForDeath(T3Srvr.java:1207)
        - locked <0x114ab5f0> (a weblogic.t3.srvr.T3Srvr)
        at weblogic.t3.srvr.T3Srvr.run(T3Srvr.java:389)
        at weblogic.Server.main(Server.java:32)

"VM Thread" prio=5 tid=0x00a0b6f8 nid=0x195c runnable

"VM Periodic Task Thread" prio=10 tid=0x00a26d38 nid=0x15dc waiting on condition
"Suspend Checker Thread" prio=10 tid=0x009cfce8 nid=0x1188 runnable
0
 
LVL 3

Accepted Solution

by:
yuseungkim earned 125 total points
Comment Utility
It looks like you have different value for the Oracle Prefetch Value other than default value, which is 10.

If you do, can you please try to adjust that value and see whether you see any performance difference.

Oracle Row-Prefetching Limitations
There is no maximum prefetch setting, but empirical evidence suggests that 10 is effective. Oracle has never observed a performance benefit to setting prefetch higher than 50. If you do not set the default row-prefetch value for a connection, then 10 is the default.

A statement object receives the default row-prefetch setting from the associated connection at the time the statement object is created. Subsequent changes to the default connection row-prefetch setting have no effect on the statement row-prefetch setting.

If a column of a result set is of data type LONG or LONG RAW, that is, the streaming types, JDBC changes the statement row-prefetch setting to 1, even if you never actually read a value of either of those types.

Andy K
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now