SQL statements check ORACLE system
SQL statements check the system.
Check to see which machine is lockeddata
1 2 3 4 5 6 7 8 9 10 11 | select c.owner, c.object_name, c.object_type, b.sid, b.serial #, 'ALTER SYSTEM KILL SESSION' '' || b.sid || ',' || b.serial # || '' ';' AS KILL_COMMAND, b.status, b.osuser, b.machine from v $ locked_object a, v $ session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id; |
Check the SQL statement takes a lot of time
1 2 3 4 5 6 7 8 9 10 11 | SELECT username parsing_schema_name, sql_fulltext sql, optimizer_cost cost, executions, ROUND (elapsed_time / executions / 1000000,2) AS "avg_time / exc (s)" FROM gv $ sqlarea WHERE optimizer_cost> 100 AND last_load_time> = TRUNC (SYSDATE) AND executions> 1 AND elapsed_time / executions / 1000000> 1 AND parsing_schema_name NOT LIKE '% SYS%' ORDER BY optimizer_cost DESC |
Finding the running task is not complete
1 2 3 4 5 6 7 8 9 | SELECT S.SID, S.SERIAL #, 'ALTER SYSTEM KILL SESSION' '' || S.SID || ',' || S.SERIAL # || '' ';' AS KILL_COMMAND, Q.SQL_TEXT FROM GV $ SESSION S, V $ SQL Q WHERE S.USERNAME IS NOT NULL AND S.STATUS = 'ACTIVE' AND S.SQL_ID IS NOT NULL AND Q.SQL_ID = S.SQL_ID; |
Find out how many% of CPUs SIDs are using
1 2 3 4 5 6 7 8 | SELECT se.username, ss.sid, ROUND (value / 100) "CPU Usage" FROM v $ session se, v $ sesstat ss, v $ statname st WHERE ss.statistic # = st.statistic # AND name LIKE '% CPU được dùng bởi phiên bản%' AND se.sid = ss.SID AND se.username IS NOT NULL ORDER BY value DESC; |