ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;


SELECT NAME, VALUE
     FROM V$PARAMETER
     WHERE NAME LIKE 'optimizer%';


SELECT NAME, VALUE
     FROM V$PARAMETER
     WHERE NAME LIKE '%DUMP%';
-- utlxpan.sql
drop table PLAN_TABLE;
create table PLAN_TABLE (
	statement_id 	varchar2(30),
	timestamp    	date,
	remarks      	varchar2(80),
	operation    	varchar2(30),
	options       	varchar2(30),
	object_node  	varchar2(128),
	object_owner 	varchar2(30),
	object_name  	varchar2(30),
	object_instance numeric,
	object_type     varchar2(30),
	optimizer       varchar2(255),
	search_columns  number,
	id		numeric,
	parent_id	numeric,
	position	numeric,
	cost		numeric,
	cardinality	numeric,
	bytes		numeric,
	other_tag       varchar2(255),
	partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
	other		long,
	distribution    varchar2(30));

-- rdbms\admin\UTLXPLS.SQL (Para ver el ultimo plan)


ALTER SESSION SET SQL_TRACE = true;


ALTER SYSTEM SET SQL_TRACE = true;

ALTER SYSTEM SET USER_DUMP_DEST= newdir. 
-- El archivo de resultado de sql trace

EXPLAIN PLAN FOR
  select * from con_saldo_vmoneda 
  where cod_cia='001' and ano_fiscal=2003 and cod_cta='10-05-01'
    and cod_moneda='CL';


DBMS_STATS.GATHER_INDEX_STATS (ownname, indname); 

DBMS_STATS.GATHER_TABLE_STATS (ownname, tabname, NULL, NULL, FALSE,
                               'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', TRUE);

DBMS_STATS.GATHER_SCHEMA_STATS ('DEMOS', null, false, 'FOR ALL COLUMNS SIZE 1',NULL,
                                'DEFAULT',TRUE);

   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE);


Example 1
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the ten statements in the trace file that have generated the most physical I/O: 

TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

Example 2
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf: 

TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)


exp username/password PARFILE=filename

exp system/manager parfile=params.dat


The params.dat file contains the following information: 

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y

exp system/manager tables=(a, scott.b, c, mary.d)

exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and sal\<1600\"

exp system/manager tables=(a, scott.b, c, mary.d)

exp demos/demos parfile=expped.par

expped:
FILE=pedxxxx
TABLES=(venpedido, vendetpedido)
query="where cia='001' and sucursal='001' and documento=22"