To view poor performance SQL statements

Query the worst-performing 10 sql

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;

The hash value of the Hash_value:sql statement.

The address of the Address:sql statement in the SGA.

These two columns are used to identify SQL statements, and sometimes two different statements may have the same hash value. At this point, the SQL statement must be confirmed with address together.

The hash value of the Hash_value:sql statement.

The address of the Address:sql statement in the SGA.

These two columns are used to identify SQL statements, and sometimes two different statements may have the same hash value. At this point, the SQL statement must be confirmed with address together.

PARSING_USER_ID: the user who resolves the first cursor for the statement

Version_count: Number of statement cursor

Kept_versions:

Total shared memory used by Sharable_memory:cursor

Total number of resident memory used by Persistent_memory:cursor

The total number of run-time memory used by Runtime_memory:cursor.

The text of the Sql_text:sql statement (maximum only the first 1000 characters of the statement can be saved).

Module,action: Information when the session parses the first cursor when using Dbms_application_info

Sorts: Indicates the number of sorts

Cpu_time: The CPU time the statement was parsed and executed

Elapsed_time: The shared time at which statements are parsed and executed

Parse_calls: Number of parsing calls (soft, hard) for statements

Executions: Indicates how many times the same SQL statement was executed

Invalidations: Cursor invalidation number of statements

LOADS: The number of statements loaded (loaded)

Rows_processed: The total number of columns returned by the statement

View the most resource-intensive SQL

SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_calls

From V$sqlarea

WHERE buffer_gets > 10000000 OR disk_reads > 1000000

ORDER by Buffer_gets + * Disk_reads DESC;

Disk_reads: Indicates the number of physical reads.

Analyze poor-performance SQL

SELECT executions, disk_reads, Buffer_gets,

ROUND ((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,

ROUND (disk_reads/executions,2) Reads_per_run,

Sql_text

From V$sqlarea

WHERE executions>0

and Buffer_gets >0

and (Buffer_gets-disk_reads)/buffer_gets < 0.8

Querying SQL statements that have already been resolved in a shared pool and their related information

--executions execution of all child cursors This statement number of times

--disk_reads the number of read disks that are caused by all child cursors running this statement

--buffer_gets the number of read memory that is caused by all child cursors running this statement

--hit_radio hit rate

--reads_per_run number of Read and write disks per execution

Generally speaking, the higher the Executions,buffer_gets,hit_radio, the more memory is read, the less disk is the ideal state, so the higher the better

The other two higher reads the disk the more times, therefore the low point is good

Get up to 10 executions of SQL

Select Sql_text,executions

From (

Select Sql_text,executions,rank () over (order by executions Desc) Exec_rank

From V$sql

)

where Exec_rank <=10;

Get the maximum 10 SQL for a single execution time

Select Sql_id,sql_text,round (exec_time/1000000,0) exec_time

From

Select Sql_id,sql_text,exec_time,rank () over (order by exec_time Desc) Exec_rank

From

(

Select Sql_id,sql_text,cpu_time,elapsed_time,executions,round (elapsed_time/executions,0) exec_time

From V$sql

where executions>1

)

)

where Exec_rank <=10;

CPU and IO occupy the most:

Select Sql_text,executions,buffer_gets,disk_reads from V$sql

where Buffer_gets > 100000

or Disk_reads > 100000

ORDER BY buffer_gets+100*disk_reads Desc

The top 5 spends the most CPU and time:

Select Sql_text,executions,

Round (elapsed_time/1000000,2) Elapsed_seconds,

Round (cpu_time/1000000,2) Cpu_secs from

(SELECT * from V$sql ORDER BY elapsed_time Desc)

where rownum<6

Because V$sql is a dynamic performance graph, only some frequently executed SQL can be kept.

View the most resource-intensive sessions

Select To_char (m.end_time, ' dd-mon-yyyy HH24:MI:SS ') E_dttm, m.intsize_csec/100 ints, S.username usr, m.session_id Sid, M . Session_serial_num ssn, ROUND (M.CPU) cpu100, M.physical_reads prds, M.logical_reads lrds, m.pga_memory, m.hard_parses hp , M.soft_parses sp, m.physical_read_pct PRP, m.logical_read_pct LRP, s.sql_id from V$sessmetric m,v$session s where (M.PHY sical_reads>100 or m.cpu>100 or m.logical_reads>100) and M.session_id=s.sid and m.session_serial_num=s.serial # ORDER BY M.physical_reads Desc,m.cpu desc,m.logical_reads DESC;

Query the 5 most frequently used queries:

Select Sql_text,executions from (select Sql_text,executions, Rank () up (order by executions Desc) Exec_rank from V$sql) where Exec_rank <=5;

The SQL TOP5 that consumes the most disk reads:

Select Disk_reads,sql_text from (select Sql_text,disk_reads, Dense_rank () Up (order by disk_reads Desc) Disk_reads_rank From V$sql) where Disk_reads_rank <=5;

Find queries that require a lot of buffered read (logical read) operations:

Select Buffer_gets,sql_text from (select Sql_text,buffer_gets, Dense_rank () Up (order by buffer_gets Desc) buffer_gets_ Rank from V$sql) where buffer_gets_rank<=5;

To view poor performance SQL statements