oracle 性能排查与锁表处理
在 Oracle 数据库中，查看 SQL 语句的执行计划可以帮助我们理解查询的执行方式，以及如何优化查询性能。以下是几种常用的方法来查看 Oracle 执行计划：
- 使用 EXPLAIN PLAN
使用 EXPLAIN PLAN 语句可以查看 SQL 查询的执行计划。执行以下步骤：
| * 首先，执行 `EXPLAIN PLAN FOR <你的SQL语句>`。例如： |
| ``` |
| sql`EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;` |
| ``` |
| * 然后，查询计划表以查看执行计划： |
| ``` |
| sql`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` |
| ```这会返回一个执行计划，展示查询是如何被优化和执行的。 |
2. 使用 SET AUTOTRACE
你可以使用 SET AUTOTRACE 来查看 SQL 语句的执行计划和统计信息。执行以下步骤：
| * 在 SQL*Plus 或 SQL Developer 中，首先执行 `SET AUTOTRACE ON`。 |
| * 然后执行你的 SQL 语句。例如： |
| ``` |
| sql`SET AUTOTRACE ON; |
| SELECT * FROM employees WHERE department_id = 10;` |
| ```执行完毕后，你将看到执行计划和相关的统计信息。 |
3. 使用 TOAD 或 PL/SQL Developer
如果你使用 TOAD 或 PL/SQL Developer 这样的工具，通常只需要输入 SQL 语句，然后执行。这些工具通常提供了一个执行计划的选项或按钮，你可以直接点击来查看执行计划。
请注意，Oracle 的执行计划可能会因数据库版本、表结构、索引和数据量的不同而有所差异。因此，理解和优化执行计划需要根据具体的环境和需求来进行。
Oracle 性能监控是一个持续的过程，用于评估数据库的健康状况、检测瓶颈、预测未来的性能问题，并确保数据库高效、稳定地运行。以下是一些建议和常用的工具来进行 Oracle 性能监控：
使用 Oracle Enterprise Manager (OEM)
OEM 是一个集成的 Web 界面工具，提供了数据库性能、健康状况和配置的全面视图。你可以使用它来监控各种性能指标、设置阈值警报、查看历史数据等。
2. AWR 和 ASH 报告
* **Automatic Workload Repository (AWR)**: 定期收集性能统计信息，并提供关于数据库的各种活动的详细报告。
* **Active Session History (ASH)**: 提供近实时的性能问题诊断，帮助你识别哪些会话或 SQL 语句可能导致了性能问题。
3. SQL Trace 和 TKPROF
使用 SQL Trace 来收集 SQL 语句的执行详细信息，并使用 TKPROF 来格式化和分析跟踪文件。这有助于诊断 SQL 语句的性能问题。
4. Oracle Dynamic Performance Views
这些视图，如 V$SYSTEM_EVENT, V$SESSION, V$SQL 等，提供了关于数据库性能的实时信息。你可以查询这些视图来获取关于数据库活动的详细信息。
5. 监控表空间和数据文件
定期检查表空间的使用情况，确保有足够的空间，并监控数据文件的增长。
6. 监控 redo log 和 archive log
确保 redo log 和 archive log 的生成和备份是正常的，以避免任何潜在的数据丢失问题。
7. 硬件和操作系统监控
使用系统和硬件监控工具来监控 CPU、内存、磁盘 I/O 和网络等的使用情况，以确保数据库运行在健康的环境中。
8. 定期的健康检查
进行定期的健康检查，包括检查数据库的配置、补丁级别、备份状态等。
9. 培训和经验
最后但同样重要的是，确保你的团队有足够的培训和经验来解读性能数据、识别问题并采取适当的行动。
总之，Oracle 性能监控是一个多方面的任务，需要结合多种工具和方法来确保数据库的高效和稳定运行。
在 Oracle 数据库中，当一个事务正在对某个表进行操作时，其他事务无法同时对该表进行修改或删除操作。这就是所谓的锁表。锁表的目的是为了保证数据的一致性和完整性，防止多个事务同时对同一数据进行操作导致数据冲突和错误。以下是查询 Oracle 中锁表情况的几种方法：
使用 V$LOCK 视图：
sql
SELECT * FROM V$LOCK;
该视图会返回当前数据库中所有的锁表信息，包括锁定的对象、锁定的模式、锁定的会话等。
2. 使用 DBA_BLOCKERS 和 DBA_WAITERS 视图：
sql
SELECT * FROM DBA_BLOCKERS;
SELECT * FROM DBA_WAITERS;
DBA_BLOCKERS 视图会返回当前正在阻塞其他事务的会话信息，而 DBA_WAITERS 视图会返回当前正在等待其他事务释放锁的会话信息。
3. 使用 DBMS_LOCK 包：
你也可以使用 Oracle 提供的 DBMS_LOCK 包来查看当前锁表情况。这需要 PL/SQL 代码进行查询。
当发现锁表问题时，可以采取等待锁释放、强制释放锁或优化事务并发性等方法来解决。预防锁表问题的方法包括合理设计数据库、使用合适的隔离级别、控制事务并发度和监控锁表情况等。
要查询 Oracle 中锁表的 SQL 语句以及被锁的表，可以使用以下的 SQL 查询：
sql
SELECT
l.session_id,
l.lock_type,
l.locked_mode,
l.oracle_username,
l.os_user_name,
l.machine,
o.object_name,
o.object_type,
s.sql_text
FROM
v$locked_object l
JOIN
dba_objects o ON l.object_id = o.object_id
JOIN
v$session s ON l.session_id = s.sid
WHERE
o.object_type = 'TABLE'
ORDER BY
o.object_name;
这个查询会返回以下信息：
session_id: 锁表的会话 ID。
lock_type: 锁的类型（例如，表锁或行锁）。
locked_mode: 锁的模式（例如，共享锁或排他锁）。
oracle_username: Oracle 数据库的用户名。
os_user_name: 操作系统的用户名。
machine: 客户端机器的名称。
object_name: 被锁的表名。
object_type: 被锁对象的类型（在这种情况下，是 'TABLE'）。
sql_text: 导致锁的 SQL 语句。
请注意，要运行这个查询，你需要有足够的权限来访问 v$locked_object, dba_objects 和 v$session 视图。如果你没有权限，可以联系你的数据库管理员来获取所需的权限。
此外，请注意，这个查询只显示当前正在发生的锁。如果锁已经被释放，那么它不会出现在查询结果中。
在 Oracle 数据库中，当一个进程（或会话）由于某种原因持有锁并导致其他进程被阻塞时，你可能需要终止（kill）这个进程以释放锁。请注意，强制终止进程可能会导致数据丢失或事务回滚，因此在进行此操作之前，请确保已经备份了重要数据，并通知了相关用户。
以下是终止持有锁的 Oracle 会话的步骤：
查找持有锁的会话 ID：
首先，你需要找到持有锁的会话的 ID。可以使用以下 SQL 查询来查找相关信息：
sql
SELECT * FROM v$locked_object;
或者，如果你知道被锁的表名，可以使用以下查询：
sql
SELECT * FROM v$locked_object WHERE object_id = (SELECT object_id FROM user_objects WHERE object_name = '你的表名');
记下 SESSION_ID 和 ORACLE_USERNAME 列的值。
2. 终止会话：
一旦你找到了持有锁的会话 ID，你可以使用以下 SQL 语句来终止该会话：
sql
ALTER SYSTEM KILL SESSION 'sid,serial#';
其中 sid 是会话 ID，serial# 是会话的序列号。你可以从第一步的查询结果中找到这些信息。
例如，如果会话 ID 是 123，序列号是 456，那么你将执行：
sql
ALTER SYSTEM KILL SESSION '123,456';
确认锁已被释放：
执行上述命令后，等待一段时间，然后重新运行第一步中的查询来确认锁已被释放。
请注意，终止会话是一个高风险的操作，可能会导致数据丢失或事务回滚。在执行此操作之前，请确保已经备份了重要数据，并通知了相关用户。如果可能的话，最好先尝试联系持有锁的用户并请求他们释放锁，以避免不必要的风险。
