定位高消耗资源的sql语句

第一天写博客，就从一条sql语句开始吧！

用到的sql视图有v$sqlstats、v$sqltext

下面对v$sqlstats的官方解释：

V$SQLSTATS returns basic performance statistics for SQL cursors, with each row representing the data fora unique combination of SQL text and optimizer plan (that is, unique combination of SQL_ID, and PLAN_HASH_VALUE). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that itis faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA

select max(disk_reads),max(executions),max(buffer_gets),max(sorts) from v$sqlstats;

列的意思：

disk_reads:Number of disk reads for all cursors with this SQL text and plan

executions:Number of executions that took place on this object since it was brought into the library cache

buffer_gets:Number of buffer gets for all cursors with this SQL text and plan

sorts:Number of sorts that were done for the child cursor

找到buffer_gets过大的sql

select sql_text from v$sqlstats where buffer_gets > ????;

要想看全的sql信息，查询v$sqltext视图。