=====================================================================
alter session set optimizer_mode=first_rows_1;
alter session set optimizer_mode=first_rows_10;
alter session set optimizer_mode=first_rows_100;
alter session set optimizer_mode=first_rows_1000;
alter session set optimizer_mode=first_rows;
alter session set optimizer_mode=all_rows;  -- по умолчанию 
alter session set optimizer_mode=choose;
alter session set optimizer_mode=rule;

=======================================================================
EXPLAIN PLAN 
=======================================================================

drop table t;
drop table t1;

create table t as select * from all_objects;
create table t1 as select * from all_objects;

create index i on t(object_name);
create index i1 on t1(object_id);

delete from plan_table;

explain plan for select t.* from t left join t1 on t.object_name = t1.object_name where t1.object_id = 100;

select distinct optimizer from plan_table;

select * from table(dbms_xplan.display());

===============================================================================


delete from plan_table;

explain plan for select * from t where s in (select s from t1 where s like 'M%');

select 
	id, substr(lpad(' ',level-1)||operation||' '||options||' '||object_name, 1, 50) Plan,
	substr(statement_id, 1, 15) statement_id, 	
	timestamp, substr(remarks, 1, 10), substr(object_owner, 1, 5), cardinality,
	object_instance, object_type, substr(optimizer, 1, 20), parent_id, position,
	cost, bytes, cpu_cost, io_cost, temp_space
from
	plan_table
connect by 
	prior id = parent_id
start with 
	id = 0
order by 
	id;

select * from table(dbms_xplan.display());

explain plan set statement_id = 'my' into plan_table for select * from t where s in (select s from t1 where s ='MAR');

select * from table(dbms_xplan.display('plan_table', 'my', 'basic'));
select * from table(dbms_xplan.display('plan_table', 'my', 'typical'));
select * from table(dbms_xplan.display('plan_table', 'my', 'all'));
select * from table(dbms_xplan.display('plan_table', 'my', 'serial'));

=========================================================================================================