Current Sessions
Obviously, when there are performance issues, it is necessary to take a look
at the current sessions on the database. There is no sp_who, sp_who2, or
sp_lock in Oracle, but there is the v$session view. This view shows
which sessions are active. You can join this with another view to see which
queries a session is running.
SQLPLUS> select username, schemaname,osuser, lockwait,status
from v$session
where status='ACTIVE' and username not in ('SYS','SYSTEM');
USERNAME SCHEMANAME OSUSER LOCKWAIT STATUS

DBSNMP DBSNMP oracle (null) ACTIVE
MMALCHER MMALCHER mmalcher (null) ACTIVE
USER1 APP1 user1 (null) ACTIVE
## Lockwait will be a non-null value when waiting on a resource
## such as a lock or a latch
## Another view to see this would be v$session_wait
## To see a SQL statement from one of the users currently active
SQLPLUS> select sa.sql_text
from v$sqlarea sa, v$sqltext st, v$session s
where sa.sql_id=st.sql_id
and s.sql_hash_value=st.hash_value and s.username='SCOTT';
SQL_TEXT

INSERT INTO log_messages (id,service,processed_date, log_date)
VALUES(:"SYS_B_0",:"SYS_B_1",TO_TIMESTAMP(:"SYS_B_2",:"SYS_B_3"),
:"SYS_B_4",TO_DATE(:"SYS_B_5",:"SYS_B_6"))
INSERT INTO log_messages (id,service,processed_date,log_date)
VALUES(:"SYS_B_0",:"SYS_B_1",TO_TIMESTAMP(:"SYS_B_2",:"SYS_B_3"),
:"SYS_B_4",TO_DATE(:"SYS_B_5",:"SYS_B_6"))
## To see what locks are current on an object

SQLPLUS> select session_id, owner, type, mode_held,
mode_requested
from dba_ddl_locks;
SESSION_ID OWNER TYPE MODE_HELD MODE_REQUESTED

871 YELL1 Table/Procedure/Type Null None
627 SNAP Table/Procedure/Type Null None
284 SNAP Table/Procedure/Type Null None
286 ADB Table/Procedure/Type Null None
357 ADB 18 Null None
222
Oracle Database Administration for Microsoft SQL Server DBAs
Activity Monitors
In OEM, under the Performance tab, you’ll find additional monitoring links
for looking at the top activity, instance activity, blocking sessions, and
currently running SQL, as shown in Figure 8-1. There are statistics that are
gathered as part of the Automatic Workload Repository to provide reports
for analyzing the health of the database and looking for performance issues.
The historical views are based on snapshots that have been gathered. (The
Automatic Workload Repository is discussed a little later in the chapter.)
Viewing these areas of activity can help you to troubleshoot performance
issues by pointing to an area that might be responding slowly or be
experiencing an overload, such as too many physical I/Os or hard parsing of
SQL statements.
Figure 8-2 shows the Top Activity section. This has information about the
resources that are being used, top SQL statements, and top sessions in the
database. This can be the current information or information from another
point in time. Viewing historical information is useful when a user drops by
in the afternoon to say that he was having issues in the morning, although
everything is fine now. From here, you can drill down to the SQL statements

and look into SQL tuning. Drilling down on the session ID or SQL hash
value can get back to the SQL being run. (Remember the SQL_hash_
value from the v$session table from the example under the Current
Sessions section?)
The Instance Activity section shows values since the database has been
up and running or back until the last snapshot that is available. By default,
these snapshots are kept seven days. Figure 8-3 has a chart of instance
activity about cursors, transactions, physical and logical I/O, and other
activity. It is useful to see the workload on the database server and look at
what is currently running, as well as the snapshots.
Chapter 8: Performance and Tuning
223
FIGURE 8-1.
OEM monitoring links
224
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 8-2.
OEM Top Activity section
FIGURE 8-3.
OEM Instance Activity section
Waits
Another area to check in Oracle for performance issues is wait events. This
information will be available in the OEM views we just looked at, and also
available to query in the v$ views such as v$session_wait. Here are a
few examples of quick queries to check for current database wait events:
SQLPLUS> select sid, event, p1text, wait_time , p1, p2
from v$session_wait;
SID EVENT P1TEXT WAIT_TIME P1 P2

378 SQL*Net message from client 0 1952673792 1

385 SQL*Net message from client 0 1413697536 1
431 buffer busy waits component 429 3 123
## p1 and p2 will provide additional information about
## the p1text so if it is an i/o wait, would have data file
## or could be about a latch type or object
SQLPLUS> select segment_name, segment_type
from dba_extents where file_id = 3
and 123 between (block_id and block_id + blocks -1);
SEGMENT_NAME SEGMENT_TYPE

IDX2_SALES_PROD INDEX
SQLPLUS> select event, total_waits, time_waited
from v$system_event;
EVENT TOTAL_WAITS TIME_WAITED

db file sequential read 3591611 2309586
SQL*Net message from client 3950929 1892
log file sync 182955 1134406
Some waits give clues to performance issues; others are normal events
that are to be expected. For example, a db file sequential read event is a
block read by indexes. So the indexes are being used instead of full-table
scans. On the other hand, db file scattered read waits could indicate the use
of full-table scans. However, you should gather more information if you see
that the waits are too high because of I/O issues.
The SQL*Net message from client event is the wait for the client to tell
the database server to do something. It is just waiting for instructions, and
really isn’t contributing to issues—you can’t blame the database for being
slow because a session was waiting for an action while the user went to get
coffee. There might be applications that open sessions and then just wait for
responses before getting data from the database.

Chapter 8: Performance and Tuning
225
We’ll look at some other ways to check waits in the “Automatic
Workload Repository” section later in this chapter.
SQL Plans
As a DBA, you know that tuning SQL statements is a good place to start
improving performance. If changes can be made to the code or available
indexes, that is normally the quickest way to get results.
With SQL Server, you can get the execution plan from SQL Server
Management Studio or by enabling showplan_all. This will provide
information about the plan for which order to join the tables and the
indexes to use.
Oracle plans are available through the OEM tools, and they can be
traced through SQL*Plus as well. The plans can be saved in the plan table
or just seen in the output from the trace.
Viewing Explain Plans
Let’s first look at a quick way to use SQL*Plus to see the explain plan for
a query.
## To see query results and the execution plan set autotrace
SQLPLUS> set autotrace on explain
SQLPLUS> select empno from emp where deptno=10;
EMPNO

7782
7839
7934
Execution Plan

Plan hash value: 3956160932


| Id | Operation | Name | Rows |Cost (%CPU)| Time

| 0 | SELECT STATEMENT | | 5 | 3 (0)| 00:00:01
|* 1 | TABLE ACCESS FULL| EMP | 5 | 3 (0)| 00:00:01

Predicate Information (identified by operation id):

1 - filter("DEPTNO"=10)
## Traceonly will not execute the query but just
## show the plan
226
Oracle Database Administration for Microsoft SQL Server DBAs
SQLPLUS> set autotrace traceonly explain
SQLPLUS> select empno from emp where deptno=10;
Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Cost (%CPU)| Time

| 0 | SELECT STATEMENT | | 5 | 3 (0)| 00:00:01
|* 1 | TABLE ACCESS FULL| EMP | 5 | 3 (0)| 00:00:01

Predicate Information (identified by operation id):

1 - filter("DEPTNO"=10)
## Only difference was that the rows were not returned
## Notice the plan hash is the same
## Add index to see new plan
SQLPLUS> create index idx_emp1 on emp(deptno);

Index created.
SQLPLUS> select empno from emp where deptno=10;
Execution Plan

Plan hash value: 306890541 ## NEW PLAN VALUE

| Id | Operation | Name | Rows | Cost (%CPU)| Time

| 0 | SELECT STATEMENT | | 5 | 2 (0)| 00:00:01
| 1 | TABLE ACCESS BY
INDEX ROWID| EMP | 5 | 2 (0)| 00:00:01
|* 2 | INDEX RANGE SCAN | IDX_EMP| 5 | 1 (0)| 00:00:01

Predicate Information (identified by operation id):

2 - access("DEPTNO"=10)
The plan table, if it does not already exist, can be created from the SQL
provided in ORACLE_HOME/rdbms/admin/utlxplan.sql.
## To put the execution plan into the plan table for viewing
SQLPLUS> explain plan set statement_id='my_example' for
select * from claim where claim_id=100;
Explained.
## To see the results
SQLPLUS> select * from table(dbms_xplan.display);
Chapter 8: Performance and Tuning
227
PLAN_TABLE_OUTPUT

Plan hash value: 3956160932


| Id | Operation | Name | Rows |Cost (%CPU)| Time

| 0 | SELECT STATEMENT | | 5 | 3 (0)| 00:00:01
|* 1 | TABLE ACCESS FULL|CLAIM | 5 | 3 (0)| 00:00:01

You can also see the explain plan through the SQL Developer GUI. In
Figure 8-4, the icons to get the explain plan are circled.
Tuning Using Explain Plans
In the explain plan, look for how the tables and indexes are accessed. Also
see if the indexes being used are the ones expected and if there are other
options that would be better.
228
Oracle Database Administration for Microsoft SQL Server DBAs
FIGURE 8-4.
Viewing an explain plan in SQL Developer
The explain plan will show the method used to access indexes:
■
A unique scan is normally seen with an unique index.
■
A range scan is used when multiple values could be returned for the
nonunique index.
■
A full or fast full scan of the index may be performed when more
data is being accessed or it is just more efficient to scan all the data
instead of sorting for the range scan.
For tables, the quickest access method is by row ID. Seeing full-table
scans might mean that you can use indexes to improve performance. How
the tables are being joined also affects the query. The methods for joining
tables include sort merge join, nested loops, and hash join.
The cost-based optimizer (CBO) pulls in the details about the system and

objects to create execution plans. The CBO evaluates the details, and then it
does transformation of the statements for complex queries. In performing
these steps, it also calculates the cost to choose the access paths, join orders,
and join methods. The cost of the query is included in the explain plan, which
can help you with tuning and knowing which plan might be better.
The object statistics are needed for the CBO to be able to create the best
execution plan. Incomplete or stale information could cause the optimizer
to use a full-table scan or an inefficient method to access indexes. To make
the CBO’s job easier, you should ensure that the queries are coded the best
they can be, the statistics are updated, and the required indexes are present.
NOTE
Earlier versions of Oracle used a rule-based
optimizer (RBO). Then in the next couple of
Oracle versions, you could choose between the
RBO and CBO, and even let Oracle choose the
best method. There were even times when
deleting statistics from a table or using the
RULE
hint (no longer available with Oracle Database
11
g
) would improve performance. Now, with
the automated statistics gathering and the use of
the information that is available, as well as being
able to do some transformations of the queries,
the CBO has gotten smarter and can create
efficient query plans.
Chapter 8: Performance and Tuning
229
You can use hints to suggest another path or direction for the CBO. Here

are a couple examples:
## Hint to just append the rows to the end on an insert
SQLPLUS> insert /*+ APPEND */ into table1 select …
## Hint to use an index
SQLPLUS> select /*+ INDEX(a) */ col1, col2 from a where …
However, you should be careful about using hints. They might get you a
performance boost for the current plan, but upgrades and other information
might come along, and the CBO could have better information about a
faster plan to use.
The CBO definitely needs valid statistics and information, and there are
database parameters that can help to decide the best execution plans.
Statistics for Tables and Indexes
Because of the CBO, statistics is one of the first areas that I validate when
looking at performance. Do the index and table have statistics? Are they
current statistics? Do the row counts in the statistics match the current row
count? The row counts could point to a load happening after or during the
time statistics are being generated. The CBO uses the information available,
and if the information is not current or valid, that will not lead to good
execution plans.
SQLPLUS> select num_rows, last_analyzed
from dba_tab_statistics
where table_name='SALES';
NUM_ROWS LAST_ANALYZED

1490 15-MAR-10
SQLPLUS> select count(1) from sales;
COUNT(1)

3919232
## Actual row count and number of rows in statistics different

## gathering statistics could be useful here.
You may consider making adjustments to the sample size and the
frequency of the statistics collection. The statistics can also be adjusted to
improve plans and methods of scans and access. Specific values can be set
230
Oracle Database Administration for Microsoft SQL Server DBAs
for an index such that it appears that there are more or less distinct values or
a different number of rows.
SQLPLUS> exec dbms_stats.set_column_stats('SCHEMA1','TAB1',
'COL1', DISTCNT => 8, NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.
## Once set the statistics can be locked
## Now lock statistics
SQLPLUS> exec dbms_stats.lock_table_stats('SCHEMA1','TAB1');
PL/SQL procedure successfully completed.
## Or if didn’t help just unlock and gather the stats again
SQLPLUS> exec dbms_stats.unlock_table_stats('SCHEMA1','TAB1');
PL/SQL procedure successfully completed.
SQLPLUS> exec dbms_stats.gather_table_stats('SCHEMA1','TAB1',
CASCADE => TRUE);
PL/SQL procedure successfully completed.
## Or restore the statistics (schema, table and timestamp)
SQLPLUS> exec dbms_stats.restore_table_stats('SCHEMA1',
'TAB1','12-MAR-10 06.40.33.900462 PM -05:00');
PL/SQL procedure successfully completed.
## Number of rows might be useful if a table is loaded as a
## batch process and starts off with zero each time.
SQLPLUS> exec dbms_stats.set_table_stats('SCHEMA1','TAB2',
NUMROWS => 4000000, NO_INVALIDATE => FALSE);
Taking a look in the area of table and index statistics is well worth the

time when it comes to tuning current statements. You do need to be careful
to avoid overdoing adjustments or trying to outsmart the CBO. However,
when other options are exhausted, a gentle nudge in one direction can help
improve the execution of the queries.
Database Parameters
Not to say that these are the only parameters to look at when tuning queries,
but Figure 8-5 has the list of database parameters that are classified in the
optimizer area. For most parameters, the Help column includes a link to
specific information about the parameter’s default value and use.
Chapter 8: Performance and Tuning
231