[Turn] Oracle SQL query suddenly slows down--case study

Transferred from: http://jingyan.baidu.com/article/8275fc868ce57946a03cf692.html

A sudden SQL execution slows down, takes 9 seconds, the application can not be changed, only from the database to solve

Step thinking:

1: See if SQL goes through the index

2: See if the index is invalid

3:hint forced to walk the index (only used to view the hint state, whether the query changes, the application can not be changed)

4: Collect all information about the table (including index)

5: Analyze all information (including index) of the table

6: Execute again and view

Note: Which user performs slower, which user to work with, so that accurate

Method/Step

2. 1

   See if SQL goes through the index

   The following is an abrupt query for a slower SQL statement:

   SELECT * FROM WWFF

   where Jgsj>=to_date (' 2014-10-26 00:00:00 ', ' yyyy-mm-dd HH24:Mi:SS ')

   and Sjzt=1 and Fjbj=3 and Fjr=1 and rownum <= 1

   Takes 9 seconds

   Note: An index has been created on the JGSJ field to view the execution plan and the table is not indexed for full table scan

   Steps to read

4. 2

   See if the index is invalid

   Select ' Alter index ' | | a.owner| | '. ' | | a.index_name| | ' rebuild nologging online; '

   From Dba_indexes A

   where A.table_name= ' WWFF '

   and a.status<> ' VALID '

   and a.partitioned<> ' YES '; --because the table is not a partitioned table

   Steps to read

6. 3

   Hint forced to walk the index (only used to view the hint state, whether the query changes, the application can not be changed)

   Select/*+index (WWFF idx$$_wwff_jgsj) */* from WWFF

   where Jgsj>=to_date (' 2014-10-26 00:00:00 ', ' yyyy-mm-dd HH24:Mi:SS ')

   and Sjzt=1 and Fjbj=3 and Fjr=1 and rownum <= 1

   Takes 0.03 seconds

   After forcing the index, it takes only 0.03 seconds, so the query's slower SQL must go to the index

   Steps to read

8. 4

   Collect all information about the table (including index)

   sql> exec dbms_stats.gather_table_stats (ownname =>user,tabname=> ' wwff ', estimate_percent = 20,degree = > 10,granularity = ' all ', cascade = TRUE);

   Ownname =>user user indicates the current users

   Cascade = True True indicates that the index is included

   Steps to read
10. 5

    Analyze all information (including indexes) for this table

    Analyze table WFXX compute statistics;

    Steps to read

12. 6

    Execute again and view

    SELECT * FROM WWFF

    where Jgsj>=to_date (' 2014-10-26 00:00:00 ', ' yyyy-mm-dd HH24:Mi:SS ')

    and Sjzt=1 and Fjbj=3 and Fjr=1 and rownum <= 1

    Time: 0.03 seconds

    After collecting the statistics and analyzing the tables, I found that SQL started to walk the index

    Note: Collecting statistics or analyzing table information for a table may not take effect and must be done in two

    steps to read = = END

[Turn] Oracle SQL query suddenly slows down--case study