Re: Query help

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Thu, 12 Mar 2015 14:35:33 +0100
Message-ID: <CA+S=qd0X-JqEk06EEm4ZcfYT-g9z3z5Fb++ji4SSgVzWxGPiJQ_at_mail.gmail.com>

Hi, Andrew

Well, the simple way to get rid of WITH is to replace with an inline view:

  select get_date, sum(size_gb) tot_size, sum(used_gb) used_size     from ( select to_char(s.begin_interval_time, 'yyyy-mm-dd') get_date

                 , v.name ts_name
                 , (round(max((t.tablespace_size * 8192)) / 1024 / 1024 /
1024, 2)) size_gb
                 , (round(max((tablespace_usedsize * 8192)) / 1024 / 1024 /
1024, 2)) used_gb
              from v$tablespace v, dba_hist_snapshot s,
dba_hist_tbspc_space_usage t
             where t.tablespace_id = v.ts# and t.snap_id = s.snap_id
          group by to_char(s.begin_interval_time, 'yyyy-mm-dd'), v.name)

group1
group by get_date
order by get_date
/

But I guess that's not what you are really asking. You want to avoid doing TWO aggregations.

If the inner aggregation had been a SUM and the outer aggregation had been a SUM of that sum - probably you would make do with just the outer aggregation or some ROLLUP if you wished to keep both results of inner aggregation or outer aggregation.

As it is, your inner aggregation uses MAX and the outer aggregation is a SUM. I don't see how that could be combined with ROLLUP or similar. Nesting aggregations I do like you have done - an inline view or with clause.

It's possible to nest an aggregate in an analytic function, like for example:

  select to_char(s.begin_interval_time, 'yyyy-mm-dd') get_date

       , v.name ts_name
       , (round(sum(max((t.tablespace_size * 8192)) / 1024 / 1024 / 1024)
over (
             partition by to_char(s.begin_interval_time, 'yyyy-mm-dd')
          ), 2)) tot_size
       , (round(sum(max((tablespace_usedsize * 8192)) / 1024 / 1024 / 1024)
over (
             partition by to_char(s.begin_interval_time, 'yyyy-mm-dd')
          ), 2)) used_size

    from v$tablespace v, dba_hist_snapshot s, dba_hist_tbspc_space_usage t    where t.tablespace_id = v.ts# and t.snap_id = s.snap_id group by to_char(s.begin_interval_time, 'yyyy-mm-dd'), v.name order by get_date, ts_name
/

But that does not give the result you want, as even though it calculates the correct tot_size and used_size you want, it keeps the rows for each date/tablespace combination rather than aggregating to date level.

I can't offhand think of a way to avoid two aggregations. Two aggregations is the way to do what you want, as far as I can think of ;-)

Do you actually want the maximum tablespace size for each tablespace per day? Or would you actually want the *latest* tablespace size for each tablespace per day?

I mean, if you during a day move a lot of data from one tablespace to another, the max for the first tablespace will be a high value from *before* the move, the max for the second tablespace will be a high value from *after* the move. Then the sum for the day will be artificially high, as the amount of moved data will count twice in the sum, won't it?

You could utilize the KEEP functionality of aggregates in the inner aggregation to do something like this:

  select to_char(get_date, 'yyyy-mm-dd') the_date

       , round(sum(ts_size) * 8192 / 1024 / 1024 / 1024, 2) tot_gb
       , round(sum(ts_used) * 8192 / 1024 / 1024 / 1024, 2) used_gb
    from (  select trunc(s.begin_interval_time) get_date
                 , max(t.tablespace_size) keep (
                      dense_rank last order by s.begin_interval_time
                   ) ts_size
                 , max(tablespace_usedsize) keep (
                      dense_rank last order by s.begin_interval_time
                   ) ts_used
              from v$tablespace v, dba_hist_snapshot s,
dba_hist_tbspc_space_usage t
             where t.tablespace_id = v.ts# and t.snap_id = s.snap_id
          group by trunc(s.begin_interval_time), v.name) group1

group by get_date
order by get_date
/

Using the KEEP with dense_rank last order by s.begin_interval_time means that the MAX function only operates on those rows (within the group) that have the latest begin_interval_time. So that query will not find the *highest* values for a tablespace on a given day, but the *latest*.

Whether that is what you want is another question - perhaps both versions actually might be useful ;-)

But avoiding two aggregates I just don't think is possible (at least not without some very tricky weird SQL that probably would cause optimizer meltdown ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Thu, Mar 12, 2015 at 1:55 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> This is a learning experience for me.  I wrote the query below to pull
> sizing trends at the database level.  Note it uses a with.  I think it can
> be written with a single select and a roll up clause, or perhaps another
> function I am not aware of. Any ideas?
>
> with group1 as (select
> to_char(s.begin_interval_time,'yyyy-mm-dd') get_date,
> v.name ts_name,
> (round(max((t.tablespace_size*8192))/1024/1024/1024,2)) size_gb,
> (round(max((tablespace_usedsize*8192))/1024/1024/1024,2)) used_gb
> from v$tablespace v, dba_hist_snapshot s, dba_hist_tbspc_space_usage t
> where t.tablespace_id=v.ts#
> and t.snap_id=s.snap_id
> group by to_char(s.begin_interval_time,'yyyy-mm-dd'), v.name)
> select get_date, sum(size_gb) tot_size,sum(used_gb) used_size
> from group1
> group by get_date
> order by get_date
>
> Sent from my iPad--
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Mar 12 2015 - 14:35:33 CET