Oracle DBA – A lifelong learning experience

When partitions in a table have been explicitly locked, “lock_table_stats” and “unlock table_stats” do not override this behaviour

Posted by John Hallas on March 18, 2011

Problem Statement:
rdbms version 11.1.0.7 – HPUX
When partitions in a table have been explicitly locked, “lock_table_stats” and “unlock table_stats” do not override this behaviour.
 
Question To Answer:
 
Is what we are seeing expected behaviour/documented?
I have a table (t1). It has two partitions (p1 and p2).   I lock the statistics on p1 using dbms_stats.lock_partition_stats. I lock all statistics on table t1 using lock_table_stats. I unlock all statistics on table t1 using unlock_table_stats.

I would expect both partitions p1 and p2 to be unlocked. However, partition p1 still has the statistics locked.  This can be proved by being unable to gather stats on that table (error = partition locked) unless you use the force option. The same applies when you gather partition stats.

TEST CASE:

-- Set up test case
-- Partitioned table, 2 populated partitions.
drop table test_stats
create table test_stats (
column1 number(10),
column2 varchar2(20),
CONSTRAINT "TEST_PK" PRIMARY KEY (column1))
PARTITION BY RANGE (column1)
(PARTITION "PART_1"  VALUES LESS THAN (3),
 PARTITION "PART_2" VALUES LESS THAN (5)
 )
insert into test_stats (column1, column2) values (1,'aaaaaaaaaa');
insert into test_stats (column1, column2) values (2,'bbbbbbbbbb');
insert into test_stats (column1, column2) values (3,'cccccccccc');
insert into test_stats (column1, column2) values (4,'dddddddddd');
commit;
select count (*) from test_stats;
select *
from   test_stats partition (part_2)
--begin dbms_stats.gather_table_stats('SYSTEM','TEST_STATS'); end;
begin
  dbms_stats.gather_table_stats(OWNNAME => 'SYSTEM'         ,
   TABNAME =>'TEST_STATS'        ,
   --PARTNAME                    ,
   ESTIMATE_PERCENT => 0.01      ,
   BLOCK_SAMPLE=>FALSE           ,
   METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO',
   DEGREE=>8                     ,
   GRANULARITY=>'AUTO'           ,
   CASCADE=>TRUE                 ,
   FORCE=>TRUE
  );
end;

— Initially all partitions are unlocked.

select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, stattype_locked
from dba_tab_statistics
where table_name = 'TEST_STATS';
"TABLE_NAME" "PARTITION_NAME" "OBJECT_TYPE" "SAMPLE_SIZE" "LAST_ANALYZED" "GLOBAL_STATS" "STATTYPE_LOCKED"
"TEST_STATS" ""               "TABLE"       "4"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_2"         "PARTITION"   "2"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_1"         "PARTITION"   "2"           "08-MAR-11"     "YES"           ""
-- Then lock one partition
begin dbms_stats.lock_partition_stats('SYSTEM','TEST_STATS','PART_1'); end;
select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, stattype_locked
from dba_tab_statistics
where table_name = 'TEST_STATS';
"TABLE_NAME" "PARTITION_NAME" "OBJECT_TYPE" "SAMPLE_SIZE" "LAST_ANALYZED" "GLOBAL_STATS" "STATTYPE_LOCKED"
"TEST_STATS" ""               "TABLE"       "4"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_2"         "PARTITION"   "2"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_1"         "PARTITION"   "2"           "08-MAR-11"     "YES"           "ALL"

— Now lock the whole table

begin dbms_stats.lock_table_stats('SYSTEM','TEST_STATS'); end;
select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, stattype_locked
from dba_tab_statistics
where table_name = 'TEST_STATS';
"TABLE_NAME" "PARTITION_NAME" "OBJECT_TYPE" "SAMPLE_SIZE" "LAST_ANALYZED" "GLOBAL_STATS" "STATTYPE_LOCKED"
"TEST_STATS" ""               "TABLE"       "4"           "08-MAR-11"     "YES"           "ALL"
"TEST_STATS" "PART_2"         "PARTITION"   "2"           "08-MAR-11"     "YES"           "ALL"
"TEST_STATS" "PART_1"         "PARTITION"   "2"           "08-MAR-11"     "YES"           "ALL"

— Now unlock the whole table

begin dbms_stats.unlock_table_stats('SYSTEM','TEST_STATS'); end;
select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, stattype_locked
from dba_tab_statistics
where table_name = 'TEST_STATS';

— Partition locked above is still locked.
— Expected that all partitions are unlocked.
— If this is expected, where can I see metadata to reflect this?

"TABLE_NAME" "PARTITION_NAME" "OBJECT_TYPE" "SAMPLE_SIZE" "LAST_ANALYZED" "GLOBAL_STATS" "STATTYPE_LOCKED"
"TEST_STATS" ""               "TABLE"       "4"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_2"         "PARTITION"   "2"           "08-MAR-11"     "YES"           ""
"TEST_STATS" "PART_1"         "PARTITION"   "2"           "08-MAR-11"     "YES"           "ALL"

After a lot of testing and talking with Oracle support we got the following response

The dbms_stats.unlock_table_stats procedure is behaving as designed but the implementation of showing the stats as locked on partitions is not very intuitive. The key to understanding what is happening is in the code used to create the view DBA_TAB_STATISTICS which is in file $OH/rdbms/admin/catost.sql. The dbms_stats procedures to lock / unlock tables are simply setting / unsetting single bits in the base tables (sys.tab$ and sys.tabpartv$). The view definition for DBA_TAB_STATISTICS decodes these bits with bitand operations to show the NULL, ‘DATA’, ‘CACHE’, ‘ALL’ values. There was no intention of having the unlock_table_stats call cascade to partitions but it would be  a nice enhancement to have a argument on the procedure that allowed this. It also might be a nice enhancement to have the procedure unlock_table_stats put out a warning if there are partitions with the lock bit set (“Warning: table stats unlocked but there are partitions with locked stats”).

That seems a very reasonable explanation and I will follow through and raise the enhancement request myself

Advertisements

2 Responses to “When partitions in a table have been explicitly locked, “lock_table_stats” and “unlock table_stats” do not override this behaviour”

  1. AS said

    Hi John, hope all’s well. I’m a reporter looking to write a story about how customers are affected by Oracle’s decision not to support Itanium. Could we chat about the topic? Feel free to e-mail agam_shah AT idg.com

    Thanks

    • John Hallas said

      It is a bit worrying for a site like ours which is totally Oracle on HPUX (bar a few Windows databases). It looks like it is a choice between Solaris or Linux. How long will it be before Oracle start pushing Solaris ahead of Linux. I know they have OEL but that is just an off-shoot of RH Linux and I think Oracle would want to see a site which is completely Oracle from the hardware through the OS, through the storage layer (ASM and Exadata) and then through the database.

      Interesting times

      John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: