dba:oracle:oracle_sql_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
dba:oracle:oracle_sql_querys [2023/01/31 08:15] – dodger | dba:oracle:oracle_sql_querys [2023/01/31 08:16] (current) – removed dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | <WRAP center round important 60%> | ||
- | THIS document is [[dba: | ||
- | |||
- | |||
- | ====== [DOC][OLD] Oracle basic queries ====== | ||
- | |||
- | |||
- | ======SECCIÓN : QUERYS====== | ||
- | |||
- | ====longest querys==== | ||
- | Las querys con duración de más de 10 segundos: | ||
- | <code sql> | ||
- | COL SQL_TEXT FORMAT A130 WORD WRAPPED | ||
- | SELECT * FROM | ||
- | ( | ||
- | SELECT | ||
- | CPU_TIME/ | ||
- | CPU_TIME, | ||
- | EXECUTIONS, | ||
- | SQL_TEXT | ||
- | FROM V$SQLAREA | ||
- | WHERE EXECUTIONS> | ||
- | ORDER BY EXECUTIONS DESC, TIME_PER_EXECUTION DESC, CPU_TIME DESC | ||
- | ) | ||
- | WHERE ROWNUM <31; | ||
- | </ | ||
- | |||
- | |||
- | Ver también: | ||
- | * V$SQL | ||
- | * V$SESSION | ||
- | |||
- | |||
- | ====Long operations==== | ||
- | La vista LONGOPS nos da información de las operaciones largas/ | ||
- | * [[http:// | ||
- | * [[https:// | ||
- | |||
- | |||
- | <code sql> | ||
- | |||
- | COL UNITS FORMAT A12 | ||
- | COL opname FORMAT A30 | ||
- | COL target FORMAT A30 | ||
- | COL message FORMAT A70 | ||
- | COL SID_SERIAL FORMAT A15 | ||
- | |||
- | select * from | ||
- | ( | ||
- | select | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | from | ||
- | v$session_longops | ||
- | order by start_time desc | ||
- | ) | ||
- | where rownum <=1; | ||
- | </ | ||
- | |||
- | ====Bind variables de querys==== | ||
- | |||
- | <code sql> | ||
- | COL SQL_ID FORMAT A15 | ||
- | COL NAME FORMAT A30 | ||
- | COL POSITION FORMAT 999 | ||
- | COL DATATYPE_STRING FORMAT A20 | ||
- | COL VALUE_STRING FORMAT A100 | ||
- | SELECT SQL_ID, | ||
- | LAST_CAPTURED, | ||
- | HASH_VALUE, | ||
- | NAME, | ||
- | POSITION, | ||
- | DUP_POSITION, | ||
- | DATATYPE_STRING, | ||
- | VALUE_STRING | ||
- | FROM V$SQL_BIND_CAPTURE | ||
- | WHERE sql_id=' | ||
- | ORDER BY 1,2,4 | ||
- | ; | ||
- | </ | ||
- | ======SECCIÓN : Execution PLANS====== | ||
- | ===== Hidden options/ | ||
- | |||
- | The display_cursor funcion has the following undocumented format options: | ||
- | * ALL | ||
- | * ADVANCED | ||
- | * OUTLINE | ||
- | * PROJECTION | ||
- | |||
- | |||
- | <code sql> | ||
- | SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(' | ||
- | </ | ||
- | |||
- | [[https:// | ||
- | |||
- | |||
- | ===== Ver un plan de ejecución ===== | ||
- | ==== De una query ==== | ||
- | Capturar la query: | ||
- | <code sql> | ||
- | EXPLAIN PLAN FOR | ||
- | SELECT * FROM emp e, dept d | ||
- | WHERE e.deptno = d.deptno | ||
- | AND e.ename=' | ||
- | </ | ||
- | y | ||
- | <code sql> | ||
- | SET LINESIZE 280 | ||
- | SET PAGESIZE 999 | ||
- | SELECT * FROM table(DBMS_XPLAN.DISPLAY); | ||
- | </ | ||
- | |||
- | ==== De un SQL_ID ==== | ||
- | <code sql> | ||
- | SELECT * FROM table ( | ||
- | | ||
- | </ | ||
- | |||
- | ==== Eliminar un Plan de ejecución ==== | ||
- | Primero, hay que saber el SQL_ID y el PLAN_HASH_VALUE, | ||
- | <code sql> | ||
- | select sql_id, sql_text, hash_value, plan_hash_value | ||
- | from v$sql | ||
- | where sql_text like ' | ||
- | / | ||
- | </ | ||
- | |||
- | Después, ejecutar el siguiente proceseo: | ||
- | <code sql> | ||
- | vari rc number | ||
- | exec : | ||
- | </ | ||
- | Substituir: | ||
- | * ''< | ||
- | * ''< | ||
- | |||
- | Habitualmente con eso vale, auqnue a lo mejor hay que hacer " | ||
- | |||
- | Obtener los valores necesarios para borrarlo (los hashes): | ||
- | <code sql> | ||
- | COL SQL_HANDLE FORMAT A50 | ||
- | COL PLAN_NAME FORMAT A50 | ||
- | select sql_handle, plan_name, enabled, accepted, | ||
- | </ | ||
- | |||
- | Ejecutar el siguiente procedimiento para borarrlo: | ||
- | <code sql> | ||
- | SET SERVEROUTPUT ON | ||
- | DECLARE | ||
- | l_plans_dropped | ||
- | BEGIN | ||
- | l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( | ||
- | sql_handle => '< | ||
- | plan_name | ||
- | | ||
- | DBMS_OUTPUT.put_line(l_plans_dropped); | ||
- | END; | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ==== Links ==== | ||
- | |||
- | * http:// | ||
- | * http:// | ||
- | * http:// | ||
- | ======SECCIÓN : Performance====== | ||
- | ===== Hard parse ===== | ||
- | * How to Identify Hard Parse Failures (Doc ID 1353015.1) | ||
- | * http:// | ||
- | ===== Top 100 querys ===== | ||
- | Esta query se basa en las estadísticas guardadas en el repositorio awr. | ||
- | |||
- | <code sql> | ||
- | COL CPU_RANK FORMAT 999 ; | ||
- | COL TOTAL_CPU_TIME FORMAT 999999999 ; | ||
- | COL SQLID FORMAT A23 ; | ||
- | select CPU_RANK, SQLID, TOTAL_CPU_TIME, | ||
- | from ( | ||
- | select | ||
- | RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank, | ||
- | ROUND(MAX(S. CPU_TIME_TOTAL)/ | ||
- | MODULE | ||
- | from | ||
- | dba_hist_sqlstat s, | ||
- | dba_hist_snapshot sn | ||
- | where | ||
- | | ||
- | group by | ||
- | | ||
- | ) | ||
- | where cpu_rank <=100 | ||
- | ; | ||
- | </ | ||
- | ===== SGA stats ===== | ||
- | Full: | ||
- | <code sql> | ||
- | select POOL, NAME, BYTES/ | ||
- | </ | ||
- | Únicamente lo libre: | ||
- | <code sql> | ||
- | select POOL, NAME, BYTES/ | ||
- | </ | ||
- | |||
- | |||
- | ===== Shared pool histogram ===== | ||
- | <code sql> | ||
- | COL BEGIN_INTERVAL_TIME FORMAT A50 | ||
- | COL END_INTERVAL_TIME FORMAT A50 | ||
- | COL NAME FORMAT A50 | ||
- | SELECT sn.BEGIN_INTERVAL_TIME, | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | FROM dba_hist_snapshot sn, DBA_HIST_SGASTAT sg | ||
- | | ||
- | AND POOL = ' | ||
- | AND NAME LIKE '%free memory%' | ||
- | ORDER BY BEGIN_INTERVAL_TIME | ||
- | ; | ||
- | </ | ||
- | ===== Library cache Hit ratio ===== | ||
- | <code sql> | ||
- | SELECT ' | ||
- | ROUND ( | ||
- | (congets.VALUE + dbgets.VALUE - physreads.VALUE) | ||
- | * 100 | ||
- | / (congets.VALUE + dbgets.VALUE), | ||
- | 2) | ||
- | VALUE | ||
- | FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads | ||
- | | ||
- | AND dbgets.NAME = 'db block gets' | ||
- | AND physreads.NAME = ' | ||
- | UNION ALL | ||
- | SELECT ' | ||
- | | ||
- | SIGN ( | ||
- | ROUND ( | ||
- | (ec.VALUE - pc.VALUE) | ||
- | * 100 | ||
- | / DECODE (ec.VALUE, 0, 1, ec.VALUE), | ||
- | 2)), | ||
- | -1, 0, | ||
- | ROUND ( | ||
- | | ||
- | 2)) | ||
- | FROM v$sysstat ec, v$sysstat pc | ||
- | | ||
- | AND pc.NAME IN (' | ||
- | UNION ALL | ||
- | SELECT ' | ||
- | ROUND ( | ||
- | ms.VALUE | ||
- | / DECODE ( (ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE)) | ||
- | * 100, | ||
- | 2) | ||
- | FROM v$sysstat ds, v$sysstat ms | ||
- | WHERE ms.NAME = 'sorts (memory)' | ||
- | UNION ALL | ||
- | SELECT 'SQL Area get hitrate', | ||
- | FROM v$librarycache | ||
- | WHERE namespace = 'SQL AREA' | ||
- | UNION ALL | ||
- | SELECT 'Avg Latch Hit (No Miss)', | ||
- | ROUND ( (SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2) | ||
- | FROM v$latch | ||
- | UNION ALL | ||
- | SELECT 'Avg Latch Hit (No Sleep)', | ||
- | ROUND ( (SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2) | ||
- | FROM v$latch; | ||
- | </ | ||
- | |||
- | ===== Espacio usado dentro de la SGA ===== | ||
- | <code sql> | ||
- | set pagesize 132 | ||
- | |||
- | column owner format a16 | ||
- | column name format a36 | ||
- | column sharable_mem format 999,999,999 | ||
- | column executions | ||
- | </ | ||
- | |||
- | ==== Memory Usage of Shared Pool Order - Biggest First ==== | ||
- | |||
- | <code sql> | ||
- | column name format 45 | ||
- | select | ||
- | where sharable_mem > 10000 | ||
- | and type in (' | ||
- | order by sharable_mem desc | ||
- | / | ||
- | </ | ||
- | |||
- | ==== Loads into Shared Pool - Most Loads First ==== | ||
- | <code sql> | ||
- | select | ||
- | where loads > 3 | ||
- | and type in (' | ||
- | order by loads desc | ||
- | / | ||
- | </ | ||
- | ==== Executions of Objects in the Shared Pool - Most Executions First ==== | ||
- | <code sql> | ||
- | select | ||
- | where executions | ||
- | and type in (' | ||
- | order by executions | ||
- | / | ||
- | </ | ||
- | |||
- | ===== AWR ===== | ||
- | |||
- | * [[awr|Automated Workload Repository (awr)]] | ||
- | |||
- | |||
- | ===== Paralelismo de objetos ===== | ||
- | |||
- | |||
- | **DEGREE** in dba_* views is VARCHAR! | ||
- | ==== indexes ==== | ||
- | |||
- | Object parallelism ignoring degree 0 or 1 | ||
- | <code sql> | ||
- | SELECT | ||
- | owner, | ||
- | INDEX_NAME, | ||
- | degree | ||
- | FROM | ||
- | DBA_INDEXES | ||
- | WHERE | ||
- | NOT RTRIM(LTRIM(DEGREE)) IN ( ' | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | ==== TABLES ==== | ||
- | |||
- | Object parallelism ignoring degree 0 or 1 | ||
- | <code sql> | ||
- | SELECT OWNER, | ||
- | TABLE_NAME, | ||
- | DEGREE | ||
- | FROM | ||
- | DBA_TABLES | ||
- | WHERE | ||
- | NOT RTRIM(LTRIM(DEGREE)) IN ( ' | ||
- | ; | ||
- | </ | ||
- | |||
- | ======SECCIÓN : Otros====== | ||
- | |||
- | ===== Añadir un redo group ===== | ||
- | <code sql> | ||
- | ALTER DATABASE ADD LOGFILE GROUP 5 (' | ||
- | </ | ||
- | Hay que tener en cuenta los thread groups (rac), si tenemos varios thread groups, habrá que añadirlo al thread correspondiente: | ||
- | <code sql> | ||
- | ALTER DATABASE ADD LOGFILE thread 1 GROUP 5 (' | ||
- | </ | ||
- | |||
- | ===== Errores de usuario ===== | ||
- | Hay veces que show err no muestra todos los errores: | ||
- | <code sql> | ||
- | select * from user_errors where name like ' | ||
- | </ | ||
- | Desde SYS pueden no aparecer los errores, hay que conectarse con el usuario. | ||
- | ===== Crear un directorio ===== | ||
- | <code sql> | ||
- | CREATE DIRECTORY < | ||
- | </ | ||
- | ===== List directories ===== | ||
- | <code sql> | ||
- | col directory_name format a40 | ||
- | col directory_path format a160 | ||
- | select directory_name, | ||
- | </ | ||
- | |||
- | ===== Estadísticas de generacion de REDO ===== | ||
- | |||
- | <code SQL> | ||
- | FROM | ||
- | ( | ||
- | SELECT To_Char(First_Time,' | ||
- | FROM v$log_history | ||
- | GROUP BY To_Char(First_Time,' | ||
- | ORDER BY 1 DESC | ||
- | ) A, | ||
- | ( | ||
- | SELECT Avg(BYTES) AVG# | ||
- | FROM v$log | ||
- | ) B | ||
- | ORDER BY DAY | ||
- | ; | ||
- | </ | ||
- | ===== Log Buffer Hit Rate Histogram ===== | ||
- | In order to track the amount of redo read from the in-memory log buffer vs. the amount of redo read from disk, a log buffer hit rate histogram has also been implemented. A new '' | ||
- | |||
- | <code SQL> | ||
- | COL BUFSIZE FORMAT A30 | ||
- | COL BUFINFO FORMAT A30 | ||
- | select BUFSIZE, RDMEMBLKS, RDDISKBLKS, HITRATE, BUFINFO from X$LOGBUF_READHIST; | ||
- | </ | ||
- | It returns the following columns: | ||
- | |||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | * '' | ||
- | In the histogram, the data in the row pointed to by '' | ||
- | |||
- | (Doc ID 951152.1) | ||
- | ===== Switch de Archived por hora ===== | ||
- | <code SQL> | ||
- | SELECT to_date(first_time) DAY, | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | to_char(sum(decode(to_char(first_time,' | ||
- | from v$log_history | ||
- | where to_date(first_time) > sysdate -8 | ||
- | GROUP by to_char(first_time,' | ||
- | order by to_date(first_time) | ||
- | / | ||
- | </ | ||
- | |||
- | ===== Flush de redos ===== | ||
- | <code sql> | ||
- | alter system checkpoint ; | ||
- | </ | ||
- | |||
- | ===== Stopping hardway ===== | ||
- | Stop/ | ||
- | <code sql> | ||
- | sqlplus -prelim "/ as sysdba" | ||
- | shutdown abort | ||
- | </ | ||
- | |||
- | =====Localizar una query desde un sql_id del alert===== | ||
- | Para localizar la query, tenemos que tener el SQL_ID que aparece en el alert y esperar que la query no se haya ido de memoria. | ||
- | <code sql> | ||
- | |||
- | ===== Dropping the database ===== | ||
- | <code sql> | ||
- | startup mount exclusive restrict; | ||
- | drop database; | ||
- | exit ; | ||
- | </ | ||
- | |||
- | ===== Listar Parámetros ocultos e indocumentados ===== | ||
- | Información completa: | ||
- | <code sql> | ||
- | COL NAME FORMAT A60 | ||
- | COL VALUE FORMAT A30 | ||
- | COL DEFLT FORMAT A30 | ||
- | COL TYPE FORMAT A10 | ||
- | COL description FORMAT A120 | ||
- | select | ||
- | b.ksppstvl value, | ||
- | b.ksppstdf deflt, | ||
- | decode(a.ksppity, | ||
- | a.ksppdesc description | ||
- | from sys.x$ksppi a, sys.x$ksppcv b | ||
- | where a.indx = b.indx | ||
- | and a.ksppinm like ' | ||
- | order by name ; | ||
- | </ | ||
- | únicamente nombre y descripción: | ||
- | <code sql> | ||
- | COL KSPPINM FORMAT A60 | ||
- | COL ksppdesc FORMAT A120 | ||
- | select ksppinm, ksppdesc | ||
- | from x$ksppi | ||
- | where substr(ksppinm, | ||
- | order by 1,2; | ||
- | </ | ||
- | ===== db_link / dblink ===== | ||
- | DATABASE LINKS! | ||
- | ==== crear ==== | ||
- | <code sql> | ||
- | sqlplus SYSTEM | ||
- | </ | ||
- | <code sql> | ||
- | CREATE DATABASE LINK VOXEL CONNECT TO SYSTEM identified by ******* using ' | ||
- | </ | ||
- | |||
- | ==== listar ==== | ||
- | |||
- | <code sql> | ||
- | SET PAUSE 'Press Return to Continue' | ||
- | SET PAGESIZE 60 | ||
- | SET LINESIZE 300 | ||
- | |||
- | COLUMN owner FORMAT A30 | ||
- | COLUMN db_link FORMAT A50 | ||
- | COLUMN username FORMAT A30 | ||
- | COLUMN host FORMAT A30 | ||
- | |||
- | SELECT owner, | ||
- | | ||
- | | ||
- | host | ||
- | FROM | ||
- | ORDER BY owner, db_link | ||
- | / | ||
- | </ | ||
- | |||
- | ==== Borrar ==== | ||
- | <code sql> | ||
- | DROP DATABASE LINK < | ||
- | </ | ||
- | o | ||
- | <code sql> | ||
- | DROP PUBLIC DATABASE LINK < | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== Multi-line comments ===== | ||
- | Para insertar un comentario multi-linea: | ||
- | * Habilitar las blanklines: | ||
- | <code sql> | ||
- | SET SQLBLANKLINES ON | ||
- | </ | ||
- | * Insertar el comentario: | ||
- | <code sql> | ||
- | COMMENT ON TABLE TESTTABLE IS 'TEST | ||
- | |||
- | MULTILINE | ||
- | |||
- | COMMENT! | ||
- | ' | ||
- | ; | ||
- | </ | ||
- | |||
- | ===== REGEXP_LIKE ===== | ||
- | |||
- | * [[http:// | ||
- | * [[http:// | ||
- | * [[http:// | ||
- | * [[http:// | ||
- | |||
- | ===== DBMS_SCHEDULER ===== | ||
- | |||
- | ==== Listar jobs ==== | ||
- | |||
- | <code sql> | ||
- | COL owner FORMAT A20 | ||
- | COL job_name FORMAT A40 | ||
- | COL job_subname FORMAT A30 | ||
- | COL job_style FORMAT A30 | ||
- | COL job_creator FORMAT A30 | ||
- | COL client_id FORMAT A30 | ||
- | COL program_owner FORMAT A20 | ||
- | COL program_name FORMAT A35 | ||
- | COL job_type FORMAT A30 | ||
- | COL job_action FORMAT A30 | ||
- | COL schedule_owner FORMAT A20 | ||
- | COL schedule_name FORMAT A30 | ||
- | COL REPEAT_INTERVAL FORMAT A70 | ||
- | COL START_DATE FORMAT A50 | ||
- | |||
- | SELECT owner, | ||
- | | ||
- | -- job_subname, | ||
- | -- job_style, | ||
- | -- job_creator, | ||
- | -- client_id, | ||
- | -- program_owner, | ||
- | -- program_name, | ||
- | | ||
- | -- job_action, | ||
- | -- schedule_owner, | ||
- | -- schedule_name, | ||
- | | ||
- | | ||
- | | ||
- | FROM dba_SCHEDULER_JOBS | ||
- | WHERE ENABLED=TRUE | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | ==== Historial de ejecutiones ==== | ||
- | <code sql> | ||
- | COL owner FORMAT A20 | ||
- | COL job_name FORMAT A40 | ||
- | COL job_subname FORMAT A30 | ||
- | COL job_style FORMAT A30 | ||
- | COL job_creator FORMAT A30 | ||
- | COL client_id FORMAT A30 | ||
- | COL program_owner FORMAT A20 | ||
- | COL program_name FORMAT A35 | ||
- | COL job_type FORMAT A30 | ||
- | COL job_action FORMAT A30 | ||
- | COL schedule_owner FORMAT A20 | ||
- | COL schedule_name FORMAT A30 | ||
- | COL REPEAT_INTERVAL FORMAT A70 | ||
- | COL START_DATE FORMAT A50 | ||
- | |||
- | SELECT owner, | ||
- | | ||
- | -- job_subname, | ||
- | -- job_style, | ||
- | -- job_creator, | ||
- | -- client_id, | ||
- | -- program_owner, | ||
- | -- program_name, | ||
- | | ||
- | -- job_action, | ||
- | -- schedule_owner, | ||
- | -- schedule_name, | ||
- | | ||
- | | ||
- | | ||
- | FROM dba_SCHEDULER_JOBS | ||
- | WHERE ENABLED=TRUE | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | |||
- | ======SECCIÓN : Documentos adicionales====== | ||
- | |||
- | =====Auditoria===== | ||
- | * [[audit_mini-howto|Auditing Mini-HOWTO]] | ||
- | |||
- | =====DataGuard===== | ||
- | |||
- | * [[dataguard_mini-howto# | ||
- | |||
- | =====RMAN===== | ||
- | * [[RMAN_basic_querys|RMAN basic querys]] | ||
- | |||
- | =====RAC Status===== | ||
- | * [[CLI_tools|CommandLine tools para el control de Oracle]] | ||
- | |||
- | |||
- | =====Links===== | ||
- | See [[: | ||
- | |||
- | |||
- | |||
- | ===== Lista de DocId interesantes ===== | ||
- | * How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1) | ||
- | |||
dba/oracle/oracle_sql_querys.1675152903.txt.gz · Last modified: 2023/01/31 08:15 by dodger