Oracle PL/SQL Tutorial/SQL PLUS Session Environment/OPTIMIZER MODE
Содержание
alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
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>
alter session set optimizer_max_permutations=80000
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;
alter session set optimizer_mode=all_rows
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>
alter session set OPTIMIZER_MODE = choose
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.
alter session set optimizer_mode=first_rows
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;