v$SORT_USAGE.SQL_ID 不是会话当前的执行的SQL ID
Kevin Zou
2011-9-2
数据库的临时表空间使用率较高,在查询那个会话在使用临时表空间时,发现视图v$SORT_USAGE.SQL_ID 不是会话当前的执行的SQL ID。
当前使用了临时段的会话:
SYS@hr9prd>select username,session_addr,sql_id,blocks from v$sort_usage;
USERNAME SESSION_ADDR SQL_ID BLOCKS
------------------------------ ---------------- ------------- ----------
SYSADM 07000003CF4AAAB8
cka9cd1fxjsyf 8704
SYSADM 07000003C9477278
an24bnt74vjj7 128
SYSADM 07000003C947C858
74nqkqnrq57p2 128
SYS 07000003CA4BA160
9babjv8yq8ru3 256
SYS 07000003C84A9850
a96buqnd9gfnf 180992
SYS 07000003C84A9850
a96buqnd9gfnf 861440
SYS 07000003C84A9850
a96buqnd9gfnf 128
SYS 07000003C84B1920
bmj0tpq5j7m17 128
SYSADM 07000003CF4DF000
an24bnt74vjj7 128
|
发现SYS用户的在使用较多的临时段。通过SQL_ID查看其运行的SQL:
SYS@hr9prd>SELECT /*+ ORDERED */
2 sql_text
FROM v$sqltext a
WHERE a.sql_id = '&sql_id'
ORDER BY piece ASC 3 4 5 ;
Enter value for sql_id: a96buqnd9gfnf
old 4: WHERE a.sql_id = '&sql_id'
new 4: WHERE a.sql_id = 'a96buqnd9gfnf'
SQL_TEXT
----------------------------------------------------------------
alter user sys tempo
这个SQL是我曾经运行过的,但不会占用这么多的临时段。
通过V$SESSION 和V$SORT_USAGE联合来查询:
SYS@hr9prd>select s.username, s.status, s.prev_sql_id, s.sql_id, u.sql_id
2 from v$session s, v$sort_usage u
where s.SADDR = u.session_addr 3 ;
USERNAME STATUS PREV_SQL_ID Current SQ V$SORT_USAGE.SQL_ID
------------------------------ ------------------------ ------------- ------------- -------------
SYSADM ACTIVE cka9cd1fxjsyf 07r07hppftnd1 cka9cd1fxjsyf
SYSADM ACTIVE bfubyxbqd8vbh bmmyrbb7n3pa4 1vd8n1t6vzv1t
SYSADM INACTIVE 74nqkqnrq57p2 74nqkqnrq57p2
SYS INACTIVE 9babjv8yq8ru3 9babjv8yq8ru3
SYS ACTIVE a96buqnd9gfnf bt6tw6204sj44 a96buqnd9gfnf
SYS ACTIVE a96buqnd9gfnf bt6tw6204sj44 a96buqnd9gfnf
SYS ACTIVE 05qwx9346vy5k 538hpu9pg0gb7 05qwx9346vy5k
SYSADM ACTIVE an24bnt74vjj7 gmcba0bqsar5t an24bnt74vjj7
|
看到V$SORT_USAGE.SQL_ID其实对应的V$SESSION.PREV_SQL_ID的字段,也就是会话运行SQL的前一个SQL。
-THE END-
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/40239/viewspace-706477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/40239/viewspace-706477/