SQL Performance, SQL Performance Analysis Tool

SQL Performance, SQL Performance Analysis Tool

--- Executing
Select a. username, a. sid, B. SQL _TEXT, B. SQL _FULLTEXT
From v $ session a, v $ sqlarea B
Where a. SQL _address = B. address

--- Executed
Select B. SQL _TEXT, B. FIRST_LOAD_TIME, B. SQL _FULLTEXT
From v $ sqlarea B
Where B. FIRST_LOAD_TIME between '2017-10-2009: 24: 47 'and
'2017-10-2009: 24: 47 'order by B. FIRST_LOAD_TIME
(This method allows you to view the SQL statements executed in a certain period of time, and SQL _FULLTEXT contains the complete SQL statement)

Others
Select OSUSER, PROGRAM, USERNAME, SCHEMANAME, B. Cpu_Time, STATUS, B. SQL _TEXT
From V $ session a left join v $ SQL B on a. SQL _ADDRESS = B. address and a. SQL _HASH_VALUE = B. HASH_VALUE order by B. cpu_time desc

Select address, SQL _text, piece
From v $ session, v $ sqltext
Where address = SQL _address
-- And machine = <you machine name>
Order by address, piece

Find the top 10 SQL statements with poor performance.
SELECT * FROM (select PARSING_USER_ID, EXECUTIONS, SORTS,
COMMAND_TYPE, DISK_READS, SQL _text FROM v $ sqlarea
Order BY disk_reads DESC) where ROWNUM <10;

View the running sessions that account for a large io
SELECT se. sid, se. serial #, pr. SPID, se. username, se. status,
Se. terminal, se. program, se. MODULE, se. SQL _address, st. event, st.
P1text, si. physical_reads,
Si. block_changes FROM v $ session se, v $ session_wait st,
V $ sess_io si, v $ process pr WHERE st. sid = se. sid AND st.
Sid = si. sid AND se. PADDR = pr. addr and se. sid> 6 AND st.
Wait_time = 0 AND st. event not like '% SQL %' ORDER BY physical_reads DESC

SQL comparison

B's efficiency is high
A first queries table1, and then cyclically runs table1 row by row. queries the records matching table2 and calculates count, that is, calculates count cyclically, and queries table2 as many table records as A table.
B queries table1 and table2, joins the table, group by, calculates count, table2 queries only once
Therefore, B queries quickly.

SQL query Problems

Are there any indexes? If there is no index, it can only be a full table scan.