Practical Skills for Oracle Authentication

Database personnel handy series:Oracle CertificationPractical Tips: DML performance is low, one of the most serious reasons is the existence of useless indexes. All SQL insert, update, and delete operations will become slower when they need to modify a large number of indexes when each row of data is changed. Many Oracle engineers assign an index to a column of WHERE statements in an SQL query.

Several tips for using oracle

1. Identify useless indexes:

DML performance is low. One of the most serious causes is the existence of useless indexes. Insert all SQL statements,

The update and delete operations are slower when they need to modify a large number of indexes when each row of data is changed.

Many Oracle engineers assign an index to a column of WHERE statements in an SQL query.

Although this method can make SQL run faster, the function-based Oracle index makes the database administrator

The operator may overallocate the index on the row of the data table. Excessively allocating indexes seriously affects the performance of key Oracle data tables.

Before Oracle9i appeared, there was no way to determine which indexes were not used for SQL queries.

Oracle9i has a tool that allows you to monitor the use of indexes using the alter index Command.

Then you can search for unused indexes and delete them from the database.

The following is a script that enables the monitoring of all indexes in a system:

Spool run_monitor. SQL

Select 'alter Index' | owner | '.' | index_name | 'monitoring usage ;'

From dba_indexes

Where owner not in ('sys ', 'system ');

Spool off;

@ Run_monitor

Wait for a while until enough SQL statements are run on the database, and then you can query the new V $ OBJECT_USAGE view.

Select index_name, table_name, mon, used

From v $ object_usage;

Below, we can see that a column of V $ OBJECT_USAGE is called USED and its value is YES or NO.

It does not tell you how many times Oracle has used this index, but this tool is useful for identifying unused indexes.

SQL> select * from v $ object_usage where rownum <10;

INDEX_NAME TABLE_NAME monitoring used START_MONITORING END_MONITORING

ASD DIM_ACCT_ITEM_TYPE_TEMP yes no 01/15/2004 13:50:59

IDX_ACCOUNT_ACCESSORY_TARIFF1 ACCOUNT_ACCESSORY_TARIFF yes no 01/15/2004 13:50:59

IDX_ACCOUNT_QUOTA_LOG1 ACCOUNT_QUOTA_LOG yes no 01/15/2004 13:50:59
The Oracle Authentication skills are described as follows. If you have more and better methods, share them with everyone.