Moving through some observations of an ongoing investigation tackling the easiest ones first.
Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.
Statement was sql id 9v9n97qj8z1dg:
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum);
Yesterday I mentioned issues with a recursive delete operation on statistics history.
This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.
COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.
create table t1 as select * from dba_objects; alter session set statistics_level = all; (more...)
Today I helped resolve a problem quickly but to the detriment of my understanding of exactly what was going on and why.
And as part of the quicker resolution, I had to destroy the evidence which would have helped understand better.
So… now need to go back and figure it out if I have time to model it properly, etc.
Here’s what little I know so far.
What happened was that there were a number (more...)
Prior to 188.8.131.52, DBMS_STATS.PURGE_STATS does a slow delete of stats before the parameterised input timestamp.
Why might you be purging? Here’s one such illustration:
This delete can be slow if these tables are large and there are a number of reasons why they might be so, notably if MMON cannot complete the purge within its permitted timeframe.
But note that if you’re happy to purge all history, there (more...)
Just a reference to a really simple difference between CALL and EXEC.
I thought I had mentioned this before but couldn’t find it so…
EXEC/EXECUTE is a SQL*Plus command which wraps the proc call in an anonymous BEGIN … END; block.
CALL is a SQL command hence it is limited to SQL data types and there are other restrictions which the documentation sums up pretty well.
Because CALL is SQL, there is on key behavioural (more...)
Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.
First – old news – note that initial extent size for partitioned tables changed in 184.108.40.206.
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.
Pre 11.2. (more...)
Learning, relearning or unforgetting…
What value is there in a DISABLEd constraint?
This was a question on the OTN forums this week and a) my first reply was wrong and b) I couldn’t find a clear demonstration elsewhere.
The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that (more...)
Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.
This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.
This is quite a long article so I will do a top-level TOC first
With Basic Compression, you cannot drop a column.
create table t1 (col1 number ,col2 number) compress; table T1 created. alter table t1 drop column col2; SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables 39726. 00000 - "unsupported add/drop column operation on compressed tables" *Cause: An unsupported add/drop column operation for compressed table was attemped. *Action: When adding a column, do not specify a default value. DROP column is only supported in the form (more...)