 Performance degrades with big block size tablespace [message #403613] |
Sun, 17 May 2009 10:26  |
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
|
Junior Member |
|
|
Hi bangalibor here,
I have a huge table which sizes near about 50GB and have five columns and no primary key exists.Because the performance is going down( it should as most of the queries are going for full table scan), we need to tune it.
The considerations are:
a. We will not go into partition table.(why?..dont ask plz)
b. There is no joins in the query with other table so, that i can tune.Most of the queries are simple like
select * from ... where a1=...;
I have tried with index, but the cost of using index is high, so, oracle surpresses ......
c. So, only way out for me , was to make table scan faster..
That was my point of working.
The test machine says, I took half of the data in a test machine, where i had two tables with different tablespace blocksize to compare.
table 1:
8k blocksize, db_buffer 150M , db_multiblock_read_count=16
table 2:
32K blocksize, db_32k_.. 500M , db_multiblock...=32
I have achived 40% less cpu cost in 32k compare to 8k. But, the elapsed time was only 5% better in 32k.
(my first doubt to you is, why this happens)
i thought well , the best i can get is this 5%, and planned to go for implementation and i collapsed as,
The query in 32k ,was taking three times more than the test machine...
so, please help me ......................
The production is of,
db_32k_cache_size=3G compare to db_cache of 1.8G
there are some more difference between the test and production...
in test, pga_aggregate_target=8G
shared_pool= 1.5G
in prouction, pga_aggregate_target=2G
shared_pool=1G
One more thing, the cpu cost of 32k is still 40% less.
So, i think i have yorked myself here... please help
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Performance degrades with big block size tablespace [message #404183 is a reply to message #403628] |
Wed, 20 May 2009 12:18  |
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
|
Junior Member |
|
|
Look MR. BLACKSWAN, we are here to get sugesstions of a problem what we have faced. And what we expect a better directions to our problems.
Remember you are not bound to reply. So, if you cannot give a proper direction , please stay away.
Dont think, replying to every question whether it has a worthful meaning to the approach towards solution or not, will make you experience in orafaq or you will become a moderator or like that.
Your reply to my posts like, "YOU ARE WELCOME","HOW CAN I MAKE MY GOAT FLY" is surely a bad practice and should be avoided.
You have done the same to other people's post too.This will surely demotivate poeple towards ORAFAQ.
For your kind information, i have send the post to other forums also.. please check the feedback i got.
http://dbaforums.org/oracle/index.php?s=88c3135eb43f11105d87705b6541cf12&showtopic=18038
And stop browsing through the questions with your high-thought,shakespeare attitude.
Thanks for your interest what you have shown to my problem.
Thanks, can anybody help me to solve the issue
[Updated on: Wed, 20 May 2009 12:21] Report message to a moderator |
|
|
|
| Re: Performance degrades with big block size tablespace [message #404213 is a reply to message #404183] |
Wed, 20 May 2009 22:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You say Production is twice as big as Test, but that is based on ROWS. Check the number of BLOCKS. Analyse both tables and check the stats in USER_TABLES.
Also check the speed of physical disk IO on both machines to make sure they are comparable.
The PGA_AGGREGATE_TARGET difference should only affect sorts and joins. You have already said there is no join. If there is no ORDER BY, DISTINCT, UNION, GROUP BY or Analytic Functions, then it should make no difference.
Your query is IO bound. The only way to make it faster is to make the IO faster or to do less IO. By increasing your block size and multiblock read count, you have decreased the amount of fiddling about that Oracle has to do in the Buffer Cache - hence the reduced CPU - but you have NOT reduced the amount of IO - hence no appreciable improvement in performance.
Faster IO will cost you money - you will need to buy better disk.
Less IO is a lot easier. You really need to reconsider partitioning. If someone - a DBA perhaps - is denying you the option of partitioning, it is probably because they are either lazy or misinformed... or possibly both.
Do another test on a partitioned table in Test and show the results to the guy who pays your salary, then explain that someone is wasting days of your time and his money by not allowing this solution to be used.
Ross Leishman
EDIT: BlackSwan rightly points out via PM that the OP might be on a database without the partitioning option installed. From the OPs comments, I doubt that is the case. But even if it is, if you have large volumes of data and performance is a priority, then you need to buy the appropriate tools (i.e. Partitioning option for your Oracle DB).
[Updated on: Thu, 21 May 2009 02:45] Report message to a moderator |
|
|
|
|
|
| Re: Performance degrades with big block size tablespace [message #406866 is a reply to message #406848] |
Sat, 06 June 2009 20:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
First of all, I would ask your System Administrator whether there were any differences between disks physically (ie. one faster than the other) or logically (ie. one raided, one not).
If you then wanted to do a physical test, I'm sure there would be a good diagnostic that your system administrator could run, but failing that you could probably do something simple like (in Unix) cat dbfilename > /dev/null
where dbfilename is one of your Oracle database files. Run this at least 5 times on both machines and work out an average MB/s for each disk.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
