RE: wierd performance problem

In line.  

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
To: oracle-l_at_freelists.org
Subject: Re: wierd performance problem  

I am going to try to respond to several emails in one post.

1. 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
2. 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.
3. we are using locally managed tablespaces with fixed extent sizes. All extents are 20 mbs in the tablespace.
4. we did do a few million deletes, but its a relatively small number of deletes relative to the size of the table
5. 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).
6. I am currently running an alter table shrink space operation to see if this fixes the problem
7. the table may grow to 2 TBs, so these types of solution are problematic long term
8. 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
9. 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:
select count(*)
from myBigTable
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?

--
http://www.freelists.org/webpage/oracle-l