Это 9-й день моего участия в августовском испытании обновлений.Подробности о мероприятии:Испытание августовского обновления
Прежде всего, поделитесь всеми предыдущими статьями, ставьте лайки, добавляйте в избранное и пересылайте три раза подряд. >>>>😜😜😜
Сборник статей:🎁nuggets.capable/post/694164…
Github :👉github.com/black-ant
Резервное копирование CASE:👉git ee.com/ant black/wipe…
Введение
Запишите процесс устранения неполадок медленного запроса Oracle один раз, чтобы его можно было использовать непосредственно в будущем.
После прочтения некоторых документов оптимизированное решение в Oracle в основном такое же, как и в Mysql, обычно включающее следующие направления:
- Ориентиры(Пропускная способность): в том числе сама пропускная способность Oracle и пропускной способности диска ввода / вывода
- аппаратный анализ(Ресурсы): включая просмотр ЦП сервера, использование жесткого диска
- SQL-анализ: Проанализируйте, есть ли медленный запрос в SQL и попадает ли он в индекс
- Оптимизация конфигурации: Проанализируйте, можно ли улучшить производительность за счет конфигурации среды.
Вышеуказанные аспекты могут в основном найти проблему, а затем рассмотреть решение с помощью проблемы
2. Действия по устранению неполадок
2.1 Запрос журналов медленных запросов
В отличие от журнала, который Mysql записывает непосредственно в журнал, Oracle может вытащить журнал Excle медленного запроса с помощью инструкции @Медленный запрос Oracle — я принадлежу машине — Blog Park (cnblogs.com)
# 慢查询耗时
select *
from (select sa.SQL_TEXT "执行 SQL",
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
# 查询次数最多的 SQL
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "执行次数",
s.PARSING_USER_ID "用户名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;
Интерпретация результатов:
Получив среднее время выполнения, вы, очевидно, можете найти SQL с большим временем запроса, но этот тип SQL не обязательно является медленным запросом, вам нужно судить по ситуации, если есть запредельное время, вам нужно анализировать индекс
2.2 Проверка состояния индекса
explain plan for
select * from t_call_records where t_bjhm='123456'
# 查看执行结果
select * from table(dbms_xplan.display)
дополнение к содержанию указателя
Отсюда хорошо видно, что выполняется полное сканирование таблицы, поэтому нужно добавлять индексы и проверять по ситуации
- index unique scan: индексировать уникальное сканирование (индекс первичного ключа)
- index range scan: сканирование диапазона индексов (в случае составного индекса)
- index full scan: полное сканирование индекса
-
index fast full scan: Быстрое сканирование индекса, сканирование всех блоков данных в индексе, в основном аналогично полному сканированию индекса.
- Очевидная разница между ними заключается в том, что при быстром просмотре индекса запрашиваемые данные не сортируются, и данные возвращаются неотсортированными.
2.3 Проверка конкуренции блокировок
Шаг 1. Просмотрите соревнование по фоновой блокировке.
SELECT
SQ.INST_ID,
SQ.SQL_TEXT, /*SQL文本*/
SE.SID, /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/
SE.BLOCKING_SESSION,
SQ.OPTIMIZER_COST AS COST_,/* COST 值*/
SE.LAST_CALL_ET CONTINUE_TIME,/*执行时间*/
SE.EVENT,/*等待事件*/
SE.LOCKWAIT,/*是否等待LOCK(SE，P)*/
SE.MACHINE,/*客户端的机器名。(WORKGROUP\PC-201211082055)*/
SQ.SQL_ID,/*SQL_ID*/
SE.USERNAME,/*创建该会话的用户名*/
SE.LOGON_TIME,/*登陆时间*/
'ALTER SYSTEM KILL SESSION ' || SE.SID || ',' || SE.SERIAL # --若存在锁情况,会用到KILL锁释放~
FROM
gV$SESSION SE,/*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录*/
gV$SQLAREA SQ /*跟踪所有SHARED POOL中的共享CURSOR信息，包括 执行次数，逻辑读，物理读等*/
WHERE
SE.SQL_HASH_VALUE = SQ.HASH_VALUE
AND SE.STATUS = 'ACTIVE'
AND SE.SQL_ID = SQ.SQL_ID
AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME --过滤条件
AND SE.USERNAME = 'FWSB' --用户名
AND se.BLOCKING_SESSION IS NOT NULL;
// 实际运行脚本======================
SELECT
SQ.INST_ID,
SQ.SQL_TEXT,
SE.SID,
SE.BLOCKING_SESSION,
SQ.OPTIMIZER_COST AS COST_,
SE.LAST_CALL_ET CONTINUE_TIME,
SE.EVENT,
SE.LOCKWAIT,
SE.MACHINE,
SQ.SQL_ID,
SE.USERNAME,
SE.LOGON_TIME,
'ALTER SYSTEM KILL SESSION ' || SE.SID || ','
FROM
gV$SESSION SE,
gV$SQLAREA SQ
WHERE
SE.SQL_HASH_VALUE = SQ.HASH_VALUE
AND SE.STATUS = 'ACTIVE'
AND SE.SQL_ID = SQ.SQL_ID
AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME
AND SE.USERNAME = 'FWSB'
AND SE.BLOCKING_SESSION IS NOT NULL;
Пополнить: Соответствующая структура таблицы может быть запрошена для сырого молока.Официальная документация Oracle
Шаг 2. Запрос результатов
Здесь вы можете посмотреть соответствующий SQL через SID, чтобы найти соответствующий объект блокировки
2.4 Другие операторы блокировки
Следующее ссылается из:blog.CSDN.net/U011019491/…, вы можете прочитать оригинал
Запрашивайте этих пользователей, манипулируйте этими таблицами и вызывайте блокировки
SELECT
s.username,
decode(l.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL ) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.terminal,
s.machine,
s.program,
s.osuser
FROM
v$session s,
v$lock l,
all_objects o
WHERE
l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null
Детали Ссылка: ---> V$Lock
Узнайте заблокированную таблицу и идентификатор сеанса, который заблокировал таблицу
select a.session_id ,b.* from v$locked_object a,all_objects b where a.object_id=b.object_id
Найдите соответствующий оператор SQL
SELECT
vs.SQL_TEXT,
vsess.sid,
vsess.SERIAL #,
vsess.MACHINE,
vsess.OSUSER,
vsess.TERMINAL,
vsess.PROGRAM,
vs.CPU_TIME,
vs.DISK_READS
FROM
v$sql vs,
v$session vsess
WHERE
vs.ADDRESS = vsess.SQL_ADDRESS
AND vsess.sid = 36
Дополнительное заявление:
// 查哪个过程被锁 -> 查V$DB_OBJECT_CACHE视图:
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';
// 查是哪一个SID,通过SID可知道是哪个SESSION. -> 查V$ACCESS视图:
SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';
// 查出SID和SERIAL# -> 查V$SESSION视图 + 查V$PROCESS视图
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
3. Медленная оптимизация запросов
3.1 Раздел SQL
// 避免 in 操作
Oracle 中 in 会被试图转换成多个表的连接 , 转换不成功会先进行 in 中的子查询 , 再进行外部查询
// 避免 not in
不管哪个数据库 , 一般都是不推荐的 ,这种写法会跳过索引 (同理还有 is null 和 not null)
// 避免使用 <>
类似 , 不走索引
// **采用函数处理的字段不能利用索引**
// 关联查询
- 多用 Where 语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联
- 多用 右连接
// 过滤多用 where ,避免使用 having
- 这个和 mysql 是一致的 , having 是对 where 的数据进行过滤组处理 , 对于数据的过滤 , 优先用 where
- 总结 : 先过滤小的结果集，然后通过这个小的结果集和其他表做关联
// like 操作符
like 操作可以通过 instr 代替
// union操作符
- 通常不会产生重复结果 , 而 union 会额外触发一次排序
- 采用union ALL操作符替代union，因为union ALL操作只是简单的将两个结果合并后就返回
// SQL 执行保证统一性
涉及到 SGA 的概念
// where后面的条件顺序影响
这里不是全表索引的问题 , 而是由于 where 多个条件时 , 比较带来的 cpu 占用率问题
// 询表顺序的影响
- 表的顺序不对会产生十分耗服务器资源的数据交叉
// 其他的方案还包括以下方式
@ https://www.jb51.net/article/97515.htm
@ https://www.jb51.net/article/23071.htm
@ https://www.jb51.net/article/40281.htm
4. Оптимизация производительности
Это так обидно!!!
Все скопировано из книги!!!
И большинство из них еще не практиковались!!!
В конце концов, Oracle имеет ограниченный контакт.Даже если большинство проблем связаны с проблемами SQL, оптимизация производительности сталкивалась несколько раз, что привело к изучению многих методов.Я действительно использовал несколько, но я их записал! !!
4.1 Общий процесс оптимизации производительности
Здесь прямо цитируются результаты чужих статей, без проверки, только для ознакомления!
// PS : 初始化时间 49.41
// 增大 SGA Buffer Cache 和 SGA Shared Pool -> 48.57
- 增大 SGA 已经缓冲看来对于性能的提升并不显著，加载时间只提升了 1.73%
// 增大 SGA Redo Cache 和 Redo Log Files -> 41.39
- 加载时间提升了 17.35%，TPS 也提升了 9.33%。因为加载和同时插入，更新，删除需要比 8M 大的空间
- 但是看起来增加内存性能并没有显著提升
// 增大 Database Block Size (2K-4K) -> 17.35
- 加载时间提升了 138%！而对 TPS 值没有很大的影响
// 使用 Tablespaces Local -> 15.07
- TPS 轻微提升
// Database Block Size 增大 (4K-8K) -> 11.42
- TPS 继续提升 , 区别较大
// 添加 io_slaves -> 10.48
dbwr_io_slaves 4\
lgwr_io_slaves (derived) 4
// 优化Linux 内核 -> 9.40
可以看到 , 内核版本优化后 , 性能是有一定提升的
// 调整虚拟子内存 -> 5.58
- /ect/sysctl.cong
-> vm.bdflush = 100 1200 128 512 15 5000 500 1884 2
Этот процесс нельзя использовать в качестве эталона, но его можно использовать в качестве идеи для оптимизации Oracle.Видно, что производительность значительно улучшилась.
4.2 Аппаратная оптимизация
Вот использование калибровки ввода-вывода (калибровка ввода-вывода), которую можно использовать для оценки производительности ввода-вывода базы данных и оценки использования различных стратегий путем анализа результатов ввода-вывода.
// Step 1 : 确定并行度配置 (通常是核数的2倍)
show parameters parallel_thread
// Step 2 : 确定并行策略 (auto : Oracle将依据要执行的操作的特性和对象的大小来确定并行度)
- 查询策略 : show parameters parallel_degree_policy
- 设置策略 : alter session set parallel_degree_policy = 'auto'
// Step 3 : 查看并行度数据
- 打开系统默认设置的输出功能 : set serveroutput on
- 查看详情 :
set serveroutput on
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/
// 问题补充 : ORA-56708: 找不到任何具有异步 I/O 功能的数据文件
- 确定 sync : show parameter filesystemio_options
- 设置 sync : filesystemio_options
- ASYNCH: 使Oracle支持文件的异步（Asynchronous）IO
- DIRECTIO：使Oracle支持文件的Direct IO
- SETALL：使Oracle同时支持文件的Asynchronous IO和Direct IO
- NONE：使Oracle关闭对Asynchronous IO和Direct IO的支持
1> alter system set filesystemio_options=setall scope=spfile;
2> shutdown immediate;
3> startup
// PS : 注意其中管理员权限问题
alter system set filesystemio_options=none scope=spfile;
V. Дополнение к концепции
5.1 SGA
Глобальная область системы (SGA)Набор структур общей памяти, называемых компонентами SGA, которые содержат данные и управляющую информацию для экземпляра базы данных Oracle. SGA совместно используется всеми серверными и фоновыми процессами. Примеры данных, хранящихся в SGA, включают кэшированные блоки данных и общие области SQL.
компонент :
-
Database buffer cache: кеш данных
- Перед запросом или изменением данных, хранящихся в базе данных, данные должны быть прочитаны с диска и сохранены в буферном кеше.
- Все пользовательские процессы, подключенные к базе данных, имеют общий доступ к буферному кешу.
- Для достижения наилучшей производительности буферный кэш должен быть достаточно большим, чтобы избежать частых операций ввода-вывода с диском.
-
Shared pool: общий пул кэширует информацию, совместно используемую пользователями, включая следующие
- Повторно используемые операторы SQL
- Информация из словаря данных, такая как данные учетной записи пользователя, описания таблиц и индексов, а также привилегии.
- Хранимые процедуры, которые представляют собой исполняемый код, хранящийся в базе данных.
- Redo log buffer: этот буфер повышает производительность за счет кэширования информации о повторах до тех пор, пока ее нельзя будет записать в физический онлайн-файл журнала повторов, хранящийся на диске.
- Large pool: Эта необязательная область используется для буферизации больших запросов ввода-вывода для различных серверных процессов.
- Java pool: Бассейн Java - это область памяти, используемая для всех конкретных сеансов Java-кода и данных на виртуальной машине Java (JVM)
- Streams pool: Пул потоков — это область памяти, используемая функцией Oracle Streams.
- Result cache: результат запроса к буферу кэша. Если вы выполняете запрос, который сохраняет результаты в кэше результатов, база данных возвращает результаты запроса из кэша результатов вместо повторного выполнения запроса.
Суммировать
Автор только оптимизирует Oracle с точки зрения бизнес-требований и не углубляется в оптимизацию бизнеса Oracle, если интересно, можете посмотреть., Оптимизация системы для базы данных
Последующие действия могут продолжать углубляться в детали Oracle, и мы подождем и увидим.
Проверка документации действительно слишком классная, но документация Oracle очень полная и простая в использовании.
Справочная документация (большое спасибо)
Блог Woo Woo.cn на.com/wolf plan/afraid/…