How to use TOP command to identify the top server consuming resources

You have a Linux server that hosts multiple databases. Users are reporting sluggishness with an application that uses one of the databases. You want to identify which processes are consuming the most resources on the server and then determine if the top consuming process is associated with a database.

for this top command shows a real-time display of the highest resource-consuming processes on a server. Here’s the simplest way to run top:

$top

Below are the partial output:

Type q or press Ctrl+C to exit top. In the prior output, the first section of the output displays general system information such as how long the server has been running, number of users, CPU information, and so on.

The second section shows which processes are consuming the most CPU resources (listed top to bottom). In the prior output, the process ID of 19888 is consuming a large amount of CPU. To determine which database this process is associated with, use the ps command:

In the prior output, the fourth column displays the value of oracleO11R2. This indicates that this is an Oracle process associated with the O11R2 database. If the process continues to consume resources, you can next determine if there is a SQL statement associated with the process or terminate the process.

the top utility is often the first investigative tool employed by DBAs and system administrators to identify resource-intensive processes on a server. If a process is continuously consuming excessive system resources, then you should further determine if the process is associated with a database and a specific SQL statement.

By default, top will repetitively refresh (every few seconds) information regarding the most CPUintensive processes. While top is running, you can interactively change its output. For example, if you type >, this will move the column that top is sorting one position to the right. Below table lists the most useful hot key features to alter the top display to the desired format

you can get the help from below table to understand what is the meaning of several shortcuts mentioned in the top command.

ORA-09817: Write to audit file failed Linux Error: 28: No space left on device

Users are reporting that they can’t connect to a database. You log on to the database server, attempt to connect to SQL*Plus, and receive this error:
ORA-09817: Write to audit file failed.
Linux Error: 28: No space left on device
Additional information: 12

You want to quickly determine if a mount point is full and where the largest files are within this mount point.

To resove this, use the df command to identify disk space issues. This example uses the -
h to format the output so that space is reported in megabytes or gigabytes:

$ df –h

Here is some sample output:

The prior output indicates that the root (/) file system is full on this server. In this situation, once a full mount point is identified, then use the find command to locate the largest files contained in a directory structure. This example navigates to the ORACLE_HOME directory and then connects the find, ls, sort, and head commands to identify the largest largest top 10 files beneath that directory:

$ cd $ORACLE_HOME

$ find . -ls | sort -nrk7 | head -10

If you have a full mount point, also consider looking for the following types of files that can be moved or removed:

• Deleting database trace files

• Removing large Oracle Net log files

• Moving, compressing, or deleting old archive redo log files

• Removing old installation files or binaries

• If you have datafiles with ample free space, consider resizing them to smaller sizes

Another way to identify where the disk space is being used is to find the largest space-consuming directories beneath a given directory. This example combines the du, sort, and head commands to show the ten largest directories beneath the current working directory:

$ du -S . | sort -nr | head -10

The prior command is particularly useful for identifying a directory that might not necessarily have large files in it, but lots of small files consuming space (like trace files).

How to increase index creation speed

Suppose you’re creating an index based on a table that contains millions of rows. You want to create the index as fast as possible.

So here is the solution for increasing the speed of index creation:

• Turning off redo generation

• Increasing the degree of parallelism

You can use the prior two features independently of each other, or they can be used togather.

Turning Off Redo Generation

You can optionally create an index with the NOLOGGING clause. Doing so has these implications:

• The redo isn’t generated that would be required to recover the index in the event of a media failure.

• Subsequent direct-path operations also won’t generate the redo required to recover the index information in the event of a media failure.

Here’s an example of creating an index with the NOLOGGING clause:

create index inv_idx1 on inv(inv_id, inv_id2)
nologging
tablespace inv_mgmt_index;

You can run this query to determine whether an index has been created with NOLOGGING:

SQL> select index_name, logging from user_indexes;

Increasing the Degree of Parallelism

In large database environments where you’re attempting to create an index on a table that is populated with many rows, you may be able to reduce the time it takes to create the index by using the PARALLEL clause. For example, this sets the degree of parallelism to 2 when creating the index:

create index inv_idx1 on inv(inv_id)
parallel 2
tablespace inv_mgmt_data;

You can verify the degree of parallelism on an index via this query:

SQL> select index_name, degreel from user_indexes;

How It Works

The main advantage of NOLOGGING is that when you create the index, a minimal amount of redo information is generated, which can have significant performance implications when creating a large index. The disadvantage is that if you experience a media failure soon after the index is created (or have records inserted via a direct-path operation), and subsequently have a failure that causes you to restorefrom a backup (taken prior to the index creation), then you may see this error when the index is accessed:

ORA-01578: ORACLE data block corrupted (file # 4, block # 11407)
ORA-01110: data file 4: '/ora01/dbfile/O11R2/inv_mgmt_index01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

This error indicates that the index is logically corrupt. In this scenario, you must re-create or rebuild the index before it’s usable. In most scenarios, it’s acceptable to use the NOLOGGING clause when creating an index, because the index can be re-created or rebuilt without affecting the table on which the index is based.

In addition to NOLOGGING, you can use the PARALLEL clause to increase the speed of an index creation. For large indexes, this can significantly decrease the time required to create an index.

Keep in mind that you can use NOLOGGING in combination with PARALLEL. This next example rebuilds an index in parallel while generating a minimal amount of redo:

SQL> alter index inv_idx1 rebuild parallel nologging;

How to monitor index usages in the database

You maintain a large database that contains thousands of indexes. As part of your proactive
maintenance, you want to determine if any indexes are not being used. You realize that unused indexes have a detrimental impact on performance, because every time a row is inserted, updated, and deleted, the corresponding index has to be maintained. This consumes CPU resources and disk space. If an index isn’t being used, it should be dropped.

So here is the solution:

Use the ALTER INDEX...MONITORING USAGE statement to enable basic index monitoring. The following example enables index monitoring on an index named F_REGS_IDX1:

SQL> alter index f_regs_idx1 monitoring usage;

The first time the index is accessed, Oracle records this; you can view whether an index has been accessed via the V$OBJECT_USAGE view. To report which indexes are being monitored and have ever been used, run this query:

SQL> select index_name, table_name, monitoring, used from v$object_usage;

If the index has ever been used in a SELECT statement, then the USED column will contain the YES value. Here is some sample output from the prior query:

INDEX_NAME   TABLE_NAME                MON   USED
-------------------------      ------------------------            --------          ------
F_REGS_IDX1     F_REGS                           YES           YES

Most likely, you won’t monitor only one index. Rather, you’ll want to monitor all indexes for a user. In this situation, use SQL to generate SQL to create a script you can run to turn on monitoring for all indexes. Here’s such a script:

set pagesize 0 head off linesize 132
spool enable_mon.sql
select
'alter index ' || index_name || ' monitoring usage;'
from user_indexes;
spool off;

To disable monitoring on an index, use the NOMONITORING USAGE clause—for example:

SQL> alter index f_regs_idx1 nomonitoring usage;

How It Works

The main advantage to monitoring index usage is to identify indexes not being used. This allows you to identify indexes that can be dropped. This will free up disk space and improve the performance of DML statements.

The V$OBJECT_USAGE view shows information only for the currently connected user. You can verify this behavior by inspecting the TEXT column of DBA_VIEWS for the  $OBJECT_USAGE definition:

SQL> select text from dba_views where view_name = 'V$OBJECT_USAGE';

Notice the following line in the output:

where io.owner# = userenv('SCHEMAID')

That line instructs the view to display information only for the currently connected user. If you’re logged in as a DBA privileged user and want to view the status of all indexes that have monitoring enabled (regardless of the user), execute this query:

select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;

The prior query removes the line from the query that restricts output to display information only for the currently logged-in user. This provides you with a convenient way to view all monitored indexes.

How to create function based index and how it works

Suppose a query is running slow. You examine the WHERE clause and notice that a SQL UPPER function has been applied to a column. The UPPER function blocks the use of the existing index on that column. You want to create a function-based index to support the query. Here’s an example of such a query:

SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';

You inspect USER_INDEXES and discover that an index exists on the FIRST_NAME column:

select index_name, column_name
from user_ind_columns
where table_name = 'CUST';

INDEX_NAME COLUMN_NAME
-------------------- --------------------
   CUST_IDX1    FIRST_NAME


You generate an explain plan via SET AUTOTRACE TRACE EXPLAIN and notice that with the UPPER function applied to the column, the index is not used:

You need to create an index that Oracle will use in this situation.

So here are the solution. There are two ways to resolve this issue:

• Create a function-based index.

• If using Oracle Database 11g or higher, create an indexed virtual column.

This solution focuses on using a function-based index. You create a function-based index by
referencing the SQL function and column in the index creation statement. For this example, a functionbased index is created on UPPER(name):

SQL> creae index cust_fidx1 on cust(UPPER(first_name));

To verify if the index is used, the Autotrace facility is turned on:

SQL> set autotrace trace explain;

Now the query is executed:

SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';

Here is the resulting execution plan showing that the function-based index is used:

How It Works

Function-based indexes are created with functions or expressions in their definitions. Function-based indexes allow index lookups on columns referenced by SQL functions in the WHERE clause of a query.

How to create concatenated index and how it works

Suppose you have a combination of columns (from the same table) that are often used in the WHERE clause of several SQL queries. For example, you use LAST_NAME in combination with FIRST_NAME to identify a customer:

select last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'STEVE';

You wonder if it would be more efficient to create a single concatenated index on the combination of LAST_NAME and FIRST_NAME columns or if performance would be better if two indexes were created separately on LAST_NAME and FIRST_NAME.

So here is the solution, when frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes. For this example, here’s the table creation script:

create table cust(
cust_id number primary key
,last_name varchar2(30)
,first_name varchar2(30));

Here’s an example of a concatenated index created on LAST_NAME and FIRST_NAME:

SQL> create index cust_idx1 on cust(last_name, first_name);

To determine whether the concatenated index is used, several rows are inserted (only a subset of the rows is shown here):

SQL> insert into cust values(1,'SMITH','JOHN');
SQL> insert into cust values(2,'JONES','DAVE');
..........
SQL> insert into cust values(3,'FORD','SUE');

Next, statistics are generated for the table and index:

SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT',-
tabname=>'CUST',cascade=>true);

Now Autotrace is turned on so that the execution plan is displayed when a query is run:

SQL> set autotrace on;

Here’s the query to execute:

select last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';

Listed next is an explain plan that shows the optimizer is using the index:

The prior output indicates that an INDEX RANGE SCAN was used to access the CUST_IDX1 index. Notice that all of the information required to satisfy the results of this query was contained within the index. The table data was not required. Oracle accessed only the index.

One other item to consider: suppose you have this query that additionally selects the CUST_ID column:

select cust_id, last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';

If you frequently access CUST_ID in combination with LAST_NAME and FIRST_NAME, consider adding CUST_ID to the concatenated index. This will provide all of the information that the query needs in the index. Oracle will be able to retrieve the required data from the index blocks and thus not have to access the table blocks.

How It Works

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as concatenated indexes. These indexes are especially effective when you often use multiple columns in the WHERE clause when accessing a table. Here are some factors to consider when using concatenated indexes:

• If columns are often used together in the WHERE clause, consider creating a concatenated index.

• If a column is also used (in other queries) by itself in the WHERE clause, place that column at the leading edge of the index (first column defined).

• Keep in mind that Oracle can still use a lagging edge index (not the first column defined) if the lagging column appears by itself in the WHERE clause (see the next few paragraphs here for details).

The optimizer uses a concatenated index even if the leading edge column(s) aren’t present in the WHERE clause. This ability to use an index without reference to leading edge columns is known as the skip-scan feature. For example, say you have this query that uses the FIRST_NAME column

SQL> select last_name from cust where first_name='DAVE';

Here is the corresponding explain plan showing that the skip-scan feature is in play:

A concatenated index that is used for skip-scanning is more efficient than a full table scan. However, if you’re consistently using only a lagging edge column of a concatenated index, then consider creating a single-column index on the lagging column.

How to create foreign key index and how it works

Suppose a large number of the queries in your application use foreign key columns as predicates in the WHERE clause. Therefore, for performance reasons, you want to ensure that you have all foreign key columns indexed.

So unlike primary key constraints, Oracle does not automatically create indexes on foreign key columns. For example, say you have a requirement that every record in the ADDRESS table be assigned a corresponding CUST_ID column that exists in the CUST table. To enforce this relationship, you create a foreign key constraint on the ADDRESS table as follows:

alter table address add constraint addr_fk1
foreign key (cust_id) references cust(cust_id);

You realize the foreign key column is used extensively when joining the CUST and ADDRESS tables and that an index on the foreign key column will dramatically increase performance. You have to manually create an index in this situation. For example, a regular B-tree index is created on the foreign key column of CUST_ID in the ADDRESS table:

SQL> create index addr_fk1 on address(cust_id);

You don’t have to name the index the same as the foreign key name (as we did in the prior lines of code). It’s a personal preference as to whether you do that. We feel it’s easier to maintain environments when the constraint and corresponding index have the same name.

How It Works

Foreign keys exist to ensure that when inserting into a child table, a corresponding parent table record exists. This is the mechanism to guarantee that data conforms to parent/child business relationship rules. From a performance perspective, it’s usually a good idea to create an index on foreign key columns. This is because parent/child tables are frequently joined on the foreign key column(s) in the child table to the primary key column(s) in the parent table—for example:

select
a.last_name, a.first_name, b.state
from cust a
,address b
where a.cust_id = b.cust_id;

In most scenarios, the Oracle query optimizer will choose to use the index on the foreign key column to identify the child records that are required to satisfy the results of the query. If no index exists, Oracle has to perform a full table scan on the child table.

If you’ve inherited a database, then it’s prudent to check if columns with foreign key constraints defined on them have a corresponding index. The following query displays indexes associated with foreign key constraints: