作者: Maclean Liu, post on January 8th, 2012 大表中海量历史数据的更新与删除一直是令DBA非常头痛的事情，在表已经分区的前提下我们还可以利用并行或者truncate parition等手段来为UPDATE或者DELETE提速， 但是如果对象是普通的非分区对表(non-partitioned heap table)的话，似乎就没有太好的加速方法了， nologging或parallel 对非分区表都没有效果。
之前我也有介绍过一个利用rowid将非分区表分割成指定数量个区间块的方法，见<Script:partition table into rowid extent chunks>；利用该脚本可以获取到这些分割后的区间块的起始rowid和结尾rowid，之后利用between start_rowid and end_rowid的条件构造多条DML语句， 因为这些DML语句所更新的数据都是在互不相关的区间内的，所以可以在多个终端内并行地运行这些DML语句，而不会造成锁的争用或者Oracle并行执行协调(Parallel Execution coordinator ) 所带来的一些开销。
为了加深理解，我们来实践一下<Script:partition table into rowid extent chunks>中提到的方法：
SQL> select * from v$version; BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi PL/SQL Release 10.2.0.1.0 – Production CORE 10.2.0.1.0 Production TNS . . . → Read More: 利用rowid分块实现非分区表的并行update与delete
作者: Maclean Liu, post on January 3rd, 2012 首先我们要知道这几个结构， 那就是
create table obj$ /* object table */ ( obj# number not null, /* object number */ dataobj# number, /* data layer object number */ owner# number not null, /* owner user number */ name varchar2(“M_IDEN”) not null, /* object name */ namespace number not null, /* namespace of object (see KQD.H): . . . → Read More: Script:verify Oracle Object timestamp discrepancy
作者: Maclean Liu, post on December 31st, 2011 
以下脚本可以用于收集SQL调优的相关信息，包括统计信息、优化器参数等。
When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. Health-checks are performed over:
CBO Statistics for schema objects accessed by the one SQL statement being analyzed CBO Parameters CBO System Statistics CBO Data Dictionary Statistics CBO Fixed-objects Statistics . . . → Read More: Script:SQL调优健康检查脚本
作者: Maclean Liu, post on December 26th, 2011 以下脚本可以用于列出本会话(local session)的sid、pid、spid、trace 路径等有用信息； 因为不依赖于dbms_session等包体，所以仅需要select_catalog_role的角色即可执行，更为灵活。
set serveroutput on set linesize 200 pagesize 1400 set time on declare l_audsid number; l_sid number; l_serial number; l_module varchar2(128); l_pid number; l_spid number; l_trace varchar2(2000); l_user varchar2(128); begin DBMS_APPLICATION_INFO.SET_MODULE(module_name => ‘Maclean Liu OppO’, action_name => ‘ACTIVE’); select . . . → Read More: Script:列出本会话的细节信息
作者: Maclean Liu, post on November 30th, 2011 虽然我们也可以通过dbv(db file verify)工具做到对单个数据文件的坏块检测，但是直接使用RMAN的”backup validate check logical database;”结合V$DATABASE_BLOCK_CORRUPTION视图要方便地多。
Script:
1) $ rman target / nocatalog 2) RMAN> run { allocate channel d1 type disk; allocate channel d2 type disk; allocate channel d3 type disk; allocate channel d4 type disk; backup validate check logical database; } 3) select * from V$DATABASE_BLOCK_CORRUPTION ; REM www.oracledatabase12g.com & www.askmaclean.com 4) . . . → Read More: 利用RMAN检测数据库坏块的脚本
作者: Maclean Liu, post on November 6th, 2011 以下脚本可以用于收集 11gR2中 RAC DRM 动态资源管理 特性的诊断信息:
REM for 11.2 only REM written by Maclean.Liu set linesize 140 pagesize 1400 select DRMS, AVG_DRM_TIME, QUIESCE_T,FRZ_T,CLEANUP_T,REPLAY_T,FIXWRITE_T,SYNC_T from X$KJDRMAFNSTATS / select * from GV$DYNAMIC_REMASTER_STATS / select object, node, sopens, xopens, xfers from x$object_policy_statistics — where object=&object_id / select data_object_id, current_master, previous_master, remaster_cnt from gv$gcspfmaster_info / select * from gv$policy_history . . . → Read More: Script: 收集RAC DRM 诊断信息
作者: Maclean Liu, post on October 27th, 2011 以下SQL脚本可以用于列出数据库最近1分钟的性能度量信息(performance metric):
set linesize 80 pagesize 1400 SELECT /*+ ORDERED USE_MERGE(m) */ TO_CHAR(FROM_TZ(CAST(m.end_time AS TIMESTAMP), TO_CHAR(systimestamp, ‘tzr’)) AT TIME ZONE sessiontimezone, ‘YYYY-MM-DD HH24:MI:SS’), SUM(CASE WHEN a.internal_metric_name = ‘transactions_ps’ THEN m.value ELSE 0 END) transactions_ps, SUM(CASE WHEN a.internal_metric_name = ‘physreads_ps’ THEN m.value ELSE 0 END) physreads_ps, SUM(CASE WHEN a.internal_metric_name = ‘physreads_pt’ THEN m.value ELSE 0 END) . . . → Read More: Script:数据库最近的性能度量
作者: Maclean Liu, post on October 26th, 2011 以下脚本可以用于收集数据库中用户的角色和表空间等信息(user_role_tbs.sql):
SET pagesize 50 linesize 115 REM COLUMN username format a10 heading User COLUMN default_tablespace format a12 heading Default COLUMN temporary_tablespace format a12 heading Temporary COLUMN granted_role format a25 heading Roles COLUMN default_role format a10 heading Default? COLUMN admin_option format a7 heading Admin? COLUMN profile format a12 heading Profile REM BREAK on username skip . . . → Read More: Script:收集数据库中用户的角色和表空间等信息
|
|
最新评论