Before giving you a specific introduction to Oracle Hints, let us first understand what Oracle Hints is and then fully introduce Oracle Hints, which I hope will be useful to you. The cost-based optimizer is very smart, and in most cases it chooses the right optimizer to ease the burden on the DBA. But sometimes it's smart and smart, and you choose a very poor run plan, making a statement run incredibly slow.
The DBA is required to intervene manually, telling the optimizer to generate a run plan with the access path or connection type that we specify, so that the statement runs efficiently. For example, suppose we think that for a particular statement, running a full table scan is more efficient than running an index scan, then we can instruct the optimizer to use a full table scan. In Oracle, the optimization of the intervention optimizer is achieved by adding Hints (hint) to the statement.
Oracle hints is a mechanism that tells the optimizer to generate a run plan according to the way we tell it. We can use Oracle hints to achieve:
1) type of optimizer used
2) The optimization goal of the cost-based optimizer is all_rows or first_rows.
3) The Access path to the table is a full table scan, an index scan, or a direct use of ROWID.
4) Type of connection between tables
5) Order of connections between tables
6) The degree of parallelism of the statement
2, hint can be based on the following rules to produce a role
Table join order, table join method, Access path, degree of parallelism
In addition to the "RULE" hint, once the other hints are used, the statement will instead use the CBO optimizer on its own initiative, assuming that there are no statistics in your data dictionary, the default statistics will be used. Therefore, it is recommended that you use the CBO or hints hints to make periodic analysis of tables and indexes.
How to use hints:
Hints is only applied to the SQL statement block (statement block, which is identified by SELECT, INSERT, UPDATE, Deletekeyword), and has no effect on other parts of the SQL statement or statement. For example, for the 2 SQL statements that use the Union operation, assuming that there is hints on only one SQL statement, the hints does not affect an SQL statement.
We can use gaze (comment) to add hints to a statement, a block of statements can only have a gaze, and the gaze can only be placed behind a select, UPDATE, or Deletekeyword
Syntax for using Oracle hints:
{delete| Insert| Select| UPDATE}/*+ hint [text] [Hint[text]] ... */
Or
{delete| Insert| Select| UPDATE}--+ hint [text] [Hint[text]] ...
Annotations:
1) DELETE, INSERT, select, and update are keyword that identify the beginning of a block of statements, including a hint of gaze that can only be present behind these keyword, otherwise the hint is invalid.
2) the "+" sign indicates that the gaze is a hints, and the plus must immediately follow the "/*", with no spaces in the middle.
3) Hint is one of the detailed tips described below, assuming that you include multiple hints, you need to separate each hint with one or more spaces.
4) text is the other description of hint's gaze text
Assuming that you do not have the correct designation Hints,oracle will ignore the hints and will not give any errors whatsoever.
1./*+all_rows*/
Indicates that the cost-based optimization method is selected for the statement block, and the optimal throughput is obtained, minimizing the resource consumption.
Like what:
SELECT/*+all+_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
2./*+first_rows*/
This indicates that the cost-based optimization method is selected for the statement block, and the optimal response time is obtained, minimizing the resource consumption.
Like what:
SELECT/*+first_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
3./*+choose*/
It shows that the statistics of the Access table in the hypothetical data dictionary will be based on the cost optimization method, and the best throughput is obtained.
It shows that the statistical information of the data dictionary is not provided, and the optimization method based on the rule cost is given.
Like what:
SELECT/*+choose*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
4./*+rule*/
Indicates that the rule-based optimization method is selected for the statement block.
Like what:
SELECT/*+ RULE */emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
5./*+full (TABLE) */
Indicates how to select a global scan for a table.
Like what:
SELECT/*+full (a) */Emp_no,emp_nam from Bsempms A WHERE emp_no= ' SCOTT ';
6./*+rowid (TABLE) */
The hint clearly indicates that the specified table is based on ROWID.
Like what:
SELECT/*+rowid (BSEMPMS) */* from Bsempms WHERE rowid>= ' aaaaaaaaaaaaaa '
and emp_no= ' SCOTT ';
7./*+cluster (TABLE) */
Hints indicate an access method to select a cluster scan for a specified table, which is only valid for cluster objects.
Like what:
SELECT/*+cluster */Bsempms. Emp_no,dpt_no from Bsempms,bsdptms
WHERE dpt_no= ' TEC304 ' and Bsempms. Dpt_no=bsdptms. Dpt_no;
8./*+index (TABLE index_name) */
Indicates the scan method for the table selection index.
Like what:
SELECT/*+index (Bsempms sex_index) use Sex_index BECAUSE there is fewmale Bsempms * * from Bsempms WHERE sex= ' M ';
9./*+INDEX_ASC (TABLE index_name) */
Indicates the scan method for the table selection index ascending.
Like what:
SELECT/*+index_asc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';
Ten./*+index_combine*/
Select a bitmap for the specified table to access the path, assuming that Index_combine does not provide an index as a parameter, a Boolean combination of bitmap indexes will be selected.
Like what:
SELECT/*+index_combine (Bsempms sal_bmi hiredate_bmi) */* from BSEMPMS
WHERE sal<5000000 and HireDate
/*+index_join (TABLE index_name) */
Tip understand that the command optimizer uses an index as an access path.
Like what:
SELECT/*+index_join (Bsempms sal_hmi hiredate_bmi) */sal,hiredate
From Bsempms WHERE sal<60000;
/*+index_desc (TABLE index_name) */
Indicates the scan method for descending the table selection index.
Like what:
SELECT/*+index_desc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';
/*+index_ffs (TABLE index_name) */
Runs a high-speed full-index scan on the specified table instead of a full-table scan.
Like what:
SELECT/*+index_ffs (Bsempms in_empnam) */* from Bsempms WHERE dpt_no= ' TEC305 ';
/*+add_equal TABLE index_nam1,index_nam2,... */
Tip understand the options for running a plan, combining several single-column index scans.
Like what:
SELECT/*+index_ffs (Bsempms in_dptno,in_empno,in_sex) */* from Bsempms WHERE emp_no= ' SCOTT ' and dpt_no= ' TDC306 ';
/*+use_concat*/.
A combined query that converts the or condition after the where in the query to union ALL.
Like what:
SELECT/*+use_concat*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';
/*+no_expand*/.
For query statements that are followed by or or in-list, No_expand prevents them from being extended based on the optimizer.
Like what:
SELECT/*+no_expand*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';
/*+nowrite*/.
Suppresses query rewrite operations on query blocks.
/*+rewrite*/.
The view can be used as a number of parameters.
/*+merge (TABLE) */
You can merge the individual queries on the view.
Like what:
SELECT/*+merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (Selet dpt_no
, AVG (SAL) as avg_sal from Bsempms B GROUP by Dpt_no) V WHERE a.dpt_no=v.dpt_no
and a.sal>v.avg_sal;
/*+no_merge (TABLE) */
Views that have a consolidated view are no longer merged.
Like what:
Select/*+no_merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (SELECT dpt_no,avg (SAL) as avg_sal from Bsempms B Grou P by Dpt_no) V WHERE a.dpt_no=v.dpt_no and a.sal>v.avg_sal;
/*+ordered*/.
Ordered enables Oracle to connect to it in this order, depending on the order in which the tables are present from.
Like what:
SELECT/*+ordered*/a.col1,b.col2,c.col3 from TABLE1 a,table2 b,table3 C WHERE a.col1=b.col1 and b.col1=c.col1;
/*+USE_NL (TABLE) */
Joins the specified table with the row source of the nested connection and takes the specified table as an internal table.
Like what:
SELECT/*+ordered use_nl (BSEMPMS) */BSDPTMS. Dpt_no,bsempms. Emp_no,bsempms. Emp_nam from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
/*+use_merge (TABLE) */
Joins the specified table with other row sources through a merge sort connection.
Like what:
SELECT/*+use_merge (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
/*+use_hash (TABLE) */
Connects the specified table to another row source by hashing the connection.
Like what:
SELECT/*+use_hash (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
/*+driving_site (TABLE) */
Forces a query to run on a table that differs from the location selected by Oracle.
Like what:
SELECT/*+driving_site (DEPT) */* from Bsempms,[email protected] WHERE Bsempms. Dpt_no=dept. Dpt_no;
/*+leading (TABLE) */
Takes the specified table as the first table in the join order.
/*+cache (TABLE) */
When a full-table scan is performed, the cache hint can place the retrieved block of the table in the buffer cache near least recent list of LRU's recent uses
Like what:
SELECT/*+full (BSEMPMS) Cahe (BSEMPMS) */Emp_nam from Bsempms;
/*+nocache (TABLE) */
When a full-table scan is performed, the cache hint can place the retrieved block of the table in the buffer cache near least recent list of LRU's recent uses
Like what:
SELECT/*+full (BSEMPMS) Nocahe (BSEMPMS) */Emp_nam from Bsempms;
/*+append*/.
Inserted directly to the end of the table, it is possible to raise the speed quickly.
Insert/*+append*/to test1 select * from Test4;
/*+noappend*/.
Start a regular insert by stopping the parallel mode during the INSERT statement lifetime.
Insert/*+noappend*/to test1 select * from Test4;
No_index: Specify which indexes are not used
/*+ no_index (table [index [index] ...]) */
Select/*+ no_index (EMP ind_emp_sal ind_emp_deptno) */* from EMP where deptno=200 and sal>300;
Parallel.
Select/*+ Parallel (emp,4) */* from EMP where deptno=200 and sal>300;
Another: Each select/insert/update/delete command can only have a/*+ * *, but the hint content can have multiple, can be separated by commas, spaces can also.
Example:/*+ ordered index () use_nl () */
---------
A statement such as the following: INSERT INTO XXXX select/*+parallel (a) */* from XXX A; Data volume about 75G, the brother from the morning to the afternoon has not run out, come to ask me what the hell, The usual 2hrs ran for hours and then ran out of things. Viewing system performance is also relatively normal, cpu,io are not busy, the average read speed around 80m/s (reluctantly), but the average write speed is only 10M. Wait for the event inside a large number of ' PX Deq credit:send blkd ', here can see the parallel out of the problem, and finally learned that there is a problem in parallel use method, after the change of 20 minutes to complete the operation. The right approach should be:
Alter session enable DML parallel;
Insert/*+parallel (xxxx,4) */into XXXX select/*+parallel (a) */* from XXX A;
Because Oracle does not open PDML by default, DML statements must be manually enabled. It is also important to say that parallelism is not an extensible feature, it is only in the data warehouse or as a few tools as a DBA to facilitate the full use of resources in bulk data operations, and the use of parallelism in an OLTP environment needs to be prudent. In fact, Pdml still have a lot of limitations, such as not supporting triggers, referential constraints, advanced replication and distributed transactions, and other features, the same time will bring additional space occupancy, PDDL same. The official documentation for parallel excution can be found in Thomas Kyte's new book, "Expert Oracle Database Architecture", as well as insightful storytelling.
---------
Select COUNT (*)
From Wid_serv_prod_mon_1100 A
where a.acct_month = 201010
and a.partition_id = 10
and Serv_state not in (' 2HB ', ' 2HL ', ' 2HJ ', ' 2HP ', ' 2HF ')
and Online_flag in (0)
and incr_product_id in (2000020)
and product_id in (2020966, 2020972, 2100297, 2021116)
and billing_mode_id = 1
and Exp_date > To_date (' 201010 ', ' yyyymm ')
And NOT EXISTS (select/*+no_index (b idx_w_cdr_mon_serv_id_1100) */
1
From wid_cdr_mon_1100 b
where b.acct_month = 201010
and B.ana_event_type_4 in
(' 10201010201 ', ' 10202010201 ', ' 10203010201 ', ' 10203010202 ', ' 10203030201 ', ' 10203030202 ', ' 10204010201 ', ' 10204010202 ', ' 10204030201 ')
and a.serv_id = b.serv_id)
Oracle hints Specific explanations
