1. Find the most ordered SQL

SQL>
SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS FROM V$SQLAREA ORDER BY SORTS DESC;

2. Find the SQL with the most reads and writes on the disk

SQL>
SELECT * FROM (SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" 
FROM v$sql WHERE executions>0 
and is_obsolete='N' ORDER BY 4 desc) WHERE ROWNUM<11 ;

3. Find the most workloaded SQL (which is also sorted by disk read-write, in fact)

SQL>
select substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text 
from(select address,disk_reads,executions,pct,rank() 
over (order by disk_reads desc) ranking 
from (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct 
from sys.v_$sql 
where command_type != 47) 
where disk_reads > 50 * executions) s,sys.v_$sqltext p 
where s.ranking <= 5 and p.address = s.address order by 1, s.address, p.piece;

4. Find inefficient SQL statements

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 Order by 4 desc;

5. View sql running for corresponding connection based on sid

SQL>
select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>'||address sql_address,'N'status 
From v$sqlarea 
Where address=(select sql_address from v$session where sid=&sid);

6. View datafile data file usage (sys user execution)

SQL>
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 

7. View database tablespace usage
Statement one:

SQL>
SELECT a.tablespace_name "Tablespace name", 
total "Table space size", 
free "Table space remaining size", 
(total - free) "Table space usage size", 
total / (1024 * 1024 * 1024) "Table space size(G)", 
free / (1024 * 1024 * 1024) "Table space remaining size(G)", 
(total - free) / (1024 * 1024 * 1024) "Table space usage size(G)", 
round((total - free) / total, 4) * 100 "Usage %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

Statement two:

SQL>
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 

8. View the name and size of tablespace physical files

SQL>
SELECT tablespace_name,
 file_id,
 file_name,
 round(bytes / (1024 * 1024 * 1024), 0) total_space_GB
 FROM dba_data_files
 ORDER BY tablespace_name;

9. View the date the database was created and how it was archived

SQL>
 SELECT created, log_mode, log_mode FROM v$database;

10. Query whether to turn on archiving mode

 SQL>
 select name,log_mode,open_mode from v$database;

LOG_MODE=ARCHIVELOG if in Archive Mode
LOG_MODE=NOARCHIVELOG if not in Archive Mode
11. See if the tablespace expands automatically

SQL>
select file_name,autoextensible,increment_by from dba_data_files;

12. Maximum Query Table Space Extension Size (Note: When the table space has Auto-Expansion turned on, the table space will continue to expand to the maximum size supported by the operating system)

SQL>
select tablespace_name,maxblocks*8 from dba_data_files;

13. View the top 10 SQL statements that take the most time

SQL>
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() 
over(order by v.elapsed_time desc) elapsed_rank 
from v$sql v) a where elapsed_rank <= 10;

14. Viewing the top 10 SQL statements consuming the most CPU time

SQL>
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() 
over(order by v.cpu_time desc) elapsed_rank from v$sql v) a 
where elapsed_rank <= 10;

15. View the top 10 SQL statements consuming the most disk reads

SQL>
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() over(order by v.disk_reads desc) elapsed_rank 
from v$sql v) a 
where elapsed_rank <= 10;

16. List sql whose cpu_time occupies top 10

SQL>
select cpu_time,sql_text 
from (select sql_text,cpu_time,rank() over (order by cpu_time desc) exec_rank from v$sql) 
where exec_rank <=10;

17. top 10 with the most executions

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;

18. Encountered excessive cpu usage, as shown by high%usr, or oracle server process is the process with the most cpu usage in top or topas. Then according to pid, the corresponding sql_text of the PID can be found

SQL>
select se.username,se.machine,sq.cpu_time,sq.sql_text 
from v$process p,v$session se,v$sqlarea sq 
where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.addr='&pid';

19. How to query Oracle encoding format

SQL>
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

20. Query oracle client encoding

SQL>
select * from nls_instance_parameters where parameter='NLS_LANGUAGE';

21. Buffer hit ratio: better than 98%

SQL>
select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0)) +sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio" 
from v$sysstat;

22. Data dictionary cache hit ratio: greater than 98% is the best

SQL>
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

23. Library Cache Hit Ratio: More than 98% is the best

SQL>
select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

24. When the ratio of idle data buffers is higher than 25%, the data buffers are set too large, which may waste resources.

SQL>
select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK STATUS",count(*) 
from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);

25. The top 10 statements that waste most memory account for the proportion of all statements (less than 5% is optimal)

SQL>
select sum(pct_bufgets) "Percent" 
from (select rank() over ( order by buffer_gets desc ) as rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets 
from v$sqlarea ) where rank_bufgets < 11;

26. Adjust the main statements that abuse disk read operations

SQL>
select disk_reads, substr(sql_text,1,4000) from v$sqlarea  order by disk_reads desc;