SQL & PL/SQL : When Your Query Takes Too Long

When your query takes too long and too slow, first of all you have to investigate the root caused of the problem, you have to know why it is slow

The tools at your disposal are, among more:
1. dbms_profiler
2. explain plan
3. SQL*Trace / tkprof
4. statspack

Use dbms_profiler if you want to know where time is being spent in PL/SQL code.
Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.

explain plan in SQL*Plus you have to type:

explain plan for [your query];
select * from table(dbms_xplan.display);

When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.

SQL*Trace/tkprof

For this you have to type in SQL*Plus:
- alter session set sql_trace true;
- run your query
- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)
- identify your trace file in the server directory as specified in the parameter user_dump_dest
- on your operating system: "tkprof [trace file] a.txt sys=no sort=prsela exeela fchela"

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

So before rushing into possible solutions, always post the output of explain plan and tkprof with your question and don't forget to post them between the tags [pre] and [/pre] for readability.

SQL & PL/QL : Deleting Table with Millions Rows

Here are the case:
You have to compare 2 tables, both having around million rows, and delete data based on single column.

SQL>DELETE FROM TABLE_A WHERE COLUMN_A NOT IN
(SELECT COLUMN_A FROM TABLE_B)

when you execute the above command, it takes a long time. So what is the best way to achieve it?
Here are some solutions:

1. If the rows percentage to be deleted is large then you can try the following step:
* Create a new table where COLUMN_A NOT IN
(SELECT COLUMN_A FROM TABLE_B)
* TRUNCATE table_A
* Insert in to table_A (select * from new table)
* If you have any constraints then may be it can be disabled.
2. If the query is properly indexed you might get better performance from a correlated subquery instead of NOT IN, something like

DELETE FROM TABLE_A
WHERE NOT EXISTS(
SELECT COLUMN_A
FROM TABLE_B
WHERE b.column_a = a.column_a
)

Lost SYSOPER Password

Thera are several solutions to this issue, you can use one of the following methods:

• Make sure that the database and listener services are started. Then open a command prompt and log in as / as sysdba. Then change the sys password.
  
  c:> sqlplus /nolog
  SQL> connect / as sysdba
  SQL> alter user sys identified by ;
  
  If you get asked for a password when you log in as / you need to create or recreate the password file using orapw. Check the $ORACLE_HOME/database directory for a file called orapw. That is the password file for the database. Create (or recreate) it using:
  
  c:\> orawpd file=$ORACLE_HOME/database/orapwd password= entries=5
  
  Notice it is from the dos prompt
• you may also try this method:
  
  1. login oracle user
  2. cd $ORACLE_HOME/network/admin
  3. ed(vi) file sqlnet.ora
     
  4. Remark by # at begining of line
     SQLNET.AUTHENTICATION_SERVICES = (NONE)
     => #SQLNET.AUTHENTICATION_SERVICES = (NONE)
  5. sqlplus /nolog or (svrmgrl) command
  6. connect sys as sysdba, or connect internal
     (not need input the password).
     

Oracle Data Security Tips

If you're building a client-server application, you can use these tips:

1. Build a three tier application, so the client is not connect to the database directly.
2. Allow only 1 IP Address (as application server) connect to the oracle database (you can use blocking IP tips).
3. Never use Oracle user ID (specially DBA user) as your Application user ID, instead create a user table with user and encrypted password in it.
4. Give your Oracle Listener a password, so when someone unauthorized send a stop message to the listener it didn't work out.

Oracle SAVEPOINT

A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

SQL> INSERT INTO AUTHOR
2 VALUES ('X123', 'mister',
3 'popo', '62-274-4567',
4 'jl sudirman', 'jakarta',
5 'JKBKJ','10001', '9999');

1 row created.

SQL> savepoint in_author;

Savepoint created.

SQL> INSERT INTO BOOK_AUTHOR VALUES ('X123', 'B130', .20);
1 row created.

SQL> savepoint in_book_author;

Savepoint created.

SQL> INSERT INTO BOOK
2 VALUES ('B130', 'P002', 'how to make love',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2007','MM-DD-YYYY'));
1 row created.

SQL> rollback to in_author;

Rollback complete.

In the example above, I inserted a row into the AUTHOR table and created a SAVEPOINT called in_author. Next, I inserted a row into the book_author table and created another SAVEPOINT called in_book_author. Finally, I inserted a row in the BOOK table. I then issued a ROLLBACK to in_author.

Row locks are NOT released by SETTING a savepoint. Row locks are release by one of three events - commit, rollback, or rollback to savepoint. My argument is that Oracle does not handle the latter well.

1. If transaction A updates row 1, sets a savepoint, and updates row 2 (but does not as yet commit). Then transaction B wishes to update row 2.
2. Transaction B will correctly block on the commit or rollback of transaction A.
3. However, if transaction A does a rollback to savepoint, it will continue to have hold a lock on row 1 (but not row 2).
4. In fact a third transaction can now update row 2 (as it's not locked by transaction A).
5. However, our poor transaction B, is still waiting (incorrectly) for transaction A to commit or rollback.

The problem is that Oracle provides no way of waiting on a row - you can only wait on a transaction - and sometimes transactions (through the use of rollback to savepoint) release rows WITHOUT committing or aborting.

Imagine what could happen when by issuing a savepoint the lock of an standing transaction would be released, and another transaction would change 'my' row, and then I do a full rollback.

Repair UNDO Log Corruption

In rare cases (usually DBA error) the Oracle UNDO tablespace can become corrupted.

This manifests with this error: ORA-00376: file xx cannot be read at this time

In cases of UNDO log corruption, you must:

• Change the undo_management parameter from “AUTO” to “MANUAL”
• Create a new UNDO tablespace
• Drop the old UNDO tablespace

Dropping the corrupt UNDO tablespace can be tricky and you may get the message:

ORA-00376: file string cannot be read at this time

To drop a corrupt UNDO tablespace:

1. Identify the bad segment:
   

   select
   segment_name,
   status
   from
   dba_rollback_segs
   where
   tablespace_name='undotbs_corrupt'
   and
   status = ‘NEEDS RECOVERY’;
   

   
   SEGMENT_NAME STATUS
   ----------------------------------------------
   _SYSSMU22$ NEEDS RECOVERY

2. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the bad segment name:
   _OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$
3. Bounce database, nuke the corrupt segment and tablespace:
   
   SQL> drop rollback segment "_SYSSMU22$";
   Rollback segment dropped.
   
   SQL > drop tablespace undotbs including contents and datafiles;
   Tablespace dropped.