Oracle database How to get started optimizing a SQL

This is the ultimate problem, because the complexity of optimization itself is difficult to summarize, many times the method of optimization is not the use of the mysterious technology, but only a level of ideological differences, and these are likely to lead to significant performance differences.
So sometimes we need to figure out what the demand is and whether SQL itself is reasonable, and these thoughts are likely to make the optimization work less. This article assumes that SQL itself is reasonable, from some of the technical means Oracle provides to us to briefly introduce the Oracle database, how to use some existing technology to optimize the performance of a SQL execution.

1. Identify the SQL text that needs to be optimized and the current SQL execution plan
2. Determine information about all tables and their indexes that are involved in SQL
3. Run SQL Tuning Advisor to get tuning recommendations for optimization reference
4. Collecting table information
5. Collecting index information
6. SQL profile
7. Materialized view

1. Identify the SQL text that needs to be optimized and the current SQL execution plan

Before tuning, determine what SQL text to optimize and what the current SQL execution plan is, and note that the execution plan F5 seen by PL/SQL developer is likely to be inaccurate.
Related content Reference:

• SQL Tuning Basics Overview 01-autotrace Settings
• SQL Tuning Basics Overview of the use of 02-explain plan
• SQL Tuning Basic Overview 03-execution plan with Sql_trace and 10046 event tracking

2. Determine all the tables and their indexes related to SQL

Determines the underlying information about all the tables and their indexes involved in the query. Like what:

The amount of data for each table
Table and Index types
Table partitioning information, the amount of data per partition
Indexed fields
Index partition information
Table Association method
Number of result sets

To determine the relevant information, take the T2 table as an example:

--General table/partition table information select * FROM dba_tables WHERE table_name = ' T2 '; select * FROM dba_part_tables WHERE table_name = ' T2 ';  --Average table/partition table for each partition approximately __g size select (t.bytes/1024/1024) "MB", t.* from dba_segments t where segment_name = ' T2 '; --Table data Volume information--General table data Volume select COUNT (1) from T2;  --____ data--partition table of a partition data volume select COUNT (*) from T2 partition (P20160101); --____ data about SELECT COUNT (*) from T2 partition (P20160102);--table index information--normal table index and index column for each index select * from Dba_indexes where table_ name = ' T2 '; select * from Dba_ind_columns where index_name in (select Index_name from dba_indexes where table_name = ' T2 ') Order by index_name, column_position;--Partition table index and index column for each index select * FROM dba_part_indexes WHERE table_name = ' T2 '; select * FROM  Dba_ind_columns where index_name in (select Index_name from dba_part_indexes where table_name = ' T2 ') Order by Index_name, column_position;--Index Segment size information--select (t.bytes/1024/1024) "MB", t.* from Dba_segments t where segment_name in (select Index_n AME from dba_part_indexes where table_name = ' T2 ') Order by SEGMent_name, Partition_name; 

Related content Reference:

• SQL Tuning Basic Overview 04-oracle table types and introduction
• SQL Tuning Basics Overview 05-oracle Index types and introduction
• SQL Tuning Basic Overview 06-Table Association: Nested Loops join,merge Sort Join & Hash Join
• SQL Tuning Basic Overview 07-sql Joins

3. Run SQL Tuning Advisor to get tuning recommendations for optimization reference

Running SQL Tuning Advisor gets tuning Recommendations for optimization reference, and SQL Tuning Advisor's optimization recommendations are for informational purposes only, and how to do this also requires a combination of business realities.

Related content Reference:

• SQL Tuning Basic Overview 08-sql Tuning Advisor

4. Collecting table information

For example, collect statistics for ZJY users under the T2 table. (T2 is a range partition table, divided by day, the amount of data per day is about 80w, storing half a year)

SQL> execute dbms_stats.gather_table_stats(ownname => ‘ZJY‘, tabname => ‘T2‘, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO‘, cascade => TRUE, degree => 16); PL/SQL procedure successfully completed Executed in 5896.641 seconds

Statistical information locking/unlocking

Related content Reference:

• "Reprint" Dbms_stats Import and export table statistics

5. Collecting index information

For example, only the index idx_t2_1 statistics of the T2 table are collected for ZJY users. (Idx_t2_1 is a partitioned index with 4 fields)

SQL> execute dbms_stats.gather_index_stats(ownname => ‘ZJY‘, indname => ‘IDX_T2_1‘, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8); PL/SQL procedure successfully completed Executed in 44.312 seconds

Sometimes it may be necessary to create a new index online at the time of business

--不记录日志在线并行创建单列索引IDX_T2_2（并行度视生产环境当前的CPU资源使用情况来确定合理的值）create index IDX_T2_2 on T2(start_time) tablespace DBS_I_JINGYU nologging parallel 12 online;alter index IDX_T2_2 noparallel;alter index IDX_T2_2 logging;

6. SQL Profile

SQL Profile is a new feature in 10g as part of the automatic SQL tuning process. SQL profile is an object that contains information that can help the query optimizer find an efficient execution plan for a specific SQL statement. This information includes the execution environment, object statistics, and remediation information for the evaluation made by the query optimizer. One of its greatest advantages is the decision to affect the query optimizer without modifying the SQL statement and session execution environment. SQL profile contains information that is not a single execution plan, and SQL profiles does not fix an execution plan for an SQL statement. When the table's data grows or the index is created or deleted, the execution plan using the same SQL profile may change, and the information stored in SQL profiles will continue to work. Therefore, after a long period of time, its information may be outdated and need to be regenerated.

Related content Reference:

• Use of "reprint" Sql_profile
• "Reprint" Sql_profile Fast binding script

7. Materialized view

Oracle's materialized views can be used to pre-compute and save results for more time-consuming operations, such as table joins or aggregates, so the rational use of materialized views avoids these time-consuming operations when executing queries, resulting in quick results.

Related content Reference:

• "Reprint" materialized View detailed

Oracle database How to get started optimizing a SQL