oracle监控脚本（Oracle监控脚本）.docVIP

oracle监控脚本（Oracle监控脚本） SQL statement acquisition of SQL execution of Oracle Database Collect the top SQL statements of the system 1: SQL takes some time (in seconds) to execute Refer to the SQL statement, such as the crawl script of the SQL statement with a execution time greater than 3 seconds: - SQL takes time (by seconds) to execute at some point Select, a.username, a.sql_text, round (a.elapsed_time_s, 1), spend processing, press seconds, as, remark From ( Select, ds.username, Sql_Text, round ((sum (sa.elapsed_time) / sum (sa.EXECUTIONS)) / 1000000, 3) as Elapsed_Time_S From v$sqlarea SA Inner, join, dba_users, DS, on, sa.PARSING_USER_ID = ds.user_id Where, sa.EXECUTIONS 0, and (sa.elapsed_time / sa.EXECUTIONS) / 1000000 1 --and upper (ds.username) like%USERNAMe% -- SQL statements executed by a particular user. The actual execution is replaced by the system. Group, by, ds.username, Sql_Text Order by elapsed_time_s desc ) A Where a.elapsed_time_s 3 -- greater than 3 seconds 2: SQL consumes memory at a certain time (press K) Refer to the SQL statement, such as SQL, to execute fetching scripts that consume SQL statements larger than 256K: -- SQL consumes memory at a certain time (press K) Select, a.username, a.sql_text, round (a.usedmemory/1024,1), memory consumption, press K, as, remark From ( Select, ds.username, Sql_text, round (sum (sa.sharable_mem) / sum (sa.EXECUTIONS), 3) as, UsedMemory From v$sqlarea SA Inner, join, dba_users, DS, on, sa.PARSING_USER_ID = ds.user_id Where sa.EXECUTIONS 0 --and upper (ds.username) like%USERNAMe% -- SQL statements executed by a particular user. The actual execution is replaced by the system. Group, by, ds.username, Sql_text Order by UsedMemory desc ) A Where a.usedmemory / 1024 256 -- greater than 256K 3: a certain period of time, SQL execution of CPU, IO, the number of executions, consumption of memory and so on Refer to the SQL scripting statement: Table structure definition of data storage for acquisition results: Crea