Find the most resource-consuming SQL statement in Oracle

1. Find the most resource-consuming SQL

 

We can first use top and other tools to find the process with the best resources (remember the process number), for example, the operating system process number is 2796, And then according to this process number (V $ process. spid) Find the process address (V $ process. ADDR), and then find the corresponding Sid (V $ session. sid), and then find the corresponding hash alue (V $ session. SQL _hash_value), and then find the corresponding SQL statement (SQL _text) in the V $ sqltext, $ SQL, V $ sqlarea and other views based on the hash alue ).

Select * from V $ process where spid = '000000 ';

Select SQL _hash_value, machine, username, program from V $ session where paddr = '63b7a584 ';

Select * from V $ sqltext where hashvalue = '201312 ';

Select * from V $ SQL where hashvalue = '2013 ';

Select * from V $ sqlarea where hashvalue = '201312 ';

Select. username,. machine,. program,. sid,. serial #,. status, C. piece, C. SQL _text from V $ session A, V $ process B, V $ sqltext C where B. spid = '000000' and B. ADDR =. paddr and. SQL _address = C. address (+) order by C. piece

========================================================== ====

Select SQL _text
From v $ sqltext
Where (hash_value, SQL _address) in
(
Select decode (SQL _hash_value, 0, prev_hash_value, SQL _hash_value ),
Decode (SQL _hash_value, 0, prev_ SQL _addr, SQL _address)
From v $ session
Where paddr = (select ADDR from V $ process where spid = 'operating system process id ')
)

========================================================== ==========

Ii. Oracle lock problem set and Solutions

 

1. Error: ORA-28000: the account is locked

Cause analysis:The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter failed_login_attempts, or the DBA has locked the account Solution:Wait for password_lock_time or contact DBA

2. View locking

Solution:

1. Find out the reason for waiting for the event
Select event, P1, P2, Sid from V $ session_wait where event not like 'SQL %' and event not like 'rdbms % ';
2. Find the locked Process
Select object_id, session_id, locked_mode from V $ locked_object;
3. Find the operating system process of the locked Process
Select spid from V $ process where ADDR = (select paddr from V $ session where Sid = 144); (19 represents the locked Sid)
4.
4. delete a process on the Operating System
Orakill eoffice 2768 (the first parameter is the SID of the database, and the second parameter is the process ID. This number is the process ID obtained above, which is operated in the doscommand window)

The following is an operation instance.
First, drop related locked objects such as drop view vw_wf_critcondition, and then stop PL/SQL Dev and open it again before the following operations can be performed.

SQL> select event, P1, P2, Sid from V $ session_wait where event not like 'SQL %' and event not like 'rdbms % ';

Event P1 P2 Sid
----------------------------------------------------------------------------------------------
Null event 1413697536 1 18
Null Event 1 95552 22
Pmon timer 300 0 1
SMON timer 300 0 5
Library cache lock 763239564 792075180 23 (this indicates that a database is locked .)
Wakeup time manager 0 0 8

6 rows selected

SQL> select object_id, session_id, locked_mode from V $ locked_object;

Object_id session_id locked_mode
-------------------------------
77 22 3
69 22 3
70 22 3
316 22 3
314 22 3
356 22 3
68 22 3
72 22 3
73 22 3
74 22 3
75 22 3

11 rows selected

SQL> select spid from V $ process where ADDR = (select paddr from V $ session where Sid = 22 );

Spid
------------
3348

Operations in DOS, and can only be performed on the local machine where data is installed
Orakill eoffice 3348

 

3.Locate the lock wait in the Oracle database

First, log on to the database as a DBA (not necessarily system), create three basic tables: my_session, my_lock, and my_sqltext, and create corresponding indexes on the columns that will be queried. The statement is as follows: REM extracts the fields of interest from the V $ session view, creates a my_session table, and creates an index on the fields to be used in the query to speed up the query.

Drop table my_session;
Create Table my_session
As
Select a. username, A. Sid, A. Serial #,
A. lockwait, A. Machine, A. status,
A. last_call_et, A. SQL _hash_value, A. Program
From v $ session
Where 1 = 2;

Create unique index my_session_u1 on my_session (SID );
Create index my_session_n2 on my_session (lockwait );
Create index my_session_n3 on my_session (SQL _hash_value );

---- REM extracts fields from the V $ lock view, creates the my_lock table, and creates indexes on the fields to be used in the query to speed up the query.
Drop table my_lock;
Create Table my_lock
As
Select id1, kaddr, Sid, request, Type
From v $ lock
Where 1 = 2;

Create index my_lock_n1 on my_lock (SID );
Create index my_lock_n2 on my_lock (kaddr );

---- REM extracts fields from the V $ sqltext view, creates a my_sqltext table, and creates an index on the fields to be used in the query to speed up the query.
Drop table my_sqltext;
Create Table my_sqltext
As
Select hash_value, SQL _text
From v $ sqltext
Where 1 = 2;

Create index my_sqltext_n1 on my_sqltext (hash_value );

---- Create an SQL script file so that it can be called directly from SQL * Plus if necessary. First, use the truncate table name command to delete the records in the table. The truncate command, instead of the DELETE command, is used because the record will be replayed during the execution of the DELETE command, which is slow and the space occupied by the index is not actually released, if the insert and delete operations are performed repeatedly, the space occupied by the index increases and the query speed slows down. The truncate command does not generate replays, And the execution speed is faster than that of delete, and the index space is released accordingly. After deleting a record, insert the relevant records in the three views into the three tables created by the user. Finally, the query results are displayed immediately because there is an index and the number of records is relatively small after the filter condition is inserted.
---- If the process blocking other user processes is normal, the user can be notified to submit the process to release the lock resources. If the process is not normal, that is, if the status is "inactive" and Its last_call_et is a relatively long time, you can run the following statement to clear the process and the system will automatically roll back it, to release the locked resources.

Alter system kill session 'sid, serial #';
---- The SQL script is as follows:
Set echo off
Set feedback off
Prompt 'delete old record .....'
Truncate table my_session;
Truncate table my_lock;
Truncate table my_sqltext;

Prompt 'get data .....'
Insert into my_session
Select a. username, A. Sid, A. Serial #,
A. lockwait, A. Machine, A. status,
A. last_call_et, A. SQL _hash_value, A. Program
From v $ session
Where nvl (A. username, 'null') <> 'null;

Insert into my_lock
Select id1, kaddr, Sid, request, Type
From v $ lock;

Insert into my_sqltext
Select hash_value, SQL _text
From v $ sqltext S, my_session m
Where S. hashvalue = M. SQL _hash_value;

Column username format A10
Column Machine format A15
Column last_call_et format 99999 heading "seconds"
Column Sid format 9999

Prompt "waiting for other users"
Select a. Sid, A. Serial #,
A. Machine, A. last_call_et, A. username, B. id1
From my_session A, my_lock B
Where a. lockwait = B. kaddr;

Prompt "Waiting users"
Select a. Sid, A. Serial #,
A. Machine, A. last_call_et, A. username,
B. B. type, A. Status, B. id1
From my_session A, my_lock B
Where B. id1 in
(Select distinct E. id1
From my_session D, my_lock E
Where D. lockwait = E. kaddr)
And a. Sid = B. Sid
And B. Request = 0;

Prompt "find its SQL"
Select a. username, A. Sid, A. Serial #,
B. id1, B. type, C. SQL _text
From my_session A, my_lock B, my_sqltext C
Where B. id1 in
(Select distinct E. id1
From my_session D, my_lock E
Where D. lockwait = E. kaddr)
And a. Sid = B. Sid
And B. Request = 0
And C. hash_value = A. SQL _hash_value;