Oracle dbms_sqltune.docx - 墨天轮文档

Oracle dbms_sqltune.docx

jenk

325

119页

1次

2021-02-22

40墨值下载

如何启动



2012-11-11 19:28:01

分类：



block change tracking

记录上一次备份变化的数据块

并将这些信息记录在日志文件

中

,rman

增量备份的时候就直接扫描这些日志文件

去判断需要备份的增量数据块

这极大提高了备份的性能

尝试启用

block change tracking

SQL> alter database enable block change tracking;

alter database enable block change tracking

ERROR at line 1:

ORA-19773: must specify change tracking file name

设置

db_create_file_dest

SQL> alter system set DB_CREATE_FILE_DEST='d:\oracle' scope=both;

System altered.

再次执行

SQL> alter database enable block change tracking;

Database altered.

查看变化的数据块

SQL> column status format a8;

SQL> column filename format a50;

SQL> column bytes format 99999999;

SQL> select status,filename,bytes from v$block_change_tracking;

STATUS FILENAME BYTES

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

ENABLED D:\ORACLE\HXL\CHANGETRACKING\O1_MF_89RS6R01_.CHG

11599872

停止

block change tracking

alter database disable block change tracking;

备注

若是没有设置

DB_CREATE_FILE_DEST,

也可以使用如下命令指定日志文件

alter database enable block change tracking usingfile 'd:\rman\trace.log';



 和  



 



连用 

2014-02-17 15:46 639

人阅读评论

(0)

收藏举报

场景：有些

SQL

，运行好几十分或超一个钟，当你用

explain plan for

去查看时正常，但在

实际执行的时候却相当的慢，尤其要运行超过

个钟，几个钟的

SQL

，难以等待，不能及

时掌握其运行情况；

而

/*+ monitor */

和

dbms_sqltune.report_sql_monitor

可以观察正在运行的实时

SQL

的执

行计划情况；

Oracle Database 11g: Real-Time SQL Monitoring

Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a very effective

way to identify run-time performance problems with resource intensive long-running and

parallel SQL statements. Interactive Enterprise Manager screens display details of SQL

execution using new, fine-grained SQL statistic that are tracked out-of-the-box with no

performance penalty to production systems. Statistics at each step of the execution plan

are tracked by key performance metrics, including elapsed time, CPU time, number of

reads and writes, I/O wait time and various other wait times. This allows DBAs to analyze

SQL execution more deeply than previously possible and decide on the most appropriate

tuning strategies for monitored SQL statements.

MONITOR Hint

The MONITOR hint forces real-time SQL monitoring for the query, even if the statement

is not long running. This hint is valid only when the

parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to

DIAGNOSTIC+TUNING.

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS;

control_management_pack_access string DIAGNOSTIC+TUNING

The real-time SQL monitoring feature of Oracle Database enables you to monitor the

performance of SQL statements while they are executing. By default, SQL monitoring

automatically starts when a SQL statement runs parallel, or when it has consumed at

least 5 seconds of CPU or I/O time in a single execution.

当

SQL

以并行方式或者单独运行占用

CPU

或

的时间超过

秒钟就会自动被

ORACLE

启用监视；

例

select /*+ parallel(8) */count(1) from test where object_id=99;

执行后可以马上在

v$sql_monitor

中看到并行的信息；

STATUS USERNAME MODULE PROGRAM SQL_ID SQL_TEXT

1 EXECUTING 8q4t38pwpx829

2 EXECUTING 8q4t38pwpx829

3 EXECUTING 8q4t38pwpx829

of 119

40墨值下载

database

关注