Re: Time series deletion performance

On Oct 12, 8:53 am, pe..._at_alum.wpi.edu wrote:
> I'm working with a large table of time series data that contains 30
> days worth of information (100 Million+ rows). The issue is that
> every day we attempt to delete data > 30 days old and it can take up
> to 10+ hours. This is unacceptable as we have a constant load on our
> web site at all times of the day and causes a slowdown of the entire
> db.
>
> It looks similar to this:
>
> time_series_table
> -------------------------
> timestamp NUMBER(38), --Timestamp down to millisecond, yes we need
> that precision
> id1 NUMBER(38), --reference to another table id
> id2 NUMBER(38), --reference to another table id
> value VARCHAR2(500) -- Could be free text or numeric
>
> There are indexes on (id1, id2) and (timestamp). id1 has about 10,000
> unique values and id2 has about 50 unique values. The value column is
> customer dependent and could be a continuous numeric value or free
> text value depending on customer need. This table gets installed at
> various customer sites that may not have Oracle EE, so I cannot always
> depend on partitioning. Also, the table is analyzed weekly, so my
> stats are up to date. Data is inserted into this table with timestamp
> = sysdate (data is not inserted out of date order). Data is always
> deleted by oldest timestamp as well.
>
> This seems like a trivial problem if the table is stored on disk by
> timestamp (Oracle could just drop the first x blocks of the table). I
> considered storing this table as an IOT based off the timestamp, but
> timings prove that it doesn't dramatically help. Aside from creating
> a view of daily tables and dropping/creating them daily (manually
> partitioning), is there a better way to store or delete from this
> table?

I think you better start with the concepts manual, especially the parts about blocks and segments, then google for freelists. You need to understand how Oracle allocates blocks that have been emptied and freed for insertion of data, I don't think from your description that you are deleting the first x blocks of the table. Then you should look at the wait states and evaluate what is taking so long with your delete. Details may vary by configuration (including block size for freelists) and version - so you should always post such information. But you need to know what the real problem is to make a proper fix.

If you have _some_ customers that have partitioning, you might consider forking your source.

Also check asktom.oracle.com for ideas on mass deletes, and http://www.psoug.org/reference/dbms_space.html .

jg

--
@home.com is bogus.
A billion here, a billion there, pretty soon you're talking real
money.  http://www.itnews.com.au/News/62937,us-faces-us100-billion-fine-for-web-gaming-ban.aspx