Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the scripts for solving problems in the Oracle database?" interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the scripts for solving problems in Oracle databases?"

> View operating system load

After logging on to the database server, the first one is to confirm whether CPU, memory and Imax O are abnormal through system commands. The commands of each system are different, such as top, topas, vmstat and iostat.

> View wait events

The second step is to connect to the database to view the active waiting events, which is the most basic means of monitoring, patrolling and diagnosing the database. Usually 81% of the problems can be initially identified as the cause through waiting events, which is the most direct reflection of the operation of the database. For example, the following script checks the number of waiting events and the waiting time, and excludes some common IDLE waiting events.

-- wait_event col event for A45 SELECT inst_id,EVENT, SUM (DECODE (WAIT_TIME, 0,0,1)) "Prev", SUM (DECODE (WAIT_TIME, 0,1,0)) "Curr", COUNT (*) "Tot", sum (SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAITWHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client') 'gcs remote message') AND event NOT LIKE'% idle%' AND event NOT LIKE'% Idle%' AND event NOT LIKE'% Streams AQ%' GROUP BY inst_id,EVENT ORDER BY 1pje 5 desc

Here, you need to understand the causes of some common exception waiting events and form conditioned reflexes, such as library cache lock, read by other session, row cache lock, buffer busy waits, latch:shared pool, gc buffer busy, cursor: pin S on X, direct path read, log file sync, enq: TX-index contention, PX Deq Credit: send blkd, latch free, enq: TX-row lock contention, and so on. If the number of exception waiting events and waiting time is very long, then the entry point for troubleshooting the cause is here.

> check the session according to the waiting event

After getting the exception wait event, we check the session details based on the wait event, that is, to see which sessions are performing and which SQL is waiting, as well as the user name and machine name, and whether it is blocked. In addition, the following script can be rewritten to look up the session according to the user, the session according to SQL_ID, and so on.

-- Motianlun session_by_event SELECT / * + rule * / sid, s.serialwheel, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE | |'. | | blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6

> query the details of a session

After getting the list of sessions, you can query the details of a session based on the following SQL, such as the last executed SQL_ID, login time, etc., and the SQL can also be rewritten into multiple ones.

-- Motianlun session_by_sid SELECT s.sid, s.serialwheel, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session bambooses, v$process p WHERE sid ='& sid' AND s.paddr = p.addr

> query object information

You can see the object ID that the session is waiting for from the first two SQL, and you can query the details of the object through the following SQL.

-- Mo Tianlun obj_info col OBJECT_NAME for A30 select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid; > query SQL statement

Query the SQL statement according to SQL_ID and HASH_VALUE. If it is not found in the v$sqlarea, you can try the query in the DBA_HIST_SQLTEXT view.

-- Mo Tianlun sql_text select sql_id,SQL_fullTEXT from v$sqlarea where (sql_id='&sqlid' or hash_value=to_number ('& hashvale')) and rownum > query session blocking

Find out how many sessions are blocked by a session using the following SQL.

Mo Tianlun blocking_sess select count (*), blocking_session from v$session where blocking_session is not null group by blocking_session

> query the lock of the database

Use the following SQL to query the locks of a session, what are the TM and TX locks, and the SQL of the session and lock associated query. Note that the ctime is specified to be greater than 100 seconds. In 30% of the cases, the lock table is manipulated by human error, resulting in the application SQL being blocked and unable to run.

-- Motianlun lock set linesize 180 col username for a15 col owner for a15 col OBJECT_NAME for A30 col SPID for A10-- query the lock select / * + rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS of a session, LOCKED_MODE from gv$locked_object where session_id=&sid;-- query TM, TX lock select / * + rule*/* from v$lock where ctime > 100 and type in ('TX','TM') order by 3Prior 9 -- query the lock select / * + rule*/s.sid,p.spid,l.type,round (max (l.ctime) / 60Power0) in the database, lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process pdcmccccccdccccccccccccccccccccccccccccccccccccccccccccccdcccccccccccccccccdcccccccccccdcccccccccccccccccccccccccccccccccccccdccdhcw / * + rule*/s.sid,p.spid,l.type,round (max (l.ctime) / 60Power0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process pdcdcmcmcmcmcccccccccc Dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime > 100and l.type in ('TX','TM','FB') group by s.sidreagep.spidrect l.typewrits.sqlSecretidreages.USERNAMEJournal b.ownerdirection b.objectpapertypeauthorb.objectaccountypename order by 9dy1co.3

Retain on-the-spot evidence

Some problems may take a long time to analyze, or need the assistance of outsiders, so it is very important to keep the on-site evidence. The following scripts are systemstate dump and hanganalyze steps. If sqlplus cannot log in, you can add the-prelim parameter.

-- systemstate dump sqlplus-prelim / as sysdba oradebug setmypid oradebug unlimit; oradebug dump systemstate 266;-- wait for 1 min oradebug dump systemstate 266;-- wait for 1 min oradebug dump systemstate 266; oradebug tracefile_name;-- hanganalyze oradebug setmypid oradebug unlimit; oradebug dump hanganalyze 3-- wait for 1 min oradebug dump hanganalyze 3-- wait for 1 min oradebug dump hanganalyze 3 oradebug tracefile_name

> kill session

In general, in order to quickly resume business after initially defining a problem, you need to kill some sessions, especially batch kill sessions, and sometimes directly kill all LOCAL=NO processes. When you kill a session, you must check and confirm it, let alone execute it on other nodes or other servers.

-Motianlun kill_sess set line 199 col event format A35-kill a SID session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1 -- according to SQL_ID kill session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1 -- Kill session SELECT / * + rule * / sid, s.serialkeeper, 'kill-9' according to waiting event | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1 | -- according to user kill session SELECT / * + rule * / sid, s.serialconversation, 'kill-9' | | spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1;-- kill all LOCAL=NO processes ps-ef | grep LOCAL=NO | grep $ORACLE_SID | grep-v grep | awk'{print $2}'| xargs ki

> restart method

Tail-f alert_.logalter system checkpoint;alter system switch logfile;shutdown immediate;startu

If you need to modify static parameters, memory and other problems, you need to restart the database (do not think that restarting is very LOW. In many cases, you often use this trick from Internet cafes in order to quickly restore business). Remember not to stumble over the cause of the problem at this time as a topic for research. Our first task is to restore business.

CRT Button Tips

Another tip is to organize commonly used scripts into SecureCRT's Button Bar. Just click on the set button, which is equivalent to directly executing the corresponding SQL statement, so that you don't have to paste and copy each time, or upload the script to each server. However, do not set operational button such as DDL, so as not to be late.

At this point, I believe you have a deeper understanding of "what are the scripts for solving problems in the Oracle database?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.