检查Oracle数据库中不合理的sql语句

经常可以遇到这样的情况，由于程序员的sql语句写的非常不合理，导致Oracle数据库查询速度缓慢，严重的情况会导致数据库的故障。
举个例子，我以前就遇到过Oracle数据库定期（一周左右）出现 ORA-4031错误（shared pool内存不够，无法连接Oracle数据库），此时数据库已经无法再使用了。必须关闭重起，来释放那些占在shared pool里面无法释放的对象。
所以对于一个Oracle DBA来说，需要定期的检查shared pool中的sql语句占用的内存空间，对于严重占用shared pool，无法释放的sql语句，必须要求程序员修改或优化sql语句。

Java代码

1. select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;   

select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ; 

上面的sql语句是查询shared pool中占用内存超过100K的sql语句。

这个sql可以非常有效的检查出Oracle shared pool中那些严重占用内存的sql，根据我的经验，绝大多数有问题的sql语句都会在这里留下痕迹，通过在这里找出有问题的sql语句并进行修改，再反复运行这个sql脚本，直到所以有问题的sql都处理完毕，这就是对Oracle数据库在sql上面的最好的优化，可以保证不会因为程序员的sql语句问题导致Oracle数据库的性能问题。

共享池主要由库缓冲区（共享SQL区和PL/SQL区）和数据字典缓冲区组成。

select * from v$sgastat; --显式SGA的状态信息。

有的人写的SQL语句非常复杂，嵌套了好几层，SQL语句本身写的很差，就有可能会占用很大的SQL区域。

这是我从Oracle Metalink里面摘下来的：

引用

If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of  dynamic sql fragmenting the shared pool.  This can be caused by:

        o Not sharing SQL
        o Making unnecessary parse calls (soft)
        o Not using bind variables