¿Cómo configurar pasos de ajuste del rendimiento para optimizar el rendimiento de Oracle10g?
1. La matriz de discos estándar
RAID 10 es más adecuada para sistemas OLTP que RAID5. RAID10 primero refleja el disco y luego lo segmenta. Debido al frecuente acceso a datos a pequeña escala, es adecuado para OLTP. La ventaja de RAID5 es que puede aprovechar al máximo el espacio en disco y reducir el coste total de la matriz. Pero cuando la matriz envía una solicitud de escritura, el bloque modificado en el disco debe cambiarse, por lo que es necesario leer el bloque de "paridad" del disco, calcular el nuevo bloque de paridad utilizando el bloque modificado y luego escribir los datos en el disco, lo que limitará el rendimiento. Tiene un impacto en el rendimiento, RAID5 es adecuado para sistemas OLAP.
2. Distribución de archivos de datos
Separe las siguientes cosas para evitar la competencia de discos.
Espacio de tabla del sistema
Espacio de tabla temporal
Restaurar espacio de tabla
Registro de rehacer en línea (en el disco más rápido)
Disco del sistema operativo
Directorio de instalación de ORACLE
Archivos de datos a los que se accede con frecuencia
Espacio de tabla de índice
Área de archivo (siempre debe estar separada del datos a recuperar)
Ejemplo:
/:Sistema
/u01: Software Oracle
/u02: Espacio de tabla temporal, archivo de control 1
/u03: Deshacer segmento, archivo de control 2
/u04: Rehacer registro, archivar registro, archivo de control 4
/u05: Sistema, Espacio de tabla SYSAUX
/u06: Datos 1, archivo de control 3
/u07: Espacio de tabla de índice
/u08: Datos 2
Consulta para determinar el problema de IO a través de la siguiente declaración
Seleccione nombre, phyrds, phywrts, readtim, writetim
De v $ estado del archivo a, v $ archivo de datos b
Donde a.Número de archivo=b.El número de archivo está ordenado por Redim·desc;
3. Aumentar el archivo de registro
★Aumentar el tamaño del archivo de registro, aumentando así la proporción de operaciones de inserción, eliminación y actualización a gran escala.
Consultar el estado del archivo de registro
Seleccione a.member, b.* del archivo v＄log a, v＄log b donde a.GROUP#=b.GROUP#
p>Hora de cambio del registro de consultas
seleccione b.RECID, to_char(b.FIRST_TIME, ' yyyy-mm-DD hh24:mi:ss ')start_TIME, a.RECID, to_char( a .FIRST_TIME, 'yyyy-mm-dd hh24:mi:ss') end_time, round ((a . FIRST_TIME-b . FIRST_TIME) * 25) * 60, 2) minutos
Desde v$log_historya, v$log_historyb
donde a.RECID=b.RECID+1
se ordena por desc por primera vez
Aumentar el tamaño de los archivos de registro y aumente los archivos de registro para cada grupo (un archivo maestro y un archivo multiplex).
u Se agregó el parámetro LOG_CHECKPOINT_INTERVAL, que ahora está obsoleto.
Si el registro se cambia en menos de media hora, aumente el tamaño del registro de rehacer en línea. Si cambia con frecuencia al procesar tareas por lotes grandes, aumente la cantidad de registros de rehacer en línea.
Agregue el miembro del archivo de registro "/log.ora" al grupo 1;
altere el miembro del archivo de registro de eliminación de la base de datos "/log.ora";
4. Restaurar espacio de tabla
Modificar tres parámetros iniciales:
Deshacer gestión=automático
Restaurar espacio de tabla=Restaurar Yunhai
DESHACER _ RETENCIÓN = <Minutos>
5. No realice la clasificación en el espacio de la tabla del sistema.
2. Configuración de parámetros de inicialización
El soporte máximo para direccionamiento de 32 bits debe ser 2 elevado a 32, que es el tamaño de 4G. Pero, de hecho, los sistemas de 32 bits (sistemas MS de 32 bits como XP, Windows 2003 y sistemas Linux de 32 bits como Ubuntu) utilizan tecnología de reasignación de memoria para utilizar la memoria 4G. Requiere soporte para placa base y sistema. Si desactiva la función de reasignación del BIOS de la placa base, el sistema no podrá usar la memoria 4G y es posible que solo alcance 3,5G, y lo que ve en Windows generalmente es 3,25G. Por lo tanto, SGA se establece en el 40% de la memoria, pero no puede exceder los 3,25 G
1 Parámetros de inicialización importantes
Tamaño máximo de SGA
Objetivo de SGA
.l PGA_AGGREGATE_Target
Tamaño de caché de la base de datos
l Tamaño del grupo compartido
2. Ajuste DB_CACHE_SIZE para mejorar el rendimiento.
Establece el tamaño del área SGA en la memoria para almacenar y procesar datos. La lectura de datos de la memoria es 10.000 veces más rápida que la lectura de datos del disco.
Según la siguiente consulta, puede encontrar la tasa de aciertos de la caché de datos.
seleccione suma(decodificar(nombre, 'lecturas físicas', valor, 0)) phys,
suma(decodificar(nombre, 'bloque db obtiene', valor, 0)) get,
suma(decodificar(nombre,' consistente obtiene ',valor,0)) con_gets,
(1- (suma(decodificar(nombre,' lecturas físicas ',valor , 0))/(suma(decode(nombre, ' db block gets ', valor, 0)) + suma (decode (nombre, ' consistente gets ', valor, 0))) * 100 tasa de aciertos
de v $ sysstat
Un programa de procesamiento de transacciones debe garantizar una tasa de aciertos de más del 95%. Aumentar la tasa de aciertos del 90% al 98% puede aumentar el rendimiento de ORACLE en un 500%. se utilizan para analizar el rendimiento del sistema y no se presta mucha atención a la tasa de aciertos. Sin embargo, ahora el caché de la biblioteca y el caché del diccionario todavía utilizan la tasa de aciertos como método de ajuste básico.
Al ajustar DB_CACHE_SIZE, utilice V$. DB_CACHE. _ ADVICE.
seleccione size_for_estimate, estd_physical_read_factor, estd_physical_reads
de v$db_cache_advice
donde nombre = 'DEFAULT
Si la consulta hits Si es demasiado bajo, significa que el índice falta o es limitado y que la ejecución de la consulta SQL a través de la vista V$SQLAREA es muy lenta.
3. los datos leídos
OLTP son generalmente 8K
OLAP son generalmente 16K o 32K
4.
Al ajustar este parámetro correctamente, las sentencias SQL se pueden disfrutar igualmente, de modo que las sentencias SQL utilizadas se pueden encontrar en la memoria. Para reducir la cantidad de análisis duros y optimizar el uso de * * * áreas SQL, es necesario utilizar procedimientos almacenados y vincular variables tanto como sea posible.
Asegúrese de que la tasa de aciertos de la caché del diccionario de datos sea superior al 95 %
select((1-sum(get misses)/(sum(gets)+sum(get misses)) )* 100 ) tasa de aciertos
de v$rowcache
donde obtiene+obtiene miss <& gt0;
Si la tasa de aciertos es inferior al 99% , puede considerar aumentarlo. Grupo compartido para mejorar la tasa de aciertos de la caché de la biblioteca.
SELECT SUM(pin) "ejecutar", SUM(recargar) "error de caché durante la ejecución", 1 - SUM(recargar)/SUM(pin)
De V$LIBRARYCACHE
La regla habitual es establecerlo entre el 50% y el 150% del tamaño DB_CACHE_SIZE. En sistemas que utilizan una gran cantidad de procedimientos o paquetes almacenados pero tienen memoria limitada, la asignación final es del 150%. En sistemas que no utilizan procedimientos almacenados pero asignan una gran cantidad de memoria a DB_CACHE_SIZE, este parámetro debe ser del 10% al 20%.
5. Ajuste PGA_AGGREGATE_TARGET para optimizar la aplicación de memoria.
u OLTP: Memoria total *80%*20%
u DSS: Memoria total *80%*50%
6.25 parámetros de inicialización importantes
DB_CACHE_SIZE: Memoria de inicialización asignada al caché de datos.
Destino SGA: Establezca este parámetro si se utiliza la gestión automática de memoria. Establezca en 0 para desactivarlo.
PGA_AGGREGATE_TARGET: El valor máximo de memoria virtual PGA para todos los usuarios.
SHARED_POOL_SIZE: Memoria asignada para diccionario de datos, SQL y PL/SQL.
SGA_max_size: La memoria máxima que SGA puede crecer dinámicamente.
Modo optimizador:
CURSOR_SHARING: la conversión de SQL literal a SQL con cambios de enlace puede reducir la sobrecarga del análisis.
OPTIMIZER_INDEX_COST_ADJ: ajusta la sobrecarga del escaneo del índice y la sobrecarga del escaneo completo de la tabla. Establecer esto entre 1 y 10 obligará al uso frecuente del índice para garantizar su disponibilidad.
QUERY_REWRITE_ENABLED: Se utiliza para habilitar vistas materializadas e índices basados en funciones.
DB_FILE_MULTIBLOCK_READ_COUNT: para un escaneo completo de la tabla, para realizar IO de manera más eficiente, este parámetro puede leer varios bloques en un IO.
LOG_BUFFER: Asigna un búfer para transacciones no confirmadas en la memoria (parámetros no dinámicos).
DB_KEEP_CACHE_SIZE: Memoria asignada al pool KEEP o caché de datos adicional.
Tamaño de caché de reciclaje de base de datos:
DBWR_IO_SLAVES: si no hay IO asíncrono, este parámetro es equivalente al número de escritores de SGA al disco asignados por DB_WRITER_PROCESSES para simular IO asíncrono. Si tiene IO asíncrona, use DB_WRITER_PROCESSES para configurar múltiples escritores para escribir bloques sucios más rápido durante DBWR.
LARGE_POOL_SIZE: el número total de bloques asignados a un grupo grande para PLSQL grande o alguna otra opción ORACLE poco utilizada.
STATISTICS_LEVEL: Habilita información de orientación y opcionalmente proporciona más estadísticas del sistema operativo para mejorar las decisiones del optimizador. Valor predeterminado: típico
JAVA_POOL_SIZE: memoria asignada para los procedimientos almacenados JAVA utilizados por la JVM.
JAVA_MAX_SESSIONSPACE_SIZE: Memoria máxima utilizada para rastrear el estado de la sesión del usuario de las clases JAVA.
MAX_SHARED_SERVERS: El límite superior de servidores compartidos cuando se utilizan * * * servidores compartidos * * *.
WORKAREA_SIZE_POLICY: Habilita la gestión automática del tamaño de PGA.
FAST_START_MTTR_TARGET: Tiempo/segundo aproximado para completar la recuperación del fallo
LOG_CHECKPOINT_INTERVAL: Frecuencia del punto de control.
OPEN_CURSORS: Especifica el tamaño del área privada donde se guardan las declaraciones de los usuarios, por lo que configurarlo demasiado alto provocará ORA-4031.
DB_BLOCK_SIZE: El tamaño de bloque predeterminado de la base de datos.
OPTIMIZER_DYNAMIC_SAMPLING: Controla el número de bloques leídos por consultas de muestreo dinámico. Esto es muy útil para sistemas que utilizan tablas temporales globales.
3. Ajuste SQL 1. Consejos de uso
1.1 Cambie la ruta de ejecución
Utilice el parámetro OPTIMIZER_MODE para especificar cómo utilizar el optimizador. El valor predeterminado es ALL_ROWS.
ALL_ROWS ejecuta la consulta en todas las filas con el mejor rendimiento.
FIRST_ROWS(n) permite al optimizador recuperar la primera fila más rápido:
select/*+FIRST_ROWS(1)*/store_id,… from tbl_store
1.2 Uso de sugerencias de métodos de acceso
Permite a los desarrolladores cambiar el método de acceso de consulta real y, a menudo, utilizar sugerencias de índice.
Clúster fuerza el uso de clústeres
Todos
Mixto
Sintaxis de índice: /*+index(índice de tabla 1, índice 2 .. .)*/Columna 1,...
Si no se especifica ningún índice, el optimizador elegirá el mejor índice.
SELECT/*+INDEX */STORE _ ID FROM TBL store
INDEX_ASC 8I comienza en orden ascendente de forma predeterminada, por lo que el efecto es el mismo que INDEX.
INDEX_DESC
INDEX_COMBINE se utiliza para especificar múltiples índices de mapas de bits en lugar de seleccionar el mejor.
INDEX_JOIN solo necesita acceder a estos índices, ahorrando tiempo para recuperar la tabla nuevamente.
INDEX_FFS realiza un escaneo global rápido del índice y solo procesa el índice sin acceder a tablas específicas.
INDEX_SS
Índice SSX
Índice DESC
NO_INDEX
No, índice FFS
Sin índice
1.3 Utilice sugerencias de transformación de consultas
Es muy útil para el almacén de datos.
Hechos
Fusionar
Sintaxis NO_EXPAND: /*+ NO_EXPAND */ columna1,…
Asegúrate de estar en una lista que contenga de o No hay problemas. /*+Primera línea NO_EXPAND */
Sin datos
No_merge
Sin conversión de consulta
No_rewrite
Sin transformación de estrella
No_Not Set
Anular
Transformación de estrella
Revertir
p>USE_CONCAT
1.4 Consejos para usar operaciones de unión
Muestra cómo fusionar datos en tablas unidas. Dos sugerencias pueden afectar directamente el orden de conexión. LEADING especifica la tabla que se usará primero en el orden de unión, y ORDERED le dice al optimizador que una las tablas de acuerdo con el orden de las tablas en la cláusula FROM y use la primera tabla como tabla conductora (la tabla con más accesos a filas). .
Sintaxis ordenada: /*+ordered */column1,...
El orden de acceso a la tabla se basa en el orden de la tabla después de FROM.
Sintaxis inicial:/*+leading(table 1)*/column 1,...
Similar al orden, especifique la tabla de conducción.
Ninguno usa hash
No usa fusión
No usa
USE_HASH presupone suficiente HASH_AREA_SIZE o PGA_AGGREGATE_TARGET.
Suele proporcionar el mejor tiempo de respuesta para conjuntos de resultados grandes.
Utilizar combinación
USE_NL normalmente puede devolver una fila lo más rápido posible.
Utilice _NL_WITH_INDEX
1.5 adopta la ejecución paralela.
No paralelo
Sin índice paralelo
Paralelo
Índice paralelo
PQ_DISTRIBUTE
1.6 Otros consejos
APPEND no verifica si queda espacio restante en el bloque utilizado actualmente, sino que lo inserta directamente en la tabla, lo que agregará datos directamente al nuevo bloque.
El caché almacena todos los escaneos completos de la tabla en la memoria para que los datos se puedan encontrar directamente en la memoria sin tener que consultarlos en el disco.
cursor_shared_accurate
ubicación_de_conducción
muestreo dinámico
model_minimum_analysis
No apareció
NOKACHI
NO_PUSH_PREDECODE
NO_PUSH_SUBQ
SIN FILTRO DE CONEXIÓN DE PIXEL
Push_Predecode
PUSH_SUBQ fuerza a que la subconsulta se ejecute primero . Cuando una subconsulta devuelve algunas filas muy rápidamente, éstas se pueden utilizar para limitar el número de filas devueltas por la consulta externa, lo que puede mejorar considerablemente el rendimiento.
Ejemplo: select/*+push_subq */emp.empno, emp.ename.
desde emp, orden
donde EMP deptno =(seleccione deptno de departamento donde loc = ' 1 ')
PX_join_filter
p>QB_NAME
2. Ajustar la consulta
2.1 Seleccione la consulta que consume más recursos en V$SQLAREA.
hash_value: el valor hash de la sentencia SQL.
dirección: La dirección de la declaración SQL en SGA.
PARSING_USER_ID: el usuario que analizó el primer cursor de la declaración.
VERSION_COUNT: El número de cursores de declaración.
Versión reservada:
Sharable_memory: La * * * cantidad total de memoria compartida utilizada por el cursor.
Memoria _ persistente: el número total de memoria residente utilizada por el cursor.
Runtime_memory:Total RUNTIME_MEMORY:cursor a cursor.
SQL _ text: El texto de la declaración SQL (solo se pueden guardar los primeros 1000 caracteres de la declaración como máximo).
Al utilizar DBMS_APPLICATION_INFO, la sesión analiza la información del primer cursor.
Ordenar: el número de clasificación de la declaración.
CPU_TIME: Tiempo de CPU al analizar y ejecutar sentencias.
ELAPSED_TIME: El * * * tiempo necesario para analizar y ejecutar la declaración.
PARSE_CALLS: el número de llamadas de análisis (llamadas suaves y llamadas duras) de la declaración.
EJECUCIONES: El número de veces de ejecución de la declaración.
INVALIDACIONES: El número de fallos del cursor de la declaración.
LOADS: Número de sentencias de carga (descarga)
ROWS_PROCESSED: Número total de columnas devueltas por la sentencia.
seleccione b . nombre de usuario a . DISCO _ LECTURAS a . DISCO _ LECTURAS/decodificación (a . EJECUCIONES, 0, 1, a . EJECUCIONES) rds _ exec _ relación a . /p>
Desde v$sqlarea a, dba_users b
donde a.parse ID de usuario=b.ID de usuario, a.disk read>100 ordenado por a.DISK_READS desc;
2.2 Seleccione la consulta que consume más recursos en V$SQL
Similar a V$SQLAREA
seleccione * de
(seleccione sql_text, ranking ()sobre (ordenar por buffer _ obtiene desc) como rango_buffers, a _ char(100 * ratio _ to _ report(buffer _ gets)over(), ' 999.99 ') pct _ buf obtiene de v＄SQL)
Entre ellos rank_buffers y lt11
2.3 Determinar cuándo usar el índice
Cuando la condición de consulta solo necesita devolver unas pocas filas (columnas restringidas), debe crear un índice. Este requisito de devolución es diferente en diferentes versiones.
V5:20% V7:7% V8i, V9i:4% V10g: 5%
Ver el índice en la tabla
seleccione un nombre de tabla , a. Nombre del índice, a. Nombre de la columna, a. Posición de la columna, a. Propietario de la tabla
Columna del índice a de la base de datos
Donde a.table_owner='CLOUDSEA'
Para corregir índices incorrectos, puede utilizar sugerencias para limitar los índices incorrectos, como índices y sugerencias completas.
Usa índices en columnas en SELECT y WHERE.
Ejemplo: seleccionar nombre de tbl donde no=?
Crear índice: cree una prueba de índice (...) en el espacio de tabla TBL (nombre, número) de almacenamiento Yunhai_index.
Para consultas clave en el sistema, puede considerar establecer Tal índice de unión.
Puede haber problemas cuando hay varios índices en una tabla. Utilice el índice de sugerencias para especificar el índice que se utilizará.
Utilice la combinación de índices y utilice el mensaje INDEX_JOIN.
Según el índice de funciones, la consulta es lenta debido al uso de funciones. El modelo de optimización debe estar basado en costos. Parámetros:
QUERY_REWRITE_ENALED=TRUE
QUERY _ REWRITE _ INTEGRITY = Confiable (u obligatorio)
Crear una prueba de índice para suma(prueba);
2.4 Caché de tablas en la memoria
Caché de tablas relativamente pequeñas de uso común en la memoria, pero tenga en cuenta que esto afectará la tabla de conducción en la conexión de bucle anidado.
Cambiar la caché del nombre de la tabla;
2.5 usa EXISTS y subconsultas anidadas en lugar de en
SELECT…FROM EMP WHERE DEPT _ NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT = 'A');
(Método 1: Eficiente)
Seleccione... DEL Empleado A, Departamento B, donde A. Número de Departamento = B. Departamento (+ ), B. El número de departamento está vacío, B. Número de departamento (+) = 'A '
(Método 2: el más eficiente)
Seleccionar...Nunca el empleado E existe (seleccione "X" del Departamento D donde D.DepartmentNumber = E.DepartmentNumber y DepartmentCategory = "A");
En cuarto lugar, utilice los informes STATSPACK y AWR para ajustar la espera y el bloqueo.
Guión en 1. 10GR2
Bajo $ORACLE_HOME/RDBMS/ADMIN.
Spcreate.sql crea el entorno STATSPACK llamando a spcsur.sqlspcttab.sql y spcpkg.sql y lo ejecuta usando SYSDBA.
Spdrop.sql llama a sptab.sql y spdusr.sql para eliminar todo el entorno STATSPACK y ejecutarlo con SYSDBA.
Spreport.sql Este es el script principal utilizado para generar informes, ejecutado por el usuario de PERFSTAT.
Sprepins.sql genera informes de instancia para la base de datos y la instancia especificadas.
Sprepsql.sql genera un informe SQL para el valor hash SQL especificado.
Sprsqins.sql genera informes SQL para la base de datos y la instancia especificadas.
Spauto.sql utiliza DBMS_JOB para recopilar automáticamente datos estadísticos (tomar fotografías).
Sprepcon.sql configura variables SQLPLUS para establecer archivos de configuración, como umbrales.
Primavera. SQL elimina un rango específico de ID de instantáneas para una instancia de base de datos determinada sin eliminar la instantánea de referencia.
Sptrunc.sql trunca todos los datos de rendimiento en la tabla STATSPACK.
Verb (abreviatura de verbo) realiza una comprobación rápida del sistema1. Tasa de aciertos del búfer
Tasa de aciertos del búfer de consultas
select(1-(sum(decode(name, ' lecturas físicas ', valor, 0)) /
(suma(decodificar(nombre, 'obtiene bloque db', valor, 0)) +
suma(decodificar(nombre, 'obtiene consistente', valor, 0))))* 100" tasa de aciertos "
desde v$sysstat