Oracle Tips

最新推荐文章于 2024-09-15 21:58:44 发布

fuyuncat

最新推荐文章于 2024-09-15 21:58:44 发布

阅读量1.8k

点赞数

分类专栏： Oracle专栏文章标签： oracle session sql semaphore statistics buffer

本文链接：https://blog.csdn.net/fuyuncat/article/details/322248

版权

Oracle专栏专栏收录该内容

4 篇文章 0 订阅

订阅专栏

查看哪些SQL语句消耗资源：
select * from
(select SQL_TEXT,EXECUTIONS,DISK_READS,BUFFER_GETS from v$sqlarea order by BUFFER_GETS desc)
where rownum <= 10;

查看表空间占用率：
SQL> col tablespace_name format a15
SQL> select a.tablespace_name,    a.bytes/1024/1024 totalbytes, sum(b.bytes)/1024/1024 freebytes
,
round(100-sum(nvl(b.bytes,0))/(a.bytes)*100,2)||'%' used_percent
from   dba_data_files a, dba_free_space b
where a.file_id = b.file_id
group by a.tablespace_name, a.bytes
order by a.tablespace_name;   2    3    4    5    6

查看锁：
SELECT DECODE(request,0,'锁持有者 ','等待者') as Type,sid as SessionID,
         id1, id2, lmode, request, type
    FROM V$LOCK
   WHERE (id1, id2, type) IN
             (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
   ORDER BY id1, request

HP系统内核参数意义：
shmmax
　　含义：这个设置并不决定究竟Oracle数据库或者操作系统使用多少物理内存，只决定了最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。
　　设置方法：0.5*物理内存
　　例子：Set shmsys:shminfo_shmmax=10485760
　　shmmin
　　含义：共享内存的最小大小。
　　设置方法：一般都设置成为1。
　　例子：Set shmsys:shminfo_shmmin=1：
　　shmmni
　　含义：系统中共享内存段的最大个数。
　　例子：Set shmsys:shminfo_shmmni=100
　　shmseg
　　含义：每个用户进程可以使用的最多的共享内存段的数目。
　　例子：Set shmsys:shminfo_shmseg=20：
　　semmni
　　含义：系统中semaphore identifierer的最大个数。
　　设置方法：把这个变量的值设置为这个系统上的所有Oracle的实例的init.ora中的最大的那个processes的那个值加10。
　　例子：Set semsys:seminfo_semmni=100
　　semmns
　　含义：系统中emaphores的最大个数。
　　设置方法：这个值可以通过以下方式计算得到：各个Oracle实例的initSID.ora里边的processes的值的总和（除去最大的Processes参数）＋最大的那个Processes×2＋10×Oracle实例的个数。
　　例子：Set semsys:seminfo_semmns=200
　　semmsl:
　　含义：一个set中semaphore的最大个数。
　　设置方法：设置成为10＋所有Oracle实例的InitSID.ora中最大的Processes的值。
　　例子：Set semsys:seminfo_semmsl=-200

性能测试:
select SQL_TEXT,DISK_READS,executions,buffer_gets from v$sqlarea order by disk_reads desc
select * from v$nls_parameters
select * from v$resource_limit
select * from v$parameter

可同时获得使用率（PCT USED)和碎片情况(FSFI)
col Tablespace format a24
select df.tablespace_name "Tablespace",
       df.bytes/(1024*1024) "Total Size",
       sum(fs.bytes)/(1024*1024) "Free Size",
       round(sum(fs.bytes)*100/df.bytes) "Pct Free",
       round((df.bytes-sum(fs.bytes))*100/df.bytes) "Pct Used",
    ROUND(100*SQRT(MAX(fs.bytes)/SUM(fs.bytes))*
  (1/SQRT(SQRT(COUNT(fs.bytes)))) ,2) FSFI
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes
     from dba_data_files
      group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes

oracle查找锁的标准文档
$ORACLE_HOME/rdbms/admin/utllockt.sql

查找运行作业的进程ID
select b.spid from v$session a , v$process b, dba_jobs_running c where a.sid=c.SID and a.PADDR= b.ADDR
select b.spid from v$session a , v$process b, dba_jobs_running c where a.sid=c.SID and a.PADDR= b.ADDR and c.job =??

select a.sid from v$session a , v$process b where b.spid = 25924 and a.PADDR= b.ADDR

col sql_text format a50;
select sql_text from v$sqltext, v$session
where v$sqltext.hash_value = v$session.prev_hash_value
and v$sqltext.address = v$session.sql_address
and v$session.sid = (select a.sid from v$session a , v$process b where b.spid = 25924 and a.PADDR= b.ADDR)

获取长事务对象:
select target,count(*)
from v$session_longops
group by target order by count(*) desc;

获取对象占用空间
select owner,segment_name,bytes from dba_segments order by bytes desc

导出脚本
col text format a500
set heading off
spool test.out
select text from user_source;
spool off

--查看所有会话的信息，包括会话ID、用户名、状态、客户端机器名、运行的程序以及登陆时间
prompt show info of session
col username format a10;
col machine format a10;
col terminal format a10;
col program format a10;
select sid,serial#,username,status,machine,terminal,program,logon_time from v$session;

--查看所有会话的锁
--一般情况下返回记录数应为0，如果有记录应该检查等待时间（字段ctime，单位秒），
--如果等待时间较长则需要检查该会话是否被其他会话的锁阻塞，字段request不为0表示该会话在等待其他会话释放锁而被阻塞，
--字段request为0表示该会话已获得锁，block不为0表示该会话阻塞了其他会话。
prompt show info of lock
select sid,type,id1,id2,lmode,request,ctime,block from v$lock where type in ('TM','TX');

--查看被锁锁住了的对象
prompt show object be locked
col "loked object" format a20;
col "os user" format a20;
col "oracle user" format a20;
col "os process id" format a20;
select b.object_name "loked object",a.session_id "session id",a.oracle_username "oracle user",
a.os_user_name "os user",a.process "os process id" from v$locked_object a,dba_objects b
where a.object_id = b.object_id;

--查询锁涉及到的SQL语句
prompt show sqltext issued by locked object
col sql_text format a50;
select sql_text from v$sqltext, v$session
where v$sqltext.hash_value = v$session.prev_hash_value
and v$sqltext.address = v$session.sql_address
and v$session.sid
in
(select distinct sid from v$lock where ctime>0 and type in ('TM','TX'));

--查看SQL语句执行情况
--查看SQL缓冲池中物理读最多的语句
prompt show disk_read info
select * from (
select sql_text, disk_reads/executions "Avg Disk_Reads", executions from V$SQLArea
where executions > 0 order by disk_reads/executions desc
)where rownum <= 20;

--查看SQL缓冲池中逻辑读最多的语句
prompt show buffer_gets info
select * from (
select sql_text, buffer_gets/executions "Avg Disk_Reads", executions from V$SQLArea t
where executions > 0 order by buffer_gets/executions desc
)where rownum <= 20;

NOTE:163424.1

获取热点块对象
select CHILD# "cCHILD", ADDR "sADDR", GETS "sGETS", MISSES "sMISSES", SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 4, 1, 2, 3;

获取热点块对象
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr='以上语句查询到得ADDR'
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;

获取热点块对象
select /*+ use_hash(bh) */
de.owner ||'.'|| de.segment_name segment_name,
de.segment_type segment_type,
de.extent_id extent#,
bh.dbablk - de.block_id + 1 block#,
bh.tch
from
sys.x$bh bh,
sys.dba_extents de
where
de.file_id = bh.file# and
bh.dbablk between de.block_id and de.block_id + de.blocks -1
and bh.tch > 10
order by bh.tch

获取热点块对象
select /*+ ordered */
de.owner ||'.'|| de.segment_name segment_name,
de.segment_type segment_type,
de.extent_id extent#,
bh.dbablk - de.block_id + 1 block#,
bh.lru_flag,
bh.tch,
lc.child#
from
(select max(sleeps) maxsleeps
from v$latch_children
where name='cache buffers chains'
) max_sleeps,
v$latch_children ls
sys.x$bh bh,
sys.dba_extents de
where
lc.name = 'cache buffers chains' and
lc.sleeps>(0.8*maxsleeps) and
bh.hladdr=lc.addr and
de.file_id = bh.file# and
bh.dbablk between de.block_id and de.block_id + de.blocks -1
order by bh.tch

解决oracle工具乱码：
我的NLS_LANG原来是simplified chinese_china.zhs16gbk改为AMERICAN_AMERICA.ZHS16GBK就好了，在注册表中应该修改home0或homeN中NLS_LANG，oracle下的NLS_LANG没有用的。

分页的实现：
select * from ( select a.*, rownum r from t_userinfo a where rownum <= 6000 order by registertime ) where r >=3000

查看user trace文件
SELECT    d.VALUE
       || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
       || p.spid
       || '.trc' trace_file_name
FROM (SELECT p.spid
          FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM SYS.v$thread t, SYS.v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (SELECT VALUE
          FROM SYS.v$parameter
         WHERE NAME = 'user_dump_dest') d
/

跟踪操作（以实体化视图刷新为例）
conn / as sysdba
@ORACLE_HOME/rdbms/admin/dbmssupp.sql
GRANT execute ON dbms_support TO hw;
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

conn hw/hw
exec dbms_support.start_trace(waits=>TRUE,binds=>TRUE);
exec dbms_refresh.refresh('"HW"."G101"');
exec dbms_support.stop_trace;

tkprof $ORACLE_BASE/admin/<SID>/udump/*****.trc

跟踪其他进程代码
dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)

dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)

dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => false)