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
- 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
- 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
- 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
- 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
- 5
Analyze all information (including indexes) for this table
Analyze table WFXX compute statistics;
Steps to read
- 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