Solved

Oracle JDBC Performance : Query Tuning

Posted on 2002-04-23
17
1,273 Views
Last Modified: 2007-12-19
Oracle 8.1.6, JDBC, JSP, Tomcat3.2.3, JDK1.3.1, 10-15 Concurrent Users

My application is like this ...
"JSP -> JDBC -> Oracle_Thin/OCI8_Driver -> Oracle8.1.6"

Facing performance issue. Don't know what specific is causing it. I have following questions...

[1]. When the application is running for some time the performance keeps going down. How to know that what queries is consuming what time. Is there any way to know from Oracle as what queries (select, insert, update, trigger etc) have taken what time (the history table), so that I could analyze it.

[2]. Since my application allows the users to build the query, sometime user end up writing querries that takes huge time causing system to almost hangs down and hence the other users suffer. How to set the query timeout at Oracle as well as Java level (setQueryTimeout(int seconds)?).

[3]. Will the Oracle resources be automatically released when a query execution is timedout.

[4]. How to know from Oracle as how many Cursors are open at a given point of time.

[5]. In general what steps one should follow to fine tune the database communications (Java + Oracle). I have around 15 main tables that is being populated. Currently the maximum records in few of these tables are only 3000 (3K). There are 15 other corresponding audit tables that I populate for any record-update in the previous 15 tables. Apart from this I have 40 master tables having 5-20 records each used for choice listing in the GUI and for referencing in the main 15 tables. I have generated no index etc etc. Just plain vanilla tables. Please suggest the performance tuning points, tools etc.

Best Regards
0
Comment
Question by:javaq092999
  • 5
  • 4
  • 3
  • +4
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962443
[1] set up the TopSession utility from Oracle CD, this should give you great insights...

[2] you should not let the users build the complete query, but only give them the option to add parameters. Otherwise you will always end up in the situation that resources are badly used. using Timeout doesn't really help, because some queries WILL take some more time as others...

[3] yes, although not necessarily immediately. Some items can be shared, rows that where read from disk to memory will stay until replaced by other data.

[4]
SELECT sid, user_name ,count(*)
    FROM v$open_cursor
    GROUP BY sid, user_name
[   ORDER BY user_name / count(*) DESC .... ]

[5] tuning is a difficult an ongoing process. This said, you should have indexes on your tables, at least the implicit index used by the primary key.
If you have the option, you should have your database files into several tablespaces on different disk drives/controllers.
Ensure your application only queries for data it REALLY needs, limiting the query in rows and columns the user will see at some point. Don't query the whole table to finally display only 1 record...

CHeers
0
 
LVL 3

Accepted Solution

by:
p_yaroslav earned 100 total points
ID: 6962735
Hi!

As I am true understanding....


You tables is very small and you needn't use indexes at all!
Your problem may be in memory resources.

You may consider tracing most hard sessions:
___________
SQL>connect internal
SQL>alter system set timed_statistics=true;
SQL>dbms_system.set_sql_trace_in_session(SID,SERIAL#)

Where SID and SERIAL# you can select from v$session table.

after that you get dump file (it's location in user_dump_dest - init.ora parameter) for example - 1234.trc.

run:
tkprof 1234.trc 1234.txt.

after that you may see in 1234.txt file statistics abou your sql statements and time for there executions.
__________

But in common case you need read related Oracle documentation "Designing and tuning for performance"

http://technet.oracle.com/doc/oracle8i_816/server.816/a76992/toc.htm


Best regards!
Yaroslav.
0
 
LVL 5

Expert Comment

by:sora
ID: 6962814
for [2] and [3], you can look limit the resource utilization per Oracle user account by creating profiles and assigning it to users.

see below for more...

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem21.htm#2065932


for [4],

select a.sql_text
from v$session s, v$open_cursor o, v$sqlarea a
where
s.saddr=o.saddr and
s.sid=o.sid and
o.address=a.address and
o.hash_value=a.hash_value and
s.schemaname='<the name of the schema>';


You can also limit the maximum number of open cursors, by setting open_cursors in your init<sid>.ora. This only lets your system resources off the hook, but would prevent users from executing new queries when this limit is exceeded

[5]

In addition to the fine comments in the above posts, as you rightly guessed indexes on those tables having > 3000 records might help - but it is really hard to say offhand.

SGA size, shared pool size, system real memory, I/O contention, rollback segment sizing - to name just a few - are what one would look at in the tuning process.


sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6962984
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6963500
It sounds like you have a relatively small Oracle database, and relatively few users so it should be possible to get very good response times even on a modest server.  Oracle tables with only a dozen or two dozen rows may not benefit from indexes (at least from a performance perspective, unique indexes can still be handy from a data integrity perspective to prevent duplicates or to support referential integrity).  All Oracle tables though that have a couple hundred or more rows should have at least one index, or performance will suffer.

Can you give us some details about your database server: number and speed of CPUs; amount of RAM; number of disk drives; RAID or non-RAID; number of other applications running on the server; etc.  Also please tell us your Oracle SGA size and the SGA breakdown (how much for shared_pool (variable size), how much for db_block_buffers (database buffers) etc.
0
 

Author Comment

by:javaq092999
ID: 6965171
p_yaroslav and All

I am not able to execute SQL>dbms_system.set_sql_trace_in_session(1,1)

Still Looking For
-----------------
How to set the Query Time Out before running a query on SQL propmt.

My Oracle Details
-----------------
(Plain Vanilla Desktop :)) Windows 2000, 1 Celeron 466 CPU, 384MB RAM, 10GB HDD, Non-RAID, Oracle 8.1.6, No application running apart from Tomcat, Oracle and my JSP application inside Tomcat

Total System Global Area            68162828 bytes
Fixed Size                             70924 bytes
Variable Size                       40464384 bytes
Database Buffers                    27549696 bytes
Redo Buffers                           77824 bytes
db_files = 1024
open_cursors = 100
db_file_multiblock_read_count = 8
db_block_buffers = 3363
db_block_size = 8192
shared_pool_size = 15728640
0
 
LVL 5

Expert Comment

by:sora
ID: 6965180
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6965199
Hi!

1)For execution of dbms_system.set_sql_trace_in_session(1,1)
 you must have administrative privileges or grant to this package.
Second way for obtain trace:
You may alter user session putting additional code in it:

execute immediate "alter session set sql_trace=true".

2) You may set on server in sqlnet.ora file parameter:
SQLNET.EXPIRE_TIME=10 (after 10 minutes user will be disconnected from server)

Regards,
Yaroslav.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:javaq092999
ID: 6965350
Yaroslav,

1) I tried following and got the error (note : I am not an Oracle expert)

SVRMGR> connect internal/oracle
Connected.
SVRMGR> alter system set timed_statistics=true;
Statement processed.
SVRMGR> dbms_system.set_sql_trace_in_session(1,1);
dbms_system.set_sql_trace_in_session(1,1)
*
ORA-00900: invalid SQL statement
SVRMGR>

Similar error comes when tried on SQL*Plus SQL>

2) Nope you have got my questions wrong. I want Query Execution Timed Out. That is I want to set at SQL> promt a time of 120 sec for QTO. Now if I execute a query that takes Oracle to execute in more than 120 seconds will result in abort of query execution by Oracle.

0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6965388
Hi!

1) More detailed:
SQL> execute dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);

2) FOR QTO you nedd use profiles for users.
You may set in profile:
-CPU/Session
-CPU/Call
-Connect time
-Idle time
-Reads/Session ....

Regards,
Yaroslav.
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 6965946
I think what is happening is that

1.  Cursors are not getting closed and eventually memory problems.  I know that users will get errors on unix system but I am not sure about NT.

2.  Shared pool behaving like a queue (sql statements entering and exiting). Are you using bind variables for queries (you can still use bind variables for queries being generated on the fly)?

3.  Do you have enough memory?  Check if your memory is paging/swappning?

Just monitor the open cursors and other memory for sometime and you will eventually find the culprit.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6966198
An Oracle database running on single 466mHZ CPU with 384MB of RAM and just one hard drive will be *SLOW* especially if that machine also has to run the client interface!

Oracle on NT will be much faster if you can put it on a dedicated server (running nothing else) with at least three SCSI hard drives (six to 10 drives would be nice) and 512MB of RAM.  Ideally you spread out the Oracle data tablespace(s), index tablespace(s), rollback tablespace, temp tablespace, system tablespace, redo logs, and the NT swap file all on separate disks.  If they all have to contend for just one disk, nothing will be fast.

If you have to keep it on that single-disk machine with just 384MB of RAM and run other applications at the same time, you will really need to do a thorough job of tuning Oracle and use NT's Performance Monitor to check on where the bottleneck is (CPU, memory or disk).  It is most likely disk access.  To see the disk counters in Performance Monitor you will need to do:
diskperf -y
from a DOS prompt and reboot, if that hasn't ever been done on that machine.

Tuning Oracle precisely is a complex task.  The two most important variables in the init*.ora file are: shared_pool_size and db_block_buffers.  You may have to adjust those down to reduce (or eliminate if possible) NT's use of the swap file, since that is a performance killer.  Then you need to check the statements that are executed in Oracle with a tool like Oracle's trace utility (or a third-party tool) to see the execution plan and adjust them, or adjust indexes to make them as efficient as you can.  None of this is simple.
0
 
LVL 5

Expert Comment

by:sora
ID: 6966249
javaq

I had posted the URL earlier for you to see how timeouts and resources can be implemented using profiles - but since you refuse to look inside those URLs, here is a snippet:


CREATE PROFILE <profile_name>
   LIMIT SESSIONS_PER_USER    UNLIMITED
   CPU_PER_SESSION            <hundredths of a second>
   CPU_PER_CALL               <hundredths of a second>
   CONNECT_TIME               <total elapsed time in mins>
   LOGICAL_READS_PER_SESSION  DEFAULT
   LOGICAL_READS_PER_CALL     1000
   PRIVATE SGA                15K;

You can then assign this profile to different users

ALTER USER <user_name> PROFILE <profile_name>;

By the way, did you note markgeer's comments on why a system with your configuration is not one where you would expect things to be lightning fast?


sora
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6972700
javaq,
Moderators have gone a long way to close over 20 questions you left open.

Do not let this happen here.

Computer101
E-E Moderator
0
 

Author Comment

by:javaq092999
ID: 7001204
Thank you all for comments, specially markgeer. I picked ideas from almost all the comments but accepting this one as answer as I used this. Also the comments from Markgeer are worth an answer for me. Can we select multiple comments as answer :) ?
0
 
LVL 5

Expert Comment

by:sora
ID: 7001218
You can post a separate question titled "Points for markgeer" and award him any number of points as you see fit. When markgeer posts a comment to the new question, just accept that as an answer

sora
0
 

Author Comment

by:javaq092999
ID: 7001233
;) Good idea! But will not do that!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

708 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

18 Experts available now in Live!

Get 1:1 Help Now