My site uses a 3rd party SQL monitoring tool which collects data based on the Oracle view v$sqlstats. The tool collects data for all sql statements which have been executed since the previous collection using the last_active_time column. A few months ago we noticed (after an upgrade to 11g) we (more...)
I just stumbled upon this bug reference on My Oracle Support:
Bug 13262857 Enh: provide some control over DBMS_STATS index clustering factor computation
This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.
The flaw in the over-simplistic and pessimistic original computation was (more...)
I’ve worked on 24×7 systems for more than a decade, and I have a real dislike of downtime. For one, it can be a real pain to agree any downtime with the business, and while RAC can and does help when you do work in a rolling fashion, there is still risk.
The promise of online patching has been a long one, and it is only recently that I dipped my toe in the water with them. Unfortunately, they are not a panacea, and in this blog posting I’m going to share some of the downsides.
Of course not all (more...)
Three items within:
Part 1 – Subquery materialisation not possible
Part 2 – Materialised views
Part 3 – Lock scripts
Part 1 – Subquery materialisation not possible
Yesterday I was reminder of a familiar issue, officially listed as a bug, that can occur when combining subquery materialisation with distributed transactions.
I had previously tuned a query for a colleague, the best approach for which was using implicit materialised subquery factoring (implicit because it wasn’t hinted as /*+ materalize */ but was referenced multiple times in the main query) and I had provided such a solution, without even a thought (more...)
I recently investigated an IO performance “spike” on a large 22.214.171.124 transactional system and I thought I would cover some interesting issues found. I am going to take the approach of detailing the observations made from our production and test systems and avoid attempting to cover how other versions of Oracle behave. The investigation also uncovers a confusing database statistic which we are currently discussing with Oracle Development so they can decide if this is an Oracle coding bug or a documentation issue.
The initial IO issue
We run a simple home grown database monitor which watches database wait events and sends an email alert if it detects either a (more...)
Recently, i was troubleshooting a datapump export duration problem. Over the period of 18 months, the duration of a multi-table export increased dramatically. A quick analysis showed that the export duration was mainly dependent on one big table. This 50 GB table with no BLOB/CLOB/LONG datatypes took more than 4 hours on a modern system wheras it should not take more than 10 – 15 minutes. The system was performing ONLY single-block I/O requests (db file sequential read). I found this strange and started investigating.
Beginning with 11g, you can enable sql_trace (10046) for datapump with the new syntax:
Original Post can be viewed at Limiting I/O and CPU resources using 11g Oracle Resource Manager
Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager. We decided using elapsed_time as criteria for cancelling sql.But during [...]
We need to follow the
I would like to point your attention to a bug in RAC 126.96.36.199. The bug leads to instance crashes during Dynamic Remastering. As of June 1st, there is no patch publicly available. The workaround seems to be to set:
So, if you are thinking about upgrading your RAC to 188.8.131.52, maybe you should wait just a little bit longer…
More details are in MOS: Bug 13397104: INSTANCE CRASH WITH ORA-600 [KJBLPKEYDRMQSCCHK:PKEY]
Update 12.06.2012: Patch 13397104 is now available in MOS for 184.108.40.206.
Oracle provides and documents a huge load of possibilities and functions for nearly every purpose. For me it is impossible to know all of them. Even to know such an area exists is hard.
But still sometimes these functions Oracle does not document for customers purpose seems to be more attractive than those officially available.
One of these attractive packages is DBMS_SYSTEM. You will not find any description of this package in the official Oracle documentation. There are some small traces available, but nothing really useful.
Oracle also have quite clear words about using such unofficial, and hidden, packages:
Oracle yesterday announced certification of RHEL6 / OEL6 with Oracle Database 11gR2 and Oracle Fusion Middleware 11.1.
Moreover, Oracle will from now on provide errata packages for free. Until now, they could only be obtained with a valid ULN support contract. I think this will be a big plus for Oracle Linux in competition with Red Hat Enterprise Linux.
I wonder how long it will take before they ship Exadata and Oracle Database Appliance (ODA) with Unbreakable Kernel.
Gone are the day when cleartext passwords had to be stored in scripts for Oracle database access. The solution to this requirement is “Oracle Secure External Password Store (SEPS)”. This article will give a short introduction and a practical example of the solution.
no Advanced Security Option (License) necessary
every unix-account, who has access to the wallet can use it to log on to the contained databases without a password! Therefore prevent other unix-accounts from accessing your wallet! (chmod, chown)
Oracle Client: Unix-Account, who wishes to connect to the database without providing a password needs to (more...)
If you want to use Oracle file watcher, you need to Create a Credential. As there a password needs to be stored in the database, Oracle tries to save it in a secure way. But as the password must be decrypted for the purpose to login on the file watchers agent side, it is not safe at all:
The credentials are stored with
DBMS_SCHEDULER.CREATE_CREDENTIAL. Here an example:
credential_name => 'local_credential',
username => 'oracle', password => 'welcome1');
credential_name => 'local_credential2',
username => 'oracle2', password => 'welcome1');
It's quite easy to see the values (more...)
I currently have the fun to review DBMS_SCHEDULER. As I'm always interested in ways to trace anything, to dig deeper in case of problems, I searched for ways to trace it.
As I did not find a collected list of events anywhere, I start them here. It's by far not a complete list, so feel free to discuss and contribute, if you want!
- resolve default queue owner to current user in enqueue/dequeue
- Cause: resolve default queue owner to current user in enqueue/dequeue.
- Action: turn on if client wish to resolve the default queue owner to the current user. (more...)
I just saw MOS Note
ORA-4031 After Upgrade From 220.127.116.11 To 18.104.22.168 With Leak in ‘KTC latch subh’ Subheaps [ID 1398984.1]
giving very little information about this bug. At the time of this writing, there is no patch available but I expect it to be in a few days. I have checked some 22.214.171.124 instances and found only one with > 60 MB for this subheap.
To check the performance of RMAN backup I recently started to trace it a little bit. As most of the time was not spent in any reading from disk or writing to media manager library event, it was on CPU. It's good to know the CPUs are of any good, but as I still want to know what's going on I tried to dig any deeper. CPU cycles are not just a magic black box where we put in a problem and the answer comes out after some times. At an abstraction layer it's a chain of functions where one (more...)
At one of my customers, we wondered about some entries in v$datafile.unrecoverable_change# for the most critical tablespace of the application. Certainly no application components were allowed to perform nologging operations for segments inside this tablespace and yet there was evidence that “something” was done with nologging.
- The unrecoverable_time was shortly after 10 pm
- The ASH data covering this time period showed activity by DBMS_SCHEDULER and Autotask Jobs.
- An internal DDL log table showed the following operations:
CREATE TABLE "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP tablespace "DAT_MYOWNER" nologging AS SELECT /*+ FULL("MYOWNER"."MYTAB") */ * FROM "MYOWNER"."MYTAB" sample block( 3.55) (more...)