脚本:监控数据库中的活跃用户及其运行的SQL

where a.address = b.sql_address -- and b.status = ACTIVE /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE -- ACTVE TRANSACTION ON THAT MOMENT */ and b.paddr = c.addr and a.hash_value = b.sql_hash_value order by c.spid, a.hash_value, a.piece REM TOP SESSION with sessions as (select /*+ materialize */ sess.inst_id, sess.sid, sess.serial#, sess.username, sess.module, sess.program, stat.value cpu_used_by_this_session, i.physical_reads, i.block_gets, sess.command, sess.status, sess.lockwait, decode(sess.sql_hash_value, 0, sess.prev_hash_value, sess.sql_hash_value) sql_hash_value, decode(sess.sql_address, 00, sess.prev_sql_addr, sess.sql_address) sql_address from gv$sesstat stat, gv$session sess, gv$sess_io i where stat.statistic# = (select statistic# from v$statname where name = CPU used by this session) and stat.sid = sess.sid and stat.inst_id = sess.inst_id and (stat.value 100 or i.physical_reads 100 or i.block_gets 100) and sess.username is not null and i.sid = sess.sid and i.inst_id = sess.inst_id), sqlarea as (select inst_id, sql_fulltext sql_text, hash_value, address from gv$sqlarea) select * from sessions, sqlarea where sessions.inst_id = sqlarea.inst_id and sessions.sql_hash_value = sqlarea.hash_value and sessions.sql_address = sqlarea.address order by cpu_used_by_this_session desc

最新内容请见作者的GitHub页：http://qaseven.github.io/

以小博大外小内大,Db数据库SQL优化之小数据驱动大数据 SQL优化中，有一条放之四海而皆准的既定方针，那就是：永远以小数据驱动大数据。其本质其实就是以小的数据样本作为驱动查询能够优化查询效率，在SQL中，涉及到不同表数据的连接、转移、或者合并，这些操作必须得有个数据集作为“带头”大哥，即驱动数据，而这个驱动数据最好是数据量最小的那一个。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载