是否可以检查在 PLSQL 块内执行的 SQL 语句的执行计划？
DECLARE
l_count PLS_INTEGER;
BEGIN
SELECT COUNT(1) INTO l_count
FROM foo;
END;
/
对于常规 SQL，我通常会运行以下命令来检查执行计划：
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
但是，这只是报告：
NOTE: cannot fetch plan for SQL_ID: 3q0sujncq54wy, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
我有一个带有自引用外键的表，我想使用相同的递归更新来更新给定的父级以及父级的所有后代。
当使用带有递归 CTE 的 UPDATE 时，它仅返回 25K 行中的 10 行，优化器使用散列半连接和对正在更新的表进行顺序扫描，而不是更优化的嵌套循环和索引扫描。它的执行时间很慢，约为 5-10 毫秒。
将此表的大小增加到 250K 行将导致使用索引扫描。具有讽刺意味的是，与 25K 表（约 5-10 毫秒秒）相比，执行时间实际上要快得多（约 0.5 到 1.0 毫秒）一个数量级，这正是因为它使用索引而不是顺序扫描。
我在这里的猜测是优化器无法先运行 CTE 然后计划更新，而是需要提前计划，并且错误地假设 CTE 返回的行数比实际多得多。
Postgres 不允许索引优化器提示。在生产中没有将 enable_seqscan 设置为 off，是否有任何解决方法可以让 postgres 使用索引？
设置：
drop table emp;
create table emp (id int primary key, manager_id int, department text);
create index emp_manager_id on emp (manager_id);
insert into emp
select i id,
case when mod(i, 10) = 0 then null else i - 1 end manager_id,
null department
from generate_series(0, 25000) as i;
analyze emp;
vacuum emp;
这是更新的 DML。这只会更新 10 行。不管我是使用 IN、EXISTS 还是从递归 CTE 更新，它们都会导致顺序扫描
explain
with recursive foo as (
select id, manager_id, department
from emp
where id = 1000
union all
select emp.id, emp.manager_id, emp.department
from emp join foo on emp.manager_id = foo.id
)
update emp
set department = 'IT'
where id in (select id from foo);
结果是
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Update on emp (cost=766.85..939.24 rows=101 width=74)
CTE foo
-> Recursive Union (cost=0.29..763.57 rows=101 width=40)
-> Index Scan using emp_pkey on emp emp_1 (cost=0.29..8.30 rows=1 width=40)
Index Cond: (id = 1000)
-> Nested Loop (cost=0.29..75.32 rows=10 width=40)
-> WorkTable Scan on foo foo_1 (cost=0.00..0.20 rows=10 width=4)
-> Index Scan using emp_manager_id on emp emp_2 (cost=0.29..7.50 rows=1 width=40)
Index Cond: (manager_id = foo_1.id)
-> Hash Semi Join (cost=3.28..175.67 rows=101 width=74)
Hash Cond: (emp.id = foo.id)
-> Seq Scan on emp (cost=0.00..145.01 rows=10001 width=14)
-> Hash (cost=2.02..2.02 rows=101 width=32)
-> CTE Scan on foo (cost=0.00..2.02 rows=101 width=32)
解释分析给出相同的结果。为简洁起见，我在这里使用说明。
考虑到 25,000 行中只有 10 行正在更新，这种带有顺序扫描的散列半连接并不是最优的。带有索引扫描的嵌套循环在这里是理想的。
设置 enable_seqscan=off 将时间减少到 ~0.1ms（从 ~5-10ms）
如果我不使用递归 CTE，则使用以下更新generate_series显示 emp_id 索引已正确用于通过嵌套循环执行更新。这是我对递归 CTE 更新的期望。
explain
update emp
set department = 'IT'
where id in (
select i from generate_series(1000,1009) i
);
QUERY PLAN
------------------------------------------------------------------------------------------
Update on emp (cost=0.43..83.59 rows=11 width=74)
-> Nested Loop (cost=0.43..83.59 rows=11 width=74)
-> HashAggregate (cost=0.14..0.25 rows=11 width=32)
Group Key: i.i
-> Function Scan on generate_series i (cost=0.00..0.11 rows=11 width=32)
-> Index Scan using emp_pkey on emp (cost=0.29..7.58 rows=1 width=14)
Index Cond: (id = i.i)
如果我将表中的行数从 10K 增加到 250K，则说明计划确实会导致索引的最佳使用。但是，对于 25K 行/seq 扫描，执行需要大约 5-10 毫秒。对于 250K 行，索引扫描大约需要 0.5-0.1 毫秒。
我的猜测是 postgres 无法先运行 CTE，然后计算更新计划。它需要在运行 CTE 之前计算一个计划。因此 postgres 无法知道 CTE 只返回了 10 行，而是必须猜测数字。所以 postgres 猜测 CTE 将返回 1000 行之类的东西，这使得它在表仅包含 25K 时更喜欢顺序扫描。我假设我的 250K 表使用索引扫描的原因是 postgres 继续猜测 CTE 正在返回 1000 行，但在 250K 中，索引扫描更有意义。
Postgres 不允许索引优化器提示。在生产中没有将 enable_seqscan 设置为 off，是否有任何解决方法可以让 postgres 使用索引？
@a_horse_with_no_name 使用的解决方案emp.id = any(array(select id from foo))很棒。它导致以下解释简单，略有不同：
QUERY PLAN
------------------------------------------------------------------------------------
Update on emp (cost=44.19..48.93 rows=10 width=46)
CTE foo
-> Recursive Union (cost=0.00..42.17 rows=101 width=11)
-> Seq Scan on emp emp_1 (cost=0.00..3.08 rows=1 width=11)
Filter: (id = 0)
-> Hash Join (cost=0.33..3.71 rows=10 width=11)
Hash Cond: (emp_2.manager_id = foo.id)
-> Seq Scan on emp emp_2 (cost=0.00..2.66 rows=166 width=11)
-> Hash (cost=0.20..0.20 rows=10 width=4)
-> WorkTable Scan on foo (cost=0.00..0.20 rows=10 width=4)
InitPlan 2 (returns $2)
-> CTE Scan on foo foo_1 (cost=0.00..2.02 rows=101 width=4)
-> Seq Scan on emp (cost=0.00..4.73 rows=10 width=46)
Filter: (id = ANY ($2))
谁能解释这两个部分之间的区别：
原始的 enable_seqscan=off：
-> Nested Loop (cost=2.56..294.11 rows=101 width=74) (actual time=0.091..0.118 rows=10 loops=1)
-> HashAggregate (cost=2.27..3.28 rows=101 width=32) (actual time=0.076..0.080 rows=10 loops=1)
Group Key: foo.id
Batches: 1 Memory Usage: 24kB
-> CTE Scan on foo (cost=0.00..2.02 rows=101 width=32) (actual time=0.024..0.068 rows=10 loops=1)
-> Index Scan using emp_pkey on emp (cost=0.29..2.88 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=10)
Index Cond: (id = foo.id)
使用any(array(...))：
InitPlan 2 (returns $2)
-> CTE Scan on foo foo_1 (cost=0.00..2.02 rows=101 width=4)
-> Seq Scan on emp (cost=0.00..4.73 rows=10 width=46)
Filter: (id = ANY ($2))
foo.id首先，在执行 CTE 扫描后，我的原始查询导致递归 cte 的 HashAggregate 。只有在此之后，它才会遍历emp索引。我不明白它为什么这样做。使用any(array(...))，它将跳过这一步，并简单地在 cte 扫描和索引扫描上嵌套循环。
其次，可能也是最重要的，any(array(...))在 this 中使用结果InitPlan 2。我相信这里发生的事情是以any(array(...))某种方式迫使查询规划器将它们作为两个不同的查询来执行。首先它执行 CTE，它只返回 10 行。然后，规划器知道只有 10 行它可以使用索引扫描而不是 seqscan。由于某种原因，我的原始解决方案无法强制查询规划器将这些作为两个不同的查询执行，因此查询规划器事先不知道要返回多少行。
有任何想法吗？
通常，要检查 Oracle 中的存在，我会这样做：
SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'
但是，如果foo表包含多行 where bar='baz'，则此查询不必要地扫描整个表以报告总计数。
如何快速检查表中的列是否包含至少一个具有指定值的行，并使查询短路，以便一旦检测到该值存在，查询将停止并返回，而不是扫描整个表？
理想情况下，无论表中有多少行包含该值，我都希望此查询返回一个标量值。
以下解决方案似乎是短路的（与 相比SELECT COUNT(1)，这对于大表来说要快得多），但是如果值存在，它将返回 1，或者如果值不存在，则根本不返回任何行。如果总是返回一行会更好。
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM foo
WHERE bar = 'baz'
)
DBMS_SCHEDULER 占用了我所有的 SYSAUX 表空间。我运行了dbms_scheduler.purge_logwhich 从 中删除了 1 亿行dba_scheduler_job_run_details，但是v$sysaux_occupants数据文件大小保持不变。我需要采取一些额外的措施来清除 DBMS_SCHEDULER 生成数据的 SYSAUX 表空间吗？
尝试将单行插入到我的常规表空间中新创建的表中失败：
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX
我可以通过查询看到它JOB_SCHEDULER占用了 SYSAUX 表空间的 92.4% v$sysaux_occupants：
SELECT
occupant_name,
round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
FROM v$sysaux_occupants
GROUP BY occupant_name
ORDER BY 2 desc NULLS LAST
;
我最初在 dba_scheduler_job_run_details 中有超过 1 亿行。
昨天，我运行了 purge 命令（耗时 3.5 小时）：
BEGIN
dbms_scheduler.purge_log;
END;
/
今天，dba_scheduler_job_run_details有不到 1K 行。
但是，查询v$sysaux_occupants不变；今天它仍然说JOB_SCHEDULER是占92.4%。同样查询我的数据文件大小显示 SYSAUX 仍然被最大化：
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_data_files d,
v$datafile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;
看来我实际上并没有删除 DBMS_SCHEDULER 占用的空间。
我是否缺少一些步骤来清理 DBMS_SCHEDULER 消耗的所有空间？