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:
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
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
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.
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:
- Database Buffer Cache
- Redo Log Buffer
- Shared Pool
- Large Pool
- Java Pool
- Streams Pool
- 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.
