Common Oracle dba scripts

The following are common scripts for Oracle DBAs.

1. Check data buffer and shared zone hit rate

1. Declare
2. Reads_value number (15 );
3. Direct_value number (15 );
4. Lob_value number (15 );
5. Consical_value number (15 );
6. Db_value number (15 );
7. X number;
8. Y number;
9. Z number;
10. Begin
11. Select value into reads_value from v $ sysstat where name ='Physical reads';
12. Select value into direct_value from v $ sysstat where name ='Physical reads direct';
13. Select value into lob_value from v $ sysstat where name ='Physical reads direct (lob )';
14. Select value into consical_value from v $ sysstat where name ='Consistent gets';
15. Select value into db_value from v $ sysstat where name ='Db block gets';
16. X: = direct_value + lob_value;
18. Y: = 1-(reads_value-x)/(consical_value + db_value-x );
19. Dbms_output.put_line ('Data buffer hit rate is :'| Y );
20. If(Y & gt; = 0.9) then
21. Dbms_output.put_line ('The data cache size with a hit rate greater than 0.9 is more appropriate');
22. Else
23. Dbms_output.put_line ('Data cache hit rate is less than 0.9. debug db_cache_size');
24. EndIf;
25. Select sum (pinhits-reloads)/sum (pins) into z from v $ librarycache;
26. Dbms_output.put_line ('Shared area hit rate is :'| Z );
27. If(Z >=0.9) then
28. Dbms_output.put_line ('The shared area hit rate is greater than 0.9');
29. Else
30. Dbms_output.put_line ('Shared area hit rate is less than 0.9. debug db_cache_size');
31. EndIf;
32. End;

2. Check the usage of the table space.

1. Select a. tablespace_name, total, free, (total-free)/total used from
2. (Select tablespace_name, sum (bytes)/1024/1024 total from dba_data_files group by tablespace_name),
3. (Select tablespace_name, sum (bytes)/1024/1024 free from dba_free_space group by tablespace_name) B
4. Where a. tablespace_name = B. tablespace_name;

3. Check and table Space Generation statements

1. -- Table Space Generation statement, run in SQL form
2. Select dbms_metadata.get_ddl ('Tablespace', Tablespace_name) from dba_tablespaces;
3. -- Table Generation statement
4. Select dbms_metadata.get_ddl ('Table', Upper ('& TABLE_NAME'), Upper ('& USER_NAME') From dual;

4. Find out the statements with high cpu consumption and io IN THE SYSTEM

1. Select SQL _TEXT, SQL _FULLTEXT from v $ SQL a where a. HASH_VALUE
2. In(Select hash_value from v $ sqlarea where buffer_gets> 1000000
3. Or disk_reads> 1000000/* Order by buffer_gets + disk_reads desc */);

5. Find the slow SQL statement in the current system

① Use the topas command in aix to find the pid with a relatively large cpu system

② Use the SQL statement method to find specific statements

1. -- Spid is the process number
2. Select SQL _TEXT, SQL _FULLTEXT, FIRST_LOAD_TIME from v $ SQL where ADDRESS = (select SQL _ADDRESS from v $ session where
3. PADDR = (select ADDR from v $ process where spid = & spid ));

6. Find More tablespace fragments

1. Select tablespace_name,
2. Count (*) chunks,
3. Max (bytes/1024/1024) max_chunk
4. From dba_free_space
5. Group by tablespace_name;

Note: the higher the score, the larger the tablespace fragmentation.