Oracle PL/SQL Tutorial/SQL PLUS Session Environment/OPTIMIZER MODE
Содержание
alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
<source lang="sql">
SQL> create table myTable1
2 as
3 select mod(rownum,1000) id, rpad("x",300,"x") data
4 from all_objects
5 where rownum <= 5000;
SQL> SQL> create table myTable2
2 as
3 select rownum id, rpad("x",300,"x") data
4 from all_objects
5 where rownum <= 1000;
SQL> SQL> create index myTable1_idx on myTable1(id); SQL> SQL> create index myTable2_idx on myTable2(id); SQL> SQL> begin
2 dbms_stats.gather_table_stats( user, "myTable1", method_opt => "for all indexed columns",cascade=>true ); 3 dbms_stats.gather_table_stats( user, "myTable2", method_opt => "for all indexed columns",cascade=>true ); 4 end; 5 /
SQL> set autotrace traceonly explain SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 5 and 55;
Execution Plan
Plan hash value: 3784280921
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 56 | 20664 | 260 | | 1 | TABLE ACCESS BY INDEX ROWID | MYTABLE1 | 1 | 178 | 5 | | 2 | NESTED LOOPS | | 56 | 20664 | 260 | | 3 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 51 | 9741 | 5 | |* 4 | INDEX RANGE SCAN | MYTABLE2_IDX | 51 | | 2 | |* 5 | INDEX RANGE SCAN | MYTABLE1_IDX | 1 | | |
Predicate Information (identified by operation id):
4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> alter session set optimizer_index_cost_adj = 50; SQL> SQL> alter session set optimizer_index_caching = 0; SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 5 and 55;
Execution Plan
Plan hash value: 3784280921
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 56 | 20664 | 156 | | 1 | TABLE ACCESS BY INDEX ROWID | MYTABLE1 | 1 | 178 | 3 | | 2 | NESTED LOOPS | | 56 | 20664 | 156 | | 3 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 51 | 9741 | 3 | |* 4 | INDEX RANGE SCAN | MYTABLE2_IDX | 51 | | 1 | |* 5 | INDEX RANGE SCAN | MYTABLE1_IDX | 1 | | 1 |
Predicate Information (identified by operation id):
4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable1; SQL> drop table myTable2; SQL></source>
alter session set optimizer_max_permutations=80000
<source lang="sql">
SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics
2 for table 3 for columns id;
SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for
2 select count(*) 3 from t t1, t t2, t t3, t t4, t t5, t t6 4 where t1.id = t2.id 5 and t1.id = t3.id 6 and t1.id = t4.id 7 and t1.id = t5.id 8 and t1.id = t6.id;
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;</source>
alter session set optimizer_mode=all_rows
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> set autotrace traceonly explain SQL> SQL> alter session set optimizer_mode=all_rows; SQL> SQL> select t1.object_name, t2.object_name
2 from myTable t1, myTable t2 3 where t1.object_id = t2.object_id 4 and t1.owner = "WMSYS" 5 /
Execution Plan
Plan hash value: 2666611345
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 77 | 3 | |* 1 | HASH JOIN | | 1 | 77 | 3 | |* 2 | TABLE ACCESS FULL| MYTABLE | 1 | 47 | 1 | | 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2460 | 1 |
Predicate Information (identified by operation id):
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OWNER"="WMSYS")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable; SQL> SQL></source>
alter session set OPTIMIZER_MODE = choose
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> begin
2 dbms_stats.set_table_stats 3 ( user, "EMPLOYEE", numrows => 10000000, numblks => 1000000 ); 4 end; 5 /
PL/SQL procedure successfully completed. SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
alter session set OPTIMIZER_MODE = RULE;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> alter session set OPTIMIZER_MODE = choose; Session altered. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
alter session set optimizer_mode=first_rows
<source lang="sql">
SQL> SQL> SQL> SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
SQL> SQL> SQL> SQL> alter session set optimizer_mode=first_rows; SQL> SQL> select t1.object_name, t2.object_name
2 from myTable t1, myTable t2 3 where t1.object_id = t2.object_id 4 and t1.owner = "WMSYS" 5 /
SQL> alter session set sql_trace=true; SQL> drop table myTable;</source>
