1 Reply Latest reply: Nov 26, 2012 3:53 AM by 975952 RSS

    ghijk

    975952
      ============================================
      Table analyzing:
      =====================
      set time on

      analyze table tm_nmc_online_exp estimate statistics sample 20 percent;
      exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HRMSMAIN0904', TABNAME=>'DRAFT_PAYBILL_HISTORY',PARTNAME => 'PART_MAR2008',ESTIMATE_PERCENT => 15, GRANULARITY => 'DEFAULT' ,CASCADE => TRUE );
      exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HRMSMAIN0904', TABNAME=>'DRAFT_PAYBILL_HISTORY',PARTNAME => 'PART_JAN2007',ESTIMATE_PERCENT => 15, GRANULARITY => 'ALL' ,CASCADE => TRUE );

      ===================================
      SGA Settings:
      ========================
      Total SGA should not exceed more then half of RAM Size.
      Variable Size is the Shared_pool_size
      Database Buffers is the product of DB_BLOCK_SiZE and DB_BLOCK_BUFFER
      Redo Buffer is the product of DB_BLOCK_SiZE and LOG_BUFFER

      Ideal for 512MB Ram:
      --------------------
      DB_BLOCK_SIZE-->8192 Bytes (8K)
      DB_BLOCK_BUFFER-->32768 bytes(32K)
      LOG_BUFFER-->32768 bytes(32K)
      SHARED_POOL_SIZE--> Around 200K
      ================================================

      [ first flush the buffer cache ]

      sys@xxxxxxxxx> alter system flush buffer_cache;

      System altered.

      ========================================

      select substr(sys_context('userenv','ip_address'),1,15) from dual;
      select sys_context('userenv','terminal') from dual;

      ====================================================

      Deleting duplicate records from a table

           delete from order_detail t where rowid <
           (select max(rowid) from order_detail where t.ord_no = ord_no);

      ===================================================

      netstat -an | grep "172.17.1.2" | grep "TIME_WAIT"
      netstat -an | grep "172.17.1.2" | grep "ESTABLISHED" | wc -l






      sar -r

      ps -aef | grep 'ora_'

      VNC Service:
      =================

      service vncserver status
      edit:
      ---------
      vi /etc/sysconfig/vncservers

      VNCSERVERS="1:root"
      VNCSERVERARGS[1]="-geometry 800x600"

      /etc/init.d/vncserver start

      vi /root/.vnc/xstartup

      uncomment:
      unset SESSION_MANAGER
      exec /etc/X11/xinit/xinitrc

      /etc/init.d/vncserver restart

      vncpasswd


      Grid Starting:
      ==============

      cd /u04/Oracle/Middleware/oms11g/opmn/bin
      export ORACLE_INSTANCE=/u04/Oracle/gc_inst/WebTierIH1
      ./opmnctl status

      cd /u04/Oracle/Middleware/oms11g/bin
      ./emctl status oms


      ===============================================
      compiling system objects
      ============================

      @d:\Oracle\Ora81\RDBMS\ADMIN\utlrp.sql

      ==================================================

      You can use table sizes to estimate the maximum space needed.
      You can find table sizes in the USER_SEGMENTS view of the Oracle data dictionary.
      The following query displays disk usage for all tables:

      SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';


      exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

      finding buffer length for import or export:(Metalink Note: 846397.1)
      --------------------------------------------

      select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','table_part','SCOTT')) "SQL_LENGTH" from dual;


      ====================================================


      ALTER INDEX q1_sales_by_region_locix
      REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;

      Indexing to handle NULL and NOT NULL Filtering:
      ---------------------------------------------------
      create index xxxxx on xxxxx(nvl(columnname,1));

      select count(*) from test where object_id is null;
      create index fbi_test on test(nvl(object_id,-1));
      analyze table test compute statistics;
      select count(*) from test where nvl(object_id,-1)=-1;

      Indexing on varchar2 columns where using like operators:(Domain Indexing)
      ------------------------------------------------------------
      CREATE INDEX et_bus_party_dcs_PARTY_TYPE ON ET_BUSINESS_PARTY(DCS_PARTY_TYPE) INDEXTYPE IS CTXSYS.CTXCAT;

      select count(*) from ET_BUSINESS_PARTY where CATSEARCH(dcs_party_type,'BRANCH',NULL)>0


      Indexing on large objects(LOB,FILES,DOCS) columns where using like operators:
      -----------------------------------------------------------------------------------
      CREATE INDEX et_bus_party_dcs_PARTY_TYPE ON ET_BUSINESS_PARTY(DCS_PARTY_TYPE) INDEXTYPE IS CTXSYS.CONTEXT;

      select count(*) from ET_BUSINESS_PARTY where CONTAINS(dcs_party_type,'BRANCH')>0


      ============================================
      process_memory:
      ===================

      select sid,name,value
      from v$statname n,v$sesstat s
      where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%'order by 3 asc;


      =========================
      For Tracing:
      ===============

      SQL> ALTER SESSION SET sql_trace=TRUE;
      SQL> ALTER SESSION SET sql_trace=FALSE;

      SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
      SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

      SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
      SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

      SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
      SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

      Using TKPROF:
      ================
      tkprof gek1_ora_16520.trc gek1_ora_16520.txt explain=scott/tiger sort=exeela sys=no


      $ tkprof dev1_ora_367660.trc translated.txt explain=test/test table=sys.plan_table


      Log Miner:
      =============

      sqlplus / as sysdba
      SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
      SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

      execute dbms_logmnr_d.build('dictionary.ora','/home/oracle/');
      select log_id, filename from v$logmnr_logs;
      execute dbms_logmnr.add_logfile('/archive/1_5490_679786377.dbf',dbms_logmnr.addfile);
      select log_id, filename from v$logmnr_logs;
      select low_time,high_time,low_scn,next_scn from v$logmnr_logs;
      EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
      EXECUTE DBMS_LOGMNR.END_LOGMNR();

      ====================================
      SQL*Net message high for statspack
      ====================================

      select distinct event from stats$system_event where event like 'SQL%';


      set linesize 1000;

      column mydate format a25;
      column event format a30;
      column waits format 999,999,999;
      column secs_waited format 999,999,999;
      column avg_wait_secs format 99,999;

      select to_char(snap_time,'yyyy-mm-dd HH24:MI:SS') mydate,e.event,e.total_waits - nvl(b.total_waits,0) waits,
      ((e.TIME_WAITED_MICRO - nvl(b.TIME_WAITED_MICRO,0))/100) / nvl((e.total_waits - nvl(b.total_waits,0)),.01) avg_wait_secs
      from stats$system_event b,stats$system_event e,stats$snapshot sn
      where e.snap_id = sn.snap_id
      and b.snap_id = e.snap_id-1
      and b.event = e.event
      and e.event like 'SQL*Net%'
      and e.total_waits - b.total_waits > 100
      and e.TIME_WAITED_MICRO - b.TIME_WAITED_MICRO > 100;

      =======================================
      disaling scheduler jobs:
      =========================
      select JOB,SCHEMA_USER,LAST_DATE,LAST_SEC,NEXT_DATE,NEXT_SEC,INTERVAL,BROKEN,WHAT from dba_jobs;
      select JOB,SCHEMA_USER,BROKEN,WHAT from dba_jobs;

      select OWNER,JOB_NAME,REPEAT_INTERVAL,Job_action from dba_scheduler_jobs where job_name='QUANTUM_POMS'
      select OWNER,JOB_NAME,enabled,Job_action from dba_scheduler_jobs where job_name='QUANTUM_POMS'

      set lines 1000
      col LAST_START_DATE for a35
      col NEXT_RUN_DATE for a35
      select OWNER,JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE,Job_action from dba_scheduler_jobs
      select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE,Job_action from dba_scheduler_jobs ;
      select OWNER,JOB_NAME,JOB_ACTION from dba_scheduler_jobs;

      exec dbms_scheduler.disable('SYS.EVENT_JOB');
      EXEC dbms_scheduler.DROP_JOB('SYS.INACTIVE_SESSION_CLEANUP_JOB');

      BEGIN
      DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'INACTIVE_SESSION_CLEANUP_JOB'
      ,JOB_TYPE => 'PLSQL_BLOCK'
      ,JOB_ACTION => 'begin INACTIVE_SESSION_CLEANUP; end;'
      ,START_DATE => SYSTIMESTAMP
      ,REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=05; BYMINUTE=10'
      ,END_DATE => NULL
      ,ENABLED => TRUE
      ,COMMENTS => 'scheduler Inactive session cleanup for every 2hr');
      END;
      /

      DDL Restictions:
      ------------------

      CREATE OR REPLACE TRIGGER DDL_SECURITY_VISILOGPLUS_DEV
      BEFORE ALTER OR CREATE OR DROP
      ON DATABASE
      DECLARE
      V_ALTER VARCHAR2(256) := ' is enabled for DDL Event Security, ' ||
                          CHR(13) ||
                          'YOU ARE NOT AUTHORISED TO DO STRUCTURAL CHANGES ON THIS SCHEMA.' ||
                          CHR(13) ||
                          'For any Structural Changes Raise an issue to DBA team.';
      V_CREATE VARCHAR2(256) := ' Table schema is enabled for DDL Event Security, ' ||
                          CHR(13) ||
                          'YOU ARE NOT AUTHORISED TO CREATE NEW OBJECT ON THIS SCHEMA.' ||
                          CHR(13) ||
                          'To create new object Raise an issue to DBA team.';
      BEGIN
      IF ORA_DICT_OBJ_OWNER IN ('VISILOGPLUS29DEV_261010') THEN
           IF (ORA_DICT_OBJ_TYPE IN
           ('SEQUENCE', 'LOB', 'TABLE', 'INDEX', 'TYPE' /*'VIEW',,'TRIGGER','FUNCTION','PROCEDURE', 'PACKAGE', 'PACKAGE BODY'*/
           ) AND ORA_SYSEVENT = 'CREATE') THEN
                RAISE_APPLICATION_ERROR(-20001
                               ,ORA_DICT_OBJ_OWNER || V_CREATE);
           ELSIF (ORA_DICT_OBJ_TYPE IN ('SEQUENCE', 'LOB', /*'TABLE',*/
                'INDEX', 'TYPE' /*'VIEW',,'TRIGGER','FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY'*/
                ) AND (ORA_SYSEVENT = 'ALTER')) THEN
                RAISE_APPLICATION_ERROR(-20001
                               ,ORA_DICT_OBJ_OWNER || '.' ||
                               ORA_DICT_OBJ_NAME || ' ' || V_ALTER);
           ELSIF (ORA_DICT_OBJ_TYPE IN
                ('SEQUENCE', 'LOB', 'TABLE', 'INDEX', 'TYPE' /*'VIEW',,'TRIGGER','FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY'*/
                ) AND (ORA_SYSEVENT = 'DROP')) THEN
                RAISE_APPLICATION_ERROR(-20001
                               ,ORA_DICT_OBJ_OWNER || '.' ||
                               ORA_DICT_OBJ_NAME || ' ' || V_ALTER);
           END IF;
      ELSE
           NULL;
      END IF;
      END;


      ===========================================
      sqlplus / as sysdba
      error:insufficeint privilage

      create sqlnet.ora file and add this

      SQLNET.AUTHENTICATION_SERVICES= (NTS)

      ===========================================
      scp tarfilename oracle@192.168.68.177:/data1/
      scp root@192.168.60.1:/home/oracle/COREELOG22_030310.tar.gz .


      tar -zcvf orcl_dumps_12feb10.tar.gz orcl_dump1.dmp orcl_dump2.dmp

      expdp dumpfile=VISILOG21DKDUMP191110_291110.dmp logfile=VISILOG21DKDUMP191110_291110.log directory=DATAPUMP schemas=VISILOG21DKDUMP191110 tables=emp,dept version=10.2.0.4
      impdp dumpfile=VISILOG21DKDUMP191110_291110.dmp logfile=VISILOGISFTEST291110_imp.log remap_schema=VISILOG21DKDUMP191110:VISILOGISFTEST291110(fromuser:touser) directory=dpdump
      impdp dumpfile=UFMICESUAT131010.dmp logfile=UFMICESUAT131010_imp.log schemas=UFMICESUAT131010 ----- for single schema import

      db_link
      ======================================

      create database link LEIUAT20100204_CCBLEIUAT120310
      connect to CCBLEIUAT120310 identified by ccb
      using '(DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.40)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SID = black)
      )
      )';


      ==============================

      To restart ssh service or server type
      /etc/init.d/sshd restart

      ===================================

      DBA_BLOCKERS and DBA_WAITERS to discover sessions that are blocking other sessions:

      SQL> select * from dba_blockers;
      HOLDING_SESSION
      ---------------
      12


      ==============================
      CPU Usages:

      windows:
      -----------
      Select sid, serial#, process
      from v$session
      where username is not NULL;

      Linux:
      --------
      Select a.sid, a.serial#, b.spid
      from v$session a, v$process b
      where username is not NULL
      and a.paddr=b.paddr;

      SQL statement that has consumed the most CPU time,
      --------------------------------------------------------
      select fetches, executions, a.parsing_user_id, a.cpu_time, a.sql_text
      from v$sqlarea a, dba_users b
      where cpu_time = (select max(cpu_time) from v$sql)
      and a.parsing_user_id=b.user_id
      and b.username not in ('SYS','SYSTEM');

      If a transaction is consuming large amounts of rollback,
      ---------------------------------------------------------
      select a.sid, a.serial#,
             substr(b.owner,1,8) "schema",
             substr(b.object_name,1,20) "object Name ",
             substr(b.object_type,1,10) "type ",
             substr(c.segment_name,1,8) "rbs ",
             substr(d.used_urec,1,12) "# of records "
      from   v$locked_object l,
             dba_objects b,
             dba_rollback_segs c,
             v$transaction d,
             v$session a
      where  l.object_id = b.object_id and
             l.xidusn = c.segment_id and
             l.xidusn = d.xidusn and
             l.xidslot = d.xidslot and
             d.addr = a.taddr;

      determine what sessions are in a particular wait event
      --------------------------------------------------------------
      select sid, event, seconds_in_wait
      from v$session_wait
      where sid in (select sid from v$session where username is not NULL);

      top-N queries by resource utilization.
      -----------------------------------------
      SELECT * FROM
      (SELECT cpu_time, elapsed_time, disk_reads, buffer_gets, rows_processed, executions,
      sql_text from v$sql order by cpu_time desc)
      WHERE ROWNUM < 6;

      sessions:
      ---------------

      alter session set nls_date_format= 'dd-mon-yy hh24:mi:ss';

      select osuser, username, sid, serial#, machine, program, logon_time
      from v$session where username is not NULL;

      what the session is doing:
      ---------------------------

      select a.osuser, a.username, a.sid, a.serial#, p.spid, s.sql_text
      from v$session a, v$sqltext s, v$process p
      where a.prev_sql_addr = s.address
      and p.addr = a.paddr
      and a.username is not null
      order by a.username, a.osuser, a.sid, s.piece;

      buffer cache advisory statistics.
      ------------------------------------

      COLUMN size_for_estimate FORMAT 999,999,999,999 HEADING 'Cache Size (MB)'
      COLUMN buffers_for_estimate FORMAT 999,999,999 HEADING 'Buffers'
      COLUMN estd_physical_read_factor FORMAT 999.90 HEADING 'Estd Phys|Read Factor'
      COLUMN estd_physical_reads FORMAT 999,999,999 HEADING 'Estd Phys| Reads'
      SELECT size_for_estimate, buffers_for_estimate,
      estd_physical_read_factor, estd_physical_reads
      FROM v$db_cache_advice
      WHERE name = 'DEFAULT'
      AND block_size = (SELECT value FROM v$parameter 
      WHERE name = 'db_block_size')
      AND advice_status = 'ON';

      sizing of the buffer cache
      ----------------------------------

      SELECT name, block_size, size_for_estimate, size_factor,
      estd_physical_reads
      FROM v$db_cache_advice;

      Show a users current sql
      ----------------------------

      Select sql_text
      from v$sqlarea
      where (address, hash_value) in
      (select sql_address, sql_hash_value
      from v$session
      where username like '&username');

      List open cursors per user
      ---------------------------------

      set pages 999
      select      sess.username
      ,     sess.sid
      ,     sess.serial#
      ,     stat.value cursors
      from     v$sesstat stat
      ,     v$statname sn
      ,     v$session sess
      where     sess.username is not null
      and     sess.sid = stat.sid
      and     stat.statistic# = sn.statistic#
      and     sn.name = 'opened cursors current'
      order by value;

      (or)

      set lines 100 pages 999
      select count(hash_value) cursors
      , sid
      , user_name
      from v$open_cursor
      group by
      sid
      , user_name
      order by
      cursors;


      =====================================
      Cursor Usage Query:
      ======================

      select * from
      (select SID,COUNT(SQL_TEXT) cursorUsage,user_name
      FROM SYS.V_$open_cursor
      GROUP BY SID,user_name
      HAVING user_name=’eTrans’
      order by COUNT(SQL_TEXT) desc)
      WHERE ROWNUM < 10

      number of cursors in use.
      =============================

      SELECT * FROM (SELECT SID,COUNT(SQL_TEXT) cursorUsage,user_name FROM SYS.V_$OPEN_CURSOR GROUP BY SID,user_name HAVING user_name='USERNAME'
      ORDER BY COUNT(SQL_TEXT) DESC) WHERE ROWNUM <= 10;



      ====================================
      Query Usage Query:
      =====================

      SELECT * FROM
      (SELECT SESION.USERNAME,SQL_TEXT,ELAPSED_TIME,CPU_TIME
      FROM SYS.V_$SQLAREA SQLAREA,SYS.V_$SESSION SESION
      WHERE SESION.SQL_HASH_VALUE=SQLAREA.HASH_VALUE
      AND SESION.SQL_ADDRESS=SQLAREA.ADDRESS
      AND SESION.USERNAME=’eTrans’
      ORDER BY ELAPSED_TIME DESC)
      WHERE ROWNUM <10

      information about SQL statements that are in memory, parsed, and ready for execution at the time of execution.

      SELECT * FROM (SELECT
      ROWNUM,S.USERNAME,SQL_TEXT,ELAPSED_TIME/(1000000)
      ELAPSED_TIME_IN_SECONDS,CPU_TIME/(1000000)
      CPU_TIME_IN_SECONDS
      FROM SYS.V_$SQLAREA SQL,SYS.V_$SESSION S WHERE S.SQL_HASH_VALUE=SQL.HASH_VALUE AND S.SQL_ADDRESS=SQL.ADDRESS AND S.USERNAME='USERNAME'
      ORDER BY ELAPSED_TIME DESC)
      WHERE ROWNUM <10;

      =====================================
      Recomandations:

      ------------------
      Wait Events:
      ------------------
      Event      Avg Wait (ms)      Waits / txn
      latch free      3      20.4
      log file sync      6      0.8
      SQL*Net message from client      11      ?
      db file sequential read      2      3661.3
      db file scattered read      1      244.3
      buffer busy waits      5      13.9
      direct path read      0      13.8
      log file parallel write      6      1.7
      control file parallel write      2      1.3

      You have high latch free waits of 20.4 per transaction.
      The latch free wait occurs when the process is waiting for a latch held by another process.
      Check the later section for the specific latch waits.
      Latch free waits are usually due to SQL without bind variables, but buffer chains
      and redo generation can also cause them.


      You have excessive buffer busy waits with 13.9 per transaction.
      Buffer busy waits are most commonly caused by segment header contention
      and can be remedied by increasing the value of the
      tables & index freelists or freelist_groups parameters,
      tuning your database writer (DBWR process,
      or by using Automatic Segment Storage Management (ASSM) in the tablespace definition.
      Using super-fast SSD will also reduce buffer busy waits
      because transactions are completed many times faster.

      -------------------
      Latch Activity
      -------------------
      Latch      Get Requests      % Get Miss      % NoWait Miss
      cache buffers chains      803,593,366      0.3%      0%
      cache buffers lru chain      30,611      0.2%      0.2%
      library cache      39,161,832      0.6%      10%
      redo allocation      63,537      0%      ?%
      redo copy      0      0%      0.1%

      Custom Recommendations:
      You have high cache buffer chain latches with 803,593,366 get requests at 0.3% get miss. See MetaLink about increasing the hidden parameter dbblock_hash_buckets.


      You have a high value for cache buffer LRU chain waits with 30,611 get requests at 0.2% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.


      You have high library cache waits with 39,161,832 get requests at 0.6% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.

      ==============================================

      DB_BLOCK_BUFFERS
      The value of this parameter affects the size of the SGA, or more precisely, the size of the buffer cache. This parameter is deprecated since 9i, db_cache_size should be used instead.
      With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory (of which the buffer cache is part) as required. See also sga_target.
      DB_BLOCK_CHECKSUM
      See checking block integrity before writing to disk.
      DB_BLOCK_SIZE
      Determines the size of a database blocks.
      SGA_MAX_SIZE
      sga_max_size sets the maximum value for sga_target
      If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.
      According to metalink note 223299.1, this is one of the top parameters affecting performance.
      SGA_TARGET
      This parameter is new with Oracle 10g. It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.
      This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
      sga_target cannot be higher than sga_max_size.

      ============================================
      Library Cache Hit Ratio; Library Cache Reload Ratio

      Description: library cache used to store SQL, PLSQL and analysis of trees and their implementation of the program.
      Library cache hit ratio refers to the SQL statement sent at the library cache can be found in its rate of implementation of the program;
      library cache reload sent by means of SQL statements in the library cache had the same statement and its implementation of the program,
      but was out of the library cache, the percentage of these statements is the library cache reload ratio.

      Normal: Library Cache Hit Ratio> = 90%

      Library Cache Reload Ratio <= 1% (preferably 0)

      Optimization method: increase initialization parameters: shared_pool_size value.

      ==============================================
      Dictionary Cache Getmisses Ratio

      Note: dictionary cache used to store database objects such as tables, views, such as the definition of the structure.
      When the SQL statement used in the database object, server process to go to the dictionary cache in contrast to the definition
      of the object, when found will be read from the data file into the dictionary cache, dictionary cache getmisses ratio is not
      reflected in the Ratio .

      Normal: <15%

      Optimization method: increase initialization parameters: shared_pool_size value

      =============================================
      Rollback Segment Wait Ratio

      Description: Services at the request of rollback segment occurs when the ratio of waiting.

      Normal: <1%

      Optimization method: add a new rollback segment. All rollback segment storage parameters should be consistent.

      U.S. application for the number of rollback segment to build better?
      Divided by the maximum number of concurrent users or divided by 2 to 4.
      Oracle9i system for automatic management of rollback segment, after the library without the establishment of a new rollback segment.

      ============================================
      Sorts to Disk Ratio

      Description: When creating the index; SQL statements contained in order by, group by clause;
      SQL statement containing distinct, union, intersect, minus, etc. will be used when sort (sort),
      the sort area in the SGA of the sort in area. When the sort area is not big enough when the temporary table will be
      sorted into the document space, which will happen due to the I / O operation brought about by the reduction of processing speed.
      Sorts to disk ratio means the document into the temporary table space to sort of sort of the whole of the rate of operation.

      Normal: <5%

      Optimization method: increase sort_area_size and sort_area_retained_size initialization parameter value.
      These two parameters the default value of 65536 (bytes), should monitor the process of gradual increase in the value of
      these two parameters.

      =============================================
      Cursor Usage Ratio

      Description: This value refers to the process of monitoring the database to obtain the actual number of open cursor open_coursors
      initialization parameter value ratio.

      Normal: <95%

      Optimization Methods: open_cursors increase the value of initialization parameters.
      Timely adjust the application code cursors to turn off the effective use of memory

      =============================================
      Transaction Usage Ratio

      Description: This value refers to the process of monitoring the actual activity in the affairs of a number of
      initialization parameters and the ratio of the value of transactions.

      Normal: <95%

      Optimization method: initialization parameter to increase the value of transactions

      ===============================================
      Number of users awaiting lock

      Description: Wait for lock to reflect the occurrence of the session (session) number.

      Normal: = 0

      The solution: When the lock-waiting will have a lock can only kill the session. Dba studio instrument can be used in "routine" -> "Session" tool to do, he can command: alter system kill session 'sid, serial #'; do. How do we know the session have locked the sid and serial # values it? With the following command to obtain.

      SELECT S. SID SESSION_ID, S. SERIAL #, S. USERNAME,
      DECODE (L. LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX) ', 4,' Share ', 5,' S / Row-X (SSX) ',
      6,' Exclusive ', TO_CHAR (L. LMODE)) MODE_HELD,
      DECODE (L. REQUEST, 0,' None ' , 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S / Row-X (SSX)',
      6, 'Exclusive', TO_CHAR (L. REQUEST)) MODE_REQUESTED,O. OWNER ||'.'|| O. OBJECT_NAME | | '(' | | O. OBJECT_TYPE ||')',
      S. TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2
      FROM V $ LOCK L, SYS.DBA_OBJECTS O, V $ SESSION S
      WHERE L. SID = S. SID AND L.ID1 = O. OBJECT_ID;

      ===============================================================
      Redo Log Space Waittime

      Description: This value refers to the process of waiting for users to use the log buffer space wait time.

      Normal: = 0

      Optimization Methods: log_buffer increase the value of initialization parameters.

      =================================================
      SGA Free Space Ratio

      Description: This value shows the current SGA free space area ratio of the district accounted for SGA.

      Normal: about 5%

      Optimization method: When the value is greater than 10% when the database SGA area a part of the release of
      additional memory for other applications; when the value is less than 5% on smaller SGA area.
      At this point we have to combine the above-mentioned buffer cache, library cache, dictionary cache, redo log space project
      of view on which areas need to increase the value.