Some points in Performance Tuning

Try to avoid using  following in triggers

1) UTL_ packages .  They do not roll back
2) Pragma autonomous_transaction only use if it is unavoidable


Wrong cardinality = Wrong Plan
Check Constraints can rewrite queries

If you want to limit the creation of index however want to increase performance degredated query by refering explain plan . One main point is that you want to check the the highest cardinality which is having FULL TABLE ACCESS in the explain plan , for that particular cardinality check the access predicates in the explain plan and try to create index for those columns in the join (access predicates)

Creating index will help in the performance of a query even though the join is right or left outer join .

Dimensions convey information to the optimizer.The presence of Dimensions open up access paths that would not be otherwise available.


The Forms' PL/SQL code runs in the Forms PL/SQL engine, which is different in PL/SQL engine in database , this is why you cannot access database package's variable, which result in the following error
Implementation Restriction: 'Cannot directly access remote package variable or cursor while compiling oracle forms

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; 

---- 

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

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

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

Performance Tuning without Toad ie(In oracle)

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select golflink,course_name,x,y from (select b.golflink,b.course_name,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,1) x
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) x
,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,2) y
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) y
from username.golf_area b ) where x is not null and y is not null';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => null,--sql_binds(anydata.ConvertNumber(0)),
user_name => 'USER',
scope => 'COMPREHENSIVE',
time_limit => 10,
task_name => 'RuR',
description => 'Task to tune a query on a specified employee');
END;



SELECT * FROM DBA_ADVISOR_LOG where task_name='RuR'




BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'RuR' );
END;




SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'RuR'



SELECT * FROM V$ADVISOR_PROGRESS



SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'RuR') FROM DUAL;

By executing this query the details including explain-plan will be listed whether indexes to be added or not


BEGIN
DBMS_SQLTUNE.drop_tuning_task( task_name => 'RuR' );
END;