Oracle性能优化

命令cmd进入，输入 sqlplus sys/pass as sysdba 进入界面输入相应的调优语句。

具体如下:

1.查看当前进程数和配置数：

selectvalue from v$parameter where name = 'processes';

selectcount(*) from v$process;

showparameter session;

2.查询数据库启动以来最大的并发数量：

select * from v$license;

3.修改processes和sessions值：

alter system set processes=1000 scope=spfile;

alter system set sessions=1105 scope=spfile;

4.修改SGA和DB_CACHE_SIZE：

alter system set sga_max_size=1024M scope=spfile;

alter system set sga_target=1024M scope=spfile;

--alter system set sga_max_size=1600M scope=spfile;

--alter system set sga_target=1600M scope=spfile;

--alter system set db_cache_size=400M scope=spfile;

--alter system set shared_pool_size=100M scope=spfile;

5.查询命中率：

select 1 - ((physical.value - direct.value -lobs.value) / logical.value)

    "Buffer Cache Hit Ratio"

  from v$sysstatphysical,v$sysstat direct,v$sysstat lobs,v$sysstat      logical

  wherephysical.name = 'physical reads'

  anddirect.name='physical reads direct'

  andlobs.name='physical reads direct (lob)'

  andlogical.name='session logical reads';

 

SQL> shutdown immediate

SQL> startup

6.查看当前哪些用户正在使用：

SELECT osuser,a.username,cpu_time/executions/1000000||'s',       sql_fulltext,machine  from v$session a, v$sqlarea b

where a.sql_address =b.address order bycpu_time/executions desc;

7.定位引起oracle死锁的sql：

select sql_text from v$sql where hash_value in

(select sql_hash_value from v$session where sid in

(select session_id from v$locked_object));

8.下面的SQL查询可以用于确定锁住数据库对象的锁：

select  c.owner,c.object_name, c.object_type, b.sid, b.serial#,

b.status, b.osuser, b.machine  from v$locked_object a ,

v$session b, dba_objects c  where b.sid = a.session_id  and

 a.object_id =c.object_id;

9.显示哪些会话被锁住：

COLUMN o_name format a10

COLUMN lock_type format a20

COLUMN object_name format a15

SELECT RPAD (oracle_username, 10) o_name, session_idSID,

        DECODE(locked_mode,

               0, 'None',

               1, 'Null',

               2, 'Row share',

               3, 'Row Execlusive',

               4, 'Share',

               5, 'Share Row Exclusive',

               6, 'Exclusive'

               )lock_type,

       object_name, xidusn, xidslot, xidsqn

   FROMv$locked_object, all_objects

WHERE v$locked_object.object_id =all_objects.object_id;

10.显示所有的TM和TX锁：

SELECT SID, TYPE, id1, id2,

        DECODE(lmode,

               0, 'None',

               1, 'Null',

               2, 'Row share',

               3, 'Row Exclusive',

               4, 'Share',

               5, 'Share Row Exclusive',

               6, 'Exclusive'

               )lock_type,

        request,ctime, BLOCK

   FROM v$lock

WHERE TYPE IN ('TX', 'TM');

11.查询当前执行语句 ：

SELECT b.sid oracleID , b.serial# , spid 操作系统ID, paddr , sql_text当前SQL FROM v$process a, v$session b , v$sqlarea c      WHERE a. addr = b . paddr AND spid = '6341'AND b .      sql_hash_value = c .hash_value;

12.查询表空闲空间：

select tablespace_name,sum(bytes)/1024/1024 fromdba_free_space    group bytablespace_name;

13.查看所有表空间大小：

 selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files      groupby tablespace_name;

14.查看Oracle最大游标数：

　C:\Documentsand Settings\Administrator>sqlplus / as sysdba

　　SQL*Plus:Release 9.2.0.1.0 - Production on 星期四 11月 5                  09:08:04 2012

　　Copyright(c) 1982, 2002, Oracle Corporation. All rights                          reserved.

　　连接到:

　　Oracle9iEnterprise Edition Release 9.2.0.1.0 - Production

　　With thePartitioning, OLAP and Oracle Data Mining options

　　JServerRelease 9.2.0.1.0 - Production

　　SQL>show parameter open_cursors;

　　NAME           TYPE        VALUE

　　----------------------------------------------- -------------------

　　open_cursors       integer         300

15.查看当前打开的游标数目：

select count(*) from v$open_cursor;

16.修改Oracle最大游标数：

　　SQL>alter system set open_cursors=2000 scope=both;

　　系统已更改。

　　SQL>show parameter open_cursors;

　　NAME          TYPE        VALUE

　　----------------------------------------------- --------------------

　　open_cursors      integer       1000

17.修改SGA出现问题：

1\set ORACLE_SID=ORCL;

2\用户SQLPLUS 登陆

SQL> startup pfile='/home/oracle/initORCL.ora';

SQL> create pfile='/home/oracle/initORCL.ora' fromspfile;

File created.

还原

startup pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/init.ora';

--要动态修改一定要用spfile启动

SQL>create spfile from pfile;

SQL>shutdown immediate;

18.Oracle 11G优化：

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 1024M    SCOPE = SPFILE;

SQL>ALTER SYSTEM SET MEMORY_TARGET = 1024M SCOPE = SPFILE;

SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;

SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

-----

show parameter  sga;

select pool,sum(bytes)/1024/1024  from v$sgastat  group by pool;

-----------------------

show parameter audit_trail;--oracle 是否开启awt(审计)

19.查询表空间大小及使用情况：

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC;

20.查询自增长情况：

SELECT T.TABLESPACE_NAME,D.FILE_NAME,

D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;

21.查询标记为KILLED会话：

select sid, serial#, paddr, username from v$session;

22.修改Oracleprocesses最大连接数：

alter system set processes =1500 scope = spfile;(需重启服务)

--查看当前最大连接数

Select value from v$parameter where name=’processes’;

--查看当前连接数

Select Count(*) from v$session ;

--查看当前最大连接数

Select Count(*) from v$session where status=’ACTIVE’;