Home » RDBMS Server » Performance Tuning » Performance degrades with big block size tablespace (oracle 10G R2, Sun solaris 10)
icon13.gif  Performance degrades with big block size tablespace [message #403613] Sun, 17 May 2009 10:26 Go to next message
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 #403617 is a reply to message #403613] Sun, 17 May 2009 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

What percentage of rows are being returned?
Re: Performance degrades with big block size tablespace [message #403619 is a reply to message #403617] Sun, 17 May 2009 10:51 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

Hi,

SORRY.I will take care further

It will almost return 20% of the rows.
One of my columns has low cardinality(30).I tried with bitmap index also but no improvement on the elapsed time

Thanks...
Re: Performance degrades with big block size tablespace [message #403620 is a reply to message #403613] Sun, 17 May 2009 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Your problem is like the following:
I have goat. How can I teach him to fly?

In your case, reality means your desires are not achievable.
Re: Performance degrades with big block size tablespace [message #403622 is a reply to message #403613] Sun, 17 May 2009 11:05 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

Wait a bit, you misunderstood...

I want to know two things,

1. why my query's elapsed time on test machine is only 5% better,when i have reduced the cpu cost by 40%

2. Why the same query's elapsed time is three times higher in the production table.( i ran the same query in production database one in 8k and another in 32k)

thanks, hope i can understand why my goat will not fly.

Re: Performance degrades with big block size tablespace [message #403623 is a reply to message #403613] Sun, 17 May 2009 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>why my query's elapsed time on test machine is only 5% better,when i have reduced the cpu cost by 40%

Elapsed_time = cpu_time + other_time
Elapsed_time*0.05 = cpu_time*0.40
say Elapsed_time is 1000 seconds
1000*0.05 = cpu_time*0.4
cpu_time = 125 seconds

Simply put when CPU_TIME is only a small part of overall or elapsed time,
cpu_time can be reduced a decent amount without having noticeable impact on elapsed_time.
Look to it this way even if cpu_time in case above went to ZERO,
elapsed time would still be 875 seconds for a 12.5% improvement.
Re: Performance degrades with big block size tablespace [message #403624 is a reply to message #403623] Sun, 17 May 2009 12:26 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

Thanks, for the most..

Can you explain one step further..
when my cpu cost is 537K ,my elapsed time is 28 minutes.
when it is 273K , my elapsed time is 26.30 minutes
(and both from the same machine,with a low load server)

And the second doubt plz.....
Re: Performance degrades with big block size tablespace [message #403625 is a reply to message #403613] Sun, 17 May 2009 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What is the unit of measure for the numbers in question?
MIP (Mythical Indicator of Performance)?
Re: Performance degrades with big block size tablespace [message #403626 is a reply to message #403613] Sun, 17 May 2009 12:36 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

It is the execution plan which shows the cost of cpu and the elapsed time of a query.. And i told all are straight queries, so a simple full table scan....


thanks
Re: Performance degrades with big block size tablespace [message #403628 is a reply to message #403626] Sun, 17 May 2009 13:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
bangalibor wrote on Sun, 17 May 2009 10:36
It is the execution plan

thanks



You are welcome.
Re: Performance degrades with big block size tablespace [message #404183 is a reply to message #403628] Wed, 20 May 2009 12:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #406848 is a reply to message #404213] Sat, 06 June 2009 09:13 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior 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.


Can you tell me, how do i check the physical disk i/o speed???
Thankss
Re: Performance degrades with big block size tablespace [message #406866 is a reply to message #406848] Sat, 06 June 2009 20:57 Go to previous message
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
Previous Topic: Performance of database runnig on RAC 10g with two Solaris nodes
Next Topic: very simple but very slow query
Goto Forum:
  


Current Time: Fri Dec 06 13:21:52 CST 2024