Showing posts with label Improve Performance. Show all posts
Showing posts with label Improve Performance. Show all posts

Nov 23, 2014

Improving Performance

Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT, UPDATE, DELETE, and SELECT statements directly in PL/SQL,
PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

 In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance as each statement is parsed and cached.  

CREATE PROCEDURE del_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id); 
END;

You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of emp_id  

CREATE PROCEDURE delt_employee (emp_id NUMBER) AS 
BEGIN 
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; 
END; 

---- 

Dec 21, 2011

NOT IN / NOT EXISTS



 It is recommend to use NOT EXIST instead of NOT IN ; do you know/thought  why ?  ! .




You dont see any differences here because the tables are small with less data and also there will not be any difference in the explain plan too.For very large tables performance improvement are seen by using NOT EXISTS instead  of  NOT IN ; so test it and see whats the difference .


  EXAMPLE

   create table oracle4u ( orclid number(10), constraint pk primary key(orclid) ,s_name varchar2(100) );


   create table "oracle4u.com" ( orcl4id number(10), constraint opp primary key(orcl4id) ,s_name varchar2(100) );


   insert into oracle4u select rownum,OBJECT_NAME  from SYS.ALL_OBJECTS;


   insert into oracle4ucom select rownum,TABLE_NAME  from SYS.ALL_tables union select null,'TEST'  from dual

commit;
-------------------


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from oracle4ucom fg)


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from   oracle4ucom fg where fg.orcl4id is not null)


select * from oracle4u pp where  not exists  (select fg.orcl4id  from oracle4ucom fg where pp.orclid=fg.orcl4id )


select *  from oracle4u pp  Left Outer JOIN oracle4ucom fg ON pp.orclid=fg.orcl4id  WHERE fg.orcl4id IS NULL


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



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.