1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get db row entries growth rate by day/week/month without awr

Discussion in 'Server Administration and Options' started by mkaresh85, Oct 13, 2016.

  1. mkaresh85

    mkaresh85 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Hello DB Experts,

    I'm looking for a way (Oracle 11g SQL or procedure) to find out the data growth rate in-terms of DB row entries in a given table, assuming there will a creation date column.

    We need to gather the inflow rate by day/week/month over time.

    Are there some samples or examples to start with?
    It will be very helpful if you could point me to examples to build the detailed/complex queries.

    Thanks,
    Karesh
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The first query is such a straight-forward query I'm at a loss to understand why you need assistance with it. Looking at an example along the lines of your description a table is created:

    Code (SQL):
    SQL> CREATE TABLE input_tst(
      2  myid       NUMBER,
      3  mydata     varchar2(60),
      4  ins_dt     DATE);

    TABLE created.

    SQL>
    Once populated the number of rows per day is easily found; I will leave that task for you to complete.

    The rows per week is a bit more difficult; one possible way is this:

    Code (SQL):
    DECLARE
        start_wk    DATE;
        end_wk    DATE;
        wkly_ct    NUMBER:=0;

        cursor get_start IS
        SELECT MIN(ins_dt)
        FROM input_tst
        WHERE rownum = 1;
    BEGIN
        OPEN get_start;
        fetch get_start INTO start_wk;
        close get_start;

        loop
            end_wk := start_wk + 7;
            SELECT COUNT(*)
            INTO wkly_ct
            FROM input_tst
            WHERE ins_dt BETWEEN start_wk AND end_wk;
            exit WHEN wkly_ct = 0;
            dbms_output.put_line('Row count for week beginning '||start_wk||': '||wkly_ct);
            start_wk:=end_wk;
        END loop;
    END;
    /
     
    It can be slow for an entire table; writing it as a procedure to accept start and end dates would make it faster.

    A similar approach can be used to 'walk through' the entire table and return monthly counts:

    Code (SQL):
    DECLARE
        start_mth    DATE;
        end_mth    DATE;
        mthly_ct    NUMBER:=0;

        cursor get_start IS
        SELECT MIN(ins_dt)
        FROM input_tst
        WHERE rownum = 1;
    BEGIN
        OPEN get_start;
        fetch get_start INTO start_mth;
        close get_start;

        loop
            end_mth := add_months(start_mth, 1);
            SELECT COUNT(*)
            INTO mthly_ct
            FROM input_tst
            WHERE ins_dt BETWEEN start_mth AND end_mth;
            exit WHEN mthly_ct = 0;
            dbms_output.put_line('Row count for month beginning '||start_mth||': '||mthly_ct);
            start_mth:=end_mth;
        END loop;
    END;
    /
    Again coding this as a procedure you can pass parameters to would make this much faster and allow for ad hoc counts based on the inputs.