drop表空间缓慢

Yong Huang

This guy
https://laimisnd.wordpress.com/2 ... uding-contents-and/
reported exactly the same problem and cited two Oracle documents. The first one is quite relevant
Drop Tablespace Including Contents And Datafiles Takes Hours To Complete (Doc ID 438461.1)
which suggests
execute dbms_stats.gather_schema_stats('SYS');
The only problem with that document is that it covers Oracle versions  9.2.0.1 to 10.2.0.3, even though the document was last updated in Sept 2016. You can post a comment by clicking "Give Feedback" on the upper right corner saying you only have this problem in 11.2.0.4. You can also say there's no need to gather stats for the whole SYS schema; just tab$ is enough. (By default, gathering table stats automatically cascades to its indexes.)

> 发现绑定变量的值都是9，不知道有没有问题。

I don't see any problem. You actually only have one bind variable :1, and it represents the tablespace number.

> 执行计划用图片，排版容易看。

You don't have to use an image to align text when posting the execution plan. You can use fixed-width font such as Courier or Courier New. You can manually do so by typing:
[ font = courier new ]
plan here
[ / font ]
(Remove spaces within my square brackets)

> 我用查出来的绑定变量的值9去替换有问题的sql语句中的变量后，执行速度非常快，而且结果集是0

Sometimes a literal SQL uses a different plan than one using a bind variable. But if the results are different, there may be a problem.

PiscesCanon

Yong Huang 发表于 2017-5-9 22:55
This guy
https://laimisnd.wordpress.com/2 ... uding-contents-and/
reported exactly the same proble ...

感谢提供相关问题链接。
另外，替换绑定变量后的执行计划确实和drop表空间提取的执行计划不同。
最后感谢大师耐心回复，对于该问题耽误太多时间，也发现自身太多知识盲点，接下来针对自身问题对自己充电，之后有点提升再来回顾该问题。再次感谢大师。