Oracle performance optimization is sometimes so simple _ index

Oracle performance optimization is sometimes so simple _ index

Performance optimization is sometimes so simple

I. Overview

Recently, the CPU of a system (aix6.1 + Oracle10.2.0.5 RAC) reaches every five minutes. This system is a monitoring system and takes the number of all running systems every five minutes, in fact, this was the case a year ago. I only sent frequently executed statements to the project team, and adjusted the SGA size for the system runtime environment, it's just that no alarm is triggered (if the cpu usage exceeds 90%. However, if this problem occurs in the past few days, you can continue to send frequently executed SQL statements to the project team. As all monitored systems send data to the system every five minutes, it is a matter of course that the system ignores this feature. However, in the past two days, alert messages are sent once every five minutes, and the text messages are restored. You are not bothered by yourself, and the leaders are also annoyed, let's take a look (although it's hard to catch up with SQL statements)

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Ii. handling process

By extracting AWR for a period of time, we find that the following statements are executed most frequently, and of course they consume the most cpu resources.

SELECT V1400, H, L, A, to_char (HTIME, 'yyyy-mm-dd hh24: mi: ss'), to_char (LTIME, 'yyyy-mm-dd hh24: mi: ss') from xxxdba. oracletableavailable where resourceid =: 1 and time = to_date (: 2, 'yyyy-mm-dd ')

At first glance, there are bound variables, and AWR captured several time periods found that the names of the statements executed each time are not exactly the same. Let the project team look at them. You can check the table information.

SQL> desc XXXDBA. ORACLETABLEAVAILABLE

Name Null? Type

---------------------------------------

Resourceid not null VARCHAR2 (128)

TIME NOT NULL DATE

V0000 NUMBER

V0005 NUMBER

V0010 NUMBER

V0015 NUMBER

V0020 NUMBER

V0025 NUMBER

V0030 NUMBER

V0035 NUMBER

V0040 NUMBER

......................................

V2355 NUMBER

H NUMBER

L NUMBER

A NUMBER

HTIME DATE

LTIME DATE

 

The first reaction is whether the table will be scheduled or update columns according to the condition. Let's ignore it first. Let's look at the execution plan and find that it takes so long to execute a full table scan.

SQL> explain plan for SELECT V0800, H, L, A, to_char (HTIME, 'yyyy-mm-dd hh24: mi: ss'), to_char (LTIME, 'yyyy-mm-dd hh24: mi: ss') from xxxdba. oracletableavailable where resourceid =: 1 and time = to_date (: 2, 'yyyy-mm-dd ');

Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT

Certificate quota ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1457290298

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

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

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

| 0 | select statement | 1 | 65 | 18311 (1) | 00:03:40 |

| * 1 | table access full | ORACLETABLEAVAILABLE | 1 | 65 | 18311 (1) | 00:03:40 |

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

Predicate Information (identified by operation id ):

---------------------------------------------------

1-filter ("RESOURCEID" =: 1 AND "TIME" = TO_DATE (: 2, 'yyyy-mm-dd '))

13 rows selected.

 

View the number of rows in the table

SQL> select count (*) from XXXDBA. ORACLETABLEAVAILABLE;

COUNT (*)

----------

326796

 

Check whether there is any relevant index, and there is no index. It is reasonable to say that this table is not too large, but it takes too many times to execute. It will be faster to add an index.

SQL> select owner, index_name, index_type, table_name from dba_indexes where table_name = 'oracletableavailable ';

No rows selected

 

Ask the project team and find that the table collects time point data of the monitored system and updates the table accordingly. The column name remains unchanged. In the query statement, the RESOURCEID column in the Condition Statement column is unique, we recommend that you add an index to the project team. The index information is as follows:

SQL> select owner, index_name, index_type, table_name from dba_indexes where table_name = 'oracletableavailable ';

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME

---------------------------------------------------------------------------------------------------------------------

XXXDBA P_ORACLETABLEAVAILABLE NORMAL ORACLETABLEAVAILABLE

SQL> select dbms_metadata.get_ddl ('index', 'P _ ORACLETABLEAVAILABLE ', 'xxxdba') from dual;

DBMS_METADATA.GET_DDL ('index', 'P _ ORACLETABLEAVAILABLE ', 'xxxdba ')

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

Create unique index "XXXDBA". "P_ORACLETABLEAVAILABLE" ON "XXXDBA". "ORACLETABLEAVAILABLE" ("RESOURCEID", "TIME ")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE (INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT)

TABLESPACE "PERFORMANCE"

 

View the execution plan again.

SQL> explain plan for SELECT V0900, H, L, A, to_char (HTIME, 'yyyy-mm-dd hh24: mi: ss'), to_char (LTIME, 'yyyy-mm-dd hh24: mi: ss ')

2 from xxxdba. ORACLETABLEAVAILABLE

3 where resourceid =: 1 and time = to_date (: 2, 'yyyy-mm-dd ')

4;

Explained.

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT

Certificate quota ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1279632247

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

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

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

| 0 | select statement | 1 | 65 | 3 (0) | 00:00:01 |

| 1 | table access by index rowid | ORACLETABLEAVAILABLE | 1 | 65 | 3 (0) | 00:00:01 |

| * 2 | index unique scan | P_ORACLETABLEAVAILABLE | 1 | 2 (0) | 00:00:01 |

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

Predicate Information (identified by operation id ):

---------------------------------------------------

2-access ("RESOURCEID" =: 1 AND "TIME" = TO_DATE (: 2, 'yyyy-mm-dd '))

14 rows selected.
 

For more details, please continue to read the highlights on the next page:

1 2 Next Page