From 3Tb to 1.5Tb Using standard COMPRESS and PCTFREE

Add to Technorati Favorites

Ver este articulo en Español

I've been working on a migration project, with very aggressive dates and a couple of requirements that increase difficulty: switch from filesystem to ASM, using 50% of new storage... yes, you got the right picture, we don't have full set of disks for ASM to fit all data, therefore I need to move data trying to release complete datafiles in order to free those logical volumes or LUNs, and later add them to ASM.

Anyway, I realized that given this is a DSS or Datawarehouse, there is some margin to increase "data density" per block, (more...)

Oracle patchset 11.2.0.2 released (and docs updated)

Add to Technorati Favorites
Ver este articulo en Español

***Sep 13th, Patchset has been released***

Today Oracle released latest patchset for Database Server 11g2: 11.2.0.2 which packs some very interesting improvements and new features; for now, release covers Linux x86 and x86_64, with download measuring near 5Gb(!) .

Also updated all documents for release 11.2, in order to reflect improvements introduced by 11.2.0.2. You can see on following picture a New Features addition
From now on you will see full distribution instead of a patchset (MyOracle Support Note 1189783.1 ), that's why seven ZIP (more...)

Oracle Exadata/Database Machine… nice to meet you

Add to Technorati Favorites

Ver este articulo en Español

During the last four or five weeks I've had the rare privilege of working with this amazing piece of engineering, the Oracle Database Machine.

Engaged during deployment of it, I've faced the setup steps on the SW side, applied upgrade patches & side-to-side with Oracle Sun engineers bringing this marvel to life... it's just exciting, watching all the slightly-bluish-green leds showing HD activity or admiring the most precise cabling I've ever seen... there is just one word: precision. I remember my first experience with an HP XP12000 SAN, five years ago, being not that exciting (more...)

Oracle RAC One Node – What is it?


I am reviving my blog after a long time. Hope I will be much regular. Some of my posts have been popular and received good feedback. Thanks to all of you who visited this blog and I am glad that the information has been useful.

I was looking for some information on Oracle RAC One node, early this year and did not find much. I thought to do a post on my views/opinion on this latest option introduced by Oracle. I am yet to get hands-on with this option. Please leave your comments/feedback if you have different opinion/experience with this (more...)

Oracle 11g Streams Implementer’s Guide – Highlights – Part I

Add to Technorati Favorites

Ver este articulo en Español

I finished reading this very nice book on Oracle Streams... yes, the one I shared you weeks ago (See post My first assignment on book reviews). Now as promised, getting back to you and share my comments on the book, starting with those chapters that clearly make the difference against other Oracle Streams books on the market, those which are more like "rephrasing" the manual.

Chapter 2 and Chapter 3 provide the foundation for success on implementing Streams, this is the Analysis and Design phase that is often overlooked ... but pays high rewards to (more...)

My first assignment on book reviews

Add to Technorati Favorites

Ver este articulo en Español

During the past week, Packt Publishing gave me a copy of his newest "Oracle 11g Streams Implementer's Guide" book, which you can find clicking the book cover image below.

There you may even get a free sample chapter (Ch 5 on N-Way Replication), for free!!!.

So, on days to come you may see here postings related to this endeavor... stay tuned!

See comments on the book, follow link to post Oracle 11g Streams Implementer's Guide - Highlights - Part I

Subscribe to Oracle Database Disected by Email
Delicious
Bookmark this on Delicious

Latest storage Adventure

Add to Technorati Favorites

Ver este articulo en Español

Datamarts/Datawarehouse performance issues can be scaring events, taking into account the massive amount of data they handle; the tuning task is even more daunting if they're used for operational day-to-day decision making.

During the last week and days, I've been involved on a 15 Tb datamart with performance issues on dataloading, not querying. Tablespaces for indexes and data share filesystems, nowadays this is not a concern anymore, however I prefer to encourage customers to allocate independent storage for each type, that way they are able to discriminate the source of IO.

They have concurrent schedules (more...)

Index Dynamics – Part 3

Add to Technorati Favorites

Ver este articulo en Español

See previous post Index Dynamics - Part II or first part Index Dynamics - Part I

On this part, I’m going to share with you the final results for my 2 week experiment with indexes on their habitat, a production environment.

I’ve to forewarn that some data was not available, as the ANALYZE method fails for objects currently locked, given that our environment is a “live” production database and for consistency we scheduled all tests for a given time, which some days collided with business process. The Used Space graph shows those NA data points; (more...)

Index Dynamics – Part 2 (Halloween on the Block)

Add to Technorati Favorites

Ver este artículo en Español

View starting post: Index Dynamics - Part 1

As I've promised, today will share with you mid-term results for my index observations.

First we may see a graph of Used Space, as reported by column PCT_USED of table INDEX_STATS (right after an ANALYZE over each index). This percentage accounts the space allocated to the B-Tree that is used.

There is one line for every index we are considering, and note the legend on the graph showing the index name and the number of columns inside parenthesis.

What can be observed in this chart?
1) After (more...)

How to Flush your database caches

Add to Technorati Favorites

Ver este articulo en Español

Flushing the SGA memory areas, Shared Pool and Buffer Cache, it's an uncommon task, however, it gets useful when you're doing some tests and want to override the memory and go direct to disk, or when you have shared pool issues (here is a workaround, however I encourage you to find the root cause).

Shared Pool flush
This is the only sentence you have to know for releases 9i and up...
alter system flush shared_pool

Buffer Cache flush
For Oracle 9i I didn't know how to do this, fortunately found it today on Rahat Agivetova's (more...)

Index dynamics

Add to Technorati Favorites

Sigue el link
Ver este articulo en Español



Today I will start with a series of 3 posts depicting the follow up of 4 index behavior on a Production database.

The purpose of this exercise is try to unveil the morphology these four indexes take during a given period of time, under what kind of load, model it in a graphical way. Since indexes are quite a black box, this exercises also proposes a complementary procedure for index quality measure.

Experiment subjects were selected using information on DBA_SEGMENTS+DBA_HIST_SEGMENTS, with focus on size and activity, indexes were choosen within the 400Mb-500Mb range thinking on (more...)

CURSOR_SPACE_FOR_TIME to be deprecated in future releases

According to a recently published metalink note (565424.1) the parameter "CURSOR_SPACE_FOR_TIME" is going to be deprecated starting release 11.1.0.7 onwards. In fact Oracle's next patch set release for 10g - 10.2.0.5 will also de-support this parameter. We will have to remove this parameter whenever these 2 patch sets - 10.2.0.5 for 10g and 11.1.0.7 for 11g are released and we decided to migrate. CURSOR_SPACE_FOR_TIME was introduced to reduce latch contention; This parameter may no longer be required with introduction of cursor mutexes hence Oracle has decided to deprecate this in future releases.

Oracle 11g New Features – PL/SQL enhancements Part-II

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-
CREATE OR REPLACE FUNCTION cal_employment_duration (
empid (more...)

Oracle 11g New Features – PL/SQL enhancements Part-I

I was going through the enhancements made for PL/SQL in Oracle 11g and learn that there are quite a few. I am going to post on these new features in two parts. Here goes the first part.

1. Usage of sequences in PL/SQL expressions

Allows you to assign a sequence value in an expression that is, you do not need to use a SQL query to generate sequence value and assign it to variable. Here is an example :-

SELECT seq_name.nextval INTO variable FROM dual; –> this was how we used to generate sequence values inside PL/SQL

From 11g (more...)

11g RAC Database hanged or appears to hanged

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog

SQL> set _prelim on
SQL> connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> (more...)