Quisiera platicarles un caso en que me fue de mucha utilidad.
Resulta que a cierta hora del día la aplicación de mi cliente comenzaba a degradarse, ya que un procedure pasaba de tener un tiempo de respuesta en milisegundos a segundos en cualquier momento. Generábamos estadísticas para las tablas involucradas y el comportamiento seguía igual. Probamos ejecutarlo desde TOAD y se ejecuto rapidísimo, eso me pareció muy extraño ya que después de ejecutarlo por medio de TOAD comenzó a funcionar de nuevo desde la aplicación en milisegundos. Bueno pues me vino a la mente el parámetro plsql_optimize_level, el cual en 10g esta por default en 2 y en toad también por default lo pone en dos. Pues usando el método que les doy a continuación, me permitió saber que la aplicación le ponía a la sesión el parámetro 0, lo que hacía que se generara un plan de ejecución menos eficiente.Lo que hicimos fue cambiar este parámetro desde la aplicación y el problema quedo corregido.
Bueno pues este procedimiento que les muestro, nos sirve para ver que parámetros de sesión tiene definido un usuario en particular dentro de la base de datos.
Primero hacemos un select para identificar las sesiones de la aplicación y seleccionamos el spid.
SQL:dbserver:(orcl)> select s.sid, p.spid,s.username,
TO_CHAR(s.logon_time, 'MM/DD/YY HH24:MI:SS') from v$session s, v$process p where s.paddr=p.addr and s.username = 'USER1'; 2 3 4 SID SPID USERNAME TO_CHAR(S.LOGON_T ---------- ------------ ------------------------------ ----------------- 471 30718 USER1 06/23/11 19:19:20 448 7987 USER1 06/24/11 02:15:22 416 8283 USER1 06/23/11 17:58:41
Posteriormente desde sqlplus ejecutamos los siguientes comandos anexando el spid de la sesión que nos interesa:
SQL:dbserver:(orcl):PRIMARY> oradebug setospid 7987; Oracle pid: 64, Unix process pid: 7987, image: oracle@dbserver SQL:dbserver:(orcl):PRIMARY> oradebug dump modified_parameters 1; Statement processed.
Esto nos dejará un archivo trace en el directorio udump de oracle
dbserver(oracle):orclI1:udump$ cat orcli1_ora_8283.trc Dump file /app/ora/local/admin/orcl/udump/orcli1_ora_8283.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Data Mining Scoring Engine and Real Application Testing options ORACLE_HOME = /app/ora/local/product/10.2.0.4/db_6 System name: Linux Node name: dbserver Release: 2.6.18-194.11.4.el5 Version: #1 SMP Fri Sep 17 04:57:05 EDT 2010 Machine: x86_64 Instance name: orclI1 Redo thread mounted by this instance: 1 Oracle process number: 82 Unix process pid: 8283, image: oracle@dbserver *** 2011-06-24 07:28:18.657 *** ACTION NAME:(Initialized) 2011-06-24 07:28:18.656 *** MODULE NAME:(USER1) 2011-06-24 07:28:18.656 *** SERVICE NAME:(orcl) 2011-06-24 07:28:18.656 *** SESSION ID:(416.921) 2011-06-24 07:28:18.656 Received ORADEBUG command 'dump modified_parameters 1'
from process Unix process pid: 10443, image: DYNAMICALLY MODIFIED PARAMETERS: plsql_optimize_level = 0
Aquí podemos ver que plsql_optimize_level tiene un valor de 0.