Skip to content

Optimizacion SQL.sql

pierregermain edited this page May 3, 2016 · 3 revisions

=============== TRUCOS SQL*PLUS

SQL> set heading off; -- quitar cabeceras a las tablas SQL> r; -- repetir comando SQL> connect SH/ORACLE -- conectar cómo otro usari/contraseña SQL> select * from tab; -- ver tablas SQL> select user from dual; -- ver con quien estoy conectado SQL> desc SALES; -- ver info de la tabla SALES SQL> SPOOL D:\carga.txt -- -- Salida a fichero: SQL> Clear Screen -- Ejecutar el script: SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

-- Trazar una select SQL> SET AUTOTRACE ON EXPLAIN;-- Mostrar traza y resultado de select SQL> SET AUTOTRACE TRACEONLY; -- Solo mostrar la traza SQL> SET AUTOTRACE ON STATISTICS; -- Ver estadistica

-- Para que estimaciones de tiempo sean reales SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; SQL> ALTER SYSTEM FLUSH SHARED_POOL;

===================== PLSQL INSERCION DATOS

begin for i in 1..1000000 loop insert into prueba values (i,'Heyyaa'); end loop; commit; end;

begin for i in 1..300000 loop insert into prueba values (i+1041000,'Heyyaa'); end loop; commit; end;

======================= OPERACIONES RELANZABLES

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;

--> Una session para el proceso, otro para ver las siguientes tablas

DBA_RESUMABLE V$SESSION_WAIT

SQL> select * from DBA_RESUMABLE where TABLESPACE_NAME like '%TS_CURSO%'; select column_name from all_tab_columns where table_name = '%DBA_RESUMABLE%';

======= TS UNDO

ORA-01555 Snapshot to old --> necesitamos mas espacio

=============== VER TABLESPACES

select * from dba_tablespaces --> con ese comando no se ve el espacio usado

================================================= RECUPERAR ESPACIO DE UNA TABLA BORRADA CON DELETE

  1. Activar movimiento de filas SQL> ALTER TABLE prueba ENABLE ROW MOVEMENT;

  2. Compactar el espacio SQL> ALTER TABLE prueba SHRINK SPACE COMPACT;

  3. Mover la marca de agua (HWM); SQL> ALTER TABLE prueba SHRINK SPACE;

-- Ver vistas DBA_TABLES ALL_TABLES

--> ES MUY IMPORTANTE HACER ESTO PARA QUE LA TABLA ESTE VACÍA DE VERDAD!!!

================= ANALIZAR OBJECTOS

SQL> SELECT blocks, num_rows FROM dba_tables WHERE table_name like '%PRUEBA%'; -> Si devuelve NULL en cada campo es OKEY, simplemente no sabe lo que ocupa -> Si NO devuelve filas esque no existe la tabla

SQL> ANALYZE TABLE prueba COMPUTE STATISTICS; -> Ahora ya devolvera datos el 'select * from dba_tables' de nuestra tabla. -> Sirve para actualizar los datos de 'dba_tables' -> Es un proceso pesado!!!

=========== PARTICIONES

-- MÉTODOS PARTICIONAMIENTO - RANGE

CREATE TABLE VENTAS_CUATRIMESTRES ( comercial_id NUMBER(4), comercial_nombre VARCHAR2(40), region_venta VARCHAR2(40), importe_venta NUMBER(6), fecha_venta DATE) PARTITION BY RANGE (fecha_venta) (PARTITION ventas_1cuat VALUES LESS THAN (TO_DATE('01/05/2004','DD/MM/YYYY')), PARTITION ventas_2cuat VALUES LESS THAN (TO_DATE('01/09/2004','DD/MM/YYYY')), PARTITION ventas_3cuat VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')));

--> MAXVALUE para rango superior!!!!

-- MÉTODOS PARTICIONAMIENTO - LIST -- Mapea los datos en particiones, definidas sobre una lista de valores del

partition key. CREATE TABLE ventas_regiones ( comercial_id NUMBER(4), comercial_nombre VARCHAR2(40), region_venta VARCHAR2(40), importe_venta NUMBER(6), fecha_venta DATE) PARTITION BY LIST (region_venta) (PARTITION ventas_1region VALUES ('ASTURIAS', 'GALICIA','MADRID'), PARTITION ventas_2region VALUES (‘ARAGON’,’CATALUÑA’,’VALENCIA’, ’BALEARES’), PARTITION ventas_3region VALUES (‘ANDALUCIA’,EXTREMADURA,’MURCIA’));

-- MÉTODOS PARTICIONAMIENTO - HASH

CREATE TABLE ventas_regiones ( comercial_id NUMBER(4), comercial_nombre VARCHAR2(40), region_venta VARCHAR2(40), importe_venta NUMBER(6), fecha_venta DATE) PARTITION BY HASH (comercial_id) -- Criterio de cómo ordenar los datos en particiones PARTITIONS 4; -- Número de particiones

-- MÉTODOS PARTICIONAMIENTO - COMPOSITE

CREATE TABLE ventas_composite_rl ( comercial_id NUMBER(4), comercial_nombre VARCHAR2(40), region_venta VARCHAR2(40), importe_venta NUMBER(6), fecha_venta DATE) PARTITION BY RANGE (fecha_venta) SUBPARTITION BY LIST (region_venta) SUBPARTITION TEMPLATE ( SUBPARTITION ventas_1region VALUES ('ASTURIAS', ‘GALICIA’,’CANTABRIA’,‘MADRID’), SUBPARTITION ventas_2region VALUES(‘ARAGON’,’CATALUÑA’,’VALENCIA’,’BALEARES’), SUBPARTITION ventas_3region VALUES (‘ANDALUCIA’,’C.LA MANCHA’,’MURCIA’)) ( PARTITION ventas_1cuat VALUES LESS THAN (TO_DATE(‘01/05/2004’,’DD/MM/YYYY’)), PARTITION ventas_2cuat VALUES LESS THAN (TO_DATE(‘01/09/2004’,’DD/MM/YYYY’)), PARTITION ventas_3cuat VALUES LESS THAN (TO_DATE(‘01/01/2005’,’DD/MM/YYYY’)));


TABLAS TEMPORALES

-- Mantienen los datos únicamente durante la sesión o transacción en curso. Dos tipos:

PRESERVE: datos permanecen hasta finaliza sesión

CREATE GLOBAL TEMPORARY TABLE prueba_temp2
( campo1 NUMBER)
ON COMMIT DELETE ROWS;

DELETE: datos permanecen hasta finaliza transacción

CREATE GLOBAL TEMPORARY TABLE prueba_temp1
( campo1 NUMBER)
ON COMMIT PRESERVE ROWS;

======= INDICES

EJEMPLO

CREATE TABLE INDICES (A NUMBER, B VARCHAR2(6), C VARCHAR2(40) ) TABLESPACE TS_CURSO;

BEGIN FOR I IN 1..10000 LOOP INSERT INTO INDICES VALUES (I,'A'||I,'AAAA'||I); END LOOP; COMMIT; END;

SQL> SELECT * FROM INDICES WHERE A=1;


-- PLAN DE EJECUCION (MUY IMPORTANTE) SQL> SET AUTOTRACE ON EXPLAIN;

SQL> SELECT * FROM INDICES WHERE A=1; --> nos da TABLE ACCESS FULL (ESO SIGNIFICA QUE SE HA ACCEDIDO A TODA LA TABLA)

-- CREACION DEL INDICE CREATE INDEX IDX_INDICES ON INDICES(A,B,C);

SQL> SELECT * FROM INDICES WHERE A=1; --> ahora nos da INDEX RANGE SCAN (ESO SIGNIFICA QUE NO SE HA ACCEDIDO A TODA LA TABLA)

SQL> select count(*) from indices; --> TABLE ACCESS FULL

SQL> select count (a) from indices; --> TABLE ACCESS FULL


EJEMPLO: CREACION INDICE

SQL> CREATE INDEX IDX_C ON INDICES (C);

SQL> SELECT * FROM INDICES WHERE C='AAAAA1'; --> INDEX RANGE SCAN

SQL> SELECT * FROM INDICES WHERE UPPER(C)='AAAAA1'; --> TABLE ACCESS FULL (YA QUE SE HACE WHERE CON FUNCION)


INDICE DE TIPO FUNCION

SQL> CREATE INDEX IDX_CF ON INDICES (UPPER(C)); SQL> SELECT * FROM INDICES WHERE UPPER(C)='AAAAA1'; --> INDEX RANGE SCAN


ver Para DWH ROLLUP CUBE

======================= PRACTICA FILAS MIGRADAS

CREATE TABLE FILAS_MIGRADAS (A NUMBER, B VARCHAR2(200), C VARCHAR2(400)) TABLESPACE TS_CURSO;

BEGIN FOR I IN 1 .. 1000 LOOP INSERT INTO FILAS_MIGRADAS VALUES (I,'A','AA'); END LOOP; COMMIT; END; /

-- PROVOCAR MIGRACION

(...)


-- MONOTORIZAR INDICES

-- Ver nombres de indices SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'INDICES';

-- ACTIVAR MONOTORIZACION INDICES ALTER INDEX indice MONITORING USAGE;

-- DESACTIVAR ALTER INDEX indice NOMONITORING USAGE;

-- Pedir el Grant al DBA lectura a la siguiente tabla: GRANT SELECT ON V$OBJECT_USAGE TO ;

-- Monotorizar el uso:

SELECT index_name, table_name, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE WHERE table_name = '(...)' AND Index_name = '(...)';

-- Monotorizar miles de indices a la vez: SELECT 'ALTER INDEX ' || INDEX_NAME || ' MONOTORING USAGE'; FROM ALL_INDEXES WHERE OWNER = 'CONTABILIDAD'; ---> GUARDAR EL RESULTADO EN UN TXT (ESO NOS DA LA SELECT A EJECUTAR)


-- Ejemplo MONOTORIZAR INDICES:

SELECT 'ALTER INDEX ' || INDEX_NAME || ' MONOTORING USAGE;' FROM ALL_INDEXES WHERE OWNER = 'SYSTEM' and TABLE_NAME = 'INDICES';

--Esto nos genera ALTER INDEX IDX_INDICES MONITORING USAGE; ALTER INDEX IDX_C MONITORING USAGE; ALTER INDEX IDX_CF MONITORING USAGE;

-- Luego lo ejecutamos, hacemos selects, y al final hacemos:

SQL> SELECT index_name, table_name, used, start_monitoring, end_monitoring 2 FROM V$OBJECT_USAGE 3 WHERE table_name = 'INDICES';

INDEX_NAME TABLE_NAME USE


START_MONITORING END_MONITORING


IDX_INDICES INDICES YES 06/01/2011 09:14:14

IDX_C INDICES NO 06/01/2011 09:14:17

IDX_CF INDICES NO 06/01/2011 09:14:29

======================================= ORGANIZACION DE TABLAS

Usuarios SYS SYSTEM

Tablas del Usuario USER_TABLES USER_INDEXES USER_VIEWS

Tablas mas internas ALL_TABLES ALL_*

Tablas super internas DBA_TABLES DBA_INDEXES

Tablas Dinamicas V$... K$...

================================= -- EVITAR QUERIES DE ESTE ESTILO:

= 1

WHERE CAMPO = 16 WHERE CAMPO = 18 ----> Las tiene que volver a recompilar -- MEJOR USAR: WHERE CAMPO = :variable

---> Esto es asi porque existe: 'CURSOR_SHARING' que puede tomar el valor: EXACT SIMILAR FORCE

SQL>show parameter CURSOR_SHARING;

= 2 Tratar con Objetos es menos eficiente que con datos relacionales.

========================================= -- SQL*Loader : Carga de ficheros a BD --

-- Crear objeto destino VENTAS SQL> CREATE TABLE VENTAS (ID_PRODUCTO NUMBER, ID_CLIENTE NUMBER, ID_TIEMPO DATE);

-- Salida a fichero: SQL> SPOOL D:\carga.txt

-- Quitar cabeceras SQL> SET HEADING OFF;

-- guardar datos a fichero de una tabla: SQL> SELECT prod_id ||'#'|| cust_id ||'#'|| time_id FROM SALES

-- guardar/completar con blancos (RPAD los rellena a la derecha) SQL> SELECT RPAD(PROD_ID,20,' ')||RPAD(CUST_ID,10,' ')|| RPAD(TIME_ID,15' ')

SQL> SPOOL OFF ------------------> NO OLVIDAR!! -- NO OLVIDAR SPOOL OFF

-- Ir al Enterprise Manager "Cargar datos de archivo de usuario" --> Cargamos el Fichero guardado anteriormente. Además limpiarlo!!! --> D:\carga.txt

-- ARCHIVO DE CONTROL: LOAD DATA APPEND INTO TABLE SYSTEM.VENTAS FIELDS TERMINATED BY '#' OPTIONALLY ENCLOSED BY '"' ( ID_PRODUCTO INTEGER EXTERNAL, ID_CLIENTE INTEGER EXTERNAL, ID_TIEMPO DATE )

-- Parametros CONTROL=D:\carga.CTL LOG=D:\carga.LOG DATA=D:\carga.txt DIRECT=TRUE PARALLEL=TRUE ERRORS=1 BAD=D:\IMPORT_SQL_LOADER.bad DISCARD=D:\IMPORT_SQL_LOADER.dsc

--

================================= EXPLAIN PLAN (complicado de usar)

-- Crear tabla temporal --$ORACLE_HOME/rdbms/admin/utlxplan.sql --D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\

create table PLAN_TABLE ( statement_id varchar2(30), plan_id number, timestamp date, remarks varchar2(4000), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_alias varchar2(65), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, depth 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), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000), projection varchar2(4000), time numeric, qblock_name varchar2(30), other_xml clob );

-- Creacion del plan de ejecución para cierta SELECT EXPLAIN PLAN FOR SELECT prod_Name FROM products;

-- Consultar el Plan Table: -- (1)-- Script utlxpls.sql set markup html preformat on -- (esta linea no hace falta select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

-- Ejecutar el script desde el SQL*Plus SQL>@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

==================== GENERACION DE TRAZAS

  • Siempre sacar trazas cuando se vaya a cambiar algo. No te fies nunca de los tiempos de ejecución.

-- Mirar el siguiente parametro (con el usario system) SQL> connect system/oracle SQL> SHOW parameter USER_DUMP_DEST

NAME TYPE VALUE


user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN \CLE\UDUMP -- PASOS: -- 1 Activar la traza SQL> ALTER SESSION SET SQL_TRACE = TRUE;

-- 2 Hacer SELECT SQL> Select * from INDICES where a = 100;

-- 3 Desactivar la traza SQL> ALTER SESSION SET SQL_TRACE = FALSE;

-- 4 Dar formato al siguiente fichero (el último modificado del siguiente directorio) D:\ORACLE\PRODUCT\10.2.0\ADMIN\CLE\UDUMP

-- 5 TKPROF: $ORACLE_HOME/bin/tkprof D:\oracle\product\10.2.0\db_1\BIN\tkprof.exe -- Desde MSDOS:

tkprof fichero_origen.trc fichero_salida.txt

-- 6 Buscar nuestra Query en el fichero fichero_salida.txt


Select * from INDICES where a = 100

-- () ()
call count cpu elapsed disk query current rows


Parse 1 0.00 0.03 1 3 0 0 Execute 1 0.00 0.00 0 0 0 0 --<---- OP. INSERT/DELETE/UPDATE Fetch 2 0.00 0.00 0 3 0 1 --<---- OP. SELECCION


total 4 0.00 0.03 1 6 0 1

Misses in library cache during parse: 1 -- <----- 'FALLOS EN LA COMPILACION' Optimizer mode: ALL_ROWS -- <----- Optimizador usado (ver explicación) Parsing user id: 5

Rows Row Source Operation


  1  INDEX RANGE SCAN IDX_INDICES (cr=3 pr=0 pw=0 time=15 us)(object id 52547)

--() Columnas Importantes disk: --() las veces que se ha accedido a disco count: --(*) veces que se ha realizado query: buffers leidos para la select current: buffers leidos distintas a la select

============== OPTIMIZER_MODE

Antiguamente 2 tipos de optimizadores

  • Reglas (deprecated ... Ojo: Que puede ser mejor en algunos casos)
  • CBO (basado en costes). Uso de estadisticas

3 Tipos de optimizadores basado en CBO: 'OPTIMIZER_MODE' - ALL_ROWS - FIRST_ROWS_n -- Ej) FIRST_ROWS 10 dev. las 10 primeras filas lo mas rapido posible ---> Ejemplo, en el TOAD que solo devuelve algunas filas. - FIRST_ROWS

Estos parametros son globales, afectan a toda la BBDD. Pero se puede forzar cierta select para que solo afecte asimisma

============== CURSOR_SHARING

Tipos: --> EXACT --> SIMILAR --> FORCE

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

PRACTICA TABLAS ORGANIZADAS POR INDICE

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

-- con indice CREATE TABLE PRODUCTOS_IOT ( PRODUCTO_ID NUMBER, NOMBRE VARCHAR2(200), FECHA_CREACION DATE, UNIDADES NUMBER(10), CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PRODUCTO_ID, NOMBRE)) ORGANIZATION INDEX TABLESPACE TS_CURSO;

-- sin indice CREATE TABLE PRODUCTOS ( PRODUCTO_ID NUMBER, NOMBRE VARCHAR2(200), FECHA_CREACION DATE, UNIDADES NUMBER(10), CONSTRAINT PK_PRODUCTOSS PRIMARY KEY (PRODUCTO_ID, NOMBRE)) TABLESPACE TS_CURSO;

SQL> SET AUTOTRACE ON EXPLAIN;-- Mostrar traza y resultado de select SQL> select * from productos;

--> TABLE ACCESS FULL

SQL> select * from productos_iot

--> INDEX FAST FULL SCAN

============== METODOS ACCESO

-- desbloquear usuario SYSTEM> ALTER USER OE IDENTIFIED BY ORACLE ACCOUNT UNLOCK --> OE es usuario, ORACLE es contraseña

SYSTEM> SET AUTOTRACE TRACEONLY; SYSTEM> SELECT * FROM OE.EMPLOYEES;


SQL> desc oe.employees; Nombre ¿Nulo? Tipo


EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)


SQL> SELECT * FROM OE.EMPLOYEES | 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |


SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees WHERE employee_id = 101;

| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 1 (0 |* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0


SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees WHERE employee_id > 101 and employee_id < 150;

| 0 | SELECT STATEMENT | | 49 | 539 | 2 (0 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 49 | 539 | 2 (0 |* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 49 | | 1 (0



SQL> SELECT FIRST_NAME, EMPLOYEE_ID FROM OE.employees


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 107 | 1177 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |

===== NOTA:

--> En el método anterior recorre TODA la tabla, pero si la tabla es GRANDE no lo haría.


SQL> SELECT EMPLOYEE_ID FROM OE.employees

--> ahora accede a todo el INDICE, no la tabla. Eso es mucho mas rápido que acceder a toda la tabla

| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 | 1 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01


===== ROWID

--> Acceso mas rapido --> Los ROWID son fijos, pero si se modifican los registros puede cambiar. FUCK!!! --> mucho cuidado al usarlo!!!

SQL> SET AUTOTRACE OFF SQL> SELECT ROWID,employee_id,first_name FROM OE.employees; SQL> SET AUTOTRACE ON SQL> SELECT FIRST_NAME FROM OE.EMPLOYEES WHERE ROWID = 'AAAMg6AAFAAAABYABV';

FIRST_NAME

Alexis

| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00 | 1 | TABLE ACCESS BY USER ROWID| EMPLOYEES | 1 | 19 | 1 (0)| 00

=============================== METODOS DE UNIÓN (JOIN METHODS)

SYSTEM> desc oe.departments;

Nombre ¿Nulo? Tipo


DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

SQL> SELECT department_name, first_name, last_name FROM oe.employees E, oe.departments D WHERE E.department_id = D.department_id;

| 0 | SELECT STATEMENT | | 106 | 3604 | 4 (0) | 1 | NESTED LOOPS | | 106 | 3604 | 4 (0) | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0) | 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0) |* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)

--> hay dos metodos de acceso. en realidad hace un NESTED LOOP JOIN -->> VEMOS QUE LA PRIMERA TABLA ES LA OUTER, LA SEGUNA LA INER

-- Ahora cambiamos el orden de las tablas

SQL> SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE E.department_id = D.department_id;


| 0 | SELECT STATEMENT | | 106 | 3604 | 4 (0) | 1 | NESTED LOOPS | | 106 | 3604 | 4 (0) | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0) | 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0) |* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)

--> Mismo metodo de acceso que antes. Eso es asi porque el orden no importa!!!

-- Ahora cambiamos el orden de los join

SQL> SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id ;

| 0 | SELECT STATEMENT | | 106 | 3604 | 4 (0) | 1 | NESTED LOOPS | | 106 | 3604 | 4 (0) | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0) | 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0) |* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)

--> El orden de las Join tampoco influye!!!

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

         HINTS

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

-- Son directivas que le damos al optimizador para modificar su comportamiento. -- Se ponen justo despues de la palabra reservada. Parecen comentarios pero no lo son. -- Tipos: ---> ORDERED ---> para forzar el orden de las tablas del FROM ---> USE_NL (campos) ---> para usar NESTED LOOP ---> USE_MERGE (campos) ---> para usar SORT MERGE ---> NO_USE_MERGE (campos) ---> NO USAR MERGE ---> USE_HASH () ---> NO_USE_HASH() ---> FULL (tabla) ---> acceder a toda la tabla ---> INDEX (tabla indice) ----> acceder a la tabla por cierto indice ---> NO_INDEX (tabla indice) ---> INDEX_ASC (tabla indice) ---> acceder ascendientemente por la tabla ---> INDEX_DESC(tabla indice) ---> INDEX_RS (tabla indice) ---> Forzar Range SCAN ---> NO_INDEX_RS (tabla indice)


SINTAXIS: SELECT /*+ HINT */ campos


TIPO: /*+ ORDERED */

--> Para respetar el orden de las tablas del FROM

=========== EJEMPLO UNO

(1) -- NORMAL

SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id ;


| 0 | SELECT STATEMENT | | 106 | 3604 | 4 (0) | 1 | NESTED LOOPS | | 106 | 3604 | 4 (0) | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0) | 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0) |* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)


Predicate Information (identified by operation id):

4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

(2) -- CAMBIANDO EL PLAN DE EJECUCIÓN

SELECT /*+ ORDERED */ department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id ;

| 0 | SELECT STATEMENT | | 106 | 3604 | 6 (17) | 1 | MERGE JOIN | | 106 | 3604 | 6 (17) | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0) | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0) |* 4 | SORT JOIN | | 107 | 1926 | 4 (25) | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)

Predicate Information (identified by operation id):

4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

=========== EJEMPLO DOS

(1) -- NORMAL

SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id AND D.department_id = 10;

DEPARTMENT_NAME FIRST_NAME LAST_NAME


Administration Jennifer Whalen

| 0 | SELECT STATEMENT | | 1 | 34 | 2 | 1 | NESTED LOOPS | | 1 | 34 | 2 | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 |* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 | 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 1 |* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 0

Predicate Information (identified by operation id):

3 - access("D"."DEPARTMENT_ID"=10) 5 - access("E"."DEPARTMENT_ID"=10)

---> Vemos que actua adecuadamente

(2) -- OUTER JOIN

SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+); --AND D.department_id = 10;



| 0 | SELECT STATEMENT | | 106 | 3604 | 6 (17) | 1 | MERGE JOIN OUTER | | 106 | 3604 | 6 (17) | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0) | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0) |* 4 | SORT JOIN | | 107 | 1926 | 4 (25) | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)

SELECT department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+); AND D.department_id = 10;

| 0 | SELECT STATEMENT | | 106 | 3604 | 6 (17) | 1 | MERGE JOIN OUTER | | 106 | 3604 | 6 (17) | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0) | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0) |* 4 | SORT JOIN | | 107 | 1926 | 4 (25) | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)

(3) -- USE_NL

SELECT /*+ USE_NL (E D) */department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+);

| 0 | SELECT STATEMENT | | 106 | 3604 | 10 | 1 | NESTED LOOPS OUTER | | 106 | 3604 | 10 | 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 | 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 4 | 72 | 1 |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0

(4) -- USE_MERGE

SELECT /*+ USE_MERGE (E D) */department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+);

| 0 | SELECT STATEMENT | | 106 | 3604 | 6 (17) | 1 | MERGE JOIN OUTER | | 106 | 3604 | 6 (17) | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0) | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0) |* 4 | SORT JOIN | | 107 | 1926 | 4 (25) | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)

(5) -- NO_USE_MERGE

SELECT /*+ NO_USE_MERGE (E D) */department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+);

| 0 | SELECT STATEMENT | | 106 | 3604 | 7 (15)| 00:00:01 |* 1 | HASH JOIN OUTER | | 106 | 3604 | 7 (15)| 00:00:01 | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01

(6) -- NO_USE_MERGE y NO_USE_NL juntos!

SELECT /*+ NO_USE_MERGE (E D), NO_USE_NL (E D) */department_name, first_name, last_name FROM oe.departments D, oe.employees E WHERE D.department_id = E.department_id (+);

| 0 | SELECT STATEMENT | | 106 | 3604 | 7 (15)| 00:00:01 |* 1 | HASH JOIN OUTER | | 106 | 3604 | 7 (15)| 00:00:01 | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01

====== EJEMPLO

Tomar id de empleado y usar FULL e INDEX

(1)

SQL> select employee_id from oe.employees where employee_id = 101;

| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:0 |* 1 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 4 | 0 (0)| 00:00:0

(2)

select /*+ FULL (employees) */ employee_id from oe.employees where employee_id = 101;


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 4 | 3 (0)| 00:00:01 |

(3)

select /*+ INDEX (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;

| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:0 |* 1 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 4 | 0 (0)| 00:00:0

(4)

select /*+ INDEX_DESC (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;

| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0 |* 1 | INDEX RANGE SCAN DESCENDING| EMP_EMP_ID_PK | 1 | 4 | 0 (0

(5)

select /*+ INDEX_RS (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id = 101;

| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:0 |* 1 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 1 | 4 | 0 (0)| 00:00:0

(6)

select /*+ INDEX_RS (employees emp_emp_id_pk) */ employee_id from oe.employees where employee_id > 101;

| 0 | SELECT STATEMENT | | 106 | 424 | 1 (0)| 00:00:01 |* 1 | INDEX RANGE SCAN| EMP_EMP_ID_PK | 106 | 424 | 1 (0)| 00:00:01

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

     MODO OPTIMIZADOR

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

OPTIMIZAR_MODE: ---> ALL_ROWS ---> FIRST_ROWS ---> FIRST_ROWS_N

Se puede cambiar el modo del optimizador por Session:

SQL> ALTER SESSION SET OTIMIZER_MODE = FIRST_ROWS;

Tambien se puede cambiar a nivel de HINTS el modo del optimizador:

--> /*+ ALL_ROWS / --> /+ FIRST_ROWS / --> /+ FIRST_ROWS(25) */

Los siguientes se van a quitar de Oracle, puede haber casos que son dpm, pero cuidado! --> /*+ RULE / --> /+ CHOOSE */

========= Ejemplos:

-- Para que estimaciones de tiempo sean reales ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL;

(1) select * from oe.employees ;

107 filas seleccionadas.

Plan de Ejecución

Plan hash value: 1445457117


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |

Estadísticas

   1036  recursive calls
      0  db block gets
    256  consistent gets
     35  physical reads
      0  redo size
   9800  bytes sent via SQL*Net to client
    458  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
     12  sorts (memory)
      0  sorts (disk)
    107  rows processed

(2) ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; select /*+ ALL_ROWS */ * from oe.employees ;

Plan de Ejecución

Plan hash value: 1445457117


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |

Estadísticas

   1056  recursive calls
      0  db block gets
    259  consistent gets
     29  physical reads
      0  redo size
   9800  bytes sent via SQL*Net to client
    458  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
     12  sorts (memory)
      0  sorts (disk)
    107  rows processed

---> Vemos que ha EMPEORADO "recursive calls" al usar el HINT. ---> El Oprtimizador de Oracle va bien


(3)

ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; select count (*) from indices;

COUNT(*)

  9999

Plan de Ejecución

Plan hash value: 3620601557


| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| INDICES | 9999 | 9 (0)| 00:00:01 |

Note

  • dynamic sampling used for this statement

Estadísticas

    583  recursive calls
      0  db block gets
    179  consistent gets
     54  physical reads
      0  redo size
    430  bytes sent via SQL*Net to client
    381  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
      1  rows processed

(4) ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; select /*+ ALL_ROWS / count () from indices;

COUNT(*)

  9999

Plan de Ejecución

Plan hash value: 3620601557


| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| INDICES | 9999 | 9 (0)| 00:00:01 |

Note

  • dynamic sampling used for this statement

Estadísticas

    583  recursive calls
      0  db block gets
    179  consistent gets
     52  physical reads
      0  redo size
    430  bytes sent via SQL*Net to client
    381  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
      1  rows processed

---> como por defecto es ALL_ROWS no cambia el "recursive calls"


(5) ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; select /*+ FIRST_ROWS(100) / count () from indices;

Plan de Ejecución

Plan hash value: 3620601557


| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| INDICES | 9999 | 9 (0)| 00:00:01 |

Note

  • dynamic sampling used for this statement

Estadísticas

    974  recursive calls
      0  db block gets
    239  consistent gets
     62  physical reads
      0  redo size
    430  bytes sent via SQL*Net to client
    381  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      8  sorts (memory)
      0  sorts (disk)
      1  rows processed

--> Al poner FIRS_ROWS los recursive calls son mayores, pero se supone que las primeras filas se consiguen antes.

========================================= EJEMPLO/PRACTICA:

Tabla departments Tabla employees

Se unen por department_id

Mostar department_name, department_id que tienen al menos un empleado

=================== MI FORMA (LA BUENA)

SQL> select D.department_name, D.department_id from oe.departments D where exists (select 1 from oe.employees E where E.department_id = D.department_id);

--> Lo bueno de esta forma es que en el exist en cuanto una fila cumple ya pasa del resto.

DEPARTMENT_NAME DEPARTMENT_ID


Administration 10 Marketing 20 Purchasing 30 Human Resources 40 Shipping 50 IT 60 Public Relations 70 Sales 80 Executive 90 Finance 100 Accounting 110

11 filas seleccionadas.

Plan de Ejecución


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 10 | 190 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS SEMI | | 10 | 190 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("E2"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Estadísticas

      9  recursive calls
      0  db block gets
     37  consistent gets
      0  physical reads
      0  redo size
    727  bytes sent via SQL*Net to client
    381  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     11  rows processed

========================================= EJEMPLO/PRACTICA:

Tabla departments Tabla employees

Se unen por department_id

Departamente que no tienen empleado

=================== MI FORMA (LA BUENA)

SQL> select D.department_name, D.department_id from oe.departments D where NOT exists (select 1 from oe.employees E where E.department_id = D.department_id);

DEPARTMENT_NAME DEPARTMENT_ID


Treasury 120 Corporate Tax 130 Control And Credit 140 Shareholder Services 150 Benefits 160 Manufacturing 170 Construction 180 Contracting 190 Operations 200 IT Support 210 NOC 220 IT Helpdesk 230 Government Sales 240 Retail Sales 250 Recruiting 260 Payroll 270

16 filas seleccionadas.

Plan de Ejecución


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 17 | 323 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 17 | 323 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Estadísticas

      9  recursive calls
      0  db block gets
     37  consistent gets
      0  physical reads
      0  redo size
    959  bytes sent via SQL*Net to client
    392  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     16  rows processed

========== OTRA FORMA

SQL> select D.department_name, D.department_id from oe.departments D where D.department_id NOT IN (SELECT E.department_id FROM oe.employees E where E.department_id is not null); ---- Si no pones esto se jode!!!!


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 17 | 323 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 17 | 323 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID" IS NOT NULL)

Estadísticas

      8  recursive calls
      0  db block gets
     37  consistent gets
      0  physical reads
      0  redo size
    959  bytes sent via SQL*Net to client
    392  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     16  rows processed

==== OTRA

usar el MINUS, pero no es nada eficiente y además solo puedes sacar el department_id

=========================== ESTADISTICAS


ANALIZE

ANALYZE TABLE nombre_tabla COMPUTE STATISTICS --> Dev BLOCKS-NUM_ROWS, si dev. nada hay que analizarla --> Columna LAST_ANALYZED nos dice cuando ha sido analizado por última vez (DD/MM/YYYY HH:MM:SS)

Ejemplo:

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED FROM ALL_TABLES WHERE TABLE_NAME = 'VENTAS';

BLOCKS   NUM_ROWS LAST_ANA

-- Al no dev. nada hacemos analisis

SQL> ANALYZE TABLE VENTAS COMPUTE STATISTICS

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED FROM ALL_TABLES WHERE TABLE_NAME = 'VENTAS';

BLOCKS   NUM_ROWS LAST_ANA

  2639     918843 02/06/11

SQL> select count (*) from ventas;

COUNT(*)

918843

-- Borrar estadisticas

SQL> ANALYZE TABLE VENTAS DELETE STATISTICS;

-- Recorrer un 1% de la tabla:

SQL> ANALYZE TABLE VENTAS ESTIMATE STATISTICS SAMPLE 1 PERCENT;

SQL> SELECT BLOCKS, NUM_ROWS, LAST_ANALYZED FROM ALL_TABLES WHERE TABLE_NAME = 'VENTAS';

BLOCKS   NUM_ROWS LAST_ANA

  2639     918843 02/06/11

---> Cómo esta tabla nunca ha tenido actualizaciones pues el resultado es el mismo

================ ANALIZAR INDICES ================

SQL> ANALYZE TABLE VENTAS COMPUTE STATISTICS FOR ALL INDEXES;

============ CREAR SCRIPT

SELECT 'ANALYZE TABLE' || OWNER || '.' || TABLE_NAME || 'COMPUTE STATISTICS;' FROM ALL_TABLES WHERE OWNER = 'OE';

--> Esto devuelve el siguiente script:

ANALYZE TABLEOE.WAREHOUSESCOMPUTE STATISTICS; ANALYZE TABLEOE.ORDER_ITEMSCOMPUTE STATISTICS; ANALYZE TABLEOE.ORDERSCOMPUTE STATISTICS; ANALYZE TABLEOE.PRODUCT_INFORMATIONCOMPUTE STATISTICS; ANALYZE TABLEOE.PROMOTIONSCOMPUTE STATISTICS; ANALYZE TABLEOE.SYS_IOT_OVER_52439COMPUTE STATISTICS; ANALYZE TABLEOE.SYS_IOT_OVER_52444COMPUTE STATISTICS; ANALYZE TABLEOE.PRODUCT_REF_LIST_NESTEDTABCOMPUTE STATISTICS; ANALYZE TABLEOE.SUBCATEGORY_REF_LIST_NESTEDTABCOMPUTE STATISTICS; ANALYZE TABLEOE.PRODUCT_DESCRIPTIONSCOMPUTE STATISTICS; ANALYZE TABLEOE.INVENTORIESCOMPUTE STATISTICS; ANALYZE TABLEOE.CUSTOMERSCOMPUTE STATISTICS;

====================================== RECOPILAR ESTADISTICAS DE TODA LA BBDD

Para dar permisos de hacerlo hay que ejecutar lo siguiente desde SYSTEM: GRANT EXECUTE ON DBMS_STATS TO BOS_MNTRREY;

  1. DBMS_STATS.GATHER_DATABASE_STATS --> CUIDADO AL USARLO, SACA INFO DE TODO
  2. DBMS_STATS.GATHER_DICTIONARY_STATS
  3. DBMS_STATS.GATHER_SCHEMA_STATS ('OE') --> Saca estadísticas de esquemas
  4. DBMS_STATS.GATHER_SYSTEM_STATS ('SYSTEM','VENTAS') --> Todos los objetos de SYSTEM
  5. DBMS_STATS.GATHER_TABLE_STATS ('OE','ORDERS')
  6. DBMS_STATS.GATHER_INDEX_STATS

========================== VER SELECTS MAS PESADAS

SELECT SQL_TEXT, DISK_READS, PARSE_CALLS, OPTIMIZER_MODE, OTPIMIZER_COST, CPU_TIME, ELAPSED_TIME FROM V$SQLAREA ORDER BY DISK_READS DESC;

-- A nivel de Administración V$SESSION ----> En esta vista vemos sesiones abiertas V$PROCESS ----> Cada proceso ejecutandose ahora mismo e Info de la memoria PGA de cada proceso V$SYSSTAT ----> Info de estádisticas del sistema V$SESSTAT ----> Estadistica de la sesión

=============== TABLAS EXTERNAS

drop table ext_ventas;

CREATE TABLE EXT_VENTAS (ID_PRODUCTO varchar2(30), ID_CLIENTE NUMBER, ID_TIEMPO NUMBER) ORGANIZATION EXTERNAL (DEFAULT DIRECTORY DIR_SQLOADER ACCESS PARAMETERS (RECORDS DELIMITED BY newline FIELDS TERMINATED BY '#' ) LOCATION ('carga.txt') );

select count (*) from ext_ventas;

Clone this wiki locally