select sql_text,executions,plsql_exec_time,rn from (select sql_text,executions,plsql_exec_time, dense_rank() over(order by executions desc) rn from v$sqlarea where trunc(last_active_time)=trunc(sysdate)) where rn<=10
rn是什么 还有plsql_exec_time 好像没有这个字段吧
plsql_exec_time 运行时间rn 是你要的钱10名 分析函数的别名
--------我这里有,你查下文档看下吧,我的是10g SQL> select sql_text,executions,plsql_exec_time from v$sqlarea where executions>10 and plsql_exec_time/60>=3;SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME -------------------------------------------------------------------------------- ---------- --------------- begin :id := sys.dbms_transaction.local_transaction_id; end; 129 16584 begin if :enable = 0 then sys.dbms_output.disable; else sys.dbms_out 21 3395 begin sys.dbms_output.get_line(line => :line, status => :status); end; 20 463 declare t_owner varchar2(30); t_name varchar2(30); procedure check_mvie 26 2683 begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end; 16 51980SQL>
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott
SQL> SQL> select sql_text,executions,plsql_exec_time,rn 2 from (select sql_text,executions,plsql_exec_time, 3 dense_rank() over(order by plsql_exec_time desc) rn 4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate)) 5 where rn<=10 6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN -------------------------------------------------------------------------------- ---------- --------------- ---------- begin setEMUserContext(:1, :2); end; 16 4040206 1 select s.synonym_name object_name, o.object_type from all_synonyms s, sys.a 1 2568938 2 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 630 2483728 3 select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.na 25 2312318 4 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : 285 1073391 5 begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5583 1040136 6 /* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id, 133 471066 7 select count(*) from xdb.xdb$schema s where bitand(to_number(s.xmldata.flags, 25 255429 8 /* OracleOEM */ BEGIN :message := dbms_server_alert.expand_message(:lan 116 248401 9 BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 307 107842 10
10 rows selected
SQL> ---运行时间前十名 SQL> SQL> select sql_text,executions,plsql_exec_time,rn 2 from (select sql_text,executions,plsql_exec_time, 3 dense_rank() over(order by executions desc) rn 4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate)) 5 where rn<=10 6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN -------------------------------------------------------------------------------- ---------- --------------- ---------- begin setEMUserContext(:1, :2); end; 16 4040206 1 select s.synonym_name object_name, o.object_type from all_synonyms s, sys.a 1 2568938 2 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 631 2487157 3 select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.na 25 2312318 4 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : 286 1074618 5 begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5592 1041768 6 /* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id, 133 471066 7 select count(*) from xdb.xdb$schema s where bitand(to_number(s.xmldata.flags, 25 255429 8 /* OracleOEM */ BEGIN :message := dbms_server_alert.expand_message(:lan 116 248401 9 BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 307 107842 10
10 rows selected
SQL> --运行次数前十名
--运行次数的改下SQL> select sql_text,executions,plsql_exec_time,rn 2 from (select sql_text,executions,plsql_exec_time, 3 dense_rank() over(order by executions desc) rn 4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate)) 5 where rn<=10 6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN -------------------------------------------------------------------------------- ---------- --------------- ---------- UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRIN 5935 0 1 SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 5913 0 2 begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5622 1047082 3 SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA 5622 0 3 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ 4605 0 4 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n 3724 0 5 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd 3410 0 6 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 2187 0 7 update sys.col_usage$ set equality_preds = equality_preds + decode(bitan 1984 0 8 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# 1722 0 9 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis 1496 0 10
比如说我要查今天运行的sql语句次数的前10名 应该怎么写
比如说我要查今天运行的sql语句运行时间的前10名 应该怎么写
select sql_text,executions,plsql_exec_time,rn
from (select sql_text,executions,plsql_exec_time,
dense_rank() over(order by executions desc) rn
from v$sqlarea where trunc(last_active_time)=trunc(sysdate))
where rn<=10
rn是什么 还有plsql_exec_time 好像没有这个字段吧
plsql_exec_time 运行时间rn 是你要的钱10名 分析函数的别名
--------我这里有,你查下文档看下吧,我的是10g
SQL> select sql_text,executions,plsql_exec_time from v$sqlarea where executions>10 and plsql_exec_time/60>=3;SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME
-------------------------------------------------------------------------------- ---------- ---------------
begin :id := sys.dbms_transaction.local_transaction_id; end; 129 16584
begin if :enable = 0 then sys.dbms_output.disable; else sys.dbms_out 21 3395
begin sys.dbms_output.get_line(line => :line, status => :status); end; 20 463
declare t_owner varchar2(30); t_name varchar2(30); procedure check_mvie 26 2683
begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end; 16 51980SQL>
貌似在最后的trunc(last_active_time)那里报错 00904 invalid identifier
Connected as scott
SQL>
SQL> select sql_text,executions,plsql_exec_time,rn
2 from (select sql_text,executions,plsql_exec_time,
3 dense_rank() over(order by plsql_exec_time desc) rn
4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate))
5 where rn<=10
6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN
-------------------------------------------------------------------------------- ---------- --------------- ----------
begin setEMUserContext(:1, :2); end; 16 4040206 1
select s.synonym_name object_name, o.object_type from all_synonyms s, sys.a 1 2568938 2
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 630 2483728 3
select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.na 25 2312318 4
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : 285 1073391 5
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5583 1040136 6
/* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id, 133 471066 7
select count(*) from xdb.xdb$schema s where bitand(to_number(s.xmldata.flags, 25 255429 8
/* OracleOEM */ BEGIN :message := dbms_server_alert.expand_message(:lan 116 248401 9
BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 307 107842 10
10 rows selected
SQL> ---运行时间前十名
SQL>
SQL> select sql_text,executions,plsql_exec_time,rn
2 from (select sql_text,executions,plsql_exec_time,
3 dense_rank() over(order by executions desc) rn
4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate))
5 where rn<=10
6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN
-------------------------------------------------------------------------------- ---------- --------------- ----------
begin setEMUserContext(:1, :2); end; 16 4040206 1
select s.synonym_name object_name, o.object_type from all_synonyms s, sys.a 1 2568938 2
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 631 2487157 3
select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.na 25 2312318 4
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : 286 1074618 5
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5592 1041768 6
/* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id, 133 471066 7
select count(*) from xdb.xdb$schema s where bitand(to_number(s.xmldata.flags, 25 255429 8
/* OracleOEM */ BEGIN :message := dbms_server_alert.expand_message(:lan 116 248401 9
BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 307 107842 10
10 rows selected
SQL> --运行次数前十名
2 from (select sql_text,executions,plsql_exec_time,
3 dense_rank() over(order by executions desc) rn
4 from v$sqlarea where trunc(last_active_time)=trunc(sysdate))
5 where rn<=10
6 /
SQL_TEXT EXECUTIONS PLSQL_EXEC_TIME RN
-------------------------------------------------------------------------------- ---------- --------------- ----------
UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRIN 5935 0 1
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 5913 0 2
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 5622 1047082 3
SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA 5622 0 3
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ 4605 0 4
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n 3724 0 5
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd 3410 0 6
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 2187 0 7
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan 1984 0 8
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# 1722 0 9
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis 1496 0 10
11 rows selected
SQL> --运行次数前十名