Simple tips to TUNE your SQL Statement

As we all know that all RDBMS works on SQL. Oracle database also uses the SQL to perform the DDL, DML, DCL and TCL operations. There are various ways to write SQL statement to achieve the same result. These statements will have their own COST to get the result from the database. Here are few simple tips to tune the SQL statement.
How Oracle processes the SQL statement?
Before we go ahead for the tuning, lets take a look at how Oracle processes the SQL statement. There are two steps involved in processing of SQL statement:
1. Parsing
2. Execution
In the first step, Oracle will break the submitted SQL statement into its component parts, it will then determine the type of the statement i.e. DDL, DML, DCL and then performs a series of checks on the statement.
Let’s quickly take a look at the steps involved in the Parsing of SQL statement:
1. Syntax Check
Oracle will check whether the statement submitted is syntactically correct.
2. Semantic Analysis
If the Syntax is correct, then this step is executed. In this step, Oracle will determine that the table(s), column(s) referred in the SQL statement already exists. Oracle will also determine that the user who has submitted the statement have the sufficient rights to perform the activity. It also performs the AMBIGUITY check like if there is a same column (column name) available in two tables and both these tables are referenced in the FROM clause of the statement. Then Oracle will return a message like "Column ambiguously defined".
3. Decide - Is the statement already parsed by other session?
4. If the statement is already parsed, perform a SOFT PARSE,
5. If the statement is not parsed, perform a HARD PARSE.
Let’s understand what is SOFT PARSE and HARD PARSE.
After completing the first 2 steps (mentioned above) Oracle database now needs to check if the current SQL statement is already executed by any other session. If the statement is already processed, then PARSE operation will skip the "OPTIMIZATION" and "ROW SOURCE GENERATION" steps . If these steps are skipped, then this is called as "SOFT PARSE". This saves a considerable amount of CPU utilization while running the queries.
If the statement that has been submitted is never processed then the PARSE operation will have to perform all the steps before processing the statement. This type of PARSING is known as "HARD PARSE".
It is important that the developers write the queries that will take advantage of SOFT PARSE.
---------------------------------------------------------------------------
Identical SQL
If any two SQL statements needs to be shared within Oracle, those two statements MUST BE EXACTLY MATCHING TO EACH OTHER. If the two statements are EXACT MATCH of each other, Oracle will avoid the parsing step for the subsequent statement.

1. SELECT ENAME FROM EMP WHERE EMPID = 7729;
2. SELECT ENAME FROM emp WHERE EMPID = 7729;
3. SELECT ENAME FROM EMP WHERE EMPID=7729;
4. SELECT ENAME
FROM EMP
WHERE ID=7729;
All the above statement are treated as different statement and will be parsed before executing.
---------------------------------------------------------------------------
Writing Standard SQL
Simple rules and standards will allow more memory sharing within Oracle Database.
1. Use a single case for all SQL verbs
2. Start all SQL verbs on a new line
3. Align the verbs Left or Right within the initial SQL verb
4. Separate all words with a SINGLE space
---------------------------------------------------------------------------
Bind variables
Use bind variables. The values of bind variables need not to be the same for two statements to be considered identical. Bind variables are not substituted until a statement has been successfully parsed.
Sharable SQL
1. SELECT * FROM dept
WHERE deptno = :BND1; Bind value: 7729
2. SELECT * FROM dept
WHERE deptno = :BND1; Bind value: 7345
Non-sharable SQL
1. SELECT * FROM dept
WHERE deptno = 7729;
2. SELECT * FROM dept
WHERE deptno = 7345;
---------------------------------------------------------------------------
Table Aliases
Use table aliases and a standard approach to table aliases. If two identical SQL statements differ because an identical table has two different aliases, then the SQL is different and will not be shared.
Prefix all column names by their aliases when more than one table is involved in a query. By doing this the time required for the parsing is decreased and prevents future syntax errors if someone adds a column to one of the tables with the same name as a column in another table.
---------------------------------------------------------------------------
WHERE Clause
Look out for the WHERE clauses which do not use indexes at all. Even if there is a column on which a index is defined, and that column is referenced by a WHERE clause, Oracle will ignore the index. You can always re-write the WHERE clause to make use of Indexes and return the same result.
Examples:
1.
SELECT acc_nm, amt
FROM bank_account
WHERE SUBSTR (acc_nm,1,7) = 'SAVINGS';
There is an index on "ACC_NM" column. But as we are performing the SUBSTR operation on this column, Oracle will ignore the index. Try using LIKE operator here to get the same result.
SELECT acc_nm, amt
FROM bank_account
WHERE acc_nm LIKE 'SAVINGS%';
---------------------------------------------------------------------------
HAVING Clause
HAVING clause filters rows AFTER ALL the rows are fetched by the query. As long as possible please avoid using HAVING clause in the SELECT statement. Use HAVING clause only and only when you want to perform summary operaitons on columns.
Example:
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING deptno != 20;
This statement can be written as
SELECT deptno, AVG(sal)
FROM emp
WHERE deptno != 20
GROUP BY deptno;
---------------------------------------------------------------------------
DECODE
Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.
Example:
SELECT COUNT (a.user_id), a.regionid, b.reg_nm
FROM reg_user RU, region R
WHERE RU.regionid IN (1000,1001,1002,1005)
AND RU.regionid = R.regionid

This statement can be re-written by eliminating the need of joining the region table

SELECT COUNT (user_id),
DECODE(regionid, 1000, 'USA', 1001, 'CANADA', 1002, 'UNITED KINGDOM', 1005, 'INDIA')
FROM reg_user
WHERE a.regionid IN (1000,1001,1002,1005,1006,1010)
---------------------------------------------------------------------------
UNION ALL
Detarmine whether a "UNION ALL" will be sufficient in place of a "UNION".
How UNION works?
The UNION forces all rows returned by each portion of the UNION to be sorted and merged and duplicate to be filtered before the first row is returned.
How UNION works?
UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If you don't bother about the duplicates and/or your tabels do not have duplicates then UNION ALL is much more efficient way to fetch the data from the tables.
---------------------------------------------------------------------------
DISTINCT v/s EXISTS
If DISTINCT qualifier is used on SELECT list for the queires which are used to determine data at the owner side of a 1-M (one to many) relationship. DISTINCT forces Oracle to fetch all rows satisfying the table join and then perform sorting and filtering duplicate values. EXISTS is a faster alternative, because when the subquery has been satisfied once, the Oracle optimizer realizes there is no need to proceed further and the next matching row can be fetched.
Example: Find departments (number and names) who have at-least one employee working in it.
SELECT DISTINCT deptno, dname
FROM dept D, emp E
WHERE D.deptno = E.deptno;
Consider the below query to get the same result:
SELECT deptno, dname
FROM dept D
WHERE EXISTS (
SELECT 'X'
FROM emp E
WHERE E.deptno = D.deptno);
Also please try to avoid DISTINCT with GROUP BY in the SELECT statement
---------------------------------------------------------------------------
Views
Lastly be more importantly, take a look at your Views as well. Views are SELECT statements, and you should not forget to tune them either. The SELECT statement in the views can be tuned in the same way as any other SELECT statement can be tuned.

GOOD IDEAS about Database Backups

I got this article emailed to me from the DBSPECIALIST(www.dbspecialists.com). This article is written by Iggy Fernandez, a member of Database Specialists Team. This article is Iggy's response to a question about best practices for backups.
-------------------------------------------------------------

The following practices apply to all database backups, not just Oracle backups:
1. Establish a service level agreement (SLA) for backups and recovery. Clearly document the maximum amount of data loss that is permitted, backup retention policies, and how much time is allowed for database recovery.
2. Document the backup methodology and have it formally reviewed. Publish the documentation to an audience that includes the database owner.
3. Periodically test the recoverability of backups. Typically, this is done in a testing environment. A recovery test establishes how long it will take to recover the database.
4. Be prepared to recover from user error, for example when a user inadvertently updates or deletes the wrong data.
5. Have a contingency plan that covers damage to the operating system or hardware-be prepared to move the database to another server.
6. Ensure the safety of backups. The traditional method is to send backup tapes offline. Newer methods involve backups to a backup appliance over the network. A combination of near-line (disk) and far-line (tape or network) backups can be used. Near-line backups reduce the time needed for recovery while far-line backups increase the safety of backups. The tape library should not be a single point of failure-ensure that data can be recovered even if the tape library is damaged.
7. Retain multiple backups in case one set of backups is damaged.
8. Backup scripts should incorporate error checking and an adequate amount of logging. The logs should be retained for an appropriate time. Notification of backup failures should be sent to the database administrators. Backup failures should be formally investigated.
9. Reports of backup successes and failures, the amount of data backed up, and the time it took to perform backups should be generated and sent to an audience that includes the database administrators and the database owner.
10. Changes to any aspect of backup procedures, whether temporary or permanent, should be performed under formal change control procedures.

------------------------------------------------------------------------------------------------
The following practices apply specifically to Oracle database backups:
1. Use Recovery Manager (RMAN) for backups. The advantages of RMAN are so numerous and valuable that it is hard to justify not using it. For example, RMAN checks data blocks for corruption while it is making a backup, single-block recovery is possible with RMAN, and so on. Backups performed using third-party software such as Network Appliance Snapshot can be integrated with RMAN.
2. Prevent unlogged operations in databases that use ARCHIVELOG mode; use the ALTER DATABASE FORCE LOGGING command to do this.
3. Backup all aspects of the database including the archived redo logs (for databases that use ARCHIVELOG mode), the control file, and the parameter file (spfile).
4. Create logical backups (exports) to supplement physical backups. This creates a certain amount of protection from logical damage, such as data entry errors. Use a setting such as FLASHBACK_TIME to ensure the consistency of the exported data.
5. Leverage Oracle's flashback features by increasing the value of UNDO_RETENTION from the default value of 15 minutes to a more appropriate value such as 24 hours. This allows recovery from logical damage without having to resort to physical backups.
6. Databases that run in ARCHIVELOG mode should set LAG_ARCHIVE_TARGET to an appropriate value, such as 15 minutes, to control maximum data loss.
7. Incorporate the use of techniques that check for data corruption. These include initialization parameters, such as DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_UPDATE, and commands that scan the database, such as VALIDATE DATABASE. Oracle Database 11g introduced a single parameter called DB_ULTRA_SAFE that controls the values of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_UPDATE.