A few weeks ago, our customer support team asked us to automate part of their checklist that looks at the number of active sessions in our production database. In EM12c, this seemed like a no-brainer with the Average Active Sessions metric. So I added this to my production incident ruleset and went back to another project. Over the next few days we'd get pinged by EM12c but the support folks would say it shouldn't. After (more...)
A few weeks back, we began making changes to prepare for using Oracle Golden Gate. One of the first steps required is to enable "minimal supplemental logging" at the database level. We did this during an evening maintenance window. However by the time the morning workload picked up, we started seeing a lot of sessions blocking, and the root blocker was one of the DB Writer (DBWn) processes.
Looking at the blocked sessions, a query (more...)
This week I migrated our EM12c repository database to a new server as part of it's promotion to production status. Just to make it a little more exciting, the migration also involved an in-flight upgrade from 184.108.40.206 to 220.127.116.11. Much of this post is directly inspired by Martin Bach's post on the same subject
. I ran into a few other snags that weren't mentioned so I thought it would (more...)
Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)
I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.
I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)
Readers of an earlier post on this blog
will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.
To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420
. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)
When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation
. With a 25Tb database, waiting to restore from backups is not (more...)
At March I will be presenting my very successful seminar “Mastering Backup and Recovery” in some countries of Latin America for the very first time. Thank you Panama, Chile and Brazil OUGs for inviting me to your amazing countries! Please, use the following links for registration and also to find more information about the seminar: […]
Hi All, On February 4, 2014 at 9:30 am PT I will be talking on the next OTN Virtual Developer Day about Oracle VM and Oracle Database. Come and discover the answers for the following questions: Does an Oracle Database perform well on a virtualized environment? What virtualization technology is more stable and […]
Note: The following is based on testing with 18.104.22.168 (I believe same issue exists within other Oracle versions).
I recently worked on an interesting problem relating to the “enq: TX – contention” wait event. There are a number of reasons for the wait but the most common (more...)
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 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...)
After a successful migration from Oracle 10gR2 to Oracle 11gR2, I observed a very odd behavior executing a query; On first execution the query run fast - means 0.3 s; On second not faster but with tremendous reduced speed - approximate 20 s;
Now this behavior is the opposite I experienced with complex queries (lot of joins, lot of predicates) , the first time of execution needs the extra cost of hard parsing and disk reads if the data is not in cache. the second time even the query run initial several seconds it run in a fraction of (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 [...]