Improving data move on EXADATA III

Moving to history tables

In the last post I talked about how we made the speed of actually writing all those log-records much faster. It has to date been so fast that no a single report of a problem has been filed. you find that post here.

Once the data (more...)

Speaking at E4 2013! … and some Exadata Patents good stuff

I’ll be co-speaking with Tyler Muth at E4  and we will be talking about Performance Visualization… some of you may already know that I’m really a big fan of Tableau and just to give you an example on what the tool can do I’ve consolidated all the different viz that (more...)

Improving data move on EXADATA II

Writing log records

The last post in this series introduced the problem briefly. You find that post here.

In this post I’ll talk about the changes made to make that writing of log records fast enough. There were 50 million records that was written. Each of them pretty much in its (more...)

Faster data move on EXADATA I

Introduction

In my work among other things I tune and tweak solutions for EXADATA. Today I’ll write about a big improvement we achieved with a process that moves data from the operational tables to the ones where the history is stored.

This will not be a technical post. While I (more...)

SQL Plan Stability and CBO Statistics Myths Busted

Carlos Sierra:

Since lack of Histograms or freezing CBO Statistics do not guarantee Plan Stability, do not rely on these two myths. If what you are looking for is Plan Stability use then SQL Plan Management available since 11g or SQL Profiles available from 10g.


© Eddie Awad's Blog (more...)

Latency Heat Maps in SQL*plus with OraLatencyMap

Topic: This post is about the use of heat maps to investigate wait event latency in Oracle (and in particular I/O-related latency). This post also discusses a SQL*plus-based script/tool I have developed to help with this type of monitoring and performance drill-down (OraLatencyMap).

Context: Oracle (since version 11gR1) exposes (more...)

New Version Of XPLAN_ASH Tool – Video Tutorial

A new major release (version 3.0) of my XPLAN_ASH tool is available for download.

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.

(more...)

Direct Path Reads Again

Note: This post is about diagnosing real life scenario caused by  direct path decision change on 11.2.0.3 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...)

Oracle OpenWorld Submissions – The Full Monty

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...)

ORA-00600: internal error code, arguments: [ktprhtnew6]

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...)

Oracle on AIX – where’s my cpu time ?

This story begins couple of weeks ago when I was asked to look on performance problem for critical processes before go-live. There were differences between run times between different test systems and between runs with 10046 trace enabled or not.

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...)

AWR Analytics and Oracle Performance Visualization with PerfSheet4

Topic: This post describes PerfSheet4, a tool for performance analysis aimed at streamlining access and visualization of Oracle's AWR data. The tool is aimed at DBAs and Oracle performance analysts. PerfSheet4 is a spin off of previous original work by Tanel Poder, rewritten and integrated with additional functionality for AWR analysis and with important changes to the user interface.

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...)

11.2.0.3 Strange statistic, large transactions, dirty buffers and “direct path read”

Summary

I recently investigated an IO performance “spike” on a large 11.2.0.3 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...)

DTrace is now also available for Oracle Linux

Uncategorized
| Dec 31, 2012

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

Total Time and Bits of Time

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 Bar

The classic way to graphically represent all the (more...)

OakTable World 2012

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...)

Limiting I/O and CPU resources using 11g Oracle Resource Manager

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 [...]

AskDba.org Weblog

Adaptive STAT lines in SQL trace

Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with 11.2.0.2 which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.

Here is a case to demonstrate:

declare
    n number := 0;
begin
    dbms_session.session_trace_enable(true, true, 'all_executions');
   
    for x  (more...)

Speaking at E4!

I’ll be co-speaking with Randy Johnson (one of the authors of Expert Oracle Exadata) at E4 to share about the war stories and detail on a bunch of technical stuff on a Peoplesoft and BIEE consolidation project we had on one of our clients. See the abstract below:

Randy Johnson & Karl Arao
A PeopleSoft & OBIEE Consolidation Success Story
In today’s competitive business climate companies are under constant pressure to reduce costs without sacrificing quality. Many companies see database and server consolidation as the key to meeting this goal. Since its introduction, Exadata has become the obvious choice for (more...)

The effect of ASM redundancy/parity on read/write IOPS – SLOB test case! for Exadata and non-Exa environments

Last week I had a lengthy post at oracle-l that tackles Calibrate IO, Short Stroking, Stripe size, UEK kernel, and ASM redundancy effect on IOPS Exadata which you can read here
followed by interesting exchange of tweets with Kevin Closson here (see 06/21-22 tweets) which I was replying in between games at UnderwaterHockey US Nationals 2012 which we won the championship for the B division ;) I have my awesome photo with the medal here

This post will detail on the ASM redundancy/parity effect on IOPS… if… by changing the ASM redundancy (external, normal, and high) will it decrease the workload (more...)