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 21, 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;
---------------------------