When is a row added to DBA_HIST_SQLSTAT

awroracle

I don't understand the criteria that entails an additional record in the dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement to return no record:

select 
  count(*),
  snap_id,
  sql_id
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id
having
  count(*) > 1
order by
  count(*) desc;

Yet, it returns many records, indicating that it is not possible, but the normality, that the same sql statement is captured multiple times per snap_id.

Additionaly, with more than one record per sql and snap period, how do I interpret the delta columns within this view? The documentation reads:

The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

This doesn't really make sense to me.

Update as per Justin's comment: this is not in a RAC environment: so even if I group by snap_id, sql_id, dbid, instance_number, the query returns multiple records per snap_id and sql_id.

Best Answer

Oracle collects SQL statistics for each SQL execution plan/sql_id (plan_hash_value), rather than just for a specific sql_id.

To verify that this is indeed the case, this query should return 0 rows:

select 
  count(*),
  snap_id,
  sql_id,
  plan_hash_value
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id,
  plan_hash_value
having
  count(*) > 1
order by
  count(*) desc;

Related Solutions

How will Oracle fill a datafile added to a tablespace

In general, extents are allocated in a round-robin fashion among all data files in a tablespace so long as those data files have enough free space to allocate the extent. So if you add a new data file, you would generally expect that half of the new extents would be allocated in the existing data file and half of the new extents would be allocated in the new data file. Assuming the two data files are the same size, you would generally expect that by the time the first data file got to 100% full, the second data file would be roughly 50% full.

I'm not sure, though, how this helps you deal with increasing tablespace size without wasting physical space. I'm hard-pressed to imagine in what circumstances knowing how extents are going to be allocated is going to help you use less physical space. It shouldn't matter from that perspective whether you double the size of the existing data file, add a new data file, or add multiple data files and it shouldn't matter whether one data file is filled up before the next one starts being used. In any case, you've allocated the same amount of space at the operating system level, you've allocated the same number of extents in the tablespace, and your segments are all the same size.

You would decrease the amount of space allocated at the operating system level by letting the data files autoextend rather than allocating fixed size data files but then you have to monitor the free space at the operating system level to ensure that the data files all have enough space to grow and you may complicate your ability to move data files to different mountpoints. You may also decrease the amount of parallelism you can get in your backups forcing the backup job to run a bit longer.

Select one single row from table b for each row from table a

How about this:

Select * 
from 
  tableB b
 inner join
  (select a.*, row_number() over (partition by a.type order by a.type) rn from tableA a) a1
    on b.type=a1.type and a1.name='SEARCHED_NAME' and a1.rn=1

PS. Not tested, as you didn't provide a script to create and populate the tables.

Related Question