ORACLE DBA

Summary  Show hit ratios, consistent-gets, db-block-gets, physical-reads for the sessions. SELECT se.username || '(' || se.SID || ')' "User(sid)", SUM(DECODE(NAME, 'consistent gets',VALUE, 0)) "Consis Gets", SUM(DECODE(NAME, 'db block gets',VALUE, 0)) "DB Blk Gets", SUM(DECODE(NAME, 'physical reads',VALUE, 0)) "Phys Reads", (SUM(DECODE(NAME, 'consistent gets',VALUE, 0)) + SUM(DECODE(NAME, 'db block gets',VALUE, 0)) - SUM(DECODE(NAME, 'physical reads',VALUE, 0)))/(SUM(DECODE(NAME, 'consistent gets',VALUE, 0)) + SUM(DECODE(NAME, 'db block gets',VALUE, 0))) * 100 "Hit Ratio" FROM v$sesstat ss, v$statname sn, v$session se WHERE ss.SID = se.SID AND sn.statistic# = ss.statistic# AND VALUE != 0 AND sn.NAME IN ('db block gets', 'consistent gets', 'physical read...

Summary  The old method (for my opinion) is calculating ratios and averages about system memmory allocation and I/O. Here is an example how to get this ratios.  Connect as sys and create the table create table tune ( tune_date date, buffer_hit_ratio number, dict_hit_ratio number, libr_hit_ratio number, sort_hit_ratio number, shared_pool_free_mem number, log_buff_wait number, nb_session number, nb_disk_read number); We will create a procedure called TUNING which calculates * buffer hit ratio * dictionary cache hit ratio * library cache hit ratio * sort_area_size hit ratio * shared pool free size remaining * number of process waits for space in the redo log buffer * number of sessions * number of heavy disk reads CREATE OR REPLACE PROCEDURE TUNING IS l_buff_hit number; l_dict_hit number; l_libr_hit number; l_memo_sort numb...

Summary  You can get the same result with count(*) with the query: SELECT NVL(MAX(ROWNUM),0)  FROM RANDOM_VALUES; It will count rows to table RANDOM_VALUES like SELECT COUNT(*) FROM RANDOM_VALUES;

Summary  First create a table(RANDOM_VALUES) with 100 records of random values. CREATE TABLE RANDOM_VALUES (ID NUMBER(3), col1 VARCHAR2(30)); BEGIN FOR i IN 1..100 LOOP INSERT INTO RANDOM_VALUES VALUES (i, ROUND(Dbms_Random.VALUE*100000)); END LOOP; COMMIT; END; / To select the even records from table execute SELECT * FROM (SELECT ROWNUM num, ID, col1 FROM RANDOM_VALUES) WHERE MOD(num,2) = 0; To select the odd records from table execute SELECT * FROM (SELECT ROWNUM num, ID, col1 FROM RANDOM_VALUES) WHERE MOD(num,2) = 1;

How to read AWR reports The output of the AWR report contains a wealth of information that you can use to tune your database. The output of the AWR report can be divided into the following sections: Report Header This section is self explanatory which provides database name, id, instance if RAC , platform information and snap interval. (database workload time duration in review). This report is for instance number 2 of my RAC environment. So if you need to the analysis on RAC environment, you need to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be. DB Name DB Id Instance Inst num Startup Time Release RAC TestRAC 3626203793 TestRac2 2 17-Aug-11 19:08 11.1.0.6.0 YES Host Name Platform CPUs Cores Sockets Memory (GB) ...