Oracle Database optimization-Analyze existing SQL

In the database SQL optimization, the first is to optimize the existing database SQL, mainly including the following:

1. What SQL is the database executing?

2, has been executed SQL is?

3. What is the most time-consuming first few SQL?

4. What is the most IO-consuming SQL?

5. What is the plan for a SQL execution? Does the optimization strategy on a SQL function?

The following will be stated separately for the above cases.

One, query the SQL that is being executed.

Select A.username, A.sid,b.sql_text, B.sql_fulltext
From V$session A, V$sqlarea b
where a.sql_address = b.address and B.parsing_schema_name= ' GTSPM ' #PARSING_SCHEMA_NAME a user name

Second, the query has been executed SQL

Select B.sql_text,b.first_load_time,b.sql_fulltext
From V$sqlarea b WHERE b.parsing_schema_name= ' GTSPM ' ORDER by first_load_time Desc

Third, the query performance of the worst first n sql

SELECT * FROM (select Parsing_user_id,executions,sorts,
Command_type,disk_reads,sql_text from V$sqlarea WHERE parsing_schema_name= ' GTSPM '
ORDER by Disk_reads DESC) where rownum<10;

Four, the most IO-consuming SQL

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

Five, query the execution plan of SQL.

See if the index is valid, whether full table scan is performed, and whether the index is used properly.

Oracle Database optimization-Analyze existing SQL