IMU and Private strands are interesting new entries in Oracle 10g. These to private buffer to a transaction will help to avoid many expensive latches both in buffer cache and log buffer. There are tons of data available in the net about these subjects and many experts spoke (more...)
ABMR is a cool feature; most of us know about it, so instead of talking about ABMR, want to share the issues I faced.
First one was, Oracle did not bother to start the abmr background process and attempt block recovery. Alert log exclaimed about the block corruption and (more...)
session_cached_cursors is one of the neglected parameter in oracle environment which specifies the number cursor cached in the PGA of a session. Any SQL executed multiple times will be cached and executed with a softer soft parsing. There can be 3 scenarios for any parse request.
SQL parsing (more...)
This is not the first time I was going to Swanthana (as part of St. Vincent De Paul Society, reach out to the poor) - a home for mentally challenged and disabled girls abandoned by their families. Each time I went there, I forgot problems in my life and (more...)
Got a question: What will happen a running curosr got invalidated? SQL execution will fail or invalidation is not at all possible?
There are two components for an executing cursor. A session cached cursor (private area where row source are getting processed) in PGA and sharable execution plan (shared excution (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...)
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; commit; exit $ more 4.sql update abc set owner='ABC' where mod(object_id,10)=4; commit; exit $ more 3.sql update abc set owner='ABC' where mod(object_id,10)=3; commit; exit $ more 2.sql update abc set (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 ; Table created. SQL> alter table abc_unorg pctfree 1 ; Table altered. SQL> create (more...)
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...)