Oracle 11g – Virtual Columns

That´s why i love Oracle,

It´s one amazing and simple new feature that will reduce applications errors.

Now you can create a compute column directly on table (on older versions you need to use views, but someone could by pass it accesing the main table), Now there is no excuses to don´t use calculated values since the column are already on table.

Identifying features in use on grid database

SELECT name, detected_usages DU, last_usage_date
FROM dba_feature_usage_statistics
WHERE currently_used = ‘TRUE’

Who is using which UNDO or TEMP segment?

http://www.orafaq.com/faq/who_is_using_which_undo_or_temp_segment

Preparing for the New Daylight Saving Time

from Rich Niemiec, TUSC

On August 8, 2005, President George W. Bush signed the Energy Policy Act of 2005. This Act changed the time change dates for Daylight Saving Time in the U.S. Beginning in 2007, DST will begin on the second Sunday in March and end the first Sunday in November. The Secretary of Energy will report the impact of this change to Congress.

This can impact your systems several different ways:

Operating System

UNIX, Linux, and Mac OS use the zoneinfo utility which allows a single time zone to have multiple daylight saving time rules to handle changes from year to year. The zoneinfo database is a collaborative compilation of information about the world’s time zones. New editions of the database are published as changes warrant, with the latest edition being the 2006g edition (2006-05-08) which lists 387 primary time zones and contains the new time changes.  This can be obtained from your O/S vendor.

Oracle Database

This issue will affect Oracle Server – Enterprise Edition – Versions 8.1 to 10.2. Depending on the version of your database, the impact will differ. 

In short, a database patch and a JVM may need to be applied. Once applied, a script provided by Oracle called utltzuv2.sql will need to be run. This will tell you if any code in the db needs to be modified.

Several notes exist on MetaLink regarding this change. Those notes are the following: 

• Note ID: 359145.1  Impact of 2007 of daylight saving changes on the Oracle Database
• Note ID: 402742.1 USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS
• Note ID: 357056.1 Impact of changes to daylight saving time ules on the Oracle Database
• Note ID: 397281.1 Oracle Database & Fusion Middleware products

Java

Java uses a similar database to UNIX, so rules for multiple years, and not just the current year can be represented. This database is integrated into the JRE and is separate from the underlying operating system’s time zone database, so the JRE must also be updated when DST rules change. 

The Oracle Java Virtual Machine (JVM) in the database has its own knowledge of time zones, which needs to be updated.  These fixes are included in the following patch sets:

• 9.2.0.8 
• 10.1.0.6 
• 10.2.0.3
  
• In Oracle 11 these fixes are part of the base release

Oracle Applications

Oracle says that the E-Business Suite doing the following may be impacted:

• Database running in an affected time zone 
• Users with the Client Timezone profile option set to affected time zones
• Product communications to and from another system running in an affected time zone
• Product features that have specific time zone functionality

In short, the following patches will need to be applied:

• FND patch 5619414
• Forms patch 5445721

Client

Before proceeding with patching the database make sure that you realize the impact this has on the client and middle-tier setup. You will need to patch any ‘client’ that connects to this database and uses the affected time zones as well. This includes “real” clients that connect to the database in client/server mode, but also middle-tiers like Oracle Application Server, which connect to the database. This is further documented in Note ID: 396426.1.

Effects on client and middle-tier of applying time zone patches on the Oracle Database

There is no need to run the utltzuv2.sql script on the clients before applying these files, as this script is only used to check the data in the database. The clients simply need to have the same version of the time zone files as the database they connect to (when data in the affected time frame is used).

Oracle Directories List

SELECT DISTINCT (SUBSTR(MEMBER,1,INSTR(MEMBER,’\’,-1))) FROM V$LOGFILE
UNION
SELECT DISTINCT (SUBSTR(NAME,1,INSTR(NAME,’\’,-1))) FROM V$CONTROLFILE
UNION
SELECT DISTINCT (SUBSTR(FILE_NAME,1,INSTR(FILE_NAME,’\’,-1))) FROM DBA_DATA_FILES
union
SELECT (SUBSTR(VALUE,1,INSTR(VALUE,’\’,-1)))  FROM V$PARAMETER WHERE NAME LIKE ‘%pfile%’
UNION
SELECT DISTINCT (SUBSTR(NAME,1,INSTR(NAME,’\’,-1))) FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL
UNION
SELECT DISTINCT DESTINATION FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL

UNION
SELECT DISTINCT (SUBSTR(FILE_NAME,1,INSTR(FILE_NAME,’\’,-1))) FROM DBA_TEMP_FILES;

Driving table

If your database is running on CBO than Oracle can join it in three ways:

1 – Nested Loop
2 – Sort Merge
3 – Hash Join

Driving table only apply to Nested Loop, 
If both tables have index on  join column, And both tables have a filter predicate,
Then Oracle will elect the table that will become smaller after filtering to be the drive table.

eg. 

 select t1.col1,t2.col2 from t1,t2 where t1.col3=t2.col3

where t1.col1=10 and t2.col2=20;

T1 have 10000 rows

T2 have 200000 rows

A lot of people made a mistake thinking that Oracle will elect the smaller one to be the driving table.

So let´s think about it for a moment, what difference between 10000*200000 and 200000*10000

That´s why it doesn´t work like this, 

In fact Oracle will check what table will become % smaller after filtering:

Eg. T1 will have 5,000 rows (50% of reduction)

But T2 will have 20,000 rows (90% of reduction)

So the question is , what is less effort?  5,000*200,000   or 10,000*20,000