This discussion is archived
1 Reply Latest reply: Nov 26, 2012 1:53 AM by 975952 RSS

ghijk

975952 Newbie
Currently Being Moderated
============================================
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points