oracle中查找执行效率低下的SQL.docVIP

oracle中查找执行效率低下的SQL v$sqltext：存储的是完整的SQL,SQL被分割 v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计） v$sql：内存共享SQL区域中已经解析的SQL语句。（即时） ? 根据sid查找完整sql语句： select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = sid??? )order by piece asc ? select a.CPU_TIME,--CPU时间 百万分之一（微秒）?????? a.OPTIMIZER_MODE,--优化方式?????? a.EXECUTIONS,--执行次数?????? a.DISK_READS,--读盘次数?????? a.SHARABLE_MEM,--占用shared pool的内存多少?????? a.BUFFER_GETS,--读取缓冲区的次数?????? a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)?????? a.SQL_TEXT,--Sql语句?????? a.SHARABLE_MEM,?????? a.PERSISTENT_MEM,?????? a.RUNTIME_MEM,?????? a.PARSE_CALLS,?????? a.DISK_READS,?????? a.DIRECT_WRITES,?????? a.CONCURRENCY_WAIT_TIME,?????? a.USER_IO_WAIT_TIME? from SYS.V_$SQLAREA a?WHERE PARSING_SCHEMA_NAME = CHEA_FILL--表空间?order by a.CPU_TIME desc ? 引用：/blog/700985 从V$SQLAREA中查询最占用资源的查询 select b.username username,a.disk_reads reads,????a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,????a.sql_text Statementfrom ?v$sqlarea a,dba_users bwhere a.parsing_user_id=b.user_id?and a.disk_reads 100000order by a.disk_reads desc; 用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。 v$sql：内存共享SQL区域中已经解析的SQL语句。（即时） 列出使用频率最高的5个查询： select sql_text,executionsfrom (select sql_text,executions,???rank() over????(order by executions desc) exec_rank???from v$sql)where exec_rank =5; 消耗磁盘读取最多的sql top5：select disk_reads,sql_textfrom (select sql_text,disk_reads,???dense_rank() over?????(order by disk_reads desc) disk_reads_rank???from v$sql)where disk_reads_rank =5; 找出需要大量缓冲读取（逻辑读）操作的查询： select buffer_gets,sql_textfrom (select sql_text,buffer_gets,???dense_rank() over?????(order by buffer_gets desc) buffer_gets_rank???from v$sql)where buffer_gets_rank=5; v$sqlarea字段定义：html SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor SQL_ID VARCHAR2(13) SQL identifier of the pa