RE: wierd performance problem

In line.

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dba DBA
Sent: Thursday, July 10, 2008 3:32 PM
To: oracle-l@xxxxxxxxxxxxx
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?

Other related posts: