A reader, January 27, 2017 - 12:17 pm UTC
Hi
Thank You very much for Your prompt answer.
Interesting to notice that code that I had found from internet wasn't working ok ;). Maybe here is a lesson for me to be learned.
I ran this code You generously provided and there were > 40 000 partitions with 1 or 2 blocks in memory. Bot index and tables.
E.g. Our biggest table has 2154 partitions.
It has two local indexes.
Table partitions had either 2 or 1 blocks in memory for .
First index had for 2153 partitions with 1 block in memory.
Second index had for 2153 partitions either 2 or 1 blocks in memory.
Size of the table and indexes is 65440896 blocks.
To me it looks like some blocks are 'more equal' than others and are not as likely removed from memory. Headers ?
A reader, January 27, 2017 - 12:45 pm UTC
Hi
Investigated this a bit more.
There is one obsolete schema in the database. There are no sql-statements issued against these tables.
However their partitions have 1 block in memory.
There do exists scripts which are using
user_tab_partitions for these tables and dba_free_space against tablespaces. Could these scripts cause headers of partitions to be references ?
Help is much appreciated.
January 27, 2017 - 5:20 pm UTC
What exactly is the script you're running?
A reader, January 28, 2017 - 6:53 am UTC
Hi
I just ran
select o.owner, o.object_name object_name, o.subobject_name ,
o.object_type object_type, count ( * ) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner not in ( 'SYS','SYSTEM' )
and bh.status <> 'free'
group by o.owner, o.object_name, o.subobject_name,
o.object_type
order by count ( * ) desc;
(percentage information is actually not very interesting for my purposes and for coalesced indexes it can be misleading)
January 30, 2017 - 5:44 pm UTC
Thanks. And what are the other scripts you think might be loading the blocks?
Are you sure nothing is reading the rows?
Answers
A reader, February 01, 2017 - 10:41 am UTC
Hi
Service provider is running monitoring scripts which look at following views:
dba_undo_extents
dba_free_space
dba_data_files
dba_segments
v$datafile_header
v$tablespace
v$tempfile
Our compression scripts read user_tab_partitions -view in order by determine which partitions require compressing.
Normal optimizer statistics gathering scripts are run.
I am pretty sure that for this one obsolete schema there are no applications accessing these tables. I couldn't find such statements from the memory and applications should not be doing these kind of operations.
I am now going to flush database blocks and statements from memory and try to monitor what is happening.
More information
A reader, February 01, 2017 - 11:54 am UTC
Hi
I flushed memory buffers and sql-statements.
For obsolete schema blocks have gotten back into memory, blocks are both for table and index partitions. There is typically 1 block in memory. Partitions do hold more blocks.
There are no sql-statements in sga area accessing these tables.
Table compression and optimizer statistics gathering scripts have not been run.
Service providers monitoring statements have been run. Also rman backup (most likely incremental backup with block change tracking)
A reader, February 01, 2017 - 12:19 pm UTC
A reader, February 01, 2017 - 12:23 pm UTC
Hi
Tested this a little bit.
1)Flushed buffer cache.
2)Checked with
select o.owner, o.object_name object_name, o.subobject_name ,
o.object_type object_type, count ( * ) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner not in ( 'SYS','SYSTEM' )
and bh.status <> 'free';
what is in memory.
And that was empty.
3) executed statement
select * from dba_free_space;
4)
executed step 2).
and then there were partitions with 1 block in memory.
More than 40 000. About the amount of partitions in database.
So it looks like dba_free_space is reading headers for each partition.
This is used in monitoring scripts which try to figure out are tablespaces becoming too full ?
What would be the best way to do this in heavily partitioned dw databases ?
These monitoring scripts are run quite often, so they are reserving quite a lot of memory.
February 01, 2017 - 2:28 pm UTC
I'm not seeing the partitions loaded in BH after querying dba_free_space:
drop table t purge;
create table t (
x int
) partition by range (x) interval (10) (
partition p0 values less than (10)
);
insert into t
with rws as (
select rownum x from dual connect by level < 100
)
select * from rws;
commit;
truncate table t;
select count ( distinct o.subobject_name ) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner = 'CHRIS'
and o.object_name = 'T'
and bh.status <> 'free';
NUM_BLOCKS
10
alter system flush buffer_cache;
select count ( distinct o.subobject_name ) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner = 'CHRIS'
and o.object_name = 'T'
and bh.status <> 'free';
NUM_BLOCKS
0
begin
for spce in (
select * from dba_free_space
) loop
null;
end loop;
end;
/
select count ( distinct o.subobject_name ) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner = 'CHRIS'
and o.object_name = 'T'
and bh.status <> 'free';
NUM_BLOCKS
0
But in any case, there's an obvious question:
If the schema is obsolete, why don't you drop it?
A reader, February 02, 2017 - 7:39 am UTC
Hi
This gets interesting.
I repeated Your test in our environment.
Got different results. At final step there was 10 blocks in memory.
Executing the 'select * from dba_free_space' did take some time (40 seconds). It is quite possible, that some monitoring script was executed at the same time. I have no control to those.
Our database consists of several schemas. This behaviour can be observed at all of them. I mentioned especially these obsolete schemas which have been kept for archiving purposes, because I am pretty sure there are no application scripts accessing them.
Tablespaces are by the way
- locally managed
- segment space management auto
- allocation type system or uniform.
Any ideas ?
February 02, 2017 - 4:24 pm UTC
Hmmm, not sure. As you say, 40s is long enough for something else to be hitting the table...
You're on 11.2.0.4, yes?
If the schemas are archived, why don't you take a final backup and drop them?
11.2.0.4 is used
A reader, February 07, 2017 - 12:47 pm UTC
Hi
We are using 11.2.0.4 version.
in database there are half a dozen schemas. There exists 2 obsolete small schemas which are kept in database 'if somebody would need them'. Vast majority of tables and especeially of partitions are in those avtive schemas.
I mentioned here thse obsolete schemas, because I am pretty sure, that these schemas are not used by any applications.
February 07, 2017 - 4:32 pm UTC
See if those blocks in the buffer cache are metadata about the segments themselves (for example, segment header blocks or extent mapping blocks).
Compare the block id's with those in DBA_SEGMENTS.HEADER_xxx
It might be just metadata rather than the data from the partitions themselves.
header_blocks seem to be the reason.
A reader, February 08, 2017 - 10:41 am UTC
Hi
V$BH.BLOCK# and DBA_SEGMENTS.HEADER_BLOCK were same.
So it looks like header_blocks are in memory.
I tried Your script in 12.0.1 test environment. I could not reproduce Your results. In memory were 10 blocks.
And it looked like they came into memory when issuing command 'select * from dba_free_space'.