Oracle Consulting and Training
For Better, Faster, Smarter
Oracle Tips and Tricks To Make Your Oracle Systems
Better, Faster & Smarter
As part of our on-line Oracle training - in addition to the articles and tutorials we offer (see here for more
Oracle tips and tricks) - here we answer a few of the questions sent in by our subscribers.
improving database performance
database structure - physical vs. logical
From: Oumar Dieng - I am really new within the oracle world. Today, I would like to be able to
optimize the database performance. I have 57 tables in and each table has indexes throughout a
primary key. All the SQL queries are inside the code
and today I can't remove them. I can re-write them.
Can you give me some good ways to follow in order
to reach my aim.
There isn't much to go on here, there could be many
reasons why the performance of your Oracle database is
poor. For example are the queries using the indexes? The
use of the indexes may be being prevented by the way
the SQL is written or alternatively the query may be
using the index when it would be better for overall
performance not to. It is also possible that you're
querying on a column that does not have an index in
which case you may want to add extra indexes but note
that this will slow down update, insert and delete
operations as there will be more indexes that have to be kept in line with the data.
The general advice we can offer in answer to this is three fold:
1. determine which modules/SQL statements are performing poorly, how much they need to improve, and
find out what they're doing in terms of functionality and database access - for the latter run the queries
through "explain plan"
eg.. SQL> explain plan set statement_id ="my statement" for SELECT
emp_id FROM emp WHERE hire_date < '01-JAN-2015'
2. See our series of articles on Oracle performance tuning
3. repeat steps 1 and 2 for all modules that need tuning and keep going until you get the performance you
need (you may need to create new tables, indexes, or views or use pl/sql, etc).
From: R.Lakshmi Narayanan I want to know about the Extents, Segments. And also how the data
is stored in blocks. How the memory allocation is communicated with the operating system.
From the logical view of database storage you have tablespaces, segments and extents, where a tablespace
comprises one or more segments (of the same type) which in turn comprise one or more extents.
Tablespaces are logical groupings of database object segments such as table segments or index segments or
undo segments. Database objects such as tables, indexes, LOBs, and undo data are stored in tablespaces.
etc will comprise one segment unless it has been partitioned in which case there will be one segment per
partition (indexes and tables can be partitioned separately, so there may not be an equal number).
Each segment contains data from only one object. Therefore an index segment will have data from one
index and a table segment will have data from one table. Undo segments are slightly different in that they
hold data from uncommitted transactions.
At the physical level, all data is held in data files which are stored in one or more physical blocks on the
disk (although they may be cached in memory). Therefore extents comprise one or more blocks. A
tablespace may be stored in one or more files on the disk but each file relates to just one tablespace.
The Oracle server process(es) make requests to the operating system to read data from the disk where
necessary. and the background processes (DBWR, LGWR, ARCH) make requests to the operating system
to write data to the disk.
This is a simplification of the process, for more detailed explanation see some of the books for dbas
recommended on our Oracle resources page.
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 Oracle training schedule, or tell us 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
Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top
consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .