Archive for the ‘ Oracle SQL ’ Category

Oracle Cache fusion, private inter connects and practical performance management considerations in Oracle RAC

Guenadi N Jilevski's Oracle BLOG

Oracle Cache fusion, private inter connects and practical performance management considerations in Oracle RAC

In the article you will have a look at the cache fusion from the perspective of the Practical Performance Management for Oracle RAC. Cache fusion is based and heavily depends on Private Interconnect. The article will emphasize on the Interconnects impact, often overlooked and underestimated, on the RAC performance. Tuning RAC is similar to tuning of a regular single instance database and in addition takes into account the overhead of the interconnects existing in Oracle RAC (10gR1/10gR2/11gR1/11gR2). Some well known cases will be addressed in RAC to improve performance.

In this article you will review the Oracle fundamentals and infrastructure architecture and you will look at the Cache Fusion impact on the RAC performance. The outlined in the article guidelines has two objectives:

  • Maximize the utilization of the software and hardware for Private Interconnect. Make sure that you fully utilize the maximum of your…

View original post 7,328 more words

In-memory DB

Oracle Scratchpad

A recent thread on the OTN database forum supplied some code that seemed to show that In-memory DB made no difference to performance when compared with the traditional row-store mechanism and asked why not.  (It looked as if the answer was that almost all the time for the tests was spent returning the 3M row result set to the SQL*Plus client 15 rows at a time.)

The responses on the thread led to the question:  Why would the in-memory (column-store) database be faster than simply having the (row-store) data fully cached in the buffer cache ?

Maria Colgan has addressed this question in part 3 of her series on In-Memory Database (see catalogue below), but I thought I’d repeat the basic ideas with a few imaginative numbers thrown in to give a better flavour of what’s going on. So imagine you have a table sized at 100GB, with 100 columns of data…

View original post 992 more words

Brief introduction to ASM mirroring

Uwe Hesse

Automatic Storage Management (ASM) is becoming the standard for good reasons. Still, the way it mirrors remains a mystery for many customers I encounter, so I decided to cover it briefly here.

ASM Basics: What does normal redundancy mean at all?

ASM normal redundancy

It means that every stripe is mirrored once. There is a primary on one disk and a mirror on another disk. All stripes are spread across all disks. High redundancy would mean that every primary stripe has two mirrors, each on another disk. Obviously, the mirroring reduces the usable capacity: It’s one half of the raw capacity for normal redundancy and one third for high redundancy. The normal redundancy as on the picture safeguards against the loss of any one disk.

ASM Basics: Spare capacity

ASM spare capacity usage

When disks are lost, ASM tries to re-establish redundancy again. Instead of using spare disks, it uses spare capacity. If enough free space…

View original post 273 more words

Automatic Block Media Recovery in Action on Video

Uwe Hesse

The little video below shows the 11gR2 New Feature Automatic Block Media Recovery in Action. I have already introduced the feature in this post, but some things are just more impressive when you actually see it happening, don’t you agree?

View original post

Schema Name and Size using SQL

You can use this query to retrieve Schema Name and Size from Oracle Database.

owner SchemaName, 
round(sum(bytes)/1024/1024/1024,2) TotalSizeGB 
from dba_segments 
group by owner 
order by 2 desc 

A Rahim Khan