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.

Contents

• 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. Each table, index 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 linked to individuals. View woopra privacy policy.    View our privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.