Oracle Tuning for linking local table with remote table over DBLink

When we are working with multiple applications on Oracle, there might be a need to link those applications together. One of the way to integrate them is though bank end DB Link. 

There is one technique I would like to introduce to help on the DBLink performance. This little tuning tip will help a lot when you join the tables from both side of the DBLink. 

When we join 2 or more tables from both end of the DBLink and the query is executed, the data will be executed with back-and-forth behaviour. This will cause the pain in your query performance. In case of the table on the remote is very large and the local table is smaller, the helping tips is using oracle hint /*+DRIVING_SITE (remote_table_alias)*/. Where remote_table_alias is the table alias on the remote side. 

I have come across the Oracle tuning technique that might help us over DBLink. 

There might be some scenario that we need to join local table with remote table over DBLink, the  driving_site hint might help us with the performance. Please take a look at the example below:

select /*+DRIVING_SITE(r)*/ 

   r.key, r.code, r.name

from 

    remote_table@dblink r

    tiny_local_table        l, 

  

where

     r.key = l.key;

The driving_site hint will operate the join and sorting on the remote site with this scenario.

However, this technique will not work with create table as select (CTAS) and with create materialized view syntax. 

Read More

Disable table indexes to boost up performance when doing insertion

There is pro and con for having indexes on the table.

The pro is when you use select statement indexes will help boost up the query. However, when you do DML statement (insert, update, delete) you might run into performance issue.

The work around of this when you need to insert a lot of data is just don’t use the indexes when you do the insertion. In other words, disable the indexes or make the indexes as unusable.

When you try to run the following command,

Alter Index . Disable; 

you may run into the error

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

The solution for this is using the following command to mark the index unusable.

alter index your_index unusable;

And to remark your index usable again, you must rebuild the index by executing the following command.

alter index your_index rebuild [online];

If you still experience failure when executing DML after mark the index unusable.

You need to change parameter “skip_unusable_indexes” to true.

Use the following command to check if this parameter value is false or true.

show parameter skip_unusable_indexes;
If it is false then change it to true by running the following command. 

alter session set skip_unusable_indexes = true;

Note: If you are using 10g you cannot mark unusable unique index and insert the data.

Read More

How to Boot Up Your Oracle Performance

This is a good Oracle Performance Tuning video I have came across on youtube. This is a good video for both Developer and DBA. The presenter gave a very good and clear explanation. This is one of the Oracle tutorial series from the same presenter.

There were 2 different type of tuning that were discussed in this video System Tuning and SQL Tuning. Roughly, System Tuning involves the appropriate system parameters configuration and SQL Tuning involves the individual SQL statement tuning.

Here are what were introduced in this video:

System Tuning

• Tuning log (redo log) switches
• pga_aggregate target (9i)
• Tuning advisors (views hints)
• Pinning packages in memory
• Statspack

SQL Tuning

• Indexes
• Oracle Optimizer
• Explain Plan
• Hints
• Stored Outlines

Read More

Oracle tutorial PL/SQL Basics

 I came a cross one video on PL/SQL tutorial, and it is a good one. This video give us picture of what oracle PL/SQL is with the very clear and simple presentation. This is a very recommended video for PL/SQL newbie. What were talked on the video were:

1. Why PL SQL?
2. Types of PL/SQL Code
3. Data types - Variables
4. Statements control flow
5. Cursors
6. Exception handling

Read More

ORACLE EXPLAIN PLAN FUNDAMENTALS

I came across this basic Oracle Explain Plan fundamental tutorial on Youtube. The explain plan is the  basic knowledge that the developers and DBAs may consider getting to understand.


The presenter in this video tutorial gave the theory and then went through the explain plan demonstration. What were presented in this tutorial were:

• Understand of Basic Explain Plan.
• How to setup Oracle Explain Plan and how to use it.
• Understand the Explain PLAN_TABLE, DBMS_XPLAN.DISPLAY
• How to generate and View Explain Plan
• How to read and interpret basic Explain Plan
• What is the limitation of the Explain Plan

Enjoy your watch.

Read More

ORA-28001: the password has expired

 

When you encounter an oracle error ORA-28001: the password has expired and you do not have sysdba priviledge, here is how to handle it.

What you need is sqlplus, which is located in ORACLE_HOME/bin. The step to resolve this are:

1. Login to you account by using sqlplus in command prompt

    c:\sqlplus or $sqlplus

2. Enter your user and the service

    Enter user-name: user@service

3. Enter your password when prompted to do so, and you will get the error ORA-28001

    Enter password:

    ERROR:
    ORA-28001: the password has expired

4. Then it will prompt for password change, you need to enter your new password twice.

    Changing password for user
    New password:
    Retype new password:
    Password changed

Then you will be able to login to the system.

 

Read More

How to check if your installed oracle client is 32bit or 64bit

The easy way to check the installed oracle client version in windows operating system:

The first way to check  is checking from sqlplus binary.

1. Run sqlplus in command line, and leave it prompting for user name as shown below



2. Check oracle version from windows task manager:



If the process shows "sqlplus.exe*32" as in the picture then it's the 32bit version client. If it shows "sqlplus.exe" then it's the 62bit version client.

The second way is checking from installation log:

The path of the installation lies on [ORACLE BASE]\product\11.2.0\client_1\cfgtoollogs\oui\, and the file name starts with installActions with log as an extension; for example installActions2013-07-11_03-08-26PM.log.
Here is what you have to do to check the installed oracle version:

1. Open the oracle client installation log.

2. Search for the keyword oracle_install_architecture. If you see the number as 32 then it is 32bit version client and if it is 64 then it is 64bit client. The example below shows that the installed oracle is oracle client 32bit version..





 

 

Read More

Understanding Shared Pool Memory Structures: Tips on How to Optimize Usage and Avoid Errors

The Oracle shared pool provides critical services for sharing of complex objects among large numbers of users. Prior to 10g R1, DBAs often spent much time learning about the shared pool memory management to configure and tune shared pool usage; with the tight time constraints and ever-changing workloads, many found this task daunting. The Automatic Shared Memory Management (ASMM) features introduced in 10gR1 solved this problem by providing the DBA a simple, automatic self-tuning mechanism for configuring shared memory components of the SGA, including the buffer cache and shared pool. The Automatic Database Diagnostic Monitor (also introduced in 10gR1) further simplified shared pool related tuning efforts by providing automatic diagnosis and recommendations for application specific issues.

More information : http://download.oracle.com/oowsf2005/003wp.pdf

Below are the video to help you understand more on Oracle Database Architecture.

Read More

ORA-06530: Reference to uninitialized composite

ORA-06530: Reference to uninitialized composite
ORA-06512: at "PROCEDURE/PACKAGE", line 88
ORA-06512: at "PROCEDURE/PACKAGE", line 446
ORA-06512: at line 11
06530. 00000 -  "Reference to uninitialized composite"
*Cause:    An object, LOB, or other composite was referenced as a
           left hand side without having been initialized.
*Action:   Initialize the composite with an appropriate constructor
           or whole-object assignment.

Cause:

This error would be found when using oracle collection table type, and trying to assign the value to the collect. When compiling the code, it would be fine. But, when execute the package/procedure, you will have this error message.

The error caused when trying to extend the define table type object without being initialized. See the scenario below:

CREATE TYPE obj_test AS OBJECT
       (tst_id       number,
        tst_name    VARCHAR2(50));
/
CREATE TYPE tab_test AS TABLE OF obj_test;
/

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/
Solution:

To fix this, simply add initialized object line as shown below:

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last) := type_supplier(NULL, NULL);

        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/

Read More

ORA-06052: PL/SQL: numeric or value error on PL/SQL Collections First and Last

Cause:
The error ORA-06052: PL/SQL: numeric or value error would be found when using for loop on the type collection object, as shown below, and the collection is empty:

FOR i IN type_variable.FIRST..t_variable.LAST LOOP
     ----your code
End Loop ;

When the collection is not empty, error ORA-06052: PL/SQL: numeric or value error would not be found.
This is because FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.


Solution:

Use  FOR i IN 1..t_variable.count instead of FOR i IN type_variable.FIRST..t_variable.LAST , and also apply exception block.

BEGIN
      FOR i IN 1..t_variable.count
           ----your code
      End Loop;
EXCEPTION WHEN OTHERS
     -- error handling code
END; 

Read More

Migrate SUSE Linux to Oracle Linux

If you are thinking of migrating your SUSE to Oracle Linux, there are things to think of and things to be prepared before you do it.

The preinstallation process and pro installation process need to be prepared. Below is the link to get information on the migration. Please read it before you do it.

http://www.oracle.com/technetwork/articles/servers-storage-admin/dell-linux-1498654.html?ssSourceSiteId=ocomen

Read More

The Reason to Upgrade to Oracle 11g Release 2

If you are using Oracle 9i, 10g, and decide to move forward to use 11g, below are what will you get form the 11g R2 upgrade:-

Oracle Database 11g Release 2, the latest release of the award-winning Oracle
Database 11g, enables IT professionals to deliver more information with higher quality of
service, make more-efficient use of their budgets, and reduce the risk of change in
datacenters. By deploying Oracle Database 11g Release 2 as their data management
foundation, organizations can utilize the full power of the world’s leading database to

• Reduce server costs by a factor of 5.

• Reduce storage requirements by a factor of 10. 

• Improve mission-critical system performance by a factor of 10.

• Increase DBA and developer productivity by a factor of 2.

• Maximize availability and eliminate idle redundancy.

• Maximize security and enable compliance.

• Simplify their overall IT software portfolio.

For more information, please visit http://www.oracle.com/us/products/database/039448.pdf.

Read More

How many database Indexes is too many?

Having a lot of indexes on your table will affect the performance of your table either bad side or good side. It depends on what operation you use most.
       
Index will help fasten your query, and at the same time might slow your query down. When you do a lot of insertion, update, deletion based on the index columns, this will slow the query down. On the other hand, it will also help with select statement.

So how many indexes you should have just depends on the characteristic of statement you will use. If you have a lot of insert, update, delete statement, then too many indexes is not so good. While having most of query as select statement, then it is worth having many of them.

Read More

ORA-06512: at line

ORA-06512: at stringline string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.
PostingPosting

---

Read More

For Loop in PL/SQL

One of the statement  in oracle I use most is for loop in pl/sql. This for loop is very handy to use and the basic statement comes like this:


for variable in 1..x 
loop
   statement;
end loop;


The reverse loop statement is:

for variable in reverse 1..x 

loop

   statement;

end loop;

The example code comes like this

for v_cnt in 1 .. 100

       

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

    for v_cnt in reverse 1 .. 100

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

For in pl/sql could also work with cursor, this make handy as I said before. I like to use for loop in pl/sql with cursor so I don't have open and close the cursor, the for loop will do it automatically. Here is how for loop in pl/sql works:


for c_cur in (select statement)
loop
  statement;
end loop;

for c_cur in c_name 
loop
  statement;
end loop;


Where c_name is the declared cursor and c_cur is the variable (you don't need to declare it).

For example:


for c_cur in (select a, b from tab_x) 
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;



for c_cur in declared_cursor
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;

Read More