Physical hardware and Oracle database configuration installation will, other than bottleneck resolution, generally only affect performance by between 10% and 20%. Most performance issues occur from poorly developed SQL code, with little attention to SQL tuning during development, probably causing around 80% of general system
performance problems. Poor data model design can cause even more serious performance problems than SQL code but it is rare because data models are usually built more carefully than SQL code. It is a common problem that SQL code tuning is often left to DBA personnel.
DBA people are often trained as Unix Administrators, SQL tuning is conceptually a programming skill; programming skills of Unix
Administrators are generally very lowlevel,if present at all, and very different in skills requirements to that of SQL code.
How to Tune SQL
Indexing
A:When building and restructuring of indexing never be afraid of removing unused indexes.The DBA should always be aware of where indexes are used and how.
Oracle9i can automatically monitor index usage using the ALTER INDEX index name [NO]MONITORING USAGE; command with
subsequent selection of the USED column from the V$OBJECT_USAGE column.
Taking an already constructed application makes alterations of any kind much more complex. Pay most attention to indexes most often utilized. Some small static tables may not require indexes at all. Small static lookup type tables can be cached but will probably be force table-scanned by the optimizer anyway; table-scans may be adversely affected by the addition of unused superfluous indexes. Sometimes table-scans are faster than anything else. Consider the use of
clustering, hashing, bitmaps and even index organized tables, only in Data Warehouses. Many installations use bitmaps in OLTP databases, this often a big mistake! If you have bitmap indexes in your OLTP database and are having performance problems, get rid of them!
Oracle recommends the profligate use of function-based indexes, assuming of course there will not be too many of them. Do not allow too many programmers to create their indexes, especially not function-based indexes, because you could end-up with thousands of indexes.
Application developers tend to be unaware of what other developers are doing and create indexes specific to a particular requirement where indexes may be used in only one place. Some DBA control and
approval process must be maintained on the creation of new indexes.
Remember, every table change requires a simultaneous update to all indexes created based on that table.
SQL Statement Reorganisation
SQL statement reorganization encompasses factors as listed below, amongst others.
WHERE clause filtering and joining orders matching indexes.
Use of hints is not necessarily a good idea. The optimizer is probably smarter than you are.
Simplistic SQL statements and minimizing on table numbers in joins.
Use bind variables to minimize on re-parsing. Buying lots of
expensive RAM and sizing your shared pool and database buffer cache to very large values may make performance worse. Firstly, buffer cache reads are not as fast as you might think. Secondly, a large SQL parsing shared pool, when not using bind variables in SQL code, will simply fill up and take longer for every subsequent SQL statement to search.
Oracle9i has adopted various SQL ANSI standards. The ANSI join syntax standard could cause SQL code performance problems. The most effective tuning approach to tuning Oracle SQL code is to remove rows from joins using where clause filtering prior to joining multiple tables, obviously the larger tables, requiring the fewest rows should be filtered first. ANSI join syntax applies joins prior to where clause
filtering; this could cause major performance problems. Nested subquery SQL statements can be effective under certain
circumstances. However, nesting of SQL statements increases the level of coding complexity and if sometimes looping cursors can be utilized in PL/SQL procedures, assuming the required SQL is not completely ad-hoc.
Avoid ad-hoc SQL if possible. Any functionality, not necessarily business logic, is always better provided at the application level.
Business logic, in the form of referential integrity, is usually best catered for in Oracle using primary and foreign key constraints and explicitly created indexes. Nested subquery SQL statements can become over complicated and impossible for even the most brilliant coder to tune to peak efficiency. The reason for this complexity could lie in an over-Normalized underlying data model. In general use of subqueries is a very effective approach to SQL code performance tuning. However, the need to utilize intensive, multi-layered subquery SQL code is often a symptom of a poor data model due to
requirements for highly complex SQL statement joins.
Some Oracle Tricks
Use [NOT] EXISTS Instead of [NOT] IN
In the example below the second SQL statement utilizes an index in the subquery because of the use of EXISTS in the second query as opposed to IN. IN will build a set first and
EXISTS will not. IN will not utilize indexes whereas EXISTS will.
SELECT course_code, name FROM student
WHERE course_code NOT IN
(SELECT course_code FROM maths_dept);
SELECT course_code, name FROM student WHERE NOT EXISTS
(SELECT course_code FROM maths_dept
WHERE maths_dept.course_code = student.course_code);
In the example below the nesting of the two queries could be reversed depending on which table has more rows. Also if the index is not used or not available, reversal of the subquery is required if tableB has significantly more rows than tableA.
DELETE FROM tableA WHERE NOT EXISTS
(SELECT columnB FROM tableB WHERE tableB.columnB = tableA.columnA);
Use of value lists with the IN clause could indicate a missing entity.
Also that missing entity is probably static in nature and can potentially be cached, although caching causing increased data buffer size
requirements is not necessarily a sensible solution.
SELECT country FROM countries WHERE continent IN ('africa','europe','north america');
Equijoins and Column Value Transformations
AND and = predicates are the most efficient. Avoid transforming of column values in any form, anywhere in a SQL statement, for instance as shown below.
SELECT * FROM <table name> WHERE TO_NUMBER(BOX_NUMBER) = 94066;
And the example below is really bad! Typically indexes should not be placed on descriptive fields such as names. A function-based index would be perfect in this case but would probably be unnecessary if the data model and the data values were better organized.
SELECT * FROM table1, table2
WHERE UPPER(SUBSTR(table1.name,1,1)) = UPPER(SUBSTR(table2.name,1,1));
Transforming literal values is not such a problem but application of a function to a column within a table in a where clause will use a table-scan regardless of the presence of indexes. When using a function-based index the index is the result of the function which the optimizer will recognize and utilize for subsequent SQL statements.
Datatypes
Try not to use mixed datatypes by setting columns to appropriate types in the first place. If mixing of datatypes is essential do not assume implicit type conversion because it will not always work, and implicit type conversions can cause indexes not to be used. Function-based indexes can be used to get around type conversion problems but this is not the most appropriate use of function-based indexes. If types must be mixed, try to place type conversion onto explicit values and not columns. For instance, as shownbelow.
WHERE zip = TO_NUMBER('94066') as opposed to WHERE TO_CHAR(zip) = '94066'
The DECODE Function
The DECODE function will ignore indexes completely. DECODE is very useful in certain circumstances where nested looping cursors can become extremely complex. DECODE is intended for specific
requirements and is not intended to be used prolifically, especially not with respect to type conversions. Most SQL statements containing DECODE function usage can be altered to use explicit literal selection criteria perhaps using separate SELECT statements combined with UNION clauses. Also Oracle9i contains a CASE statement which is much more versatile than DECODE and may be much more efficient.
Join Orders
Always use indexes where possible, this applies to all tables accessed in a join, both those in the driving and nested subqueries. Use indexes between parent and child nested subqueries in order to utilize indexes across a join. A common error is that of accessing a single row from the driving table using an index and then to access all rows from a nested subquery table where an index can be used in the nested subquery table based on the rowretrieved by the driving table.
Put where clause filtering before joins, especially for large tables where only a few rows are required. Try to use indexes fetching the minimum number of rows. The order in which tables are accessed in a query is very important. Generally a SQL statement is parsed from
top-tobottom and from left-to-right. The further into the join or SQL statement, then the fewer rows should be accessed. Even consider constructing a SQL statement based on the largest table being the driving table even if that largest table is not the logical driver of the SQL statement. When a join is executed, each join will overlay the result of the previous part of the join, effectively each section (based on each table) is executed sequentially. In the example below table1 has the most rows and table3 has the fewest rows.
SELECT * FROM table1, table2, table3
WHERE table1.index = table2.index AND table1.index = table3.index;
Hints
Use them? Perhaps. When circumstances force their use. Generally the optimizer will succeed where you will not. Hints allow, amongst many other things, forcing of index usage rather than full table-scans. The optimizer will generally find full scans faster with small tables and index usage faster with large tables. Therefore if row numbers and the ratios of row between tables are known then using hints will probably make performance worse. One specific situation where hints could help are generic applications where rows in specific tables can change drastically depending on the installation. However, once again the optimizer may still be more capable than any programmer or DBA.
Use INSERT, UPDATE and DELETE ... RETURNING
When values are produced and contained in insert or update
statements, such as new sequence numbers or expression results, and those values are required in the same transaction by following SQL statements, the values can be returned into variables and used later without recalculation of expression being required. This tactic would be used in PL/SQL and anonymous procedures. Examples are shown below.
INSERT INTO table1 VALUES (test_id.nextval, 'Jim Smith', '100.12', 5*10)
RETURNING col1, col4 * 2 INTO val1, val2;
UPDATE table1 SET name = 'Joe Soap'
WHERE col1 = :val1 AND col2 = :val2;
DELETE FROM table1 RETURN value INTO :array;
Triggers
Simplification or complete disabling and removal of triggers is
advisable. Triggers are very slow and can cause many problems, both performance related and can even cause serious locking and database errors. Triggers were originally intended for messaging and are not intended for use as rules triggered as a result of a particular event.
Other databases have full-fledged rule systems aiding in the
construction of Rule-Based Expert systems. Oracle triggers are more like database events than event triggered rules causing other
potentially recursive events to occur. Never use triggers to validate referential integrity. Try not to use triggers at all. If you do use triggers and have performance problems, their removal and recoding into stored procedures, constraints or application code could solve a lot of your problems.
Data Model Restructuring
Data restructuring involves partitioning, normalization and even
denormalization. Oracle recommends avoiding the use of primary and foreign keys for validation of referential integrity, and suggests
validating referential integrity in application code. Applicationcode is more prone to error since it changes much faster. Avoiding constraint-based referential is not necessarily the best solution.
Referential integrity can be centrally controlled and altered in a single place in the database. Placing referential integrity in application code is less efficient due to increased network traffic and requires more code to be maintained in potentially many applications.
All foreign keys must have indexes explicitly created and these indexes will often be used in general application SQL calls, other than just for validation of referential integrity. Oracle does not create internal indexes when creating foreign reference keys. In fact Oracle
recommends that indexes should be explicitly created for all primary, foreign and unique hey constraints. Foreign keys not indexed using the CREATE INDEX statement can cause table locks on the table containing the foreign key. It is highly likely that foreign keys will often be used by the optimizer in SQL statement filtering where clauses, if the data model and the application are consistent with each other structurally, which should be the case.
Views
Do not use views as a basis for SQL statements taking a portion of the rows defined by that view. Views were originally intended for security and access privileges. No matter what where clause is applied to a view the entire view will always be executed first. On the same basis also avoid things such as SELECT * FROM …, GROUP BY clauses and aggregations such as DISTINCT. DISTINCT will always select all rows first. Do not create new entities using joined views, it is better to create those intersection view joins as entities themselves; this applies
particularly in the case of many-to-many relationships. Also Data Warehouses can benefit from materialized views which are views actually containing data, refreshed by the operator at a chosen juncture.
Maintenance of Current Statistics and Cost Based Optimization
Maintain current statistics as often as possible, this can be automated.
Cost-based optimization, using statistics, is much more efficient than rule-based optimization.
Regeneration and Coalescing of Indexes
Indexes subjected to constant DML update activity can become skewed and thus become less efficient over a period of time. Use of Oracle Btree indexes implies that when a value is searched for within the tree, a series of comparisons are made in order to depth-firsttraverse down through the tree until the appropriate value is found.
Oracle Btree indexes are usually only three levels, requiring three hits on the index to find a resulting ROWID pointer to a table row. Index searches, even into very large tables, especially unique index hits, not index range scans, can be incredibly fast.In some circumstances
constant updating of a binary tree can cause the tree to becomemore heavily loaded in some parts or skewed. Thus some parts of the tree require more intensive searching which can be largely fruitless. Indexes should sometimes be rebuilt where the binary tree is regenerated from scratch, this can be done online in Oracle9i, as shown below.
ALTER INDEX index name REBUILD ONLINE;
Coalescing of indexes is a more physical form of maintenance where physical space chunks which are fragmented. Index fragmentation is usually a result of massivedeletions from table, at once or over time.
Oracle Btree indexes do not reclaim physical space as a result of row deletions. This can cause serious performance problems as a result of
fruitless searches and very large index files where much of the index space is irrelevant. The command shown below will do a lot less than rebuilding but it can help. If PCTINCREASE is not set to zero for the index then extents could vary in size greatly and not be reusable. In that the only option is rebuilding.
ALTER INDEX index name REBUILD COALESCE NOLOGGING;
17.DBA BASIC QUESTIONS
1)What are the prerequisites for connecting to a database
> 1) oracle net services should be available in both server and client.
2) listner should up and running. In case of remote connection..
[oracle listiner starts up a dedicated server process and passes the server protocal adress to client using that address the clients connect to the server. Once the connection is established the listiner
connection is terminated]
***********************************************************************
[AND]
1) check wether database server is installed on server or not.
2) client software should be installed on client machine.
3) check database and client are running on the same network or not.
(with the help of ping
4) ensure thar oracle listiner is up and running 5) connect to server using server protocal address 2) Create a User "TESTAPPS" identified by "TESTAPPS"
> create user identified by ;
3) Connect to DB using TESTAPPS from DB Node and MT Node
> first give grant options to the user....
grant connect,resource to ;
4)How do you identify remote connections on a DB Server
> ps -ef|grep -i local [where local=no it is a remote connection... in the os level]
5)How do you identify local connections on a DB Server
> ps -ef|grep -i local [where local=yes it is a local connection... in the os level]
6)Can you connect remotely as a user on DB Server. If so, how?
> /@ [with the help of connecting string]
7)Do you need to acess to DB Server to connect to a system schema?
> NO , just knowing the username&password u connect from the
client...
8)What is the difference between "SYS" & "SYSTEM" Schema
> SYS is a super most user...
SYS has additional roles sysdba, sysoper SYS can do only startup, shudown options
> SYSTEM schema has owns certain additional data dictonary tables..
SYSTEM donot use startup and shutdown options....
9)What are the roles/priviliges for a "SYS" Schema
>***ROLES***[select granted_role from dba_role_privs where grantee='SYS']
***PRIVILAGES****[select privileges from dba_sys_privs where grantee='SYS';]
ALTER ANY RULE SET DEQUEUE ANY QUEUE DELETE ANY TABLE DROP ANY RULE
CREATE ANY RULE SET SELECT ANY SEQUENCE
10)What are the role/privileges for a SYSTEM Schema
> **ROLES**
[select granted_role from dba_role_privs where grantee='SYSTEM';]
AQ_ADMINISTRATOR_ROLE DBA
>**PRIVILEGES***
[select privilege from dba_sys_privs where grantee='SYSTEM';]
GLOBAL QUERY REWRITE CREATE MATERIALIZED VIEW CREATE TABLE
UNLIMITED TABLESPACE SELECT ANY TABLE
11)What is the difference between SYSDBA & DBA
> SYSDBA has startup and shutdown options
> DBA has no startup and shutdown options
12)What is the difference between X$ , V$ ,V_$,GV$
> X$ is permenent views
> GV$ are used in RAC environment....
> v$, V_$ are the temporary views which exist during the run time....
13)How do you verify whether your DB is a single node or Multinode
> sho parameter cluster;
its show false ..means single node.
14)From MT connect to db using "connect / as sysdba"
> /as sysdba cannot connect to database from MT ...
or
u can connect to DB from MT by creating password file
15)Is a Listener required to be up and running for a Local Connection
> NO
16)Is a Listener required to be up and running for a remote Connection
> YES
17)How do you verify the Background processes running from the Database
> desc v$bgprocess
select * from v$bgprocess;
18)How do you verify whether a init.ora parameter is modifiable or not.
18)How do you verify whether a init.ora parameter is modifiable or not.