Gartner 2006 Worldwide RDBMS Market Share Reports 47.1% Share for Oracle

Worldwide RDBMS total software revenue totaled $15.2 billion in 2006, a 14.2 percent increase from 2005 revenue of $13.3 billion, according to Gartner, Inc

The overall RDBMS market continued to be dominated by the top-tier vendors Oracle, IBM, and Microsoft which accounted for 85.6 percent of worldwide RDBMS revenue. Out of which Oracle alone contributes 47.1 percent.

To learn more, read the press release from Gartner.

Oracle 11g

On July 11, 2007, in a live launch event from New York City, Oracle unveiled Oracle Database 11g—the next generation of enterprise information management that will help you innovate faster through better business insight.

For more information please visit http://www.oracle.com/technology/products/database/oracle11g/index.html

Oracle 10g Scheduler Overview

Automation is key process to reduce IT operating expense. An effective scheduler should have features that can automate business process with manual intervention and be easily mange/monitor the scheduled jobs. Scheduler was introduced in Oracle 10g and has exciting features. Few are listed below.

• Build into database. No extra license fee.
  
• DBA has Control over when and what to execute.
• Platform independent (Management is similar across platform).
• Can be exported using IMP/EXP utility.
• Can execute Shell scripts, PL/SQL block, PL/SQL or Java stored procedures.
• It can be accessed/controlled from enterprise manager (EM) or through DBMS_SCHEDULER API.
• Can schedule the job based on time and also on event.
  

For more information please visit
http://www.oracle.com/technology/products/database/scheduler/index.html

SELECT … FOR UPDATE Enhancement

Selecting a record for update that is already locked causes the current session to hang indefinitely until the lock is released. If this situation is unacceptable the NOWAIT keyword can be used to instantly return an error if the record is locked. Oracle 9i, feature allows us to specify maximum time before it shoots error message. This also reduces the change of indefinite locking.

Example:
Select * from emp where eno=1235 for update wait 15;

Enqueue

Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.

Following are the Classes of enqueue event
BL, Buffer Cache Management
CF, Controlfile Transaction
CI, Cross-instance Call Invocation
CU, Bind Enqueue
DF, Datafile
DL, Direct Loader Index Creation
DM, Database Mount
DR, Distributed Recovery
DX, Distributed TX
FS, File Set
IN, Instance Number
IR, Instance Recovery
IS, Instance State
IV, Library Cache Invalidation
JQ, Job Queue
KK, Redo Log "Kick"
L[A-P], Library Cache Lock
MR, Media Recovery
N[A-Z], Library Cache Pin
PF, Password File
PI, Parallel Slaves
PR, Process Startup
PS, Parallel Slave Synchronization
Q[A-Z], Row Cache
RT, Redo Thread
SC, System Commit Number
SM, SMON
SQ, Sequence Number Enqueue
SR, Synchronized Replication
SS, Sort Segment
ST, Space Management Transaction
SV, Sequence Number Value
TA, Transaction Recovery
TM, DML Enqueue
TS, Temporary Segment (also TableSpace)
TT, Temporary Table
TX, Transaction
UL, User-defined Locks
UN, User Name
US, Undo Segment, Serialization
WL, Being Written Redo Log
XA, Instance Attribute Lock
XI, Instance Registration Lock

SESSION_CACHED_CURSORS

In a typical OLTP environment, most of the SQLs will be repeatedly fired. If SESSION_CACHED_CURSORS are set to 0 and even if SQLs are found in library cache, soft parse will happen, which requires Library cache latch and few CPU cycles. This can aggregate to huge number of CPU cycles as time goes on.
Setting value greater than 0 to SESSION_CACHED_CURSORS will cache the queries which are executed three times or more. This helps oracle in doing “softer” soft parse which is less resource intensive then soft parse (in terms of Latch and CPU cycles).
Following query helps to find out session cursor cache count and number of times cached cursor was utilized.
select name, value from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name in ('session cursor cache hits', 'session cursor cache count') and sid =

Note before changing this parameter:

• Setting SESSION_CACHED_CURSORS may mislead the data in view V$OPEN_CURSOR.
• Be sure SHARED_POOL_SIZE is set appropriately.

Oracle Database 11g New Features

Oracle announced the lots of new features which could be part of new release of Oracle 11g Database and I have listed few key features are as follows.

• Query result-set caching as opposite to Data block
• Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library.
• Server side connection pooling (multiple Oracle clients to share a server-side pool of sessions, but USERIDs should match)
• Faster optimizer stats creation
• Interval partitioning, a partitioning scheme that automatically creates time-based partitions as new data is added.
• Interval partitioning, a partitioning scheme that automatically creates time-based partitions as new data is added.
• Virtual columns, columns that are actually functions and similarly, virtual indexes that are based on functions
• REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.
• New Oracle11g Advisors - New 11g Oracle Streams Performance Advisor and Partitioning Advisor.
• Faster DML triggers
• Capture/replay database workloads – We can capture the workload in prod and apply it in development
• Enhanced Read only tables
• Table trigger firing order - Oracle 11g PL/SQL will you to specify trigger firing order.
• New table Data Type "simple_integer"
  

I apologize for any typos or spelling errors in my blogs.

-thiru

Oracle DBA Toolbar

Oracle has introduced Oracle Toolbar which helps to access oracle related resource at single click. Visit http://www.oracle.com/technology/toolbar/install/index.html to know more.

Multi table INSERT in Oracle

I have given an example which demonstrates the Oracle’s strength to insert data into multiple tables using single SQL statement

SQL> create table temp1(a number(2),b varchar2(5));

Table created.

SQL> create table temp2(a number(2),b varchar2(5));

Table created.

SQL> insert all
when rownumt <10>=10 then into temp2 values(rownumt, String)
SELECT ROWNUM rownumt,DBMS_RANDOM.STRING('A',5) String FROM DUAL CONNECT BY LEVEL <=20;

20 rows created.

SQL> select * from temp1;

A B
---------- -----
1 yKXDU
2 iQPtH
3 kbBcy
4 lmUoZ
5 JiLBi
6 udQEl
7 HVDzE
8 ULixZ
9 gNUSO

9 rows selected.

SQL> select * from temp2;

A B
---------- -----
10 RYyLv
11 LnkFo
12 gEBUZ
13 TYlxG
14 pvMKv
15 KSiIr
16 Xzkzt
17 ppEpq
18 wCIAY
19 tfGiX
20 nCPeZ

11 rows selected.

SQL>