Posted by Riyaj Shamsudeen on July 11, 2014
There was a question in . Problem is that there were many corrupt blocks in the system tablespace not belonging to any segment. Both DBV and rman throws errors, backup is filling the v$database_block_corruption with numerous rows. OP asked to see if these blocks can be reinitialized. Also, note 336133.1 is relevant to this issue on hand.
$ dbv file=/oracle/SID/system_1/system.data1
DBVERIFY: Release 10.2.0.5.0 - Production on Fri Jul 11 08:04:18 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/SID/system_1/system.data1
Block Checking: DBA = 67121421, Block Type = Save undo data block
ERROR: SAVE Undo Block Corrupted. Error Code = 50
kts4subck: record (3) seq# (0), split flag (0)
and total pieces(0)
REM Many such corruptions.
select * from v$database_block_corruption order by 1,2,3,4 ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
1 12557 1 1 UNKNOWN
1 12561 1 1 UNKNOWN
1 12589 1 1 UNKNOWN
1 12593 1 1 UNKNOWN
From the output above, blocks belonging to saved undo data blocks, which probably have undo records referring to other data files also. Since these blocks do not belong to any segment (note 336133.1 has a SQL statement to verify that), we should be able to force the allocation of these blocks to a table, load that table with numerous rows, which, in theory, should re-initialize the blocks. That should eliminate rman and dbv errors.
First, let me take a selfie
Read the rest of this entry »
Posted in corruption, Oracle database internals, recovery | Tagged: corruption, v$database_block_corruption | 1 Comment »
Posted by Riyaj Shamsudeen on July 2, 2014
Data visualization is a useful method to identify performance patterns. In most cases, I pull custom performance metrics from AWR repository and use tableau to visualize the data. Of course, you can do the visualization using excel spreadsheet too.
We had huge amount of PX qref waits in a database:
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
PX qref latch 63,669,198 3341 0 34.2 Other
DB CPU 3031 31.1
direct path read temp 92,996 199. 2 2.0 User I/O
direct path write temp 12,029 109. 9 1.1 User I/O
direct path read 5,454 61.1 11 .6 User I/O
Read the rest of this entry »
Posted in 11g, 12c, Oracle database internals, Performance tuning, weird stuff | Tagged: awr scripts, awr_system_events.sql, kernel bug, mpstat, oracle performance, PX qref latch, sched_debug, vmstat | 5 Comments »
Posted by Riyaj Shamsudeen on March 20, 2014
After collaborating with many performance engineers in a RAC database, I have come to realize that there are common pattern among the (mis)diagnosis. This blog about discussing those issues. I talked about this in Hotsos 2014 conference also.
Here are the golden rules of RAC performance diagnostics. These rules may not apply general RAC configuration issues though.
- Beware of top event tunnel vision
- Eliminate infrastructure as an issue
- Identify problem-inducing instance
- Review send-side metrics also
- Use histograms, not just averages
Looks like, this may be better read as a document. So, please use the pdf files of the presentation and a paper. Presentation slide #10 shows indepth coverage on gc buffer busy* wait events. I will try to blog about that slide later (hopefully).
Golden rules of RAC diagnostics paper
Golden rules of rac diagnostics ppt
Scripts mentioned in the presentation can be downloaded here.
Posted in 11g, Performance tuning, Presentations, RAC | Tagged: gc buffer busy, oracle performance, RAC performance, RAC performance diagnostics, RAC performance myths, RAC performance scripts | 3 Comments »
Posted by Riyaj Shamsudeen on February 28, 2014
I blogged about Dynamic Resource Mastering (DRM) in RAC here . DRM freezes the global resources during the reconfiguration event and no new resources can be allocated during the reconfiguration. This freeze has a dramatic effect of inducing huge amount of waits for gc buffer busy [acquire|release] events and other gcs drm freeze release, gcs remaster events. In database version 12c, DRM has been improved further.
A major improvement I see is that not all resources are frozen at any time. Essentially, resources are broken down in to partitions and only a resource partition is frozen. This improvement should decrease the impact of DRM related waits tremendously.
LMON Trace file
Following shows the snippet from the LMON trace file. As you see, only one resource partition is frozen, at-a-time. Resources in the first partition is frozen, completes the resource remastering tasks, and unfreezes that resource partition. Then freezes next resource partition and continues until all resources are remastered.
Read the rest of this entry »
Posted in 12c, Performance tuning, RAC | Tagged: DRM, DRM freeze, DRM RAC, gc buffer busy | Leave a Comment »
Posted by Riyaj Shamsudeen on February 25, 2014
I will be presenting in HOTSOS symposium 2014 discussing correct methods to diagnose RAC performance issues. Very surprisingly, even very senior performance engineers make mistakes in their analysis while reviewing RAC issues. Come to my presentation and learn the golden rules of RAC performance diagnostics.
Posted in 12c, Performance tuning, Presentations, RAC | Leave a Comment »
Posted by Riyaj Shamsudeen on November 12, 2013
It is easier to create one or two AWR reports quickly using OEM. But, what if you have to create AWR reports for many snapshots? For example, your Oracle support analyst wants you to supply 10 1-hour AWR reports from 10AM to 8PM in a 8 node cluster? That’s about 80 AWR reports to create! Okay, okay, I may(!) be overselling it, but you get the point. It is useful to have a script to create AWR report for all instances for a given range of snapshot IDs. Following scripts are handy:
|1. To create one AWR report per instance, for the last snap duration :
|2. Same as (1) but in html format :
|3. To create one AWR report per instance, for a range of snap IDs :
|4. To create one AWR report, per instance, per snap ID :
Zip file: awrrpt_scripts
These scripts do not modify anything in the database, just retrieves the data using dbms_workload_repository package. Test the scripts to understand further. Of course, you need access to dbms_workload_repository and access to gv$instance.
Posted in Oracle database internals, Performance tuning, RAC | Tagged: AWR reports, awrrpt.sql, awrrpt_all_gen.sql, awrrpt_all_range_gen.sql | 3 Comments »
Posted by Riyaj Shamsudeen on September 18, 2013
I will be hacking RAC internals with few LINUX tools in Oaktable world presentation series, in SFO. Details are available at Oaktable World 2013
Hope to see you there!
Posted in Oracle database internals, RAC | Tagged: RAC internals | Leave a Comment »
Posted by Riyaj Shamsudeen on September 9, 2013
I blogged about DFS lock handle contention in an earlier blog entry. SV resources in Global Resource Directory (GRD) is used to maintain the cached sequence values. I will further probe the internal mechanics involved in the cached sequences. I will also discuss minor changes in the resource names to support pluggable databases (version 12c).
Let’s create an ordered sequence in rs schema and then query values from the sequence few times.
create sequence rs.test_seq order cache 100;
select rs.test_seq.nextval from dual; -- repeated a few times.
Sequence values are permanently stored in the seq$ dictionary table. Cached sequence values are maintained in SV resources in GRD and SV resource names follows the naming convention to include object_id of the sequence. I will generate a string using a small helper script and we will use that resource name to search in the GRD.
SELECT DISTINCT '[0x'
|| '],[SV]' res
FROM dba_objects WHERE object_name=upper('&objname')
AND owner=upper('&owner') AND object_type LIKE 'SEQUENCE%'
Enter value for objname: TEST_SEQ
Enter value for owner: RS
Read the rest of this entry »
Posted in 12c, Oracle database internals, Performance tuning, RAC, weird stuff | Tagged: oracle performance, pluggable database, RAC internals, RAC performance, SV resource, weird stuff | 2 Comments »
Posted by Riyaj Shamsudeen on September 8, 2013
A quick note, Expert Oracle RAC book co-written by me is available now: Expert Oracle RAC 12c. I have written about 6 chapters covering the RAC internals that you may want to learn :) I even managed to discuss the network internals in deep, after all, network is one of the most important component of a RAC cluster.
Posted in 12c, Oracle database internals, Performance tuning, RAC | Tagged: oracle performance, performance, RAC internals, RAC performance | Leave a Comment »
Posted by Riyaj Shamsudeen on June 26, 2013
I reviewed Oracle Database 12c for the InfoWorld magazine.
You can read the article here: Oracle Database 12c Review
Posted in 12c | Tagged: 12c articles, 12c optimizer features, 12c RAC features, Oracle Database 12c, pluggable database | 2 Comments »