ORACLE 日常操作【不断更新】

最新推荐文章于 2025-08-15 20:46:11 发布

launch_225

最新推荐文章于 2025-08-15 20:46:11 发布

阅读量517

点赞数

CC 4.0 BY-SA版权

文章标签： oracle

本文链接：https://blog.youkuaiyun.com/launch_225/article/details/7908528

本文深入探讨了数据库管理中的关键操作，包括UNDO表空间重建、执行计划查看、10046跟踪、表分析、sequence缓存修改、dbms_job包体使用等。同时，介绍了查询UNDO使用情况、对象ITLwaits事件统计、PGA优化策略及对象资源使用情况。通过实例演示了如何高效地管理和优化数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.重建UNDO表空间

undo_management='MANUAL' ->CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/U01/UNDOTBS2.DBF' SIZE 500M;

->undo_management='AUTO' undo_tablespace='UNDOTBS1' ->restart

2.查看执行计划

(1).explain plan for

sql;

select * from table(dbms_xplan.display);

(2).select * from table(dbms_xplan.display_cursor('5udc2pp1r6ua7',null,'BASIC')); --利用存储包

(3).select * from table(dbms_xplan.display_awr('6ujc0p8bzm409')); --利用AWR脚本

3.10046跟踪

oradebug setmypid
oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
oradebug TRACEFILE_NAME
执行sql
oradebug EVENT 10046 trace name context off

4.分析表 -- exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

5.修改sequence的cache值:alter sequence seq_share cache 1000;

6.dbms_job(包体)

7.查询UNDO使用情况以及具体SQL占用的UNDO块

SELECT DISTINCT tablespace_name,STATUS "状态",
COUNT(*) "EXTENT数量",
SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS,tablespace_name
order by 1,2;

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

select s.username, u.name, s.sql_id, t.used_ublk, sq.sql_text
from v$transaction t, v$rollstat r, v$rollname u, v$session s, v$sql sq
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
and sq.sql_id = nvl(s.sql_id, s.PREV_SQL_ID)
order by s.username;

8.停止JOB

begin
DBMS_JOB.BROKEN (6718,true);
end;

commit;

9.查询SHARE POOL字池的空闲空间

select subpool,name,sum(bytes),round(sum(bytes)/1048576,2)mb
from (select 'shared pool('||decode(to_char(ksmdsidx),'0','0-Unused',ksmdsidx)||'):' subpool,ksmssnam name,
ksmsslen bytes
from x$ksmss where ksmsslen>0
and lower(ksmssnam) like lower('%free memory%'))
group by subpool,name order by subpool asc,sum(bytes) desc

10.查询对象ITL waits事件统计排名

select t.owner,t.object_name,t.object_type,statistic_name,t.value value
from v$segment_statistics t
where t.statistic_name='ITL waits'
AND t.value>10
order by value

11.PGA优化使用目标

workarea execution-optimal >=90%

workarea execution-multipass=0%

select name,value,100*(value/decode((select sum(value) from v$sysstat
where name like 'workarea executions%'),0,null,(select sum(value) from v$sysstat
where name like 'workarea executions%'))) pct
from v$sysstat where name like 'workarea executions%'