Oracle Database Concepts

Hi Guys,

This article is intended to strenghten our Oracle database basics.(Not for DBA).Intended for average guys with little knowledge of Oracle physical and Logical structure.Must read if you are planning to go into depth for sql tuning.

If you are working on any database related application.(reporting ,ETL) .you must have writen lot of sql queries.But we hardly know the structure of oracle database how it works.How actually indexes work.What are the parameter that make our query time go high. (slow performance).

To understand these things in details we need to know Oracle basics first ( Physical gets ,Consistent reads) thease are the things that actually determine how your query will work .What is clustertering , what is memory block , what are Bitmap index ,Binary tree indexes .

What is a Index ?

Most people think they know this.Your manager might say ."It's running slow. I think I'll index some of the columns and see if it improves.

Below has been taken from OraFaq -Really a great site to learn :-).Use link to access original article 

OraFaq--What is a Index

Blocks

First you need to understand a block. A block - or page for Microsoft boffins - is the smallest unit of disk that Oracle will read or write. All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So you never read "just one row"; you will always read the entire block and ignore the rows you don't need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.

Oracle uses two different index architectures: b-Tree indexes and bitmap indexes. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the "normal" index, so we will come back to Bitmap indexes another time.

The "-Tree" in b-Tree ( B stands for balanced & not binary)

A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book.

Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells you the physical location of the telephone (row in the table).

The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

To find the name Gallileo in this b-Tree phone book, we:

Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.

Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.

Read page 350, which is a leaf block; we find Gallileo's address and phone number.

If you look at the original article you can notice his query which qives actual physical address of blocks its accessing.

How are Indexes used?

Indexes have three main uses:

1)  To quickly find specific rows by avoiding a Full Table Scan

We've already seen above how a Unique Scan works. Using the phone book metaphor, it's not hard to understand how a Range Scan works in much the same way to find all people named "Gallileo", or all of the names alphabetically between "Smith" and "Smythe". Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.

2) To avoid a table access altogether

If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we'd have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.

Oracle does the same thing. If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.

3) To avoid a sort

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

Why Full scans are not Bad ?
Up to now, we've seen how indexes can be good. It's not always the case; sometimes indexes are no help at all, or worse: they make a query slower.

A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.

Much more interesting - and important - are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.

To explain the problem, we need a new metaphor. Imagine a large deciduous tree in your front yard. It's Autumn, and it's your job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac...) would be get down on hands and knees with a bag and work your way back and forth over the lawn, stuffing leaves in the bag as you go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: you would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon

Know your data - Indexes will help to speed up only if 10% data is requested,to read 100%data indexes are very very costly .(exception if the column requested is part of index so that no table access is required)

Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), you decide to pick up the leaves in order of size. In support of this endeavour, you take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because you cover much more distance walking from leaf to leaf.

So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.

The main reasons for this are:

• As implied above, reading a table in indexed order means more movement for the disk head.
• Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.
• The db_file_multiblock_read_count setting described earlier means FTS requires fewer visits to the physical disk.
• Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.

So what's the lesson here? Know your data! If your query needs 50% of the rows in the table to resolve your query, an index scan just won't help. Not only should you not bother creating or investigating the existence of an index, you should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule - there's always one - is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.


Continued ---- Below is a link for Oracle database basics part 2 

Oracle database basics part 2


Good Article on Bitmap indexes and B-Tree indexes 

Link for Good Article on Bitmap indexes (By Oracle )

Very Useful command to check your indexes on table.Cant check one by one in toad(takes too much time)

select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('SLS_SALES_FACT')

order by a.table_name, a.index_name, a.column_position;

Disclaimer and Citations 

The content here is taken from various sources found by googling. I have given links wherever possible.For me i dont need in detail information so i have copy pasted the basic information for my use.Also taken are comments from blogs , forum. I have added lot of information according to my understanding of subjects. If anyone finds anything objectionable please leave a comment.

Sql tuning

Hi Guys,

In almost all the projects that i have worked for over the last few years one of the most common requirement in sql tuning .The database might be different but sql tuning is one of the most important things to know in Business Intellegence .It gives you the edge in any project that you are working .

Now this is a big topic and I am not DBA to know all the details but i am going to mention few points that everyone can look into and try to get their sql to perform better .I will also help you to understand the explain plan ( its quite diff to make use of it in few minutes).



Below TIP for finding query time is for Oracle 11g

One useful thing I found today .Usually we are using set timing on Or we are looking at elapsed time from sql developer.Which is not correct way of testing how much time a query takes to execute because oracle will store the result set in cache


USUAL way of looking at run time 

Consider we execute the same query 5 time then if at first run it takes 10 s then at 5th run it might take 0.5 s without us making any change to query .This is because of oracle cache.Use below statement to find the run time .Consider we run a statement 5 times then executions column will show 5.Its Best to look for time_taken when execution is 1

To identify your query just put a comment in the query UPPER(SQL_TEXT) like '%KAP%'

select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' , MODULE, SQL_TEXT elasped ,executions from v$sql

where UPPER(SQL_TEXT) like '%KAP%'

AND UPPER(MODULE) LIKE '%SQL%DEVE%'

  order by LAST_LOAD_TIME desc

Point to be noted in this time

1)      This time does not include time oracle takes to print result set to our screen

2)      Consider we run the same query 10 times then we need to divide the time taken by number of executions to get average time.

-------------------------------------------------------------------------------------------------------------------
Basic idea (irrespective of database)

1) Find the query that is causing the most trouble (delays)
a) Simple idea is to use select count(*) from ( select * from most trouble) .This will give you the time required for the query to run

2)Check the filters used . By reducing the amount of data we can speed up the query .Like if your query fetches last 4 years data check with business if some one really is using this 4 years data or can you make it to fetch only last 24 months .

3)Make sure table has indexes (Its very difficult to find project in which tables are not indexed properly but give it a shot ... )

4)Try to simplify query .Queries with nested subqueries,inline views perform poorly .

(most commonly found) ---here k is your inline view.
select k.dept,a.emp from emp a ,(select dept from xyz) k

5) Make use of WITH clause (Subquery factoring) this performs faster then your inline views.

WITH dept_cost as ( select dept from xyz)
select dept,a.emp from emp a,dept_cost

6)Most professional way of doing it is by Using explain plan.But since most developer do not have that level of db knowledge and would usaully will not like to go for it .I have discussed it at the last

7) Below are some points that i liked taken from the link below

Beginners Sql site

 Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name 
FROM employee 
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
FROM employee_details) 
AND dept = 'Electronics'; 

Instead of:

SELECT name 
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details) 
AND age = (SELECT MAX(age) FROM employee_details) 
AND emp_dept = 'Electronics';

Make use of IN and EXISTS efficiently .
a) IN has slower performance

This is not Always true it depends on your situation.  Sometimes in may be faster sometime Exists may be faster. Understand the situation first. Below is taken from ASKtom. To read full article

ASKtom website link for article

Well, the two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to 
the original table -- typically.


As opposed to 

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

It always results in a full scan of T1 whereas the first query can make use of an index 
on T1(x).


So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery
    ( select y from T2 )

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( 
select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the 
exists will be faster as the time to full scan T1 and do the index probe into T2 could be 
less then the time to simply full scan T2 to build the subquery we need to distinct on.


Lets say the result of the subquery is small -- then IN is typicaly more appropriate.


If both the subquery and the outer table are huge -- either might work as well as the 
other -- depends on the indexes and other factors. 

IN Example

select ename from emp e
    where mgr in (select empno from emp where ename = 'KING');

Same as 

select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2
    where e1.mgr = e2.empno;

The idea to use In is the result from inner query is less compared to outer query . In such cases In works well 

EXISTS

select ename from emp e
    where exists (select 0 from emp where e.mgr = empno and ename = 'KING');


My favorite is the below .As you will notice that we end up having distinct in our subqueries .Which slows the performance the most as first entire data is picked up and then grouped to find distinct .So best is to try to avoid having distinct especially in your inline views

 Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept 
FROM dept d 
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept 
FROM dept d,employee e 
WHERE e.dept = e.dept;

For those who want to impr.ove their sql tuning skill further my idea is to start from Oracle basics how it works .I have noted few points in by article below

http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html

Adding Two more things to check 

I was facing issue that the seq.next_val was taking long time as the caching value was low. This was causing the inserts to take time. You can know this by lookin at v$sesion table and knowing on what is the session waiting for . It will be waiting long time on things like db_sequention_read then you can find out what is the object on which it is waiting . you can chek out below link 

http://cognossimplified.blogspot.com/2013/10/etl-tools-and-sessions-in-db.html

Using Wrong data type for index column like product_key = '40' . This causes oracle to skip using the index on product key make sure you are passing correct data type for indexed columns

With Clause for SQL tuning  

With clause is best to be used when you code is getting too long and you can easily divide it into parts. With clause speeds up the query by allowing oracle to store intermediate result in intermediate tables. The concept is similar to global temporary concept which was there before. But only difference is global temporary table would force oracle to create temporary tables. By using with clause oracle has the choice of creating temporary tables if it think its a good idea.

But we cannot always trust oracle. It will estimate the cost of not creating temp tables and it might think its good idea not to go for temp tables. In such cases you need to force oracle to go for temp tables to store the with clause result by using the hint /*+materialize*/ . I have obtained a query improvement of 75 seconds for huge volume by going for that hint.

Regarding Using Case statement

I had a query which was taking 130 seconds to execute. When I checked by building query block by block I observed case statement takes maximum time. Without case it hardly took 10 seconds. So here is what was happening

Select case( 1=1 and 2=2 and 3=3) a1,

          case( 1=3 and 5=4 and 6=6) b1

From

( select 1 , 2 , 3 from abc inner join with hhh on 1=8 )

So what I did was push the condition. Its easy for oracle to do row level calculation like suppose we have two columns 1 and 3 then its easier for oracle to compare ,  add, subtract and all that

Select a1,b1          

From

( select 1 , 2 , 3, ( 1=1 and 2=2 and 3=3) a1, case( 1=3 and 5=4 and 6=6) b1 from abc inner join with hhh on 1=8 )

How to tune by reading Explain pland and Statistics IO

This is the actual way of doing it.But it will take atleast a weeks reading to understand the basics and use it to help you solve your professional problems.Below are some of my notes for starting.

How to check the explain plan .

run the below statement 

explain plan for (select * from abc)

this will store the explain plan data in a plan table which can be accessed by using below statement

select * from table (dbms_xplan.display)

Go through below article it will give you some basic idea of explain plan.Understanding explain plan and using it to tune is a big task will take lot of patient ( days worth of work ).Not for the faint hearted .

Explain plan basics

Below are link for my articles on Oracle db basic and Reading Explain plans(For the experienced folks )

http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html

http://cognossimplified.blogspot.in/2013/07/how-to-read-explain-plans-for-sql-tuning.html

Below is link for Article on Temp space issue in DB which was causing performance issues for us. 

http://cognossimplified.blogspot.com/2014/01/lack-of-temp-space-causing-performance.html


Bitmap Indexes on Fact Table can improve performance a lot. Hints like star transformation in oracle has huge impact 

http://cognossimplified.blogspot.com/2013/07/sql-tuning-with-bitmap-indexes-and-star.html 

Sometime Cast conversion from varchar to integer has impact on performance ,Consider you fact stores result as varchar and you are summing on it. Oracle will convert it to int. Which will affect performance. So make sure you know the datatype of columns you are summing