Archive for May, 2011

Diagnosing performance bottlenecks using statspack and the oracle performance method

Categories: Oracle

What is the difference between a database character set and a national character set?

The character set is what is used for all normal datatypes such as VARCHAR2 and CLOB. National character set is used for NVARCHAR2 and NCLOB.

Before Oracle 10g, it was pretty uncommon to use Unicode (like AL32UTF8) as your default character set. Most people chose US7ASCII or WE8ISO. US7ASCII is a 7-bit character set, meaning it can utilize 2^7 (or 128) different character types. That’s just enough for the English language. WE8ISO is an 8-bit character set, so it can use 2^8 or 256 characters. This is enough to add umlauts and accents. Unicode can hold millions of characters, even multi-byte characters, so it allows for the storage of hebrew, chinese, russian, and other laguages that have complex character types.

In Oracle 10g, a lot more people are choosing a Unicode character set as their standard character set. Now the only question is if you want a variable multi-byte character set or a fixed-width multibyte character set. Fixed-width are faster, but they use multiple-bytes per character (for instance, a VARCAR2 might only be able to hold 2000 instead of 4000). Variable multi-byte can hold any byte-length, including single byte for English, thus preserving space; however, they do not perform as well.

Read up on it here:

Thanks to Steve for this explanation.

Categories: Oracle

Oracle 11g recreate EM dbconsole

May 12, 2011 2 comments

I’ve run into the same errors trying to drop and recreate EM dbconsole.  Following the below steps still fails:

emca -deconfig dbcontrol db -repos drop 
emca -config dbcontrol db -repos create 
These type of errors would still show up in emca error log:
RROR at line 1:
ORA-04063: package body "SYSMAN.MGMT_AUDIT_LOG" has errors
ORA-06508: PL/SQL: could not find program unit being called:

Here’s how to fix it:

SQL> grant execute on dbms_obfuscation_toolkit to public;

Grant succeeded.

SQL> grant execute on utl_file to public;

Grant succeeded.

Categories: Oracle

Oracle database reference for upgrading from any patchset to any patchset

Doc ID: 730365.1 Subject: Oracle Database Upgrade Path Reference List

Categories: Oracle

Oracle 11g block change tracking for fast incremental backups

May 2, 2011 1 comment

Enable/disable first:

The dynamic performance view V$BLOCK_CHANGE_TRACKING contains the name and size of the block change tracking file plus the status of change tracking:
SQL> select filename, status, bytes from v$block_change_tracking;

To see the benefits of block change tracking, you can use the following query on the dynamic performance view V$BACKUP_DATAFILE to calculate the percentage of blocks read and the number of blocks backed up during an incremental level 1 backup:

select file#, avg(datafile_blocks) blocks,
avg(blocks_read) blocks_read,
avg(blocks_read/datafile_blocks)*100 pct_read,
avg(blocks) blocks_backed_up
from v$backup_datafile
where used_change_tracking = ‘YES’
and incremental_level = 1
group by file#
order by file#

Categories: Oracle