Hi Guys,
I got the idea of Consistent gets and Array size are related while reading a discussion on ASK tom below are some of the two best articles. Do read this in full. I am just noting down my understanding
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
Summary of What i got from the discussion
Oracle has structure like Tablespace ( Undo, Temp, Users, Sys) . Now in users tables space we have our schemas , tables, index , rollback, materialised views which are called segments. Then segment are divided into extents ( which are blocks of data close together) then we have db blocks (16 k in size) which store you data.
Then there is array size.Array size is the number of records oracle fetches to display. For sql plus its different for informatica its different, for JDBC its different. If you notice in datastage we always specify this. It is the amount of rows that will sit in oracle array.
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
So suppose your table is spread across 10 db blocks . and array size is set to number of records in block then you will see 10 consistent gets . That is your array size is 100 and each block has 100 records and in total you have 1000 records
Now suppose your array size is 50 and each block has 100 then the consistent gets will be 20 and so on.
Consider you do
select count(*) from emp . Now this will have a less consistent gets because it will read each block only once and it does not have to fetch all record into array as its not displaying data
What happens when you add index why query speeds up
When you add a index oracle does not have to read only the index blocks. Index blocks are separate db blocks so the number of consistent gets read is less
So will changing the Array size increase the query speed. Yes
Your dba must have already made sure that db block size and array size are in sync.If they are not in sync then making them to sync up will affect the performance. Tools like Datastage allows you to specify the array size for fetching data.
Below is a Article on Network Packet size. This is not related to this article but i am placing it here just like that for your interest
Increasing the network packet size in oracle can speed up the performance of loading since the header of each packet takes a space . To send packet over the network oracle breaks down into packets depending on what size is specified for oracle then network again break down into packets depending on network packet size
Now suppose oracle packet size is less then more packets so for each packets there is a header info which takes some space when network will break down again it has to take into account the header space as well suppose oracle transfers 30 kb of data . then it breaks into 4kb packets so 8 packest of 4kb which has 100bytes of header .Now when network will break down it has to break down 32 bytes of data. So by increasing packet size we are reducing the amount of data trasferred and we are keeping the network traffic to low
http://amitstechblog.wordpress.com/2011/08/06/database-protocol- packet-size-and-performance/
I got the idea of Consistent gets and Array size are related while reading a discussion on ASK tom below are some of the two best articles. Do read this in full. I am just noting down my understanding
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
Summary of What i got from the discussion
Oracle has structure like Tablespace ( Undo, Temp, Users, Sys) . Now in users tables space we have our schemas , tables, index , rollback, materialised views which are called segments. Then segment are divided into extents ( which are blocks of data close together) then we have db blocks (16 k in size) which store you data.
Then there is array size.Array size is the number of records oracle fetches to display. For sql plus its different for informatica its different, for JDBC its different. If you notice in datastage we always specify this. It is the amount of rows that will sit in oracle array.
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
So suppose your table is spread across 10 db blocks . and array size is set to number of records in block then you will see 10 consistent gets . That is your array size is 100 and each block has 100 records and in total you have 1000 records
Now suppose your array size is 50 and each block has 100 then the consistent gets will be 20 and so on.
Consider you do
select count(*) from emp . Now this will have a less consistent gets because it will read each block only once and it does not have to fetch all record into array as its not displaying data
What happens when you add index why query speeds up
When you add a index oracle does not have to read only the index blocks. Index blocks are separate db blocks so the number of consistent gets read is less
So will changing the Array size increase the query speed. Yes
Your dba must have already made sure that db block size and array size are in sync.If they are not in sync then making them to sync up will affect the performance. Tools like Datastage allows you to specify the array size for fetching data.
Below is a Article on Network Packet size. This is not related to this article but i am placing it here just like that for your interest
Increasing the network packet size in oracle can speed up the performance of loading since the header of each packet takes a space . To send packet over the network oracle breaks down into packets depending on what size is specified for oracle then network again break down into packets depending on network packet size
Now suppose oracle packet size is less then more packets so for each packets there is a header info which takes some space when network will break down again it has to take into account the header space as well suppose oracle transfers 30 kb of data . then it breaks into 4kb packets so 8 packest of 4kb which has 100bytes of header .Now when network will break down it has to break down 32 bytes of data. So by increasing packet size we are reducing the amount of data trasferred and we are keeping the network traffic to low
http://amitstechblog.wordpress.com/2011/08/06/database-protocol- packet-size-and-performance/
No comments:
Post a Comment