Common performance query SQL statements

Common performance query SQL statements

-- View table locks
Select * From SYS. V _ $ sqlarea where disk_reads> 100

-- Waiting for monitoring cases
Select event,
Sum (decode (wait_time, 0, 0, 1) "Prev ",
Sum (decode (wait_time, 0, 1, 0) "curr ",
Count (*) "tot"
From v $ session_wait
Group by event
Order by 4

-- Rollback segment contention
Select name, waits, gets, Waits/gets "ratio"
From v $ rollstat A, V $ rollname B
Where a. USN = B. USN

-- View the SQL statements being issued by the foreground
Select user_name, SQL _text
From v $ open_cursor
Where sid in (select Sid
From (select Sid, serial #, username, Program
From v $ session
Where status = 'active '))

-- Data table space usage
Select segment_name, tablespace_name, bytes, blocks
From user_segments
Where segment_type = 'table'
Order by Bytes DESC, blocks DESC

-- View the tablespace shard size
Select tablespace_name,
Round (SQRT (max (blocks)/sum (blocks ))*
(100/SQRT (count (blocks )))),
2) fsfi
From dba_free_space
Group by tablespace_name
Order by 1

-- View tables with high fragmentation levels
Select segment_name table_name, count (*) extents
From dba_segments
Where owner not in ('sys ', 'system ')
Group by segment_name
Having count (*) = (select max (count (*))
From dba_segments
Group by segment_name );

-- View the disk space occupied by the tablespace
Select B. file_id File ID,
B. tablespace_name: name of the tablespace,
B. Total size of bytes/1024/1024,
(B. bytes-sum (nvl (A. bytes, 0)/1024/1024 used size,
Sum (nvl (A. bytes, 0)/1024/1024 remaining space,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_id, B. bytes
Order by B. file_id;

-- View the rollback segments used by the session
Select R. Name rollback segment name,
S. Sid,
S. Serial #,
S. Username username,
T. status,
T. cr_get,
T. phy_io,
T. used_ublk,
T. noundo,
Substr (S. Program, 1, 78)
From SYS. V _ $ session S, SYS. V _ $ transaction T, SYS. V _ $ rollname R
Where T. ADDR = S. taddr
And T. xidusn = R. USN
Order by T. cr_get, T. phy_io

-- View the remaining available memory in the SGA Zone
Select name,
Sgasize/1024/1024 "allocated (m )",
Bytes/1024/1024 "Free Space (m )",
Round (Bytes/sgasize * 100, 2) "Free Space percentage (% )"
From (select sum (bytes) sgasize from SYS. V _ $ sgastat) s,
SYS. V _ $ sgastat F
Where F. Name = 'free memory'

-- Monitor the I/O ratio of tablespace
Select DF. tablespace_name name,
DF. file_name "file ",
F. phyrds Pyr,
F. phyblkrd PBR,
F. phywrts pyw,
F. phyblkwrt PBW
From v $ filestat F, dba_data_files DF
Where F. File # = DF. file_id
Order by DF. tablespace_name;

-- Monitor the I/O ratio of the file system
Select substr (A. File #, 1, 2 )"#",
Substr (A. Name, 1, 30) "name ",
A. status,
A. bytes,
B. phyrds,
B. phywrts
From v $ datafile A, V $ filestat B
Where a. File # = B. File #

-- Search for all indexes under a user:
Select user_indexes.table_name,
User_indexes.index_name,
Uniqueness,
Column_name
From user_ind_columns, user_indexes
Where user_ind_columns.index_name = user_indexes.index_name
And user_ind_columns.table_name = user_indexes.table_name
Order by user_indexes.table_type,
User_indexes.table_name,
User_indexes.index_name,
Column_position;

-- Check the storage of tables and Indexes
Select segment_name, sum (bytes), count (*) ext_quan
From dba_extents
Where tablespace_name = '& tablespace_name'
And segment_type = 'table'
Group by tablespace_name, segment_name;

Select segment_name, count (*)
From dba_extents
Where segment_type = 'index'
And owner = '& owner'
Group by segment_name;

-- Monitor SGA hit rate
Select a. Value + B. Value "logical_reads ",
C. Value "phys_reads ",
Round (100 * (A. Value + B. Value)-C. Value)/(A. Value + B. Value) "buffer hit ratio"
From v $ sysstat A, V $ sysstat B, V $ sysstat C
Where a. Statistic # = 48
And B. Statistic # = 51
And C. Statistic # = 55;

-- Monitor the hit rate of the Dictionary Buffer in SGA
Select parameter,
Gets,
Getmisses,
Getmisses/(gets + getmisses) * 100 "miss ratio ",
(1-(sum (getmisses)/(sum (gets) + sum (getmisses) * 100 "hit ratio"
From v $ rowcache
Where gets + getmisses <> 0
Group by parameter, gets, getmisses;

-- Monitor the hit rate of the shared cache in SGA, which should be less than 1%
Select sum (PINs) "Total Pins ",
Sum (reloads) "Total reloads ",
Sum (reloads)/sum (PINs) * 100 libcache
From v $ librarycache;

-- Monitor the hit rate of the log cache area in SGA, which should be less than 1%
Select name,
Gets,
Misses,
Immediate_gets,
Immediate_misses,
Decode (gets, 0, 0, misses/gets * 100) ratio1,
Decode (immediate_gets + immediate_misses,
0,
0,
Immediate_misses // (immediate_gets + immediate_misses) * 100) ratio2
From v $ Latch
Where name in ('redo allocation', 'redo copy ');

-- Monitor the sorting ratio of memory to hard disk, preferably make it smaller than. 10
Select name, value from V $ sysstat where name in ('sorts (memory) ', 'sorts (Disk )');

-- Monitor Dictionary Buffer
Select sum (gets) "dictionary gets", sum (getmisses) "dictionary cache get misses" from V $ rowcache

-- Display the category and size of all database objects
Select count (name) num_instances,
Type,
Sum (source_size) source_size,
Sum (parsed_size) parsed_size,
Sum (code_size) code_size,
Sum (error_size) error_size,
Sum (source_size) + sum (parsed_size) + sum (code_size) +
Sum (error_size) size_required
From dba_object_size
Group by type
Order by 2;

-- Monitor who is running the SQL statement of the current database
Select osuser, username, SQL _text
From v $ session A, V $ sqltext B
Where a. SQL _address = B. Address
Order by address, piece;

-- V $ dispatcher
Select busy/(busy + idle) "shared servers busy" from V $ dispatcher; -- when this value is greater than 0.5, the parameter must be increased
Select sum (wait)/sum (totalq) "dispatcher waits" from V $ queue where type = 'dispatcher ';
Select count (*) from V $ dispatcher;
Select servers_highwater from V $ shared_server_monitor; -- When servers_highwater is close to max_shared_servers, increase the Parameter

-- Tables created by non-system users in the system tablespace
Select owner, table_name
From dba_tables
Where tablespace_name in ('system', 'user _ data ')
And owner not in
('System', 'sys ', 'outln', 'ordsys ', 'mdsys', 'Scott ', 'hosteac ')

-- The worst-performing SQL statement
Select * from (select parsing_user_id executions, sorts, command_type, disk_reads, SQL _text
From v $ sqlarea
Order by disk_reads DESC)
Where rownum: <100;

-- SQL statements that read disks more than 100 times
Select * From SYS. V _ $ sqlarea where disk_reads> 100;

-- The most frequently executed SQL statement
Select * From SYS. V _ $ sqlarea where executions> 100

-- Query user sessions with multiple CPUs
Select a. Sid,
Spid,
Status,
Substr (A. Program, 1, 40) prog,
A. terminal,
Osuser,
Value/60/100 value
From v $ session A, V $ process B, V $ sesstat C
Where C. Statistic # = 12
And C. Sid = A. Sid
And a. paddr = B. ADDR
Order by value DESC

-- Number of objects used by each session
Select a. Sid, S. Terminal, S. Program, count (A. Sid)
From v $ access a, V $ session s
Where a. Owner <> 'sys'
And S. Sid = A. Sid
Group by A. Sid, S. Terminal, S. Program
Order by count (A. Sid)

-- Monitor the usage of log_buffer: (the value should be smaller than 1%; otherwise, the size of log_buffer should be increased)
Select rbar. Name,
Rbar. value,
Re. Name,
Re. value,
(Rbar. Value * 100)/Re. Value | '%' "radio"
From v $ sysstat rbar, V $ sysstat re
Where rbar. Name = 'redo Buffer Allocation retries'
And re. Name = 'redo entries ';

-- View running SQL statements:
Select SQL _text from V $ SQL

-- The client host information sys_context function calls the userenv namespace to obtain relevant information.
Select sys_context ('userenv', 'terminal') terminal,
Sys_context ('userenv', 'language') language,
Sys_context ('userenv', 'sessionid') sessionid,
Sys_context ('userenv', 'instance') instance,
Sys_context ('userenv', 'entryid') entryid,
Sys_context ('userenv', 'isdba ') isdba,
Sys_context ('userenv', 'nls _ territory ') nls_territory,
Sys_context ('userenv', 'nls _ currency ') nls_currency,
Sys_context ('userenv', 'nls _ calendar ') nls_calendar,
Sys_context ('userenv', 'nls _ date_format ') nls_date_format,
Sys_context ('userenv', 'nls _ date_language ') nls_date_language,
Sys_context ('userenv', 'nls _ sort ') nls_sort,
Sys_context ('userenv', 'current _ user') CURRENT_USER,
Sys_context ('userenv', 'current _ userid') current_userid,
Sys_context ('userenv', 'session _ user') session_user,
Sys_context ('userenv', 'session _ userid') session_userid,
Sys_context ('userenv', 'proxy _ user') proxy_user,
Sys_context ('userenv', 'proxy _ userid') proxy_userid,
Sys_context ('userenv', 'db _ Domain ') db_domain,
Sys_context ('userenv', 'db _ name') db_name,
Sys_context ('userenv', 'host') host,
Sys_context ('userenv', 'OS _ user') OS _user,
Sys_context ('userenv', 'external _ name') external_name,
Sys_context ('userenv', 'IP _ address') ip_address,
Sys_context ('userenv', 'network _ Protocol') network_protocol,
Sys_context ('userenv', 'bg _ job_id ') bg_job_id,
Sys_context ('userenv', 'fg _ job_id ') fg_job_id,
Sys_context ('userenv', 'authentication _ type') authentication_type,
Sys_context ('userenv', 'authentication _ data') authentication_data
From dual;

-- View the rollback segment name and size
Select a. Owner | '.' | A. segment_name roll_name,
A. tablespace_name tablespace,
To_char (A. initial_extent) | '/' | to_char (A. next_extent) in_extents,
To_char (A. min_extents) | '/' | to_char (A. max_extents) m_extents,
A. Status status,
B. bytes,
B. extents,
D. Shrinks shrinks,
D. Wraps wraps,
D. optsize OPT
From dba_rollback_segs A, dba_segments B, V $ rollname C, V $ rollstat d
Where a. segment_name = B. segment_name
And a. segment_name = C. Name (+)
And C. USN = D. USN (+)
Order by A. segment_name;

-- Parse to excute ratio (proportion of SQL statement execution and analysis in the database)
Select round (1-A. Value/B. Value) * 100, 2) "parse to excute ratio"
From v $ sysstat A, V $ sysstat B
Where a. Statistic #= 331 and B. Statistic # = 330;

-- Parse CPU to parse elapsed (the time when the database uses the analyzed CPU and the time when the analysis completes CPU)
Select round (1-A. Value/B. Value) * 100, 2) "parse CPU to parse elapsed"
From v $ sysstat A, V $ sysstat B
Where a. Statistic #= 328 and B. Statistic # = 329;

-- Non-Parse CPU (the CPU waits for other resources during non-Analysis)
Select round (1-A. Value/B. Value) * 100, 2) "non-Parse CPU"
From v $ sysstat A, V $ sysstat B
Where a. Statistic #= 328 and B. Statistic # = 12;

-- Rollback segment (waiting for the header ratio of rollback segment, the smaller the ratio, the better)
Select waits, gets, round (waits/gets * 100,2) from V $ rollstat A, V $ rollname B where a. USN = B. USN

-- Description of table fields
Select * From user_col_comments
Data Dictionary:
Select * From dict order by table_name;
Lock and Resource Information:
Select * from V $ lock; DDL lock not included
Database character set:
Select * From SYS. Props $ where name = 'nls _ characterset ';
Inin. ora parameters:
Select name, value from V $ Parameter order by name;
SQL sharing pool:
Select SQL _text from V $ sqlarea;
Database:
Select * from V $ Database
Control file:
Select * from V $ controlfile;
Redo log file information:
Select * from V $ logfile;
Log File Information from the control file:
Select * from V $ log;
Data File Information from the control file:
Select * from V $ datafile;
Current Value of the NLS parameter:
Select * from V $ nls_parameters;
Oracle version:
Select * from V $ version;
Description of the background process:
Select * from V $ bgprocess;
View version information:
Select * From product_component_version;