OraFAQ Forum: Performance Tuning » Performance issue in jsp application

Home » RDBMS Server » Performance Tuning » Performance issue in jsp application (Oracle 10.2.0.3,jsp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issue in jsp application [message #494606]

Wed, 16 February 2011 00:01

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

We are calling a stored procedure in jsp page.
The sql where bottleneck was assumed is giving better performance in sql plus (0.093 seconds) but
when called from jsp its taking too much time. Need your suggesion.

Here is the plan for the sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |     1 |   219 |
|   1 |  SORT GROUP BY                    |                   |     1 |   219 |
|   2 |   NESTED LOOPS                    |                   |     1 |   219 |
|   3 |    NESTED LOOPS                   |                   |     1 |   197 |
|   4 |     NESTED LOOPS                  |                   |     1 |   162 |
|   5 |      MERGE JOIN                   |                   |     1 |   127 |
|   6 |       TABLE ACCESS BY INDEX ROWID | policy_tbl        |     1 |    78 |
|   7 |        INDEX FULL SCAN            | pk_pol_id         |     1 |       |
|   8 |       SORT JOIN                   |                   |  1585 | 77665 |
|   9 |        TABLE ACCESS BY INDEX ROWID| policy_p_tbl      |  1585 | 77665 |
|  10 |         INDEX RANGE SCAN          | pp_param_1      |  1679 |       |
|  11 |      TABLE ACCESS BY INDEX ROWID  | policy_group_tbl  |     1 |    35 |
|  12 |       INDEX RANGE SCAN            | pg_indx_2         |     1 |       |
|  13 |     TABLE ACCESS BY INDEX ROWID   | policy_group_tbl  |     1 |    35 |
|  14 |      INDEX RANGE SCAN             | pg_indx_2         |     1 |       |
|  15 |    TABLE ACCESS BY INDEX ROWID    | policy_dtl_tbl    |     1 |    22 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  16 |     INDEX UNIQUE SCAN             | pk_pg             |     1 |       |
--------------------------------------------------------------------------------

Regards
Ved

Report message to a moderator

Re: Performance issue in jsp application [message #494612 is a reply to message #494606]

Wed, 16 February 2011 01:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

when called from jsp its taking too much time.

So the problem is not in Oracle or the query but in network or application server or its application.

Regards
Michel

Report message to a moderator

Re: Performance issue in jsp application [message #494654 is a reply to message #494612]

Wed, 16 February 2011 05:52

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try running a SQL Trace whilst the JSP is running, then run the trace file through TKPROF

Ross Leishman

Report message to a moderator

Previous Topic:	Bulk Update
Next Topic:	Performance tuning of PL/SQL Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:28:48 CDT 2024