Context: Oracle (since version 11gR1) exposes (more...)
You can download the latest version here.
In addition to many changes to the way the information is presented and many other smaller changes to functionality there is one major new feature: XPLAN_ASH now also supports S-ASH, the free ASH implementation.
If you run XPLAN_ASH in a S-ASH repository owner schema, it will automatically detect that and adjust accordingly.
Note: This post is about diagnosing real life scenario caused by direct path decision change on 22.214.171.124 which already covered by Tanel Poder on his awesome Optimizer statistics-driven direct path read decision for full table scans (_direct_read_decision_statistics_driven) post
If you already read and know how to (more...)
Here one day before deadline, I have entered almost my entire current catalog of presentations for consideration in the Oracle OpenWorld 2013 agenda. One new for this year: “Worst Oracle ADF Project Ever”
If you would like me to speak at one of your events, the following are the presentations (more...)
While recovering a database, stuck with ORA-00600 and database crashed. We have an Oracle version 11.1 and were hitting a bug: 8310931 and fixed in 11.2. The bug says, this problem will happen usually on high number of CPUs while doing transaction recovery. We got 126 CPU in the DB server.
Errors in file /opt/oracle/diag/rdbms/xxxx/xxx/trace/xxx_smon_29786.trc (incident=120257): ORA-00600: internal error code, arguments: [ktprhtnew6], , , , , , , , , , ,  Incident details in: /opt/oracle/diag/rdbms/xxx/xxxx/incident/incdir_120257/xxxx_smon_29786_i120257.trc Fri Feb 22 22:49:14 2013 Trace dumping is performing id=[cdmp_20130222224914] Fatal internal error happened while SMON was doing active (more...)
First of all I started with tracing issue. I have checked server CPU and disk utilization using NMON (tool name is important here). There wasn’t any problem with CPU utilization – or at least this is what I was thinking at that time. Tracing was enabled with level 12 (bind and wait) and it generate 2 GB of trace file (more...)
Context: There is much information in the counters and metrics of Oracle's AWR that can be of substantial help for troubleshooting and for capacity planning. Besides the standard AWR report, time-based analysis is often very useful. However this type of access is (more...)
Native PLSQL execution has changed significantly since the version 10G. The effects are less than spectacular, in the ideal case I got about 10% of the performance boost. This is much easier to administer now, there are no shared libraries in the file system, as was the case with version 10G. Oracle has re-invented the dlopen routine, with the shared library residing in the database. The gory details can be found at:
On the same web site, there is also a PDF version of the same page but WordPress displays some inane commercial, rather than to (more...)
I recently investigated an IO performance “spike” on a large 126.96.36.199 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...)
Oracle has ported DTrace for Oracle Linux. DTrace is a very powerful performance analysis and troubleshooting tool that allows you to instrument all software. It’s name is short for Dynamic Tracing. I’m not a DTrace expert but some say it is that powerful that it allows you to reverse engineer any software…
Filed under: Oracle, Performance
Our Experience Tells Us So...We know from hard-fought experience that the more work we push through a system the more processing time involved. That's not rocket science... but to mathematically represent the relationship between work and time is rocket science.
Decades ago Operations Research queuing theory researchers quantitatively established the relationship between work and time. They put into math what we all have experienced. In our DBA world this means that as the system activity increases, then so does CPU consumption and eventually Oracle non-idle wait time.
Find The Big BarThe classic way to graphically represent all the (more...)
This Monday Oct 1, 2:00-2:50PM I’ll be presenting at the OakTable World which used to be called as Oracle Closed World, this year it’s a full two-day conference with awesome speakers lineup and very interesting topics… you can just watch this video by Mogens Norgaard to know more about the roots of this very technical event.
My presentation title is “Where did my CPU go?” – monitoring & capacity planning adventures on a consolidated environment .. and If you love to hack the ASH and AWR data and curious about CPU capacity planning then you’re really going to enjoy (more...)
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 [...]