Oracle Database Concepts II

Hi Guys,

This is part 2 of Oracle database basics.For part 1 refer below link 

Link for Oracle Database basics Part 1


So consider there is a big query .First time you run it .It takes 30 seconds second time it takes 7 seconds.Now what is the query time (7 or 30) and why this happens.

Answer - First time it reads from physical memory (actual db) ,Next time it reads from cache.This is very important to understand for sql tuning (physical reads and Consistent gets) .

What is database Instance.

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.

Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.(SGA Exists in your RAM).So more the RAM for your server its faster

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

•Maintaining internal data structures that are accessed by many processes and threads concurrently

•Caching data blocks read from disk

•Buffering redo data before writing it to the online redo log files

•Storing SQL execution plans

The SGA is shared by the Oracle processes, which include server processes and background processes, running on a single computer. The way in which Oracle processes are associated with the SGA varies according to operating system.

A database instance includes background processes. Server processes, and the process memory allocated in these processes, also exist in the instance. The instance continues to function when server processes terminate.


The most important SGA components are the following:
 

1. Database Buffer Cache 
2. Redo Log Buffer
3. Shared Pool
4. Large Pool
5. Java Pool
6. Streams Pool
7. Fixed SGA

SGA (contains buffer cache)

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).

Main Areas of SGA

1) The buffer cache (db_cache_size)
2)The shared pool (shared_pool_size)
3)The redo log buffer (log_buffer)

Main thing to understand is 

The Buffer Cache (also called the database buffer cache) is where Oracle stores data blocks.  With a few exceptions, any data coming in or going out of the database will pass through the buffer cache.

When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. This practice allows to server to avoid unnecessary I/O

The database buffer cache holds copies of the data blocks read from the data files. The term data block is used to describe a block containing table data, index data, clustered data, and so on. Basically it is a block that contains data

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle

Some more point on Buffer cache(For those looking for more detail)

The total space in the Database Buffer Cache is sub-divided by Oracle into units of storage called “blocks”. Blocks are the smallest unit of storage in Oracle and you control the data file blocksize when you allocate your database files.

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle, rather than the internal block size of the operating system. In other words, you provide Oracle with a big whiteboard, and Oracle takes pens and draws a bunch of boxes on the board that are all the same size. The whiteboard is the memory, and the boxes that Oracle creates are individual blocks in the memory

Consistent Gets

A Consistent Get is where oracle returns a block from the block buffer cache but has to take into account checking to make sure it is the block current at the time the query started.

Oracle fetches pretty much all of its data that way. All your data in your database, the stuff you create, the records about customers or orders or samples or accounts, Oracle will ensure that what you see is what was committed at the very point in time your query started. It is a key part to why Oracle as a multi-user relational database works so well.
Most of the time, of course, the data has not changed since your query started or been replaced by an uncommitted update. It is simply taken from the block buffer cache and shown to you.

A Consistent Get is a normal get of normal data. You will see extra gets if Oracle has to construct the original record form the rollback data. You will probably only see this rarely, unless you fake it up.

Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a consistent view but most of the time it won’t.
DB Block Gets – Internal processing. Don’t worry about them unless you are interested in Oracle Internals and have a lot of time to spend on it.
Physical Reads – Where Oracle has to get a block from the IO subsystem

Nicely explained by below blog 
http://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/

My full article on Consistent Gets 

http://cognossimplified.blogspot.in/2013/09/using-consistent-gets-to-tune-sql.html 

What is latching 

Along with keeping physical I/O to minimum we also need to keep consistent gets to minimum as consistent gets involve latching. A latch is a lock.
Locks are serialization devices.Consider 2 users were accessing a table ( from cache ) when first user is reading a latch is set so that it reads only current data and 2 person has to wait till this latch is free

Serialization devices inhibit scalability, the more you use them, the less concurrency you get.

Though common idea is to keep physical read to minimum we also want to keep consistent gets minimum due to various reason like it might create latches issue and high cpu utilizatoin and for that consistent get to happen . physical i/o must have occured some time.

Consider you had a bigger cache that the physical i/o will be reduced but does that solve the probelem .NO


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6643159615303

what is recursive calls 

Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed tablespaces are being used. Recursive calls are also generated:

When data dictionary information is not available in the data dictionary cache and must be retrieved from disk

• In the firing of database triggers
• In the execution of DDL statements
• In the execution of SQL statements within stored procedures, functions, packages and anonymous PL/SQL blocks
• In the enforcement of referential integrity constraints

The value of this statistic will be zero if there have not been any write or update transactions committed or rolled back during the last sample period. If the bulk of the activity to the database is read only, the corresponding "per second" metric of the same name will be a better indicator of current performance.



Dynamic sampling --- allows CBO to estimate number of rows for tables that are not analysed .Which helps it to come up with better estimation plan.

The number of rows show in dynamic sampling are not actual rows but a estimate from dynamic sampling

What is Clustering factor 

Oracle has something called cluster tables ( data from two tables having common column) saved on same block and index on such cluster table is called clustered index.

Now Clustering factor is something different 


the clustering_factor column in the user_indexes view is a measure of how organized the data is compared to the indexed column, is there any way i can imporve clustering factor of a index. or how to improve it

This defines how ordered the rows are in the index.  If CLUSTERING_FACTOR approaches the
number of blocks in the table, the rows are ordered.  If it approaches the number of rows in the table, the rows are randomly ordered.  In such a case (clustering factor near the number of rows), it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Note that typically only 1 index per table will be heavily clustered (if any).  It would be extremely unlikely for 2 indexes to be very clustered.

If you want an index to be very clustered -- consider using index organized tables.  They force the rows into a specific physical location based on their index entry.

Otherwise, a rebuild of the table is the only way to get it clustered (but you really don't want to get into that habit for what will typically be of marginal overall improvement)

Easy way to create a dummy table with lot of Rows

create table t ( a int, b int, c char(20), d char(20), e int );

 insert into t select 1, 1, 1, 1, rownum from all_objects;

create unique index t_idx on t(a,b,c,d,e);



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.

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.