RE: wierd performance problem
Date: Thu, 10 Jul 2008 17:28:41 -0400
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Dba DBA
Sent: Thursday, July 10, 2008 3:32 PM
Subject: Re: wierd performance problem
I am going to try to respond to several emails in one post.
- we don't have any indexes, because I just did an insert of 100 GBs of data. and did not want indexes to slow this down. They will go back on in the future
- the select one row was because we were just pulling sample data to look at and it was taking a long time. This is not normal functionality.
- we are using locally managed tablespaces with fixed extent sizes. All extents are 20 mbs in the tablespace.
- we did do a few million deletes, but its a relatively small number of deletes relative to the size of the table
- I did large insert selects across a db link that died several times and rolled back. I think that may be the problem. Lots of fragmentation. lost connection across the database link after pulling many GBs of data. so I made my pulls smaller (got less data with each call).
- I am currently running an alter table shrink space operation to see if this fixes the problem
- the table may grow to 2 TBs, so these types of solution are problematic long term
- most of the time we will not be pulling large amounts of data with inserts should be a few megabytes at a time. just happens alot and we have to keep data for a really long time, so the table will get very big
- should not be an updates or deletes. We are in early deployment stage, so I had to clean stuff up with deletes.
@Mark W. Farnham: What is a "gorman". I am assuming you are referring to Tim
Gorman. by reorganize do you mean using dbms_redefinition on the table? Is
that better than than a shrink space?
--> A "Gorman" is an efficient partition exchange, as popularized by Tim. I
hope it will become useful shorthand for the technique. So far the frequency of having to explain it is 100%, but I still think it is a winner over time. (He also has a great presentation with excellent insights about using partition exchange to maximum advantage in a variety of scenarios.) What do you mean by
"For a partitioned table just copying the problem partition and doing a Gorman is probably the cheapest way to fix the problem. Since you're partitioning by day, it seems unlikely the problem will recur for that partition, and you *may* also benefit by copying the rows to the new physical location in a single threaded order that matches your predominant retrospective access path, if any such access path or ties being at least 25% of queries exist.)"
Are you talking about co-locating data by using an order by? Not sure how
that has anything to do with my problem of getting just 1 row.
--> See your own #1 and #2 above, and this also is compatible with your copy
to a compressed table and doing a Gorman as you contemplate below.
--> Your #4 - Fine, a small percentage, but did that result in many many
blocks being empty before you reach the first block that contains a row? It doesn't matter how many blocks you have with content, but rather how many empty blocks are you scanning before you get to a block with content.
--> Your #5 - Also a good way, as I mentioned to create an empty front,
especially if that is the first thing you did. For your particular case table scanning for the first row, I believe my expression "empty front" is the important characteristic rather than "fragmentation."
I thought about doing a CTAS to a new table and then renaming it back. That is not practical at 2 TBs so is not scaleable.
--> It probably scales reasonably well with a daily partitioning scheme
unless you're reaching back in time with deletes.
I really do not think I should scan that many blocks to get just a few rows
back. I think that statement is incorrect in a properly functioning table.
(I forgot who posted this). I tested this on another table that is about 11
Gbs and did a total of 950 phyiscal reads. This table is about 15 times
smaller than my problem table, but its 1,000s of times less physical IOs.
--> As I mentioned, Oracle does not have a concept of low water mark, so
when you scan a table you start at the beginning and scan any empty blocks from the beginning until you get a row, even with a stopkey. For a daily partitioning scheme this is exacerbated, because you'll never eat up the space in blocks put back on the freelist from deletes to yesterday's or earlier partitions. I do not believe either freelists or ASSM allocations have any sort operation to use lowest blocks first, either, so it is possible to strand low blocks a long ways away from the likelihood of being used again (but that has little to do with your daily scenario.) Another problem:
Looks like I am having problems with partition elimination. I did a test query as follows:
where mypartitioncolumn < to_date (date in the past) and rownum < 2
where "date in the past" is earlier than my earliest piece of data and should only scan empty partitions (I have empty partitions with earlier dates than I have data. I thought I was going to have to go back and get earlier data than I needed.. its about 20 empty partitions and 20 MB each so about 400 MB of space).
This above query ran for 15 minutes and I killed it. Its doing tons of scattered reads.
--> Are those empty partitions formerly populated and now empty? You'll scan
from the beginning to the highwatermark of each.
--> How long does it scan if you select directly from the first partition
that does contain rows to get rownum < 2?
Thank you to everyone who responded. If I missed some of the responses, I apologize. I'll read through the emails again.
PS-- We are considering compressing the data as follows:
we insert data to the table.
every night we have a job that copies data from a partition that is say 2 days old to a new table that is compressed and then we exchange the partitions. I noticed that this reduces space to almost 1/6 the size. This should solve any fragmentation issues since I am re-copying the data to a new table and then exchanging it?