Feeds:
Posts
Comments

Posts Tagged ‘Statistics’

When a SQL is submitted by a user to Oracle database, it never happens that Oracle will execute the SQL continuously at one go. Oracle process never get to work on the execution of statement without any interruptions. Often the process has to pause or wait for some event or some other resource to be released. Thus active Oracle process is doing one of the following thing at any point of time.

The process is executing the SQL statement.

The process is waiting for something (for example, a resource such as a database buffer or a latch). It could be waiting for an action such as a write to the buffer cache to complete.
That’s why the response time—the total time taken by Oracle to finish work—is correctly
defined as follows:

response time = service time + wait time

So only part of the time is spend by oracle process to actually “do” some thing. Rest of the time process just wait for some resource to get freed up. It can be waiting log writter process or database writter process or any other resources.

The wait event may also be due to unavailable buffers or latches.

Four dynamic performance views contain wait information: V$SESSION, V$SYSTEM_EVENT, V$SESSION_EVENT, and V$SESSION_WAIT. These four views list just about all the events the instance was waiting for and the duration of these waits. Understanding these wait events is essential for resolving performance issues.

There are different wait classes defined in database. Each class will contain different wait events. There are around 860 wait events defined in Oracle database 10g and are classified under different wait classes.

Some of the main wait classes includes:

  • Administrative: Waits caused by administrative commands, such as rebuilding an index, for example.
  • Application: Waits due to the application code.
  • Cluster: Waits related to Real Application Cluster management.
  • Commit: Consists of the single wait event log file sync, which is a wait caused by commits in the database.
  • Concurrency: Waits for database resources that are used for locking; for example, latches.
  • Configuration: Waits caused by database or instance configuration problems, including a low shared-pool memory size, for example.
  • Idle: Idle wait events indicate waits that occur when a session isn’t active; for example, the ‘SQL*Net message from client’ wait event.

You can see the complete list of wait classes using V$SESSION_WAIT_CLASS dynamic performance view.

Analyzing Instance Performance

You can check the percentage of time spend by the database in waiting for resource and percentage of time spend by database in actual execution.

SQL> SELECT METRIC_NAME, VALUE
             FROM V$SYSMETRIC
            WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,
           ‘Database Wait Time Ratio’) AND
           INTSIZE_CSEC =
           (select max(INTSIZE_CSEC) from V$SYSMETRIC);

METRIC_NAME                                                           VALUE
—————————————————————- ———-
Database Wait Time Ratio                                         15.6260647
Database CPU Time Ratio                                          84.3739353

If the database performance shows that ‘Database Wait Time Ratio’ is greater then ‘Database CPU Time Ratio’ or the value for ‘Database Wait Time Ratio’ is quite significant, then in that case you need to dig inside to get the information about where exactly oracle is waiting. You need to basically find the type of wait. This will give you root cause. Once you get the root cause you can work to fix the same.

you can determine the total waits and percentage of waits by wait class.

SELECT WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_TOTWAITS,
ROUND((TIME_WAITED / 100),2) TOT_TIME_WAITED,
round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
FROM
(select WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED
FROM V$SYSTEM_WAIT_CLASS
WHERE WAIT_CLASS != ‘Idle’),
(select sum(TOTAL_WAITS) SUM_WAITS,
sum(TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != ‘Idle’)
ORDER BY PCT_TIME DESC;

WAIT_CLASS           TOTAL_WAITS PCT_TOTWAITS TOT_TIME_WAITED   PCT_TIME
——————– ———– ———— ————— ———-
System I/O                180300        19.96          3008.8      49.53
Commit                     67855         7.51         1302.46      21.44
User I/O                  291565        32.28         1056.55      17.39
Application                 3637           .4          596.66       9.82
Other                      15388          1.7            67.4       1.11
Concurrency                 1264          .14           38.12        .63
Network                   343169        37.99            3.86        .06
Configuration                 22            0               1        .02

8 rows selected.

In the above output percentage of time waited (last column) is more important and gives the correct picture of the impact due to wait. Example if we see total number of Network waits are large but the actual percentage of time contributed to the wait is very less (0.06%).

The key dynamic performance tables for finding wait information are the V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, and the V$SESSION views. The first two views show the waiting time for different events.

V$SYSTEM_EVENT

The V$SYSTEM_EVENT view shows the total time waited for all the events for the entire system since the instance started up. The view doesn’t focus on the individual sessions experiencing waits, and therefore it gives you a high-level view of waits in the system. You can use this view to find out that the top instance-wide wait events are. You can calculate the top n waits in the system by dividing the event’s wait time by the total wait time for all events.

select EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS from V$SYSTEM_EVENT
where wait_class != ‘Idle’
order by time_waited desc;

EVENT                          TOTAL_WAITS TIME_WAITED WAIT_CLASS
—————————— ———– ———– ——————–
log file parallel write             128953      210308 System I/O
log file sync                        67904      130313 Commit
db file sequential read             259065       73686 User I/O
enq: TX – row lock contention          226       59080 Application
control file parallel write          28282       57929 System I/O
db file parallel write               19155       32924 System I/O
db file scattered read               31841       30925 User I/O
os thread startup                       95        3262 Concurrency
rdbms ipc reply                        485        2116 Other
PX Deq: Signal ACK                    1971        1103 Other
local write wait                       245         864 User I/O

we can get the session level waits for each event using V$SESSION_EVENT view. In this view the TIME_WAITED is the wait time per session.

V$SESSION_EVENT

select sid, EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS
from V$SESSION_EVENT
where WAIT_CLASS != ‘Idle’
order by TIME_WAITED;

  SID EVENT                          TOTAL_WAITS TIME_WAITED WAIT_CLASS
———- —————————— ———– ———– ————
390 os thread startup                       55      1918   Concurrency
393 db file sequential read              10334      4432   User I/O
396 db file parallel write                8637      14915  System I/O
397 db file parallel write               10535      18035  System I/O
394 control file parallel write          28294      57928  System I/O
395 log file parallel write             129020      210405 System I/O

As we can see from above output that session 395 is having maximum wait time because of system I/O. Here system I/O is the I/O because of background processes like DBWR and LGWR etc.

You can get all the database wait events from V$EVENT_NAME and the related meaning of all the wait events available in oracle 10g by checking this documentation link.

V$SESSION_WAIT

The third dynamic view is the V$SESSION_WAIT view, which shows the current waits or just completed waits for sessions. The information on waits in this view changes continuously based on the types of waits that are occurring in the system. The real-time information in this view provides you with tremendous insight into what’s holding up things in the database right now. The
V$SESSION_WAIT view provides detailed information on the wait event, including details such as file number, latch numbers, and block number. This detailed level of information provided by the V$SESSION_WAIT view enables you to probe into the exact bottleneck that’s slowing down the database. The low-level information helps you zoom in on the root cause of performance problems.

SQL> select sid, event, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE from v$session_wait
2  where wait_class != ‘Idle’;

       SID EVENT                          WAIT_CLASS            WAIT_TIME
———- —————————— ——————– ———-
SECONDS_IN_WAIT STATE
————— ——————-
337 SQL*Net message to client      Network                      -1
0 WAITED SHORT TIME

Here wait time -1 means that session has waited for less then 1/100th of a second.
You can get the complete wait information for a particular session using V$SESSION view. You can get SQLID of the sql, which is causing wait.

V$SESSION

For getting the wait statistics you can go with the following methodology.

  • First, look at the V$SYSTEM_EVENT view and rank the top wait events by the total amount of time waited, as well as the average wait time for that event.
  • Next, find out more details about the specific wait event that’s at the top of the list. You can check V$WAITSTAT view for the same. Check the type of wait this view is showing. If the wait is due to undo header or undo block then wait is related to undo segment.
  • Finally, use the V$SESSION view to find out the exact objects that may be the source of a problem. For example, if you have a high amount of db file scattered read-type waits, the V$SESSION view will give you the file number and block number involved in the wait events.

In V$SESSION we have a column called BLOCKING_SESSION_STATUS. IF this column value is ‘valid’, then we can presume that corresponding session is getting blocked.

V$SESSION_WAIT_HISTORY

The V$SESSION_WAIT_HISTORY view holds information about the last ten wait events for each active session. The other wait-related views, such as the V$SESSION and the V$SESSION_WAIT, show you only the wait information for the most recent wait. This may be a short wait, thus escaping your scrutiny.

SQL> select sid from v$session_wait_history
2  where wait_time = (select max(wait_time) from v$session_wait_history);

      SEQ# EVENT                            P1         P2         P3  WAIT_TIME
———- ———————— ———- ———- ———- ———-
1 rdbms ipc message            180000          0          0     175787
2 rdbms ipc message            180000          0          0     175787
3 rdbms ipc message            180000          0          0      60782
4 rdbms ipc message            180000          0          0     175787
5 rdbms ipc message            180000          0          0     138705
6 db file sequential read           1        368          1          0
7 rdbms ipc message            180000          0          0     158646
8 db file sequential read           1        368          1          0
9 db file sequential read           1         73          1          0
10 db file sequential read           1         30          1          0

Note that a zero value under the WAIT_TIME column means that the session is waiting for a specific wait event. A nonzero value represents the time waited for the last event.

V$ACTIVE_SESSION_HISTORY

The V$SESSION_WAIT view tells you what resource a session is waiting for. The V$SESSION view also provides significant wait information for active sessions. However, neither of these views provides you with historical information about the waits in your instance. Once the wait is over, you can no longer view the wait information using the V$SESSION_WAIT view. The waits are so fleeting that by the time you query the views, the wait in most times is over. The new Active Session History (ASH) feature, by recording session information, enables you to go back in time and review the history of a performance bottleneck in your database. Although the AWR provides hourly snapshots
of the instance by default, you won’t be able to analyze events that occurred five or ten minutes ago, based on AWR data. This is where the ASH information comes in handy. ASH samples the V$SESSION view every second and collects the wait information for all active sessions. An active session is defined as a session that’s on the CPU or waiting for a resource. You can view the ASH session statistics through the view  V$ACTIVE_SESSION_HISTORY, which contains a single row for each active session in your instance. ASH is a rolling buffer in memory, with older information being overwritten by new session data. Every 60 minutes,the MMON background process flushes filtered ASH data to disk, as part of the hourly AWR snapshots. If the ASH buffer is full, the MMNL background process performs the flushing of data. Once the ASH data is flushed to disk, you won’t be able to see it in the V$ACTIVE_SESSION_HISTORY view. You’ll now have to use the DBA_HIST_ACTIVE_SESS_HISTORY view to look at the historical data.

obtaining the objects with highest waits

SELECT a.current_obj#, o.object_name, o.object_type, a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
dba_objects o
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.current_obj# = o.object_id
GROUP BY a.current_obj#, o.object_name, o.object_type, a.event
ORDER BY total_wait_time;

 OBJECT_NAME                OBJECT_TYPE         EVENT                  TOTAL_WAIT_TIME
——————–       ——————- ———————– —————-
FND_LOGINS                 TABLE               db file sequential read   47480
KOTTB$                     TABLE               db file sequential read   48077
SCHEDULER$_WINDOW          TABLE               db file sequential read   49205
ENG_CHANGE_ROUTE_STEPS_TL  TABLE               db file sequential read   52534
JDR_PATHS_N1               INDEX               db file sequential read   58888
MTL_ITEM_REVISIONS_B       TABLE               SQL*Net more data to client

select p1text, p1, p2text, p2, p3text, p3, a.event
from v$active_session_history a
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.current_obj# = 1938000

P1TEXT     P1          P2TEXT     P2   P3TEXT          P3  EVENT
——–   —         ——- ——- ———— —— ————————
file#      71          block#    4389  blocks           1  db file sequential read
file#      187         block#   89977  blocks           1  db file sequential read
file#      80          block#   79301  blocks           1  db file sequential read
driver id  675562835   #bytes       1  0
file#      11          block#     831  blocks           1  db file sequential read
driver id  675562835   #bytes       1  0

so we can see few history wait events for a particular object in database. We can get the segment stats for this object. Finally we can got to some conclusing and implementaing the solution to reduce the wait. For example if it is a ‘db file sequential read’ wait then

  • Increase buffer cache size, but this wont help much. For this to do, you need to check cache miss percentages.
  • Check the query and optimize it, so that it can read less number of blocks
  • Increase freelists for that segment

some important wait events

The following query lists the most important wait events in your database in the last 15 minutes:

SELECT a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time between
sysdate – 30/2880 and sysdate
GROUP BY a.event
ORDER BY total_wait_time DESC;

EVENT                                                            TOTAL_WAIT_TIME
—————————————————————- —————
enq: TX – row lock contention                                          877360289
816854999
TCP Socket (KGAS)                                                       13787430
SQL*Net break/reset to client                                            6675324
db file sequential read                                                  2318850
control file parallel write                                              1790011
log file parallel write                                                  1411201
db file scattered read                                                     62132
os thread startup                                                          39640
null event                                                                     0

Users with the Most Waits

The following query lists the users with the highest wait times within the last 15 minutes:

SELECT s.sid, s.username,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a,
v$session s
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.session_id=s.sid
GROUP BY s.sid, s.username
ORDER BY total_wait_time DESC;

       SID USERNAME                       TOTAL_WAIT_TIME
———- —————————— —————
773 APPS                                 877360543
670 APPS                                 374767126
797                                       98408003
713 APPS                                  97655307
638 APPS                                  53719218
726 APPS                                  39072236
673 APPS                                  29353667
762 APPS                                  29307261
746 APPS                                  29307183
653 APPS                                  14677170
675 APPS                                  14676426

Identifying SQL with the Highest Waits

Using the following query, you can identify the SQL that’s waiting the most in your instance with in last 15 mins

SELECT a.user_id,d.username,s.sql_text,
SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a,
v$sqlarea s,
dba_users d
WHERE a.sample_time between sysdate – 30/2880 and sysdate
AND a.sql_id = s.sql_id
AND a.user_id = d.user_id
GROUP BY a.user_id,s.sql_text, d.username;

Advertisements

Read Full Post »

When Oracle encounters a table with missing statistics, Oracle dynamically gathers the necessary statistics needed by the optimizer. Based on the settings of OPTIMIZER_DYNAMIC_SAMPLING parameter which is discussed in my previous topic optimizer gathers statistics and generate execution plan for the table with missing statistics.

Though in case of remote tables and external tables oracle does not perform dynamic sampling.

Whether dynamic sampling enabled or disabled the optimizer uses default values for its statistics.

The values are listed below.

A)Default Table Values When Statistics Are Missing
—————————————————————–
1)Cardinality=num_of_blocks * (block_size – cache_layer) / avg_row_len
2)Average row length=100 bytes
3)Number of blocks=100 or actual value based on the extent map
4)Remote cardinality=2000 rows
5)Remote average row length=100 bytes

B)Default Index Values When Statistics Are Missing
——————————————————————
1)Levels=1
2)Leaf blocks=25
3)Leaf blocks/key=1
4)Data blocks/key=1
5)Distinct keys=100
6)Clustering factor=800

Read Full Post »

•Before 10g you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. It is good to remember that starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

•The job GATHER_STATS_JOB automatically gather optimizer statistics.

•This job gathers statistics on all objects in the database which have either
-Missing statistics or
-Stale statistics

•This job is created automatically at database creation time and is managed by the Scheduler. By default GATHER_STATS_JOB runs every night from 10 P.M. to 6 A.M. and all day on weekends if missing statistics or stale statistics found.

•In fact the Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.

•The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.

•Database automatically collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

•If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL then database automatically gather statistics for the objects which has stale statistics. If it is set to BASIC then then the automatic statistics gathering job is not able to detect stale statistics.

•To know about job GATHER_JOB_STATS issue the following query,
SQL>select JOB_TYPE,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,END_DATE,ENABLED,STATE,RUN_COUNT FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ‘GATHER_STATS_JOB’;

JOB_TYPE SCHEDULE_TYP START_DATE REPEA END_DATE ENABL STATE RUN_COUNT
—————- ———— ———- —– ———- —– ——— ———-
WINDOW_GROUP TRUE SCHEDULED 31

To know the database creation date issue,
SQL> select created, sysdate from v$database;
CREATED SYSDATE
——— ———
06-MAY-08 12-JUN-08

So between May 06 and Jun 08 this job ran 31 times.

•In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. If STATISTICS_LEVEL is set to TYPICAL or ALL then monitoring is enabled. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view.

Like you can query,
select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS;

•Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

Example:
—————
SQL> insert into test values(‘hi’);
1 row created.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name=’TEST’;
no rows selected

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name=’TEST’;
TABLE_NAME INSERTS UPDATES DELETES
—————————— ———- ———- ———-
TEST 1 0 0

After analyze they will disappear,
SQL> analyze table test estimate statistics;
Table analyzed.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name=’TEST’;
no rows selected

•If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

Read Full Post »

•Optimizer statistics are the collection of data that describe more details about the database and the objects in the database.

•These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

•These statistics include tables, column, system, index level statistics. The following is listed with attributes that query optimizer use.

A)Table statistics
————————-
Number of rows
Number of blocks
Average row length

B)Column statistics
——————————
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)

C)Index statistics
——————————–
Number of leaf blocks
Levels
Clustering factor

D)System statistics
———————————-
I/O performance and utilization
CPU performance and utilization

•These optimizer statitics are are stored in the data dictionary like DBA_TABLES, DBA_TAB_STATISTICS, DBA_INDEXES, DBA_IND_STATISTICS etc.

•Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.

Read Full Post »

Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale.

There may be two scenarios in this case.
•Volatile tables that are being deleted or truncated and rebuilt during the course of the day.
•Objects which are the target of large bulk loads which add 10% or more to the object’s total size.

So you may wish to manually gather statistics of those objects in order to choose the optimizer the best execution plan. There are two ways to gather statistics.

1)Using DBMS_STATS package.
2)Using ANALYZE command.

A)Using DBMS_STATS package
——————————————–
The DBMS_STATS package have several procedures which help to generate statistics.

1)GATHER_DATABASE_STATS Procedures-Gathers statistics for all objects in the database

2)GATHER_DICTIONARY_STATS Procedure-Gathers statistics for dictionary schemas ‘SYS’, ‘SYSTEM’ and schemas of RDBMS components.

3)GATHER_FIXED_OBJECTS_STATS Procedure-Gathers statistics of fixed objects.

4)GATHER_INDEX_STATS Procedure-Gathers index statistics.

5)GATHER_SCHEMA_STATS Procedures-Gathers statistics for all objects in a schema.

6)GATHER_SYSTEM_STATS Procedure-Gathers system statistics.

7)GATHER_TABLE_STATS Procedure-Gathers table and column (and index) statistics.

8)GENERATE_STATS Procedure-Generates object statistics from previously collected statistics of related objects. The currently supported objects are only b-tree and bitmap indexes.

Example:
To gather statistics of all objects inside A schema use
SQL>EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘A’);
PL/SQL procedure successfully completed.

To gather statictics of table test in SAM schema use,
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SAM’,’TEST’);
PL/SQL procedure successfully completed.

2)Using Analyze:
————————
Oracle strongly recommend not use ANALYZE command to estimate statistics. Yet it is supported for backward compatibility. To generate statistics of TEST table using ANALYZE use ANALYZE with estimate statistics keyword.,

In this example I verified that statistics of table column num_rows contain information after analyzing.

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME=’TEST’;
NUM_ROWS
———-
2
SQL> INSERT INTO TEST VALUES(‘before’);
1 row created.

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME=’TEST’;
NUM_ROWS
———-
2

SQL> ANALYZE TABLE TEST COMPUTE STATISTICS;
Table analyzed.

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME=’TEST’;
NUM_ROWS
———-
3

After gather statistics num_rows contain accurate information.

Read Full Post »

1)Verify Optimizer Statistics:
——————————————–
The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.

So, gather statistics of all tables that are involved in SQL statements. You can check whether your statistics is up to date or not by querying
SELECT COUNT(*) FROM table_name;
and,
select NUM_ROWS from dba_tables where table_name=’TABLE_NAME’;

If they are almost same then you have correct optimizer statistics. If they don’t match then gather new statistics.

2)Review the Execution Plan:
—————————————
When tuning (or writing) a SQL statement, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.

We can check it by examine the optimizer execution plan following,

•The plan is such that the driving table has the best filter.

•The join order in each step means that the fewest number of rows are being returned to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).

•The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when many rows are being returned.

•Views are used efficiently. Look at the SELECT list to see whether access to the view is necessary.

•There are any unintentional Cartesian products (even with small tables).

•Each table is being accessed efficiently:

-Consider the predicates in the SQL statement and the number of rows in the table. Look for suspicious activity, such as a full table scans on tables with large number of rows, which have predicates in the where clause. Determine why an index is not used for such a selective predicate.

-A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.

If any of these conditions are not optimal, then consider restructuring the SQL statement or the indexes available on the tables.

3)Restructuring the SQL Statements
———————————————-
Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement.

While restructuring the SQL statements keep in mind of the following issues.

•Use equijoins whenever possible.
That is compose predicate using AND and =.

•Avoid Transformed Columns in the WHERE Clause.
That is use
a=b instead of to_number(a)=to_number(b)

When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement; if you have index on varcol

TO_CHAR(numcol) = varcol

rather than

varcol = TO_CHAR(numcol)

•Write Separate SQL Statements for Specific Tasks.
SQL is not a procedural language. Using one piece of SQL to do many different things usually results in a less-than-optimal result for each task. If you want SQL to accomplish different things, then write various statements, rather than writing one statement to do different things depending on the parameters you give it.

It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator.

•Use of EXISTS versus IN for Subqueries.
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

4)Controlling the Access Path and Join Order with Hints
————————————————————————————–
You can use hints in SQL statements to instruct the optimizer about how the statement should be executed. Hints, such as /*+FULL */ control access paths.

Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:

•Avoid a full-table scan if it is more efficient to get the required rows through an index.

•Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.

•Choose the join order so as to join fewer rows to tables later in the join order.

•Be careful when joining views, when performing outer joins to views, and when reusing an existing view for a new purpose.

•Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data.

•Beware of writing a view for one purpose and then using it for other purposes to which it might be ill-suited. Querying from a view requires all tables from the view to be accessed for the data to be returned. Before reusing a view, determine whether all tables in the view need to be accessed to return the data. If not, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. The goal is to refer to the minimum number of tables and views necessary to return the required data.

•An outer join within a view is problematic because the performance implications of the outer join are not visible.

•Consider using materialized views.

5)Restructuring the Indexes
———————————————–
Often, there is a beneficial impact on performance by restructuring indexes. This can involve the following:

Remove nonselective indexes to speed the DML.
Index performance-critical access paths.
Consider reordering columns in existing concatenated indexes.
Add columns to the index to improve selectivity.

6)Modifying or Disabling Triggers and Constraints
———————————————————–
Using triggers consumes system resources. If you use too many triggers, then you can find that performance is adversely affected and you might need to modify or disable them.

7)Restructuring the Data
—————————————–
After restructuring the indexes and the statement, you can consider restructuring the data.

Introduce derived values. Avoid GROUP BY in response-critical code.

Review your data design. Change the design of your system if it can improve performance.

Consider partitioning, if appropriate.

8)Combine Multiples Scans with CASE Statements
———————————————————–
Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance.

9)Maintaining Execution Plans Over Time
——————————————————-
You can maintain the existing execution plan of SQL statements over time either using stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.

Read Full Post »