Note -- This article is for only those who have gone through explain plans and have already tried simple improvement ideas like indexes , Avoiding full table etc
What is Consistent Gets
Consistent get -- Oracle will fetch data into RAM ( this is what we call buffer or logical reads) and now when you run a query if data is in RAM it will fetch from there but for this there is a overhead oracle has to make sure that the data is RAM is in consistent state with the data on hard disk. that is no one has made any new update or inserts while we are doing query, so cpu has processes running which make sure data in RAM is consistent. So higher the consistent gets higher the overhead on the processor to keep data in consistent state . So for each consisten get hit the cpu will check with disk to check consistency of data
Consider you have a query someone rewrote it and says this is better than yours. Now the explain plans looks identical and same cost . How you say which is better. We look at Logical reads.
This is very good thing to consider.I am considering that you have gone through my earlier Oracle basics 1 ,2,3 if not look in August 2013. This is very high level statistics to check after you have tried all others
What is Consistent Gets
Consistent get -- Oracle will fetch data into RAM ( this is what we call buffer or logical reads) and now when you run a query if data is in RAM it will fetch from there but for this there is a overhead oracle has to make sure that the data is RAM is in consistent state with the data on hard disk. that is no one has made any new update or inserts while we are doing query, so cpu has processes running which make sure data in RAM is consistent. So higher the consistent gets higher the overhead on the processor to keep data in consistent state . So for each consisten get hit the cpu will check with disk to check consistency of data
Consider you have a query someone rewrote it and says this is better than yours. Now the explain plans looks identical and same cost . How you say which is better. We look at Logical reads.
This is very good thing to consider.I am considering that you have gone through my earlier Oracle basics 1 ,2,3 if not look in August 2013. This is very high level statistics to check after you have tried all others
Below are my analysis to tune a query
To tune a query we can clear the buffer cache to
get the timing it takes without data being in buffer.Consider the case
that there is data in buffer then we need to look at consistent gets
Consistent gets –Number of times a buffer is hit
We know that reading from buffer is faster than
reading from disk.But consider the case of table which is loaded into
buffer now the speed of query will depend on the consistent gets that
it how many times it hits the cache to get the
data. We want to write a query such that the it hits the buffer as less
as possible. That query will be faster. Considering it has same
physical reads
Your idea does reduce the consistent gets in test environment .So I think it’s a good approach
Now i got this idea from ASK tom site. Below is the link. But for some reason if its not working that site people keep changing links then type in google Logical reads v/s physical reads and select ASK tom link which appears
Logical reads v/s Physical reads
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6749454952894
Also read the discussion on Array size and consistent gets from ASK tom
Now i got this idea from ASK tom site. Below is the link. But for some reason if its not working that site people keep changing links then type in google Logical reads v/s physical reads and select ASK tom link which appears
Logical reads v/s Physical reads
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6749454952894
Also read the discussion on Array size and consistent gets from ASK tom
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:880343948514
Very important statement that everyone should remember
-----Almost All sql operation running in parallel will read data directly from
disk bypassing the buffer cache
Block buffer cache
is part of SGA
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.
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.
Consistent get is
function of array size . Consider array size is 15 consistent gets will be more
, same block in buffer cached will be queried more
Db Block get +
Consistent gets = Logical io
The table has 20 blocks in it.
You would expect 20 consistent gets on a full scan.
But, with an array size of 15 we will revisit some blocks MORE THEN ONCE.
You would expect 20 consistent gets on a full scan.
But, with an array size of 15 we will revisit some blocks MORE THEN ONCE.
In fact, we might revisit upto 10000/15 = 666 blocks
more then ONCE.
This is because we "start" and
"stop" the query in the middle of a block. (see the two followups
immediately above this).
immediately above this).
If you full scan a table T that has N blocks and R
rows and you use an array fetch size of A, we
will typically perform the following number of consistent gets:
will typically perform the following number of consistent gets:
N + R/A
We have to read N blocks (that should be
obvious)
We might revisit R/A blocks more then once (since we stop in the middle)
We might revisit R/A blocks more then once (since we stop in the middle)
db block gets are blocks gotten in CURRENT (as opposed
to consisten) mode. They are block gets
we
did to discover the extent map for the table (to know what to read). We get that in "current, as
of right now" mode instead of "in consistent read, as of the point in time the query began" mode.
did to discover the extent map for the table (to know what to read). We get that in "current, as
of right now" mode instead of "in consistent read, as of the point in time the query began" mode.
Oracle did 4 current mode gets in both cases to figure
out what blocks to read.
In the first example, it did 686 logical IO's
In the second, only 30
In the second, only 30
It did no physical IO.
The LIO can be
reduced by increasing buffer size but that should not be done
The other problem with consistent gets
to get a block from
the cache, we have to latch (to prevent someone from modifying the data
structures we are
currently reading).
A latch is a lock.
Locks are
serialization devices
Serialization
devices inhibit scalability, the more you use them, the less concurrency you
get.
A physical IO from temp will not, that bypasses the
cache.
When Oracle performs
a full table scan of a large table, the blocks are read into the buffer cache
but placed at the least recently used end of the LRU list. This causes the
blocks to be aged out quickly, and prevents one large full table scan from
wiping out the entire buffer cache.
Where does joining of data takes place, Where are Hash
tables created ??
1 – RAM cache for
data blocks
2 – RAM cache for
SQL statements
3 – RAM for caching
the data dictionary
4 – RAM for caching
program execution code
The first time an
Oracle Database user process requires a particular piece of data, it searches
for the data in the database buffer cache. If the process finds the data
already in the cache (a cache hit), it can read the data directly from memory.
If the process cannot find the data in the cache (a cache miss), it must copy
the data block from a datafile on disk into a buffer in the cache before
accessing the data. Accessing data through a cache hit is faster than data
access through a cache miss.
What is the PGA
Oracle Database
allocates a program global area (PGA) for each server process. The PGA is used
to process SQL statements and to hold logon and other session information. For
the purposes of memory management, the collection of all PGAs is known as the
instance PGA. Using an initialization parameter, you set the size of the
instance PGA, and the database distributes memory to individual PGAs as needed.
PGA memory is divided into the following areas
Session Memory
Private SQL Area
The private SQL area
contains data such as bind variable values, query execution state information,
and query execution work areas. Each session that issues a SQL statement has a
private SQL area. Each user that submits the same SQL statement has his or her
own private SQL area that uses a single shared SQL area. Thus, many private SQL
areas can be associated with the same shared SQL area.
Private SQL Area
Components
The private SQL area
of a cursor is itself divided into two areas whose lifetimes are different:
•The persistent area—This area contains bind
variable values. It is freed only when the cursor is closed.
•The runtime area—Oracle Database creates this
area as the first step of an execute request. It contains the following
structures:
◦Query execution
state information
For example, for a
full table scan, this area contains information on the progress of the scan
◦SQL work areas
These areas are
allocated as needed for memory-intensive operations like sorting or hash-joins.
More detail is provided later in this section.
For DML, the
run-time area is freed when the statement finishes running. For queries, it is
freed after all rows are fetched or the query is canceled.
SQL Work Areas
SQL work areas are
allocated to support memory-intensive operators such as the following:
•Sort-based
operators (order by, group-by, rollup, window function)
•Hash-join
•Bitmap merge
•Bitmap create
For example, a sort
operator uses a work area (sometimes called the sort area) to perform the
in-memory sort of a set of rows. Similarly, a hash-join operator uses a work
area (also called the hash area) to build a hash table from its left input. If
the amount of data to be processed by these two operators does not fit into a
work area, the input data is divided into smaller pieces. This enables some
data pieces to be processed in memory while the rest are spilled to temporary
disk storage to be processed later. Although bitmap operators do not spill to
disk when their associated work area is too small, their complexity is
inversely proportional to the size of their work area. Thus, these operators
run faster with larger work area.
The size of a work
area can be controlled and tuned. The database automatically tunes work area
sizes when automatic PGA memory management is enabled. See "Overview of
Memory Management Methods" for more information.
Generally, bigger
work areas can significantly improve the performance of a particular operator
at the cost of higher memory consumption. Optimally, the size of a work area is
big enough to accommodate the input data and auxiliary memory structures allocated
by its associated SQL operator. If not, response time increases, because part
of the input data must be spilled to temporary disk storage. In the extreme
case, if the size of a work area is far too small compared to the input data
size, multiple passes over the data pieces must be performed. This can
dramatically increase the response time of the operator.
No comments:
Post a Comment