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...)
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...)
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
Oracle (since version 11gR1) exposes (more...)
Note: This post is about diagnosing real life scenario caused by direct path decision change on 220.127.116.11 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...)
I just got permission from The UK Oracle Users Group to reproduce my article series on optimising scans in Oracle. Part One is available here, Part Two will follow shortly after, and then Part Three will be a few weeks away, following its publication in the magazine. Enjoy!
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...)
In past two posts ( from current schema and as SYS user ) , I have explained how we can restore a package. Some one posed me a question – how can I identify who dropped the package. Even though we are seeing a procedure or package as database object, it is stored as a table rows in the SOURCE$ table just like other tables in the database. So, any CREATE or DROP procedure / package are just INSERT or DELETE or UPDATE operations to the SOURCE$ table. We can use this along with FLASHBACK feature to identify who dropped (more...)
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...)
Ordering data in a table is important to avoid bottlenecks. In the previous post I had explained how to avoid ITL waits by ordering the data. This demo will shows how we can remove “buffer busy waits” by ordering the data. I ran the below 5 similar sql scripts on a table ABC concurrently.
$ more 5.sql
update abc set owner='ABC' where mod(object_id,10)=5;
$ more 4.sql
update abc set owner='ABC' where mod(object_id,10)=4;
$ more 3.sql
update abc set owner='ABC' where mod(object_id,10)=3;
$ more 2.sql
update abc set (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...)
As I said in the previous post, the straight forward method to resolve ITL wait is to increase the ITL slots while creating the table or modifying an existing table. As this is a standard way, I am not going explain this method. But, sometimes the data distribution in a table can contribute to the ITL waits. From my test table I will create two tables using the same data.
SQL> create table abc_unorg as select * from abc where 1 = 2 ;
SQL> alter table abc_unorg pctfree 1 ;
SQL> create (more...)
Or what happens when you don’t have node affinity.
Here’s an old, short post that was lying around unpublished.
SQL executed on the 14th on node 2, on 15th on node 1.
||Cluster Wait Time
Timings in seconds.
32625 seconds is just over 9 hours, of which 32175 is cluster wait time.
Database services and application datasource config have now been reconfigured to the extent that there is node affinity and the data required for the workload on node 1 is completely segregated (more...)
I recently investigated an IO performance “spike” on a large 18.104.22.168 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
I've been meaning to write this blog post for a while now but never seemed to find the time. Hopefully this posting will be useful information for anyone that spends a lot of time tracing processes in Oracle from the Linux Operating System. I'm one of those people, so it was good to get to [...]
ITL waits are so common and sometimes it will kill the concurrency of the application. In general there are 1 ITL slot (caused by INITRANS) for tables and 2 slots for indexes. When a data block is formatted the ITL slot is created in the variable part of the block header as specified by INITRANS for that segment. As long as free space is available in the block, ITL slots can be grown up to MAXTRANS for any future requirements. Here plays the important role PCTFREE which can impact negatively. With the default setting 10% (more...)