Nov 19, 2010

Drop foreign key from a Versioned Table

To drop a foreign key constraint from a version enable table .

EXECUTE DBMS_WM.BeginDDL('PARENT_TABLE');
EXECUTE DBMS_WM.BeginDDL('CHILD_TABLE');
ALTER TABLE CHILD_TABLE_LTS DROP CONSTRAINT FOREIGN_KEY_NAME
EXECUTE DBMS_WM.CommitDDL('CHILD_TABLE');
EXECUTE DBMS_WM.CommitDDL('PARENT_TABLE');


The foreign key constraints and its details of version enabled tables can be obtained from the view USER_WM_RIC_INFO or from
table wmsys.wm$ric_table rt

--------------------
Eg :
EXEC DBMS_WM.BEGINDDL('BILL');
EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
ALTER TABLE BILL_DETAILS_LTS DROP CONSTRAINT FK_BILL_DETAILS_CTS ;
EXEC DBMS_WM.COMMITDDL('BILL');
EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

Nov 12, 2010

Oracle GLOBALIZATION SUPPORT

GLOBALIZATION SUPPORT


Choosing a Character Set
---------------------------------
When computer systems process characters, they use numeric codes instead of the graphical representation of the character. For example, when the database stores the letter A, it actually stores a numeric code that is interpreted by software as that letter. Read on to know more about Globalization Support.


ForMoreDetails


NLS Data Dictionary Views
----------------------------
Applications can check the session, instance, and database NLS parameters by
querying the following data dictionary views:

NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the
session that is querying the view. It does not show information about the character
set.

NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that
have been explicitly set and the values of the NLS instance parameters.

NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the
database. The values are stored in the database.
--------------------------------------------------------
NLS Dynamic Performance Views

V$NLS_VALID_VALUES lists values for the following NLS parameters
NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSET

V$NLS_PARAMETERS shows current values of the following NLS parameters:
NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT,
NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_
CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_NCHAR_CHARACTERSET, NLS_COMP, NLS_LENGTH_SEMANTICS, NLS_NCHAR_CONV_EXP, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT

--------

Nov 8, 2010

Oracle Golden Gate

           For Real-time data integration.High performing enterprises need real-time data access across heterogeneous systems for improved business intelligence.Enables zero-downtime migration and upgrades for mission critical Oracle Applications.Supports the vast majority of databases and operating systems.

Continuous Availability
-----------------------

• Faster recovery
• Minimal data loss

Fast, Accurate Decision
----------------------------

• Real-time
• Operational business intelligence

Value Across Multiple Solutions
-------------------------------
• Rapid deployment
• Single technology highly flexible



-------

Oct 29, 2010

To get only numeric values

To  retrieve only numeric values from both numeric& character value

In 10 g

Select  regexp_replace(regexp_replace('5RTYDDB','([[:punct:]])'),'([[:alpha:]])')
  from dual t 
 
In 9i  
 
 select replace('BBd3',TRANSLATE('BBd3', ' +-.0123456789', ' '),'') from dual

------------

Oct 19, 2010

DBMS_WM.CompressWorkspace

DBMS_WM.Compress Workspace

 If   you version enabled the tables with  DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')   API  THEN your  Oracle Workspace  database WILL  GET SLOW  WHEN SO MANY DATA COMES ; use DBMS_WM.CompressWorkspace API  TO MAKE IT FASTER; There is a scenario to check  whether your Workspace  is getting slow or Workspace database is to be Compressed or not ,execute this  workspace API
DBMS_WM.SetDiffVersions('LIVE',wksp2) . and  then  pick a version enabled table having highest record count   , issue command select count(*) from tablename_diff ; if it takes more than a minute to GET RESULT THEN YOU CAN MAKE SURE THAT YOU NEED TO COMPRESS YOUR WORKSPACE ;here u want to use the   statement  (1) , using statement (2) will not give better result because your table is version enabled  with DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')  .

(1) exec DBMS_WM.CompressWorkspace('LIVE',compress_view_wo_overwrite => true);

(2) exec DBMS_WM.CompressWorkspace('LIVE')



VIEW_WO_OVERWRITE: The without overwrite (WO_OVERWRITE) option. A view named _HIST  is created to contain history information, and it will show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

compress_view_wo_overwrite        A Boolean value (TRUE or FALSE).

TRUE causes history information between the affected savepoints to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled.

FALSE causes history information (between the affected savepoints) for a table not to be deleted if VIEW_WO_OVERWRITE was specified when versioning was enabled. (If VIEW_WO_OVERWRITE was not specified for a table, history information for the table is deleted regardless of the parameter value.) FALSE is assumed if the procedure format without this parameter is used.


Advantage
------------
1  Run-time performance for Workspace Manager operations is improved.
2  Less disk storage is used for Workspace Manager structures

Oct 13, 2010

Use of Combined Index

The Below example shows the difference in execution plan when a composite index is created  

SELECT d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d START WITH ITEM_REF_ID is null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL



create index idx_ITEM_MASTER_refid  on ITEM_MASTER (ITEM_ID,ITEM_REF_ID)

After creating combined index check the execution plan




SELECT  d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d
START WITH ITEM_REF_ID is  null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL



Sep 30, 2010

ADD FOREIGN KEY TO VERSIONED TABLE

   ORACLE WORKSPACE MANAGER  TOPIC

    If there is  a version enabled table 'BILL' and
  
 /*  CREATE TABLE BILL (N_BILLID NUMBER(10), CONSTRAINT PK_BILL PRIMARY KEY (N_BILLID));
   EXEC DBMS_WM.ENABLEVERSIONING('BILL') ; */
   
 now i created a new table BILL_DETAILS which must be a child table of BILL
 
 /* CREATE TABLE BILL_DETAILS ( N_BILLDTID NUMBER(10),CONSTRAINT PK_BILLDETAILS PRIMARY KEY (N_BILLDTID) ,   N_BILLID NUMBER(10) , S_DESC VARCHAR2 (100), D_SYSDATE  DATE   DEFAULT SYSDATE ) ; */
  
 here you are not able to add a foreign key between one versioned and one non-versioned table.if it is done  oracle error will be the result


 ORA-20200: UNSUPPORTED CONSTRAINT
   
for that you would need to execute dbms_wm.enableversioning on the non-versioned table (you can add a foreign key between 2 version enabled tables) if you don't  want the detail to be kept as version enabled table then you can disable versioning of the detail table.

    EXEC DBMS_WM.ENABLEVERSIONING('BILL_DETAILS') 
  
    EXEC DBMS_WM.BEGINDDL('BILL');
  
    EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
  
   ALTER TABLE    BILL_DETAILS_LTS ADD CONSTRAINT FK_BILLDET_BILL FOREIGN KEY (N_BILLID)  REFERENCES BILL_LTS(N_BILLID);
  
   EXEC DBMS_WM.COMMITDDL('BILL');
  
   EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

   EXEC DBMS_WM.DISABLEVERSIONING('BILL_DETAILS')   ;

Sep 15, 2010

Oracle Spatial hints

/*+NO_QUERY_TRANSFORMATION*/

The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion,view merging, subquery unnesting, star transformation and materialized view rewrite.use the "NO_QUERY_TRANSFORMATION" hint to disable most of the transformations , although some transformations still seem to take place, e.g. IN seems to be always transformed into EXISTS .

This hint which improve performance to a great extent for my Spatial query.

SELECT /*+ NO_QUERY_TRANSFORMATION*/ * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW') = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID 


Execution Time : 47msec


SELECT * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW'
) = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID


Execution Time : 1:09 min

....

if you execute this spatial query without this hint it take around 3 minutes to execute ! amazed

If you want dumps you can mail us :oracleplsql10g@gmail.com


The cost of a query is not proportional to the time execution of a query for example If cost of a query (A) is greater than cost of another query (B) then its not always query (A) takes more execution time than query (B) .

............

Try this simple query too

SELECT /*+ NO_QUERY_TRANSFORMATION */
shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')


SELECT shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')

----------------------
These queries may get executed when no query transformation hint is not given for in some case

SELECT diminfo   FROM all_sdo_geom_metadata  WHERE owner = :own AND table_name = :tab AND column_name = :col

SELECT sdo_relation   FROM sdo_relatemask_table  WHERE sdo_mask = :b1

So evaluate your query and the data in the tables and check execution plan in both scenario and its performance and decide .

Aug 30, 2010

Advantage of analytic Function

This example shows you that  Usage of  Analytic function will make query good in performance
 
select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid and obj# in (51606,52492,52602) ;
 
explain plan set statement_id ='r' into plan_table for select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid  and obj# in (51606,52492,52602) ;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 
 
Total Cost=718
 
select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx,obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 

 

 
explain plan set statement_id ='r' into plan_table for select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx, obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 
 
Total Cost is 29
 
.`. Usage of second sql script is advisable ; where oracle optimizer works default on cost based (ie:CBO)
-----------------------------

Jul 6, 2010

Interview Questions

 1) What is the difference between an INLINE VIEW And SQL WITH CLAUSE ?

  The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

A subquery in the FROM clause of a SELECT statement is also called an inline view
Inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.


2) To view last executed statement in your session

       select * from table(dbms_xplan.display_cursor);



3)  What is the difference between execute and call statements ?

The main difference between EXECUTE and CALL command is that EXECUTE is a SQL*Plus command
whereas CALL is a SQL command AND understands SQL data types only.
In CALL STATEMENT using  PL/SQL datatypes (ie: PLSQL tables)  will result an error.

4) Difference between Varchar2(Bytes) & Varchar2(Char)  ?

 If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store , e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes  


5) Difference between Int & Number   ?
An integer is a "whole number". (Not just a display feature.) When you insert a number that has decimal places into an integer field, oracle is performing an implicition conversion from a number to an integer. (which removes the decimal places.) Any additional decimal places will be permanently lost

Jun 14, 2010

Pragma RESTRICT_REFERENCES

The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or
DETERMINISTIC  hints are used. The same rules that apply to the function itself also apply to any functions or procedures that  it calls.If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is
parsed).To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.

Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.WNDS,RNDS,WNPS,RNPS.



WNDS - Write No Database state


RNDS - Read No Database state


WNPS - Write No Package state


RNPS - Read No Package state


TRUST - the function body is not checked for violations of the constraints listed in the pragma

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.You can specify up to four
constraints (RNDS, RNPS, WNDS, WNPS,TRUST) in any order. To call a function from parallel queries, you must specify all four
constraints. No constraint implies another.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function
is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object
type spec (including the system-defined constructor for object types). You can still declare the pragma for individual
subprograms, overriding the default pragma.A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded
subprograms always applies to the most recent subprogram declaration.
Typically, you only specify this pragma for functions. If a function calls procedures, then you need to specify the pragma for
those procedures as well.

Examples

This example asserts that the function BALANCE writes no database state (WNDS) and reads no package state (RNPS). That is, it does not issue any DDL or DML statements, and does not refer to any package variables, and neither do any procedures or functions that it calls. It might issue queries or assign values to package variables.

CREATE PACKAGE loans AS
   FUNCTION balance(account NUMBER) RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES (balance, WNDS, RNPS);
END loans;

Jun 9, 2010

ORACLE NetworkTopology

    The Oracle network architecture encompasses many components - all of which neatly corresponds to the OSI networking model (see below Figure oraclenw1.jpg). This architecture enables Oracle client and server applications to transparently communicate over protocols such as TCP/IP. The session protocol that interfaces between the applications (Oracle Call Interface, or OCI, on the client and Oracle Program Interface, or OPI, on the server) and the network layer is known as Net8 (Net9), and before that SQL*Net. Between the OCI/OPI and Net8 layer is a presentation protocol called Two-Task Common (TTC) that is responsible for character set and data type conversion differences between the client and the server. The Net8 session protocol has three components - the Net Foundation and Routing/Naming/Auth and TNS - the last two making up Protocol Support. Supported transport protocols include TCP/IP, with or without TCP, Named Pipes and Sockets Direct Protocol (SDP), which enables communication over Infiband high-speed networks. Underpinning all of this is the Transparent Network Substrate protocol, also known as TNS. The task of TNS is to select the Oracle Protocol Adapter, wrapping the communication in one of the supported transport protocols.
        

           Main data dictionary view of network model is USER_SDO_NETWORK_METADATA which is in the user MDSYS", where "MD" stands for "Multi Dimensional" .

There are two  types of Network

1) LOGICAL
2) SPATIAL

 Among Spatial Netwrok there are 3 sub classification
  
  a) SDO Geometry
  b) LRS Geometry
  c) Topology Geometry
 
 LOGICAL
 --------

A network data model for representing capabilities of objects (modeled as nodes and links) in a network.


The network data model provides PL/SQL procedures (package SDO_NET) to simplify network creation and management.
The default values for network table names, column names, and metadata are as follows
Node table name: _NODE$
Link table name: _LINK$
Path table name: _PATH$ (only if the network contains paths)
Path Link table name: _PLINK$ (only if a path table is created; contains a row for
each link in each path in the network)


 Step 1:

    EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST'-- network name
  , 1 -- no of hierarchy level
  , FALSE  -- directed link?
  , FALSE  -- no with cost?
  );
 
 Step 2:

  Populate the node and link tables.
 
Step 3:

  Validate the network. select  SDO_NET.VALIDATE_NETWORK('NET_TEST') from dual ; 



During network creation if we  set cost parameter as TRUE ie :(  EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST', 1, FALSE , TRUE);  )
 Then in  _NODE$ table COST named  field will be created , and if  directlink parameter is set as TRUE then in _LINK$ table Biridected named field will be created .

----------



Apr 23, 2010

Unindexed Foreign Keys

 Less Overhead on Unindexed Foreign Keys

   A table level share lock is placed on unindexed foreign keys in a child table when doing an update or delete on primary key column in the referenced parent table. the lock is realesed immediate after obtaining it. If multiple pk key are  updated or deleted  , the lock is obtained and released once per row .The obtaining and realesing of the shared lock are as follow as

  1) Get a save point .
  2) Obtain a share lock .
  3) Roll back to save point .


  The  share lock has only one purpose , to check whether you have pending transactions against any rows . If that case , the share lock request would fail becuse of exclusive row locks.

Prior to Oracle 9i

Apr 5, 2010

RAID 2,RAID 3,RAID 4

RAID 2

Data is distributed in extremely small increments across all disks and adds one or more disks that contain a Hamming code for redundancy.This is not considered commercially viable due to the added disk requirements (10 to 20 percent must be added to allow for the Hamming disk.
The use of the Hamming(7,4) code (four data bits plus three parity bits) permits using 7 disks in RAID 2, with 4 being used for data storage and 3 being used for error correction.



RAID 2 is the only standard RAID level, other than some implementations of RAID 6, which can automatically recover accurate data from single-bit corruption in data. Other RAID levels can detect single-bit corruption in data, or can sometimes reconstruct missing data, but cannot reliably resolve contradictions between parity bits and data bits without human intervention.


--------------------------------
Characteristics & Advantages

"On the fly" data error correction
Extremely high data transfer rates possible
The higher the data transfer rate required, the better the ratio of data disks to ECC disks

-------------------------
Disadvantages

Very high ratio of ECC disks to data disks with smaller word sizes - inefficient

Entry level cost very high - requires very high transfer rate requirement to justify.

Transaction rate is equal to that of a single disk at best .

No commercial implementations exist / not commercially viable.
-------------------------------------
RAID 3
-------------
RAID 3 - Is rarely used . So here it is not discusiing

One of the side effects of RAID 3 is that it generally cannot service multiple requests simultaneously.



In our example, a request for block "A" consisting of bytes A1-A6 would require all three data disks to seek to the beginning (A1) and reply with their contents.A simultaneous request for block B would have to wait.


RAID 3. also distributes data in small increments but adds only one parity disk. This results in good performance for large transfers, but small transfers show poor performance.


RAID Level 3 requires a minimum of 3 drives to implement

Characteristics & Advantages
-----------------------------
Very high Read,write data transfer rate


Disadvantages
------------------
Transaction rate equal to that of a single disk drive at best (if spindles are synchronized)

Controller design is fairly complex

Very difficult and resource intensive to do as a "software" RAID

RAID 4

Apr 3, 2010

RAID

RAID an acronym for Redundant Array of Inexpensive Disks or Redundant Array of Independent Disks, is a technology that allows high levels of storage reliability from low-cost and less reliable PC-class disk-drive components, via the technique of arranging the devices into arrays for redundancy.

Consider Personal computer they come with more than enough memory, have more than enough processing power, and have lots of disk space. The one thing they don’t come with is a backup (or external) drive. if User need to protect their important files, such as e-mails, pictures , and all .Many homeowners don’t think about backups. They go on merrily for years, adding their financial data, their photographs, and their collections of e-mail to their hard drives. Then one day they realize about the hard drive failure. Their hard drive fails, their computer won’t boot . What about servers do they come with extra disks? Usually, yes, servers are ordered with extra disks. Depending on the model of the server, you will have a different number of options and disks available. The primary reason for extra disks, besides the need for disk capacity, is to protect yourself through the utilization of RAID in the event of a hard disk failure.

For example, Figure below shows one possible RAID configuration. In this Figure , three hard drives are tied together to appear as one. The same data gets written to all three drives. Because the three drives are mirror images of each other, we can trash one of the drives and still have access to our data on the other two. We can even trash two drives, leaving just one good one.

Figure 1



RAID for Performance

----------------------

RAID can also be utilized to improve performance. Knowing how to do that can be
helpful when trying to performance tuning queries. The faster you can read and write data to and from your disks, the faster your response times will be. You can use RAID to increase your I/O throughput by taking advantage of
striping your data across several disks at once.

For example, a traditional hard disk has a little arm inside of it that moves back and forth every time you want to read and write data. IF you had to do 50 read and 50 write operations, and each one took 1/50th of a second. Then it will take Two seconds on one disk . Now, configure RAID as shown in Figure below




Now you have two little arms moving back and forth across two disks at the same time. You would still have your two hundred operations to perform, but they would be done on two drives simultaneously. Each individual drive would get half the work. Your overall time would be reduced from two seconds to one second.
Another method for improving performance would be caching. Most RAID controllers
allow for some amount of data to be stored in cache.

---------------------------------------------------------

RAID for Fault Tolerance

RAID uses two methods to provide for fault tolerance: mirroring and parity. Mirroring is exactly what it sounds like: you have two (or more) copies of your data on two (or more) different disks. As in Figure 1

Parity occurs when you add extra data called parity data to one of your disks, which can then be used to reconstruct your real data when one disk fail

Raid Level 0 (RAID 0) - Striped Disk Array without Fault Tolerance: Provides data striping (spreading out blocks of each file across multiple disk drives) but no redundancy. This improves performance but does not deliver fault tolerance. If one drive fails then all data in the array is lost.

RAID 0 is also used in some gaming systems where performance is desired and data integrity is not very important.#and in Video Production and Editing,
,Image Editing, Pre-Press Applications,any application requiring high bandwidth

RAID 1 - Mirroring and Duplexing: Provides disk mirroring.

A RAID 1 creates an exact copy (or mirror) of a set of data on two or more disks. This is useful when read performance or reliability are more important than data storage capacity. A classic RAID 1 mirrored pair contains two disks

Characteristics & Advantages

One Write or 2 Reads possible per mirrored pair.Twice the Read transaction rate of single disks, same Write transaction rate as single disks.100% redundancy of data means no rebuild is necessary in case of a disk failure, just a copy to the replacement disk.Simplest RAID storage subsystem design

Recommended Applications

Accounting,Payroll,Financial,Any application requiring very high availability

RAID 5
-------

This level is also known as striping with parity. It is very similar to RAID 0, with one additional disk needed to hold the parity bit that gets striped across all disks. . Without a doubt, RAID 5 is a favorite RAID level,
and I have seen it used by many administrators.The reason for RAID 5 being so well liked is that if one disk fails, you simply have to add in a new disk (before a second disk fails; otherwise, you lose everything) and the array will rebuild itself. While RAID 5 can offer better performance than RAID 1, the overhead for that parity bit means it will not perform as well as RAID 0. But you do get fault tolerance with RAID 5, and that is why so many administrators love using RAID 5 over anything else.

Mar 31, 2010

LOCKING IN ORACLE

Two types of Locking


In Optimistic locking the row will not be locked until the actual update is performed. In order to prevent the lost update issue a version number column will be used.

In Pessimistic locking the row will be locked at the time when the data is selected from the database. This will prevent other users who want to update the same record. This should not prevent the readers but this behaviour varies from database to database.


Optimistic : One who usually expects a favorable outcome
Pessimistic : Expecting the worst possible outcome (negative)



The Oracle database uses Optimistic locking by default. Any command that begins with UPDATE SET that is not preceded by a SELECT FOR UPDATE is an example of optimistic locking.

SELECT ..... FOR UPDATE ,
SET TRANSACTION ISOLATION LEVEL,
LOCK TABLE PROJECT_MASTER IN ROW EXCLUSIVE MODE
are examples of Pessimistic locking


Pessimistic has two major problems

The Lockout
- An application user selects a record for update, and then leaves for lunch without finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction

The Deadlock
- Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B - who is waiting to obtain a lock held by user A. Both transactions go into an infinite wait state - the so-called deadly embrace or deadlock.

Mar 26, 2010

WIDTH_BUCKET

Syntax:

WIDTH_BUCKET(column-name/expr, low/min,high/max,bucket_count) ;

Divide a set o data into buckets (ie: partition ) with an equal interval.

eg : Number of Cars 0-10 , 10-20 ,30-40 ,40-50

If we specify n buckets then n+2 buckets are generated .The extra 2 buckets are value above the high/max boundary and below the low/min boundary.

select WIDTH_BUCKET(sal,1000,10000,3) util ,sal from (select 5700 sal from dual
union
select 500 sal from dual
union
select 1000 sal from dual
union
select 6000 sal from dual
union
select 9000 sal from dual
union
select 90000 sal from dual
union
select 4500 sal from dual)


Instead of Width_bucket Case Function can be used .But here we want to manually divide the range there may or may not be equal interval its all up to the SQL programmer .


Syntax:

CASE WHEN THEN
WHEN THEN
[ELSE Value n ] END


SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 49 THEN 1
ELSE 0 END) AS "0-49", SUM(CASE WHEN SUM(amount) BETWEEN 50 AND 100
THEN 1 ELSE 0 END) AS "50-100"  FROM sales


-----------

Benchmark

A benchmark is the act of running a computer program, a set of programs, or other operations, in order to assess the relative performance of an object


Benchmark Factory ( ie : is a product of QUEST SOFTWARE)is a database performance and code scalability testing tool that simulates users and transactions on the database and replays production workload in non-production environments. . This enables developers, DBAs, and QA teams to validate that their databases will scale as user load increases, application changes are made, and platform changes are implemented.

Benchmark Factory places enormous stress on a database system, which is typically hard to achieve in a standard testing environment.

A system typically breaks under extreme load. By identifying system capacity and performance bottlenecks before they occur,

Benchmark Factory facilitates proactive testing, which in turn reduces downtime, development costs, and potential loss of revenue.

Benchmark Factory allows you to:

Determine system throughput and capacity for database systems

Simulate thousands of concurrent users with a minimal amount of hardware

Find applications that do not scale well with an increase in the number of users

Find breaking points, weak links, or bottlenecks of a system

Quantify application or server performance

Mar 22, 2010

Data Center

The data center is the hub of IT organizations, a provider of business efficiencies (performance)delivered through mission-critical data and business applications, and a serious consumer of resources, including electricity.

Grid computing pools and provisions groups of servers depending on business demands, and it is a key part of the next-generation data center.

Oracle Real Application Clusters (Oracle RAC), a key component of Oracle Grid Computing.


Carbon emission is a problem of Data Center.

Excessive energy consumption becomes an issue for a data center .
It can limit the growth of that data center. There can be enough money, enough physical space, and enough business requirements to grow the data center, but sometimes the actual power availability into the building can become a limitation.

The greatest consumers of energy in the data center are heating and cooling. The rule of thumb is that about half of the energy going into a data center is for air conditioning to cool the equipment that produces heat. “Only a small proportion of the energy going into a data center produces the data processing that you really want.


Many servers run at 50 percent or less of their capacity but still have to be kept running. Virtualization helps us run individual servers at a much higher capacity, which means we don’t need to run nearly as many servers for the same amount of data processing.




Green Computing


The Oracle Grid Computing architecture can improve energy efficiency and data center productivity, making a “greener” data center. Some of the enabling functionality includes the following:

* Server virtualization and consolidation using Oracle Real Application Clusters, Oracle Application Server clusters, and Oracle VM save energy and lower system management overhead.

* Storage virtualization consolidation using Oracle Automatic Storage Management and Oracle Advanced Compression can save energy because companies that use fewer disks will consume less power. More-centrally managed storage also means less administrative overhead.

* Workload management using Oracle Enterprise Manager allows companies to manage spare capacity to provide more processing power to development, test, and production systems.

Mar 9, 2010

ContexT

Application contexts provides e the implementation of fine-grained access control. They allow you to implement security policies. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).

The SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

The syntax for the SYS_CONTEXT function is:

SYS_CONTEXT( namespace, parameter, [ length ] )

Namespace is an Oracle namespace which is name of that context.

What is Namespace

A namespace is an abstract container or environment created to hold a logical grouping of unique identifiers or symbols (i.e., names). An identifier defined in that namespace is associated with it. The same identifier can be independently defined in multiple namespaces. That is, the meaning associated with an identifier defined in one namespace may or may not have the same meaning in another namespace.

For example, Bill works for company X and his employee ID is 123. John works for company Y and his employee ID is also 123. The reason Bill and John can be identified by the same ID number is because they work for different companies. The different companies in this case would symbolize different namespaces. There would be serious confusion if the two men worked for the same company, and still had the same employee ID. For instance, a pay-cheque issued to employee ID 123 would not identify which man should receive the cheque.

USERENV is an Oracle provided namespace that describes the current session ; It have so many parameters.

Eg: SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr,SYS_CONTEXT('USERENV', 'HOST', 16) host,SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL', 8) netprtc
,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) curruser,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) currschema FROM DUAL;

To set Grant
GRANT CREATE ANY CONTEXT TO schema_name;

Context is of two types Normal and Accessed Globally .
In Normal context is connection based; is not valid across sessions;Accessed globally context is visible across sessions.

create context some_context using some_package; (NORMAL)
create context global_context using global_package accessed globally;

Eg: (NORMAL)

create or replace context cntx_test using pk_test;

create or replace package pk_test as
procedure set_value_in_context(some_value in varchar2);
end pk_test;


create or replace package body pk_test as
procedure set_value_in_context(some_value in varchar2) is
begin
dbms_session.set_context('cntx_test', 'test_attribute', some_value);
end set_value_in_context;
end pk_test;


exec pk_test.set_value_in_context('PLSQL');

select sys_context('cntx_test', 'test_attribute') from dual;


Globally Accessed

create context global_context using global_package accessed globally;

create or replace package global_package as
procedure set_value_in_context(global_value in varchar2);
end global_package;


create or replace package body global_package as
procedure set_value_in_context(global_value in varchar2) is
begin
dbms_session.set_context('global_context', 'global_attribute', global_value);
end set_value_in_context;
end global_package;


exec global_package.set_value_in_context('valid accross sessions');

select sys_context('global_context', 'global_attribute') from dual;


One main Benefit of Context is parametrizing views with contexts

For parametrizing views Normal context are to be created because which is session dependent .

Example

conn scott/scott


create context ctx_vw_emp using pk_ctx_vw_emp;


create or replace view vw_emp as select * from emp where deptno =sys_context('ctx_vw_emp','dept');


create package pk_ctx_vw_emp as
procedure set_department (depno in number);
end;
/


create package body pk_ctx_vw_emp as
procedure set_department(depno in number) is
begin
dbms_session.set_context('ctx_vw_emp', 'dept', depno);
end set_department;
end pk_ctx_vw_emp;
/


exec pk_ctx_vw_emp.set_department('10');

select * from vw_emp;

exec pk_ctx_vw_emp.set_department('30');

select * from vw_emp;
---------------------------




Feb 10, 2010

Insert Statements

Insert into (select S_DESIGNATION, S_DESCRIPTIONDESG from designation_mast where N_DESGCODE=2) values ( 'tttttttttt','ere')

which is similar to

Insert into designation_mast(S_DESIGNATION, S_DESCRIPTIONDESG) values ( 'tttttttttt','ere')

Feb 2, 2010

Oracle Workspace Manager

Difference between Long Transaction (Oracle WorkSpace) and Short Transaction (Normal )

Long Transactions
-----------------------

Complete over days or weeks
Likelihood of conflict is low - Optimistic concurrency permits conflicts

Selective versioning tables
Update creates a new row version
Each update is part of a short transaction

Collections of updates isolated in workspaces until merged into production

Multi user update

Short Transactions
----------------------
Complete in seconds or less
Likelihood of conflict is high - Pesimistic concurrency permits conflicts
Data is in a single state
Updates accessible upon commit
Single user updates

-----------------------------------------------------------

WorkSpace Manager Operations
----------------------------------------

Workspace : Create , Refresh,merge,rollback,remove,goto,compress,alter
Savepoints : Create , Alter,Goto,Compare,Rollback and Delete
History : Gotodate
Privileges : Access,Create,Delete,Rollback,Merge
Locks : Exclusive and Shared (Exclusive lock prevents changes by any other user Shared locks allow other users in the workspace to change row )
Differences : Compares savepoints and workspaces
Detect /Resolve Conflicts : choose version to merge.

Since the versioning process removes the physical unique index from the base table, multiple session would be allowed to enter the same value into a column that has a unique constraint defined on it. Since the changes by the other session might be part of an uncommitted transaction, the instead of triggers that are defined on the view are unable to enforce the constraint in this particular case. As a result,OWM use the _LCK VIEW to prevent this scenario by maintaining a unique constraint on the underlying table which the dmls are applied to.


Disabling Versioning for a table
------------------------------------

Disable versioning when changes to the version-enabled table are completed
Improves performance
Workspace hierarchy and savepoints remain
The latest version of each row in LIVE workspace remains.

Freeze WorkSpace
---------------------
Freezing a workspace specifies the kind of user access allowed to the workspace .

NO_ACCESS is default
READ_ONLY allows all workspace users to read.
1WRITER: Sessions are allowed in the workspace, but only one user
1WRITER_SESSION: Sessions are allowed in the workspace, but only the database session (as opposed to the database user) that called the FreezeWorkspace procedure is allowed to perform write operations
WM_ONLY: Only Workspace Manager operations are permitted.

--------------------------------------------------------------


Jan 5, 2010

Detailed Calender Query

SELECT TRUNC( sd + rn ) time_id,
TO_CHAR( sd + rn, 'fmDay' ) day_name,
TO_CHAR( sd + rn, 'D' ) day_number_in_week,
TO_CHAR( sd + rn, 'DD' ) day_number_in_month,
TO_CHAR( sd + rn, 'DDD' ) day_number_in_year,
TO_CHAR( sd + rn, 'W' ) calendar_week_number,
( CASE WHEN TO_CHAR( sd + rn, 'D' ) IN ( 1, 2, 3, 4, 5, 6 ) THEN
NEXT_DAY( sd + rn, 'SATURDAY' ) ELSE
( sd + rn ) END ) week_ending_date,
TO_CHAR( sd + rn, 'MM' ) calendar_month_number,
TO_CHAR( LAST_DAY( sd + rn ), 'DD' ) days_in_cal_month,
LAST_DAY( sd + rn ) end_of_cal_month,
TO_CHAR( sd + rn, 'FMMonth' ) calendar_month_name,
( ( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) - TRUNC( sd + rn, 'Q' ) + 1 ) days_in_cal_quarter,
TRUNC( sd + rn, 'Q' ) beg_of_cal_quarter,
( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) end_of_cal_quarter,
TO_CHAR( sd + rn, 'Q' ) calendar_quarter_number,
TO_CHAR( sd + rn, 'YYYY' ) calendar_year,
( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
- TRUNC( sd + rn, 'YEAR' ) ) days_in_cal_year,
TRUNC( sd + rn, 'YEAR' ) beg_of_cal_year,
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' ) end_of_cal_year
FROM ( SELECT TO_DATE( '12/31/2002', 'MM/DD/YYYY' ) sd,
rownum rn FROM dual CONNECT BY level <= 6575 )
/