An example of using composite index to solve performance problems

An example of using composite index to solve performance problems

Post from: http://www.laoxiong.net/composite_index_overcome_performance_problem.html

 

Old bear's blog (http://www.laoxiong.net)

 

Oracle performance optimization December 8 th, 2008

Fault occurrence time: October 30, December 6
System Environment: HP Superdome series, GB memory, 64 CPU, Oracle 9.2.0.8
Fault description: The CPU usage is nearly 100%, the running queue reaches 60-80, and the application response speed is very slow.

This is a core system of provincial Telecom.

After the user reflected that the speed was very slow, the host check found that the CPU was very high, nearly 100%, and the running queue reached 60-80. Check Oracle and find that many sessions are waiting for latch free. latch # is 98

SQL> select * from V $ latchname where latch # = 98;

Latch # Name
--------------------------------------------------------------------------
98 cache buffers chains

Check the SQL statement that is being executed by the session waiting for latch free. Most of the SQL statements are similar to the following:

Select sum (CNT ),
To_char (nvl (sum (nvl (amount, 0)/100, 0), 'fm9999999999990. 90') Amount
From (select count (payment_id) CNT, sum (amount) Amount
From payment
Where staff_id = 592965
And created_date> = trunc (sysdate)
And state = 'c0c'
And operation_type in ('5ka', '5kb', '5kc', '5kp '))

It seems that this SQL statement is not complex. view its execution plan:

Plan_table_output
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Bytes --------------------------------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost | pstart | pstop |
Bytes --------------------------------------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 26 | 125k |
| 1 | sort aggregate | 1 | 26 |
| 2 | View | 1 | 26 | 125k |
| 3 | sort aggregate | 1 | 30 |
| * 4 | table access by global index rowid | payment | 19675 | 576k | 125k | rowid | row L |
| * 5 | index range scan | idx_payment_created_date8 | 1062k | 3919 |
Bytes --------------------------------------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

4-filter ("payment". "staff_id" = 521840 and "payment". "State" = 'c0c' and ("payment". "operation_type" = '5ka 'or
"Payment". "operation_type" = '5kb' or "payment". "operation_type" = '5kc 'or "payment". "operation_type" = '5kp '))
5-access ("payment". "created_date"> = trunc (sysdate @!))

Note: CPU costing is off

From the execution plan, we can see that the Oracle evaluation shows that the number of rows returned by index scanning is as high as 1 million, which is why many latch buffers chains latch contention occurs.
Check the index of the payment table:

SQL> select index_name, index_type from dba_indexes where table_name = 'payment' and table_owner = 'acct ';

Index_name index_type
---------------------------------------------------------
Idx_operated_payment_serial8 normal
Idx_payment_acct_id8 normal
Idx_payment_created_date8 normal
Idx_payment_payed_method8 normal
Idx_payment_payment_method8 normal
Idx_payment_serv_id8 normal
Idx_payment_staff_date8 normal
Idx_payment_state_date8 normal
Pk_payment13 normal

SQL> select index_name, column_name, column_position from
Dba_ind_columns where table_owner = 'act' and table_name = 'payment' order
By 1, 3;

Index_name column_name column_position
---------------------------------------------------------------------------
Idx_operated_payment_serial8 operated_payment_serial_nbr 1
Idx_payment_acct_id8 acct_id 1
Idx_payment_created_date8 created_date 1
Idx_payment_payed_method8 payed_method 1
Idx_payment_payment_method8 payment_method 1
Idx_payment_serv_id8 serv_id 1
Idx_payment_staff_date8 staff_id 1
Idx_payment_staff_date8 state_date 2
Pk_payment13 payment_id 1

The index idx_payment_created_date8 used in the execution plan is a single column index created on the created_date column.

This SQL statement has not encountered this problem before. Where is the problem?
If you are familiar with the telecommunications system, you will know that there will be a batch charge-off action after the account is issued, which leads to the use
The created_date> = truncate (sysdate) condition returns a large number of rows from the index scan. In fact, the number of rows filtered by other conditions after the table is returned
Only about 20 thousand rows (this is the evaluation data, and the actual data is far smaller than this ). Obviously, if we create a composite index, the number of rows returned by the index scan will be greatly reduced. Here staff_id
Fields are the best fields to create a composite index with created_date.

After a composite index is created on the columns staff_id and create_date, the system returns to normal immediately. However, staff_id is used as the leading column of the composite index.
According to this SQL statement, this will greatly reduce the number of logical reads on the index leaf block, and reduce the hotspot competition on the index leaf block (create_date is a one-way growth field ). As
In-depth analysis should be combined with applications for fault handling. The primary goal is to solve the current problem.

Summary:
Using suitable composite indexes can effectively reduce the number of rows returned by index scanning and improve performance.
Be familiar with the business of the application system to better understand the root cause of the problem and reduce the troubleshooting time.

 

 

Bytes -----------------------------------------------------------------------------------------

Bytes -----------------------------------------------------------------------------------------

The old bear has a high level and does not have much basic knowledge. Now, let's sort out the basic process of solving the problem:

1.You can use v $ session_wait to find the session wait event, and then use v $ session and V $ SQL to find the corresponding SQL statement, for example:

Select <br/> U. sid, <br/> substr (U. username, 1, 12) user_name, <br/> S. SQL _text <br/> from <br/> V $ SQL S, <br/> V $ session U <br/> where <br/> S. hash_value = u. SQL _hash_value <br/> and <br/> SQL _text not like '% from V $ SQL S, V $ session U %' <br/> order by <br/> U. sid;

The rest of the old bears are very careful.

Pay tribute to the old bear!