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 220.127.116.11, 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...)
As usual the latest version can be downloaded here.
This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 18.104.22.168).
As an extra however, (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 22.214.171.124.
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...)
For one of the client, standby server went down. We had another standby server which was kept down for more than a month. Decision was taken to start the server and apply incremental SCN based backup on the standby database.
The standby was on ASM and the Primary on filesystem.Incremental backup was started from the SCN reported by below query
select min(fhscn) from x$kcvfh;
Once the backup completed, it was transferred to standby, standby (more...)
This part is about the main funcationality of XPLAN_ASH: SQL statement execution analysis using Active Session History and Real-Time SQL Monitoring.
In this video tutorial I'll explain what the output of XPLAN_ASH is supposed to mean when using the Active Session History functionality of the script. Before diving into the details of the script output using sample reports I provide some overview and (more...)
What would you say if someone tells you that (s)he just did a simple, straightforward "SELECT * FROM TABLE" that took several minutes to execute without returning, only to then error out with "ORA-01652 unable to extend temp segment", (more...)
I recently investigated a performance problem on an Oracle 11.2 OLTP trading system and although we still don’t fully understand the issue (and which versions of Oracle it effects), I thought I would share what we found (and how we found it). We had a hardware failure on the database server, within 30 seconds the database had automatically been restarted on an idle identical member of the cluster and the application continued on the (more...)
sqlplus / as sysdba <<EOF
whenever sqlerror exit sql. (more...)
Last week I had performed switchover activity of database on version 126.96.36.199 The switchover was performed using dgmgrl “swicthover to standby” command. After sometime we started receiving “ORA-00600: [ktbdchk1: bad dscn]” on the primary database.
Tue Dec 16 10:33:26 2014 Errors in file /ora_software/diag/rdbms/db02_dbv/dbv/trace/db02_ora_16271.trc (incident=434103): ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], , , , , , , , , , ,  Incident details in: /ora_software/diag/rdbms/db02_dbv/dbv/incident/incdir_434103/db02_ora_16271_i434103.trc
The trace (more...)
As usual the latest version can be downloaded here.
There were no too significant changes in this release, mainly some new sections related to I/O figures were added.
One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings (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
Just a quick blog post on things you might see after upgrading to 188.8.131.52. We recently upgraded database from 184.108.40.206 to 220.127.116.11 and query on some data dictionary views ran too slow.
1. Performace of query on dba_free_space degraded
2. Performance of query involving dba_segments is slow
DEV01> select ceil(sum(b.bytes)/1024/1024) b from sys.dba_free_space b; Elapsed: 01:31:45.78
Searching MOS pointed to these Doc Ids (more...)