Curiosidades del Oráculo..

Veremos aca algo de Tunning, el "QUE", "COMO", y "PARA QUE". Create a plan table @?/rdbms/admin/utlxplan.sql ___________________________________________________ Autotrace To switch it on: column plan_plus_exp format a100 set autotrace on explain # Displays the execution plan only. set autotrace traceonly explain # dont run the query set autotrace on # Shows the execution plan as well as statistics of the statement. set autotrace on statistics # Displays the statistics only. set autotrace traceonly # Displays the execution plan and the statistics ________________________________________________________ Find a query's hash Put something unique in the like clause select hash_value, sql_text from v$sqlarea where sql_text like '%TIMINGLINKS%FOLDERREF%' ______________________________________________________________________ Grab the sql associated with a hash select sql_text from v$sqlarea where hash_value = '&hash' / _____...

Este query nos muestro cuanto pesa una base de datos, saca un calculo en base a los data files asignados y los data files free en espacio. col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p /

Aca un script muy importante.. siempre nos piden ... creame un usuario igual a tal otro.. y tenemos que dar muchas vueltas... aca todo en un solo paso.. espero les sea util...  set lines 999 pages 999 set verify off set feedback off set heading off select username from dba_users order by username / undefine user   accept userid prompt 'Enter user to clone: '  accept newuser prompt 'Enter new username: '  accept passwd prompt 'Enter new password: '   select username , created  from dba_users  where lower(username) = lower('&newuser')  / accept poo prompt 'Continue? (ctrl-c to exit)'   spool /tmp/user_clone_tmp.sql   select 'create user ' || '&newuser' || ' identified by ' || '&passwd' || ' default tablespace ' || default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';' "user"  from dba_users  where username = '...

Este script te lo lo dice ... col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p /

Quien alguna vez no habra protestado para instalar las statpack en oracle, Este documento lo aporto una amiga de Madrid, Alicia Lopez Serrano (DBA Oracle) 1. pasos necesarios para la automatización de la toma de datos de una base de datos En las base de datos que estén en Oracle8i deberemos activar la temporización de las estadísticas, para ello ejecutaremos en la base de datos con un usuario con permisos de DBA la sentencia:   Alter system set timed_statistics=true;  En Oracle9i esto no sería necesario puesto que todas las bases de datos son creadas por defecto con este parámetro a TRUE.  A continuación vemos los pasos necesarios para la utilización del paquete dbms_statspack que se encarga de tomar las imágenes de las estadísticas internas de funcionamiento de la base de datos.   1.- Creamos un tablespace de 100 Mbytes para el usuario PERFSTAT, propietario de los objetos donde se almacenarán las estadísticas de funcionamiento de la base de datos.   create tablespace p...

Bueno aca va algo de tuning. Este es un Script que chequea toda la base de datos y da tips de que tocar y que no tocar en la base de datos.  ## by Norberto Fabian Crea ## Agosto de 2000 ## para Version 8i - 9i y 10g ## SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF SELECT * FROM   v$database; PROMPT DECLARE   v_value  NUMBER;   FUNCTION Format(p_value  IN  NUMBER)      RETURN VARCHAR2 IS   BEGIN     RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || '  ';   END; BEGIN   -- --------------------------   -- Dictionary Cache Hit Ratio   -- --------------------------   SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100   INTO   v_value   FROM   v$rowcache;   DBMS_Output.Put('Dictionary Cache Hit Ratio       : ' || Format(v_value));   IF v_value     DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');   ELSE     DBMS_Output.Put_Line('Value Acceptable.');     END ...

What Are Latches? Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the SGA). In simple terms, latches prevent two processes from simultaneously updating — and possibly corrupting — the same area of the SGA. Oracle sessions need to update or read from the SGA for almost all database operations. For instance: • When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU chain1. • When a session reads a block from the SGA, it will modify the LRU chain. • When a new SQL statement is parsed, it will be added to the library cache within the SGA. • As modifications are made to blocks, entries are placed in the redo buffer. • The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”). • The redo log writer writes entries from the redo buffer to the redo logs. Latches prevent any of these operations from colliding and possibly corruptin...