martes, 29 de noviembre de 2011

TKPROF And Oracle Trace

TKPROF And Oracle Trace

The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. To get the most out of the utility you must enable timed statistics by setting the init.ora parameter or performing the following command:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;If a suitable plan table is not present one can be created by doing the fooling as the SYS user:

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;With this done we can trace a statement:

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM dual;

ALTER SESSION SET SQL_TRACE = FALSE;The resulting trace file will be located in the USER_DUMP_DEST directory. This can then be interpreted using TKPROF at the commmand prompt as follows:

TKPROF explain=user/password@service table=sys.plan_tableThe resulting output file contains the following type of information for all SQL statements processed, including the ALTER SESSION commands:

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SELECT COUNT(*)
FROM dual

call count cpu elapsed disk query current rows
------- ----- ----- ------- ------- ------- ------- -------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 4 1
------- ----- ----- ------- ------- ------- ------- -------
total 4 0.02 0.02 0 1 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL DUAL

Statspack

1. Objetivo
El objetivo de este documento es explicar cómo se instala statspack y cómo se puede configurar statspack para obtener instantáneas de la base de datos en un momento determinado.



2. Instalación statspack
Para realizar la instalación de statspack hay que tener previamente creado un tablespace disponible para la instalación.

- En base de datos ORACLE8 y ORACLE9 podemos elegir un tablespace que tenga espacio suficiente para la instalación aunque lo más recomendable es crear uno nuevo, por ejemplo un tablespace llamado PERFSTAT.

- En bases de datos ORACLE 10g la instalación se puede realizar en el tablespace SYSAUX.

Nos conectamos a la base de datos en la que queremos instalar STATSPACK y ejecutamos con el usuario sys el script $ORACLE_HOME/rdbms/admin./spcreate.sql

Ejemplo: Instalación de statspack en una base de datos oracle9. Nos conectamos con el usuario oracle9, fijamos la variable ORACLE_SID y lanzamos el script spcreate.sql

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL > connect /as sysdbaSQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql
Durante la instalación se van a solicitar los siguientes datos:
- Contraseña para el usuario perfstat: *******

- Tablespace a instalar: Ponemos SYSAUX o PERFSTAT dependiendo de la versión en la que estemos.(tablespace creado en oracle8 y oracle9 o tablespace de oracle10).

3. Desinstalación statspack
En caso de que queramos desinstalar STATSPACK de la misma forma que lo hemos instalado en vez de ejecutar el fichero spcreate ejecutamos el fichero spdrop.sql

SQL> @$ORACLE_HOME/rdbms/admin./spdrop.sql4. Obtener una foto de la base de datos con statspack
Para ejecutar statspack y obtener una instantánea de la base de datos en ese momento lo realizamos de la siguiente forma:

Nos conectamos a la base de datos con el usuario perfstat y ejectuamos statspack.snap

sql > connect perfstat/*****sql > execute statspack.snap(I_SNAP_LEVEL=>10);Si vamos a obtener instantáneas a menudo es conveniente actualizar las estadísticas de perfstat.

sql > connect perfstat/*****sql > execute dbms_stats.gather_schema_stats(ownname=>'perfstat');5. Obtener un informe statspack
Para obtener un Informe statspack los pasos que hay que seguir son los siguientes (Hay que tener en cuenta que para sacar un informe hay que tener más de una foto y que entre éstas la instancia no se haya caído o parado):

Con el usuario propietario de oracle nos conectamos a la base de datos como perfstat.

Ejecutamos el script $ORACLE_HOME/rdbms/admin/spreport.sql . Al ejecutar este script obtenemos todas las fotos que se han realizado y vamos contestando a las peticiones que este te hace:

Foto inicial
Foto final
Lugar para generar el informe

Ejemplo para obtener un informe.

$ su – oracle9$ export ORACLE_SID=orasite$ sqlplus /nologSQL> connect perfstat/******SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql

miércoles, 23 de noviembre de 2011

VIEW WITH CHECK OPTION

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Abr 11 12:08:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Introduzca el nombre de usuario: carlos@bd01.xxxxxxxx
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO

11 filas seleccionadas.Un método sencillo es crear una vista que filtre los datos según el criterio requerido:

carlos@bd01.xxxxxxxx> CREATE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCOHasta aquí todo fácil. Podemos insertar filas en esa vista sin ningún problema y los datos se almacenarán en la tabla subyacente:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (6, 'SEIS');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS

12 filas seleccionadas.Pero aquí pueden empezar los problemas, porque a través de la vista se podrían hacer modificaciones a la tabla de una manera descontrolada:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (11,'ONCE');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT
---------- -------------------------
30 TREINTA
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
21 VEINTIUNO
22 VEINTIDOS
23 VEINTITRES
24 VEINTICUATRO
25 VEINTICINCO
6 SEIS
11 ONCE

13 filas seleccionadas.Aquí hemos visto que se puede hacer un ‘INSERT’ ‘a ciegas’: Hemos insertado en la vista una fila que no podemos ver, pero que sí fue insertada en la tabla base. Esto da pie a que puedan hacerse operaciones que pasen inadvertidas.

Incluso más: se pueden dar errores incomprensibles:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO');
INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO')
*
ERROR en línea 1:
ORA-00001: unique constraint (CARLOS.PRUEBA01_PK) violatedEn este caso y en el anterior (la inserción ‘a ciegas’ y el error) puede ocurrir que el nombre de la vista no nos dé pistas de que no se trata de una tabla ‘ordinaria’, haciendo el comportamiento más extraño a ojos de un observador.

Estos problemas se solicionarán fácilmente si utilizamos la opción ‘WITH CHECK OPTION’ en la creación de la vista. Esta opción hace que las operaciones DML sobre dicha vista pasen un control de integridad que corresponda a los criterios de definición de la propia vista:

carlos@bd01.xxxxxxxx> CREATE OR REPLACE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10
4> WITH CHECK OPTION;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

ID_N C_TXT
---------- -------------------------
1 UNO
2 DOS
3 TRES
4 CUATRO
5 CINCO
6 SEIS

6 filas seleccionadas.Si ahora intentamos hacer una inserción (o modificación) que salga de los criterios de definición de la vista tendremos que:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (12,'DOCE');
INSERT INTO PRUEBA01_VW VALUES (12,'DOCE')
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>Por supuesto, esto también es válido para ‘UPDATE’:

carlos@bd01.xxxxxxxx> UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1;
UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1
*
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>En resumen, si se utilizan vistas actualizables como mecanismo de ‘encapsulamiento de visibilidad de datos’ se debería siempre considerar la opción ‘WITH CHECK OPTION’ para evitar resultados no deseados de inconsistencia y reforzar además las referidas políticas de visibilidad (y más importante: de actualización) de los datos

JOINS

Example of a Left Outer Join 1

SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
9 Developer

SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+);

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Jack Developer
Jane
Joke
James
Jodd
Tom

9 rows selected.

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.



Example of a Right Outer Join 1

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE)
8 )
9 /

Table created.

SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E

9 rows selected.

SQL> select * from job
2 /

EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
9 Developer

SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno ;

ENAME JOBTITLE
--------------- --------------------
Jason Tester
John Accountant
Joe Developer
Tom COder
Jack Developer

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.

SQL> drop table job
2 /

Table dropped.