Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Training To Help Make Your Systems Better, Faster
For more valuable Oracle tips and tricks just subscribe to our ezine and every month we'll send you some
tips and tricks to help you make your Oracle systems better, faster and smarter.
Cause of degraded application performance
Various questions on Oracle
From: Chakersh Katare: Hi, I am facing problem related to performance. I moved all my SQL
statements from Client layer to Database layer in the form of Stored Procedures. I was thinking
that performance of my Application will be improved
but reverse is happening, performance is going
down by nearly 30% . Why is it happening can u
suggest some valuable ideas so that i can improve
performance of my database.
We can only guess that there are other factors are the
cause of this loss of performance such as more users, or
adding/dropping indexes. Our suggestions would be make
sure you analyse tables and indexes to give the optimiser
accurate statistics. Pin the most frequently used packages
into memory. Query the database to find out which
queries are taking the most time or use the most i/o and
then set about optimising them. Also make sure the
Oracle server has enough real (physcial) memory on the
machine where it is running. If PL/SQL code has been taken from the client and put on the server the
overall memory requirements may well have increased. You will need to look at the performance statistics
at the operating system level to determine this. Look for processes being swapped out of memory and/or
very high memory utilisation. Disk access is the slowest operation and swapping processes in and out of
memory will cause very poor performance.
For more help with Oracle performance tuning see the many free articles and tutorials we have on our site
or consider taking some of our formal Oracle training courses. The Oracle resources page has book
recommendations as well as links to OTN and other useful sites.
From Saritha V: I would appreciate if u could answer these questions and provide me with links
and resources where i could find info. 1. What are different types of cursors? 2. What cursor type
do you use to retrieve multiple recordsets? 3. What are different types of index? 4. which type of
index does the oracle create when we define primary key? 5. what are tradeoffs having index? 6.
What is the diference between "NULL in C" and "NULL in Oracle?" 7. What are "HINTS"? What is
"index covering" of a query?
That's a lot of questions and to answer them fully would require half a book at least. We would recommend
that you read some of the books recommended on the Oracle resources page.
Meanwhile here are some brief answers.
1. There are implicit and explicit cursors for queries, updates and deletes
2. If you're using PL/SQL you need to declare an explicit cursor to retrieve multiple records
3. There are two main types of index: unique and non-unique, there also bit-map indexes and b-tree
indexes which describe the physical structure of the indexes.
4. A primary key index is always unique - also columns in the primary key may not be null
5. The trade-offs to consider when using indexes is that whilst read operations are quicker (hopefully),
insert update and delete operations are slower because of the need to maintain the index(es) in line with the
6. NULL in Oracle means the value is undefined
7. Hints are just instructions that the Oracle optimiser will try to use to perform the required operation.
See the many free articles and tutorials we have on our site for more help or consider taking some of our
formal Oracle training courses.
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make
your systems better, faster and smarter.
© Smartsoft Computing Ltd, Bristol, England
Need help? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their