Try to avoid the * (asterisk) from select query.
When you fire the following commands:
Select * from employee
Database server needs that much space in its buffer cache (in RAM) for holding that amount of data. It will affect the performance of database if table is large. More work need to be done by Database Background process for make space in buffer cache. So try to use column name instead of *.
Always uses bind variable in SQL queries instead of literals value.
Select * from employee where emp_no =: Bind Variable;
Database Server need to parse queries every time for the same syntax queries due to literal value are different. To avoid parsing again for same SQL Statement better to use bind variable.
How to check Execution plan in Oracle:
Display Execution Plan in Oracle
EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.
DBMS_XPLAN package: is a function which can display as little (high-level) or as much (low-level) details.
It’s having following parameter for high level or low level details
Explain plan for select empno, ename, dname from emp a, dept b where a.deptno = b.deptno;
Select plan_table_output from table(dbms_xplan.display('plan_table'));
Below is the Snapshot of Execution Plan of Queries:
Select * from K where owner = 'SCOTT';
IF your table is using full access scan then its means the index is missing on the join condition column or where clause column, for make it better you can create index on column used in where clause.
Create index K_OWNER_IDX on K(owner);
And Execute the Same Query, It show the cost is very low with index.
Avoid commit Operation in loop statement for loading bulk data modules
On commit operation, database background process writes everything on data files and redo file which causes waiting event for a sync write to the file system every time which cause the performance issue. Example: if you want to make a procedure for inserting bulk data into database. So, does not use commit operation on loop statement below the insert/update operation for each row use it after 10000 or 5000 rows inserted/updated.
Do not use database triggers as code.
Do not write trigger on the database, it causes problem in replication techniques and make the coding complex. Oracle also recommends for not to use triggers as code in Database.
Create index in sequence of where clause column used.
Always try to create index on column as same sequence used in where clause and vice versa.
Example: I Created Index on for following SQL Queries:
Select * from k where object_type=’data’ and owner=’IC’ and object_name=’TRAN’>
Then create following index according to column sequence wise:
Create index k_object_idx on k(object_type,owner,object_name);
It will use the index when we execute the select command.
If you have another query which uses the one column in where clause
Select * from k where owner='IC'
If you modified the first query according to use owner as first column and create index according to that sequence then only one index will work for both upper SQL Queries.
Select * from k where owner ='IC' and object_type ='TABLE' and object_name = 'K';
Create index k_owner on k(owner,object_type,object_name)
Select * from k where owner = 'IC';
Note: Now days optimizer are too smart that it will automatically modified the statement internal and save execution plan for lower cost for SQL Statements
Primary key and Unique Key automatically create index on columns included. They will help your database to perform better when querying. Try to create Primary key and unique key on that column in application which has maximum utilization in where clause.
Use User Defined types in Programming.
Suppose if you use a Variable with system datatype in every part of coding like Pl/SQL packages, Procedure and Function. So if you want to increase its length then you need to modify all packages, Procedure and function coding. So always try to use user defined data types in coding for better flexibility.