[Reprint] Call the total Oracle Common performance Diagnostic statement. --Save your study for reference

Copyfrom Https://www.cnblogs.com/zhaoguan_wang

--1. Blocking and waiting for event information query
--Query the status of all sessions, the wait type, and the SQL script that is currently executing
Select T.sid, t.serial#, T.status, T.event, T.blocking_session, T.blocking_session_status
, T.username, T.machine, T.program, T.sql_exec_start, t.seconds_in_wait
, NVL (S1. Sql_text, S2. Sql_text) as Sql_text
From V$session t
Left joins V$sql s1 on t.sql_id = S1. sql_id and T.sql_child_number = S1.child_number
Left joins V$sql s2 on t.prev_sql_id = s2. sql_id and T.prev_child_number = S2.child_number
--where t.program = ' w3wp.exe '

--V1. V$session blocking_session is not null
Select GS. INST_ID, GS. SID, GS. Blocking_instance, GS. Blocking_session
Gs. STATUS, GS. EVENT, GS. USERNAME, GS. Machine, GS. Program
From Gv$session GS
where GS. Blocking_session is not null;

--V2. V$lock Block > 0
SELECT *
From Gv$lock
Where (ID1, ID2, TYPE) in
(select ID1, ID2, TYPE from Gv$lock where block > 0)
Order by ID1, block Desc;

--V3. Dba_waiters
SELECT * from Dba_waiters;

--V4. V$locked_object
Select L.oracle_username, L.os_user_name, L.locked_mode, O.object_name
, s.inst_id, S.sid, S.status, S.event, S.machine, S.program
, S1. Sql_text, S2. Sql_text
From Gv$locked_object l
Join all_objects o on l.object_id = o.object_id
Join Gv$session s on l.inst_id = s.inst_id and l.session_id = S.sid
Left joins Gv$sqlarea s1 on s.inst_id = S1. inst_id and s.sql_id = S1. sql_id
Left joins Gv$sqlarea s2 on s.inst_id = s2. inst_id and s.prev_sql_id = s2. sql_id

--based on SID found blocking SQL script
Select SE. inst_id, SE. SID, se.serial#, se.status, se.event, SE. Blocking_instance, SE. Blocking_session
, SE. USERNAME, SE. Machine, SE. Program, Se.sql_exec_start, se.seconds_in_wait
, Tr.xid, Tr.start_date, Tr.used_ublk, Tr.used_urec, NVL (S1. Sql_text, S2. Sql_text) as Sql_text
From Gv$session SE
Left join Gv$transaction tr on se.inst_id = tr.inst_id and se.taddr = tr.addr
Left joins Gv$sqlarea s1 on SE. inst_id = S1. inst_id and SE. sql_id = S1. sql_id
Left joins Gv$sqlarea S2 on SE. inst_id = s2. inst_id and SE. prev_sql_id = s2. sql_id
Where SE. SID in (889, 770);

--Uncommitted transactions, sessions
Select T.sid, t.serial#, T.status, t.event
, T.blocking_session, T.blocking_session_status
, T.machine, T.program, T.username
, T.TADDR, tr. XID, tr. STATUS, tr. Start_date, tr. USED_UBLK, tr. Used_urec
, NVL (S1. Sql_text, S2. Sql_text) as Sql_text
From V$session t
Join V$transaction tr on t.taddr = tr. ADDR
Left joins V$sql s1 on t.sql_id = S1. sql_id and t.sql_child_number = S1. Child_number
Left joins V$sql s2 on t.prev_sql_id = s2. sql_id and t.prev_child_number = s2. Child_number;

--Querying the current system for waiting events
SELECT event,
SUM (decode (wait_time,0,1,0)) "Curr",
SUM (decode (wait_time,0,0,1)) "Prev",
COUNT (*) "Total"
From v$session_wait
GROUP by Event
ORDER by Count (*) desc;
Copy Code
Copy Code
--2. Finding the top 10 poor performance SQL statements (large disk reads, missing indexes, or unreasonable statements)
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;

--Query the SQL that consumes the most resources
SELECT Sql_text, Hash_value, executions, buffer_gets, Disk_reads, Parse_calls
From V$sqlarea
WHERE buffer_gets > 10000000 OR disk_reads > 100000
ORDER by Buffer_gets + * Disk_reads DESC;

--attached
SELECT executions, disk_reads, buffer_gets
, ROUND ((buffer_gets-disk_reads)/buffer_gets, 2) Hit_radio
, TRUNC (disk_reads/executions) Reads_per_run
, Sql_text
From V$sqlarea
WHERE executions > 0 and disk_reads > 0 and buffer_gets > 0
and (buffer_gets-disk_reads)/Buffer_gets < 0.8
ORDER by 5 DESC;

--View the resource consumption of an SQL statement
SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_calls
From V$sqlarea
WHERE Hash_value = 228801498
and ADDRESS = Hextoraw (' cbd8e4b0 ');


--long-running SQL (the Session_longops view shows operations that have been running for more than 6 seconds.) Including backup, recovery, statistical information collection, query, etc.)
Select B.username, B.sid, b.serial#, B.start_time, B.last_update_time
, round (Sofar * 100/totalwork, 0) | | '% ' as progress
, A.sql_text
From V$sqlarea A
Join V$session_longops b on a.sql_id = b.sql_id
--where b.time_remaining! = 0--time_remaining: Estimated time remaining (seconds) to complete the operation
--and B.username = ' Sccot '

--The most CPU-intensive SQL statement in the last 10 minutes:
Select Sql_text
From (
Select Sql_id,count (*) as CN
From V$active_session_history
where Sample_time > SYSDATE-10/24/60
and Session_type <> ' BACKGROUND '
and session_state = ' on CPU '
GROUP BY sql_id
ORDER BY cn Desc
) Ash, V$sql s
where ash.sql_id=s.sql_id;

--The most recent 10-minute SQL statement that consumes IO:
Select Sql_text
From (
Select Sql_id,count (*) as CN
From V$active_session_history
where Sample_time > SYSDATE-10/24/60
and Session_type <> ' BACKGROUND '
and wait_class= ' User I/O '
GROUP BY sql_id
ORDER BY cn Desc
) Ash, V$sql s
where ash.sql_id=s.sql_id;

--Add 1 to find the SQL with long response time based on time:
Select T.sql_text, T.sql_fulltext, T.elapsed_time, T.last_load_time, t.*
From V$sql t
where T.last_load_time > To_char (sysdate-10/(24*60), ' Yyyy-mm-dd/hh24:mi:ss ')--and t.sql_text like '%LSHSXM% '
ORDER BY t.elapsed_time Desc;

--Add 2 to find hard parse critical sql:
Select Max (sql_id), substr (sql_text,0,100), COUNT (1)
From V$sql
where executions < 10
Group by substr (SQL_TEXT,0,100)
Having count (1) > 1000
Order by count (1) desc;

--3. A table that counts rows or occupies a larger space
Select T.table_name, T.num_rows, t.last_analyzed, T.temporary, t.*
From User_tables t
where t.num_rows > 0
ORDER BY t.num_rows Desc

--General table data size sorting
Select Segment_name, trunc (bytes/1024/1024)
From User_segments
where Segment_type = ' TABLE '
Order BY bytes Desc;

--Data size with LOB type table
SELECT A.table_name,
A.column_name,
Trunc (b.bytes/1024/1024),
B.segment_name,
B.segment_type,
B.tablespace_name,
B.blocks,
B.extents
From User_lobs A, user_segments B
WHERE A.segment_name = B.segment_name
ORDER by B.bytes DESC;

--Data table total data size occupied
Select Rowsize + NVL (lobsize, 0) datasize, t1.*, t2.*
From (
Select Segment_name, Trunc (bytes/1024/1024) rowsize
From User_segments
where Segment_type = ' TABLE '
) T1 LEFT JOIN (
SELECT A.table_name,
A.column_name,
Trunc (b.bytes/1024/1024) lobsize,
B.segment_name,
B.segment_type,
B.tablespace_name,
B.blocks,
B.extents
From User_lobs A, user_segments B
WHERE A.segment_name = B.segment_name
) t2 on t1.segment_name = T2.table_name
ORDER BY Rowsize + NVL (lobsize, 0) desc

Supplement, query the blocks that the table actually uses:
SELECT COUNT (DISTINCT dbms_rowid. Rowid_block_number (ROWID) | | Dbms_rowid. ROWID_RELATIVE_FNO (ROWID)) as Used_blocks
from table_name;

--4. View Oracle Memory parameter configuration
SELECT *
from V$parameter t
where t.name in (
' memory_max_target ', ' mem Ory_target ', ' sga_max_size ', ' sga_target ', ' pga_aggregate_target ', ' Cpu_count '
, ' db_cache_size ', ' Shared_pool_ ' Size ', ' large_pool_size ', ' java_pool_size ', ' streams_pool_size ', ' Log_buffer '
, ' db_2k_cache_size ', ' db_4k_cache_ ' Size ', ' db_8k_cache_size ', ' db_16k_cache_size ', ' db_32k_cache_size '
);

-Query the allocation details and usage of the SGA area
Select T1.pool, T1.RESERVED_SIZE_MB, T2.USED_SIZE_MB, T1.reserved_size_mb-t2.used_ SIZE_MB as UNUSED_SIZE_MB
from (
Select T.pool, trunc (sum (t.bytes)/1024/1024) RESERVED_SIZE_MB
from v$ Sgastat T
where T.pool is isn't null
GROUP by T.pool
) T1
Left join (
Select T.pool, trunc (SUM (t.bytes )/1024/1024) USED_SIZE_MB
from V$sgastat t
where t.pool are NOT null and t.name! = ' Free memory '
Group by T. POOL
) t2 on t1.pool = t2.pool
Union ALL
Select T.name, trunc (sum (t.bytes)/1024/1024) reserved_size_mb, NULL, NU ll
from V$sgastat T
where T.pool was null
GROUP by T.name
ORDER by 2 desc;

--View the cache hit ratio and soft resolution rate of the system
SELECT ' Cache hit ratio ' as Name, (physical_reads/(db_block_gets+consistent_gets)) *100 value
From V$buffer_pool_statistics
UNION ALL
Select ' Soft parse ratio ', 100-100* (A.value/b.value)
From V$sysstat A, V$sysstat b
Where a.name= ' parse count (hard) ' and B.name= ' parse count (total) ';

--5. Statements that query the lock table
Select ' Kill-9 ' | | Ps. SPID,
' ALTER system kill session ' | | vs.sid| | ', ' | | vs.serial#| | '; ',
/* DECODE (V$lock. TYPE, ' TM ', ' TABLE lock ', ' TX ', ' ROW lock ', NULL) lock_level,*/
Decode (VL. locked_mode,0, ' [0] none ',
1, ' [1] null empty ',
2, ' [2] row-s line Common (RS): Shared table lock, Sub Share ',
3, ' [3] Row-x (RX): For line modification, Sub exclusive ',
4, ' [4] Share shared Lock (S): block other DML operations, Share ',
5, ' [5] s/row-x shared row Exclusive (SRX): Prevents other tasks from operating, Share/sub exclusive ',
6, ' [6] Exclusive (X): independent access, exclusive ',
' [' | | Vl. locked_mode| | '] Other Lock ') Lockmode,
Ps. Spid,os_user_name,vs. Program,vs. Machine,oracle_username,object_name,vs. Logon_time,
Vs.status,vs. MODULE, NVL (Currentsql.sql_text, prevsql.sql_text) as Sql_text
From V$locked_object VL
Join Dba_objects OB on VL. object_id = OB. object_id
Join V$session VS on VL. session_id = VS. Sid
Join V$process PS on PS. ADDR = VS. Paddr
--left join V$sql Currentsql on vs. Sql_hash_value = Currentsql.hash_value and vs. Sql_address = currentsql.address
--left join V$sql Prevsql on vs. Prev_hash_value = Prevsql.hash_value and vs. PREV_SQL_ADDR = prevsql.address
Left joins V$sql Currentsql on vs. sql_id = currentsql.sql_id and vs. Sql_child_number = Currentsql.child_number
Left joins V$sql Prevsql on vs. prev_sql_id = prevsql.sql_id and vs. Prev_child_number = Prevsql.child_number
Order BY Vs.logon_time;

--6. Statements that produce a Kill session
Select A.sid,
B.spid,
a.serial#,
A.lockwait,
A.username,
A.osuser,
A.logon_time,
a.last_call_et/3600 Last_hour,
A.status,
' Orakill ' | | Sid | | "| | SPID Host_command,
' ALTER system kill session ' | | A.sid | | ', ' | | a.serial# | | "' Sql_command
From V$session A, v$process B
where a.paddr = B.addr
and SID > 6;

--7. Viewing IO conditions
Select
Df.name file name,
Fs.phyrds read times,
Fs.phywrts write times,
(Fs.readtim/decode (fs.phyrds,0,-1,fs.phyrds)) Read time,
(Fs.writetim/decode (fs.phywrts,0,-1,fs.phywrts)) write time
From V$datafile DF,
V$filestat FS
where df.file#=fs.file#
Order BY Df.name;

--8. viewing tablespace conditions
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND ((d.tot_grootte_mb-f.total_bytes)/D.TOT_GROOTTE_MB * 100, 2), ' 990.99 ') "Use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,round (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 DESC;

--9. Find the corresponding SQL based on the PID seen by the top command on UNIX
SELECT p.pid PID,
S.sid SID,
P.spid spid,
S.username username,
S.osuser Osname,
p.serial# s_#,
P.terminal,
P.program program,
P.background,
S.status,
RTRIM (SUBSTR (A.sql_text, 1,)) SQL
From V$process P, V$session S, V$sqlarea A
WHERE p.addr = s.paddr
and s.sql_address = a.address (+)
and p.spid like '%cpu the highest process corresponds to pid% ';

--Status information for databases and instances
Select Name, Log_mode, Open_mode, flashback_on, Supplemental_log_data_min, platform_name from V$database;
Select Instance_number, instance_name, host_name, version, Startup_time, status from V$instance;

--Empty the cache (shared pool and data cache), Command window executes:
alter system flush Shared_pool;
alter system flush Buffer_cache;

--Index Information view:
Select T.table_owner, T.index_name, T.blevel, t.leaf_blocks
, t.last_analyzed, T.distinct_keys, T.num_rows, t.sample_size
From user_indexes t where T.table_name = upper (' WorkItem ');

--analyze Table UPDATE STATISTICS:
Analyze table my_table compute statistics;
Analyze index idx_name compute statistics;
--or
EXEC dbms_stats.gather_table_stats (ownname = ' Socct ', tabname = ' WorkItem ', estimate_percent =>100, cascade = >true, method_opt = ' For all columns size auto ');
/*
Parameter description:
I. Estimate_percent: Percentage of sampling statistics
Second, Cascade: When False, only the table is statistically analyzed; When true, both the table and the index are statistically analyzed.
Third, method_opt:
Method_opt: Determines how the histograms information is counted. The value of Method_opt is as follows:
For all columns: counts the histograms of all columns.
For all indexed columns: counts the histograms of all indexed columns.
For all hidden columns: statistics You can't see the histograms of the column
For columns <list> SIZE <N> | REPEAT | AUTO | Skewonly
SIZE: Counts the histograms of the specified column. The value range of N [1,254];
REPEAT the last statistic histograms;
AUTO is determined by Oracle for the size of N;
Iv. degree: concurrency, can be nice value, 8/16/24/32 ...
*/

--Frees data space after data is deleted
ALTER TABLE my_table enable row movement;
ALTER TABLE my_table shrink space cascade;
ALTER TABLE my_table disable row movement;

Or
ALTER TABLE table_name move; --After performing this operation, you need to rebuild the index

Select T.num_rows, T.temporary, t.last_analyzed, t.* from User_tables t
where t.table_name = Upper (' gspauresult_tkk0107 ');

Select T.num_rows, T.distinct_keys, T.sample_size, t.last_analyzed, t.*
From User_indexes t
where t.table_name = Upper (' tkk007 ');

Select t.* from User_ind_columns t
where t.table_name = Upper (' gspauresult_tkk0107 ')
Order by T.index_name, t.column_position;

Select Dbms_metadata.get_ddl (' TABLE ', Upper (' gspauresult_tkk0107 ')) from dual;
Select Dbms_metadata.get_ddl (' INDEX ', Upper (' idx_gspauresult_tkk0107 ')) from dual;
Select Dbms_metadata.get_ddl (' CONSTRAINT ', Upper (' tkk0107 ')) from dual;
Select Dbms_metadata.get_ddl (' Ref_constraint ', Upper (' tkk0107 ')) from dual;
SELECT * FROM User_tables t order by Dbms_random.random;

Gets the execution plan for the specified SQL in memory:
Select Sql_id,child_number,sql_text from V$sql where Sql_text like ' select COUNT (1) from EMP A where a.dept_no =% ';
SELECT * FROM table (Dbms_xplan. Display_cursor (' sql_id ', 0));

1. PL/SQL Developer use F5
2. Explain plan for SELECT COUNT (1) from EMP a where a.dept_no=5;
SELECT * FROM table (Dbms_xplan.display ());
3. Use Set Autotrace traceonly exp in sqlplus;
Copy Code

[Reprint] Call the total Oracle Common performance Diagnostic statement. --Save your study for reference