How do I test the performance of indexes in Oracle and what can I use to test them?

For example, if you have a people table, let's say you have 10w rows of data.

You start by using no index, and then query by person's name.

Then create a name index, and then use the name to query and compare the time required by the two.

SQL statements with multiple tables and multiple conditions, it is difficult to establish an index to make the index effective, first specify the amount of data in your table, and then the data obtained by the query conditions does not exceed 30%, and the execution plan is based on the amount of data in the table and the number of conditions met, and the execution plan is different for different data amounts and conditions.

For testing, first debug whether to follow the index of a single statement, and then use the performance automation tool to actually run it to see the effect.

I think it's just that the table has a large amount of data, which is not conducive to querying and needs to be indexed when you need to associate with other tables.

This field is infrequently used as a query criterion or is heavily repeated, such as gender.

Data is frequently inserted, deleted, and updated.

If the amount of data is too small, there is no need to create an index.

The amount of data in the table is large and slow to query, so you can add indexes to the criteria used for the query to improve the query speed.

If you want to use this field as the query condition, you need to create an index. However, it should be noted that if you need to convert the field to case and then match, you need to consider building a function index. In addition, if you do not specify multiple indexes of the same table at the same time, you should create an index on a field that allows you to filter records based on the search conditions.

In addition, indexes are mainly used to improve query efficiency, but they can reduce performance for inserts and updates (especially for fields that are updated very frequently), so you can't create indexes arbitrarily.

You can use Oracle's built-in OEM tools to view Oracle's real-time dynamic performance.

Steps: Open any browser, enter https: full name of your computer: port (default is 1158) em console logon logon

The above is Oracle 11g, if it is Oracle 12c, it is slightly different, just remove the console logon logon.

Oracle performance judgment needs to be judged based on multiple operating metrics of the database

1. The number of processes and CPU occupancy: This mainly depends on whether the CPU is occupied for a long time. It is usually judged that the SQL is large and needs to be optimized; This can be viewed with the execution plan or the AWR report;

2. Memory usage: mainly use system commands to view the ratio of ORA occupation to total system memory, and the utilization rate of swap; Usually, it's okay if the swap usage rate is low; This one mainly uses system commands;

3. Disk occupancy: To prevent insufficient disk space, the main needs are in the system and user tablespace, RMAN and other operations; This one mainly uses system commands; rman command.

Index index is required when retrieving part of data from a large amount of data by criteria, or for queries such as select count (field) that only require indexes from that field

If you feel that your SQL efficiency is problematic, then you can create corresponding indexes to improve efficiency. There is no one standard situation.

I don't know how to monitor how many times the index is used, but if you want to monitor whether the index is being used, you can do it in the following way.

If you want to monitor whether the index is being used, you need to enable the monitoring index alter index **monitoring usage; is your index name.

You can then monitor the index.

select * from v$object usage statement.

If you use indexes after you enable monitoring, the view will be recorded, otherwise there will be no records.

There are special features in PL SQL for testing. Please refer to their help documentation for details.

Let's say you define a function:

create function y2

inx2 number)

return number is

result number(2);

beginresult := inx2*inx2;

return(result);

end y2;

It can be referenced after the query statement, for example, it is defined as a function that calculates the square y2select y2(4) from dual;

Results: 16