1z0-043 lesson 9 Automatic Performance Management

1z0-043 lesson 9

网页链接多了就给当成了spam blog, 太蠢了!
我不得不说谷歌也够垃圾的

完成
基本上都是瞎猜的

是否显示我的答案
是否显示书上的答案和我的注释


1.
1. You observe that a database performance has degraded over a period of time. While investigating the reason, you find that the size of the database buffer cache is not large enough to cache all the needed data blocks. Which advisory component would you refer to, in order to determine that required size of the database buffer cache?
A. Memory Advisor
B. Segment Advisor
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Automatic Database Diagnostic Monitor (ADDM)

A
做过

A
上一章做过

2.
51. You want to use the SQL Tuning Advisor to generate recommendations for badly written SQL statements in your development environment. Which three sources can you select for the advisor to analyze? (Choose three.)
A. Top SQL
B. snapshots
C. SQL Tuning sets
D. index access path
E. optimizer statistics
F. materialized view logs

ABE
不知道

ABC
教材9-29

3.
54. You find that the execution time of reports in your datawarehouse application is significantly high. You suspect the lack of indexes to be the reason for the degradation in performance. Which advisory component would you refer to, in order to determine the appropriate indexes?
A. Memory Advisor
B. Segment Advisor
C. SQL Access Advisor
D. Automatic Workload Repository (AWR)
E. Automatic Database Diagnostic Monitor (ADDM)

C
?

C
教材9-30
SQL Access Advisor是建议创建索引,物化视图的

4.
107. In an Oracle 10g database, the in-memory statistics are gathered at regular intervals and used to perform growth-trend analysis and capacity planning of the database. Which component stores these statistics?
A. Recovery catalog
B. Oracle Enterprise Manager Repository
C. Automatic Workload Repository (AWR)
D. Oracle 10g Enterprise Manager Grid Control
E. Automatic Database Diagnostic Monitor (ADDM)

C

C
教材9-21

5.
131. Which three methods can you use to run an Automatic Database Diagnostic Monitor (ADDM) analysis over a specific time period? (Choose three.)
A. Enterprise Manager GUI
B. DBMS_TRACE package APIs
C. DBMS_ADVISOR package APIs
D. DBMS_MONITOR package APIs
E. $ORACLE_HOME/rdbms/admin/addmrpt.sql script

ACE

ACE

见
6.2.4 Diagnosing Database Performance Issues with ADDM(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#sthref333)
While the simplest way to run an ADDM analysis over a specific time period is with the Oracle Enterprise Manager GUI, ADDM can also be run manually using the $ORACLE_HOME/rdbms/admin/addmrpt.sql script and DBMS_ADVISOR package APIs. The SQL script and APIs can be run by any user who has been granted the ADVISOR privilege.

参考:
12 DBMS_ADVISOR(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advis.htm)
17.1 Overview of the SQL Access Advisor in the DBMS_ADVISOR Package(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/advisor.htm#CHDEEBAI)
Running the Segment Advisor Manually with PL/SQL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2113)
The Undo Advisor PL/SQL Interface(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1492)

=====


6.
30. You have created a new table. How long do you have to wait to be sure that Oracle has automatically gathered optimizer statistics for the table and loaded them into the AWR?
A. Not more than 30 minutes.
B. Up to seven days.
C. Until the next scheduled automated statistics collection job is run.
D. Optimizer statistics are not stored in the AWR.
E. You must gather the statistics manually when a new table is created. Oracle will collect them automatically after that.

C
建索引是立即统计的,好像

30. D. The AWR does not store optimizer statistics. It stores dynamic performance statistics. Optimizer statistics are stored in the data dictionary. See Chapter 8 for more information.

破题,文字游戏

7.
31. You are concerned about the operating system performance as well as SQL execution plan statistics. Which STATISTICS_LEVEL parameter would be the minimum required to ensure that Oracle will collect these types of statistics?
A. BASIC
B. TYPICAL
C. ADVANCED
D. ALL
E. None of these settings will achieve the desired result.

B
忘了

31. D. The STATISTICS_LEVEL parameter must be set to ALL in order to instruct Oracle to automatically collect operating system and SQL execution plan statistics. See Chapter 8 for more information.

教材9-9
Timed system statistics are automatically collected for the database if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC, then you must set TIMED_STATISTICS to TRUE to enable the collection of timed statistics. Note that setting STATISTICS_LEVEL to BASIC disables many automatic features and is not recommended.

见STATISTICS_LEVEL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref640)
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

=====


8.
1. Which of the following is not a valid ADDM finding type?
A. Error
B. Problem
C. Information
D. Symptom
E. All are valid ADDM finding types.

D

1. A. All ADDM analysis results are categorized as Problem, Symptom, or Information findings. Problem represents the root problem identified, Symptom identifies a symptom resulting from the root problem, and Information reports on non-problem areas. ADDM does not classify its findings as Errors.

见6.2.1 ADDM Analysis Results(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#sthref318)
Each ADDM finding can belong to one of three types:
* Problem: Findings that describe the root cause of a database performance issue.
* Symptom: Findings that contain information that often lead to one or more problem findings.
* Information: Findings that are used for reporting non-problem areas of the system.

9.
2. What is the default setting for the STATISTICS_LEVEL initialization parameter?
A. BASIC
B. ALL
C. STANDARD
D. TYPICAL
E. None of the above

D

2. D. The default setting for STATISTICS_LEVEL is TYPICAL, which ensures that adequate statistics are gathered to support all of the automatic database management features.

教材9-9没讲

见STATISTICS_LEVEL
The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

10.
3. Which of the following would be used to set or change the value for DBIO_EXPECTED?
A. The DBIO_EXPECTED initialization parameter
B. The ALTER SYSTEM statement
C. The ALTER SESSION statement
D. All of the above will work.
E. None of the above will work.

C
怎么会有这种题?

3. E. The value for DBIO_EXPECTED is set by using the DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER procedure. It is not an initialization parameter value, so answers A, B, and C cannot be correct, because all of them deal with initialization parameters.

见Setting the DBIO_EXPECTED parameter(http://download.oracle.com/docs/cd/B19306_01/server.102/b28051/tdppt_auto.htm#TDPPT024)
6.2.3 Setting Up ADDM(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#CHDHAEJD)

11.
4. Which of the following are types of problems that the ADDM will consider? (Choose all that apply.)
A. Database configuration issues
B. Concurrency issues
C. CPU bottlenecks
D. Suboptimal use of Oracle by an application
E. All of the above

E

4. E. ADDM considers all of these problem areas and more when performing its analysis.

见6.2 Automatic Database Diagnostic Monitor(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#PFGRF02602)
The types of problems that ADDM considers include the following:
* CPU bottlenecks - Is the system CPU bound by Oracle or some other application?
* Undersized Memory Structures - Are the Oracle memory structures, such as the SGA, PGA, and buffer cache, adequately sized?
* I/O capacity issues - Is the I/O subsystem performing as expected?
* High load SQL statements - Are there any SQL statements which are consuming excessive system resources?
* High load PL/SQL execution and compilation, as well as high load Java usage
* RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?
* Sub-optimal use of Oracle by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?
* Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?
* Concurrency issues - Are there buffer busy problems?
* Hot objects and top SQL for various problem areas

12.
5. The statistical data needed for ADDM to accurately diagnose problems is stored in which of the following areas?
A. Automatic Workload Repository (AWR)
B. Data dictionary
C. ADDM repository
D. PERFSTAT tables
E. None of the above

A

5. A. ADDM, along with all the other advisors, utilize the AWR. The data dictionary stores optimizer statistics, whereas ADDM deals with performance statistics, so choice B is wrong. There is no such thing as an ADDM repository; therefore, choice C is wrong. And PERFSTAT tables are a part of Statspack, which is no longer used in Oracle 10g.

教材9-21

13.
6. Which of the following is not a valid DBMS_ADVISOR procedure or function?
A. CREATE_TASK_SCRIPT
B. RESTART_TASK
C. INTERRUPT_TASK
D. RESUME_TASK
E. None of the above

E

6. B. There is no RESTART_TASK procedure or function. Choices A, C, and D all represent valid procedures. E is obviously wrong.

见12 DBMS_ADVISOR(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advis.htm#sthref66)
文档上竟然没有RESUME_TASK.但其实有

PROCEDURE RESUME_TASK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN

14.
7. In an RAC environment, which element(s) always uniquely identifies a snapshot?
A. INSTANCE_ID
B. CLUSTER_ID
C. SNAP_ID
D. Both A and C
E. A, B, and C

D

7. D. Both A and C are required to uniquely identify snapshots across Real Application Clusters (RACs). In a stand-alone environment, the SNAP_ID is guaranteed to be unique. However, with RAC, a snapshot shares the same SNAP_ID across all instances, so the INSTANCE_ID is used to differentiate between them.

常识

15.
8. If the ASH buffer is filled in less than 30 minutes, which process is responsible for flushing it?
A. SMON
B. MMON
C. MMNL
D. PMON
E. AMON

B

8. C. While MMON is normally tasked with flushing the ASH buffer every 30 minutes, the MMNL process performs the task if the buffer fills before that time. SMON and PMON don’t have any interaction with the ASH flush, and AMON is not a valid Oracle process name.

见Other Background Processes(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1626)
MMON performs various manageability-related background tasks, for example:
* Issuing alerts whenever a given metrics violates its threshold value
* Taking snapshots by spawning additional process (MMON slaves)
* Capturing statistics value for SQL objects which have been recently modified
MMNL performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
MMAN is used for internal database tasks.

16.
9. Which are valid SCOPE settings for the SQL Tuning Advisor? (Choose all that apply.)
A. Full
B. Comprehensive
C. Detailed
D. Basic
E. Limited

AD

9. B, E. SQL Tuning Advisor can perform either Comprehensive or Limited analysis, based on the SCOPE setting. Answers A, C, and D are all invalid settings for the SCOPE parameter.

教材9-33
12.2.2 Tuning Options(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#CHDFHCIF)
SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.
* If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.
* If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.

17.
10. If a metrics' threshold is defined with an OBSERVATION_PERIOD setting of 5 and a CONSECUTIVE_OCCURRENCES setting of 2, an alert would be generated after how many minutes of consecutive violations?
A. 10
B. 15
C. 20
D. 5
E. None of the above

A

10. B. The CONSECUTIVE_OCCURRENCES parameter defines the number of allowable violations without an alert. This number must be exceeded before an alert is generated. Because the values are checked every five minutes (as defined by the OBSERVATION_PERIOD parameter), an alert would be generated after 15 minutes.

见DBA_THRESHOLDS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4159.htm#sthref1916)
OBSERVATION_PERIOD NUMBER Observation period length (in minutes)
CONSECUTIVE_OCCURRENCES NUMBER Number of occurrences before an alert is issued

GET_THRESHOLD Procedure(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm#sthref5781)
observation_period The period at which the metric values are computed and verified against the threshold setting.
consecutive_occurrences The number of observation periods the metric value should violate the threshold value before the alert is issued.

18.
11. Which of the following statements regarding the Automatic Tuning Optimizer (ATO) is incorrect?
A. The ATO generates more efficient execution plans than the query optimizer in normal mode.
B. The ATO is nothing more than the query optimizer in tuning mode.
C. The ATO can take a long time to process a SQL statement, especially when performing a comprehensive analysis.
D. The ATO does generate a SQL Profile when performing a limited analysis.
E. All the above statements are correct.

E

11. A. The Automatic Tuning Optimizer (ATO) does not generate execution plans at all. Instead, it will return recommendations for optimization, along with their expected benefit and rationale.

见Application and SQL Tuning(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2212)

12.1 Automatic SQL Tuning Overview(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i36217)
In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

这里说生成执行计划了呀, 答案A也对?

19.
12. The AWR resides in which tablespace?
A. SYSTEM
B. TOOLS
C. SYSAUX
D. AWR
E. None of the above

C

12. C. The AWR is the primary occupant of the SYSAUX tablespace.

教材9-21
The AWR resides in the SYSAUX tablespace.

20.
13. Which DBMS_STATS function is used to allow Oracle to dynamically choose an appropriate parallel degree based on the size of the object and the values of certain initialization parameters?
A. AUTO
B. CHOOSE
C. DETECT
D. AUTO_DEGREE
E. BEST_DEGREE

A

13. D. The AUTO_DEGREE function chooses the most appropriate degree of parallelism based on factors such as object size and init.ora settings.

见14.3.1.2 Parallel Statistics Gathering(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i42217)
The statistics-gathering operations can run either serially or in parallel. The degree of parallelism can be specified with the DEGREE argument to the DBMS_STATS gathering procedures. Parallel statistics gathering can be used in conjunction with sampling. Oracle Corporation recommends setting the DEGREE parameter to DBMS_STATS.AUTO_DEGREE. This setting allows Oracle to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters.

21.
14. Which Oracle process updates the data dictionary with DML activity information gathered by the Automatic DML Monitoring feature?
A. SMON
B. PMON
C. MMNL
D. DMON
E. MMON

A

14. A. The SMON process updates the data dictionary with DML activity information approximately every three hours, as well as at instance shutdown.

见Automatically Collecting Statistics on Tables(http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2270)
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every three hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.

22.
15. Which of the following views can be queried to display advisor findings?
A. V$ADVISOR_FINDINGS
B. DBA_ADV_FINDINGS
C. DBA_ADV_RECOMMENDATIONS
D. DBA_ADVISOR_FINDINGS
E. Findings can be viewed only through Enterprise Manager.

D

15. D. The DBA_ADVISOR_FINDINGS view shows the findings from any advisor execution. Though they can also be viewed through Enterprise Manager, answer E is incorrect.

没有ABC
见DBA_ADVISOR_FINDINGS(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3007.htm)
DBA_ADVISOR_FINDINGS displays the findings discovered by all advisors in the database.

12.5 SQL Tuning Information Views(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i35947)
This section summarizes the views that you can display to review information that has been gathered for tuning the SQL statements. You need DBA privileges to access these views.
* Advisor information views, such as DBA_ADVISOR_TASKS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and DBA_ADVISOR_RATIONALE views.
* SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views.
* SQL Tuning Set views, such as DBA_SQLSET, DBA_SQLSET_BINDS, DBA_SQLSET_STATEMENTS, and DBA_SQLSET_REFERENCES views.
* Information on captured execution plans for statements in SQL Tuning Sets are displayed in the DBA_SQLSET_PLANS and USER_SQLSET_PLANS views.
* SQL Profile information is displayed in the DBA_SQL_PROFILES view.
* Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.
* Dynamic views containing information relevant to the SQL tuning, such as V$SQL, V$SQLAREA, V$SQLSTATS, and V$SQL_BINDS views.

23.
16. Which view shows the current AWR settings?
A. DBA_AWR_SETTINGS
B. DBA_AWR_CONFIG
C. DBA_HIST_WR_CONTROL
D. DBA_HIST_WR_SETTINGS
E. DBA_REPOSITORY

A

16. C. The DBA_HIST_WR_CONTROL view shows information about the AWR, including the current settings.

见DBA_HIST_WR_CONTROL(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3202.htm)
DBA_HIST_WR_CONTROL displays the control information for the Workload Repository.

5.3.4 Using Automatic Workload Repository Views(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i35169)
Typically, you would view the AWR data through Oracle Enterprise Manager or AWR reports. However, you can also view the statistics with the following views:
* V$ACTIVE_SESSION_HISTORY
This view displays active database session activity, sampled once every second. See "Active Session History (ASH)".
* V$ metric views provide metric data to track the performance of the system
The metric views are organized into various groups, such as event, event class, system, session, service, file, and tablespace metrics. These groups are identified in the V$METRICGROUP view.
* DBA_HIST views
The DBA_HIST views contain historical data stored in the database. This group of views includes:
o DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history for recent system activity.
o DBA_HIST_BASELINE displays information about the baselines captured on the system
o DBA_HIST_DATABASE_INSTANCE displays information about the database environment
o DBA_HIST_SNAPSHOT displays information on snapshots in the system
o DBA_HIST_SQL_PLAN displays the SQL execution plans
o DBA_HIST_WR_CONTROL displays the settings for controlling AWR

24.
17. On an eight-CPU system with a Shared Pool size of 1GB, what size would the ASH buffer be set to?
A. 8MB
B. 16MB
C. 50MB
D. 32MB
E. 32KB

B

17. B. The ASH buffer will be set to the lesser of:
(Total number of CPUs × 2MB)
8 CPUs × 2MB = 16MB
or
(Shared Pool size × 5%)
1GB × 5% = 50MB
Therefore, the ASH will be sized at 16MB.

见metalink doc. 243132.1
10g and above Active Session History (Ash) And Analysis Of Ash Online And Offline(https://metalink.oracle.com/CSP/main/showdoc?db=not&id=243132.1)
ASH Memory Size
==============
Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]

25.
18. _______________ is a hash value that uniquely identifies a SQL statement in the database.
A. SQL_HASH
B. SQL_HASH_ID
C. SQL_ID
D. SID
E. SPID

C

18. C. SQL_ID, new in Oracle 10g, is a hash value that uniquely identifies a single SQL statement within a database.

没找着

26.
19. Which package(s) can be used to interface with the SQL Tuning Advisor? (Choose all that apply.)
A. DBMS_ADVISOR
B. DBMS_SQL_TUNE
C. DBMS_SQLTUNE
D. DBMS_SQL_ADVISOR
E. DBMS_SQL_TUNE_ADV

AC

19. A, C. DBMS_SQLTUNE is designed specifically as the interface to the SQL Tuning Advisor. However, the DBMS_ADVISOR package can be used to interface with all advisors, including the SQL Tuning Advisor.

见101 DBMS_SQLTUNE(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm)
The DBMS_SQLTUNE package provides the interface to tune SQL statements.

27.
20. On the Edit Thresholds screen in Oracle Enterprise Manager, what does it mean if the Select button is disabled for a specific metric?
A. The metric is disabled on the current instance.
B. The metric is stale.
C. The metric does not support multiple thresholds.
D. The threshold is locked and cannot be modified.
E. None of the above.

D

20. C. Multiple thresholds can be set only on aggregated metrics with underlying granularity. For example, Blocking Session Count aggregates all underlying blocking sessions. Therefore, you can set thresholds for individual sessions as well as for the aggregate metric.

见Setting Metric Thresholds(http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune001.htm#sthref738)
For more comprehensive management of threshold settings for the different alerts, click the radio button in the Select column for that metric, then click Specify Multiple Thresholds.
The Specify Multiple Thresholds: metric_name page appears. This page enables you to specify metric thresholds for each object.
...

Specifying Multiple Thresholds(http://download.oracle.com/docs/cd/B19306_01/em.102/b25986/howto.htm#sthref22)
The Specifying Multiple Thresholds functionality allows you to define various subsets of data that can have different thresholds. By specifying multiple thresholds, you can refine the data used to trigger alerts, which are one of the key benefits of using Enterprise Manager.The key in specifying multiple thresholds is to determine how the comparison relates to the metric threshold as a whole. What benefit will be realized by defining a more stringent or lax threshold for that particular device, mount point, and so on?For example, using the Average Disk I/O Service Time metric, you can define warning and critical thresholds to be applied to all disks (sd0 and sd1), or you can define different warning and critical thresholds for a specific disk (sd0). This allows you to adjust the thresholds for sd0 to be more stringent or lax for that particular disk.

=====



-fin-

recover from corrupted undo tablespace 恢复损坏的撤销表空间

recover from corrupted undo tablespace
恢复损坏的撤销表空间

讲述了通过设置 _offline_rollback_segments / _corrupted_rollback_segments 隐含参数, 恢复损坏的undo表空间的例子


因在netapp存储上误操作, 清除了文件锁, 导致数据库意外终止

Thu Mar  5 23:05:20 2009
KCF: write/open error block=0x724 online=1
     file=2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 32768'
Automatic datafile offline due to write error on
file 2: /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
KCF: write/open error block=0x2b31 online=1
     file=3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar  5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Automatic datafile offline due to write error on
file 3: /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
Thu Mar  5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar  5 23:05:21 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar  5 23:05:22 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
KCF: write/open error block=0x7671 online=1
     file=1 /home/oracle/app/oracle/oradata/rcat/system01.dbf
     error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar  5 23:05:23 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_dbw0_25055.trc:
ORA-01243: system tablespace file suffered media failure
ORA-01114: IO error writing block to file 1 (block # 30321)
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/rcat/system01.dbf'
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192
DBW0: terminating instance due to error 1243
Instance terminated by DBW0, pid = 25055

重启后报错

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'

和

ORA-376 encountered when generating server alert SMG-3600

告警日志:

Sat May 30 10:40:04 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 500
LICENSE_SESSIONS_WARNING = 150
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  resource_limit           = TRUE
  license_max_sessions     = 500
  license_sessions_warning = 150
  __shared_pool_size       = 306184192
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  filesystemio_options     = setall
  sga_target               = 524288000
  control_files            = /home/oracle/app/oracle/oradata/rcat/control01.ctl, /home/oracle/app/oracle/oradata/rcat/control02.ctl, /home/oracle/app/oracle/oradata/rcat/control03.ctl
  control_file_record_keep_time= 30
  db_block_size            = 8192
  __db_cache_size          = 201326592
  compatible               = 10.2.0.4.0
  log_archive_dest_1       = LOCATION=/home/oracle/app/oracle/oradata/rcat/archive
  log_archive_dest_state_1 = ENABLE
  log_archive_format       = %t_%s_%r.arc
  archive_lag_target       = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /home/oracle/app/oracle/oradata/rcat/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = TRUE
  db_domain                =
  session_cached_cursors   = 200
  utl_file_dir             = /home/oracle/app/oracle/oradata/rcat/utl
  job_queue_processes      = 10
  background_dump_dest     = /home/oracle/app/oracle/admin/rcat/bdump
  user_dump_dest           = /home/oracle/app/oracle/admin/rcat/udump
  core_dump_dest           = /home/oracle/app/oracle/admin/rcat/cdump
  audit_file_dest          = /home/oracle/app/oracle/admin/rcat/adump
  audit_trail              = DB_EXTENDED
  db_name                  = rcat
  open_cursors             = 3000
  pga_aggregate_target     = 209715200
  aq_tm_processes          = 1
PMON started with pid=2, OS id=12880
PSP0 started with pid=3, OS id=12882
MMAN started with pid=4, OS id=12884
DBW0 started with pid=5, OS id=12886
LGWR started with pid=6, OS id=12888
CKPT started with pid=7, OS id=12890
SMON started with pid=8, OS id=12892
RECO started with pid=9, OS id=12894
CJQ0 started with pid=10, OS id=12896
MMON started with pid=11, OS id=12898
MMNL started with pid=12, OS id=12900
Sat May 30 10:40:05 2009
ALTER DATABASE   MOUNT
Sat May 30 10:40:09 2009
Setting recovery target incarnation to 1
Sat May 30 10:40:09 2009
Successful mount of redo thread 1, with mount id 460376325
Sat May 30 10:40:09 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sat May 30 10:40:09 2009
ALTER DATABASE OPEN
Sat May 30 10:40:09 2009
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Sat May 30 10:40:10 2009
Started redo scan
Sat May 30 10:40:10 2009
Completed redo scan
 21 redo blocks read, 3 data blocks need recovery
Sat May 30 10:40:10 2009
Started redo application at
 Thread 1: logseq 7202, block 231, scn 6268440
Sat May 30 10:40:10 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 7202 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/rcat/redo02.rdo
Sat May 30 10:40:10 2009
Completed redo application
Sat May 30 10:40:10 2009
Completed crash recovery at
 Thread 1: logseq 7202, block 252, scn 6288463
 3 data blocks read, 3 data blocks written, 21 redo blocks read
Sat May 30 10:40:10 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=12913
Sat May 30 10:40:10 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=12915
Sat May 30 10:40:10 2009
Thread 1 advanced to log sequence 7203 (thread open)
Thread 1 opened at log sequence 7203
  Current log# 3 seq# 7203 mem# 0: /home/oracle/app/oracle/oradata/rcat/redo03.rdo
Successful open of redo thread 1
Sat May 30 10:40:10 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat May 30 10:40:10 2009
ARC0: Becoming the heartbeat ARCH
Sat May 30 10:40:10 2009
SMON: enabling cache recovery
Sat May 30 10:40:10 2009
Successfully onlined Undo Tablespace 1.
Sat May 30 10:40:10 2009
SMON: enabling tx recovery
Sat May 30 10:40:10 2009
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
replication_dependency_tracking turned off (no async multimaster replication found)
Sat May 30 10:40:11 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Starting background process QMNC
QMNC started with pid=19, OS id=12917
Sat May 30 10:40:12 2009
Completed: ALTER DATABASE OPEN
Sat May 30 10:40:13 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ORA-376 encountered when generating server alert SMG-3600
Sat May 30 10:45:13 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 10:50:14 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 10:55:15 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Sat May 30 11:00:08 2009
MMNL absent for 1203 secs; Foregrounds taking over
Sat May 30 11:00:09 2009
MMNL absent for 1203 secs; Foregrounds taking over
MMNL absent for 1203 secs; Foregrounds taking over
MMNL absent for 1203 secs; Foregrounds taking over
Sat May 30 11:00:17 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'

跟踪文件1:

/home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/10.2
System name:    Linux
Node name:      DEV-DB-2
Release:        2.6.9-34.ELsmp
Version:        #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine:        x86_64
Instance name: rcat
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 12892, image: oracle@DEV-DB-2 (SMON)

*** SERVICE NAME:() 2009-05-30 10:40:10.958
*** SESSION ID:(1649.1) 2009-05-30 10:40:10.958
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
*** 2009-05-30 10:40:11.610
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
*** 2009-05-30 10:45:13.135
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
...
...

跟踪文件2:

/home/oracle/app/oracle/admin/rcat/bdump/rcat_mmon_12898.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/10.2
System name:    Linux
Node name:      DEV-DB-2
Release:        2.6.9-34.ELsmp
Version:        #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine:        x86_64
Instance name: rcat
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 12898, image: oracle@DEV-DB-2 (MMON)

*** SERVICE NAME:(SYS$BACKGROUND) 2009-05-30 10:40:13.257
*** SESSION ID:(1646.1) 2009-05-30 10:40:13.257
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
  *** SQLSTR: total-len=328, dump-len=240,
      STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
*** kewrwdbi_1: Error=13509 encountered during run_once
keaInitAdvCache: failed, err=604
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.post_instance_up308: SQLCODE -13917,ORA-13917: Posting system
 alert with reason_id 135 failed with code [5] [post_error]
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:41:11.684
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:42:11.742
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:43:11.803
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:44:11.862
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:45:08.910
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
  *** SQLSTR: total-len=328, dump-len=240,
      STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
...
...

undo和sysaux表空间都需要恢复

SQL> set pages 50000 line 130
SQL> col name for a60
SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                OWNER  STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM                         SYSTEM                         SYS    ONLINE
_SYSSMU1$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU4$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU5$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU6$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU7$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU8$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU9$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU10$                     UNDOTBS1                       PUBLIC NEEDS RECOVERY

11 rows selected.

SQL>

设置 _offline_rollback_segments 参数, 重启

alter system set "_offline_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;
alter system set undo_management=manual scope=spfile;
shutdown immediate
startup

_offline_rollback_segments 和 _corrupted_rollback_segments 隐含参数的讲解见dsi401 Chapter 6 - Page 20
----begin----
When opening a database, any rollback segments listed in _offline or _corrupted parameters:
* Arenot scanned, and any active transactions are neither marked as dead nor rolled back
* Appear offline in dba_rollback_segs(undo$)
* Cannot be acuired by the instance for new transactions
...

If an open ITL is found to be associated with an _offline segment, the segment is read to find the transaction status
* If committed, the block is cleaned out
* If active and you want to read the block, a CR copy is constructed using undo from the segment
* If active and you want to lock the row, undesirable behavior may result
...

If an open ITL is found to be associated with a _corrupted segment, the segment is not read to find the transaction status
* It is as though the rollback segment had been dropped; the transaction is assumed to be committed and delayed block cleanout is performed
* If the transaction was not committed, logical corruption will occur

Most important, the Oracle server does not read the segment in this case. It is as if the segment has been dropped. This is the most important difference between _offline and _corrupted.
...
----end----

删除原来的撤销段, 恢复撤销表空间和系统辅助表空间

drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU2$";
drop rollback segment "_SYSSMU3$";
drop rollback segment "_SYSSMU4$";
drop rollback segment "_SYSSMU5$";
drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU7$";
drop rollback segment "_SYSSMU8$";
drop rollback segment "_SYSSMU9$";
drop rollback segment "_SYSSMU10$";
alter database recover automatic datafile 2,3;
alter tablespace undotbs1 online;
alter tablespace sysaux online;

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                OWNER  STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM                         SYSTEM                         SYS    ONLINE
_SYSSMU1$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU4$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU5$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU6$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU7$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU8$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU9$                      UNDOTBS1                       PUBLIC NEEDS RECOVERY
_SYSSMU10$                     UNDOTBS1                       PUBLIC NEEDS RECOVERY

11 rows selected.

SQL> drop rollback segment "_SYSSMU1$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU2$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU3$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU4$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU5$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU6$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU7$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU8$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU9$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU10$";

Rollback segment dropped.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           RECOVER
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            RECOVER
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> alter database recover automatic datafile 2,3;

Database altered.

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
         1 /home/oracle/app/oracle/oradata/rcat/system01.dbf            SYSTEM
         2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf           OFFLINE
         3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf            OFFLINE
         4 /home/oracle/app/oracle/oradata/rcat/users01.dbf             ONLINE
         5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf              ONLINE

SQL> alter tablespace undotbs1 online;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL>

日志:

Sat May 30 13:39:26 2009
Completed: ALTER DATABASE OPEN
Sat May 30 13:39:26 2009
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
ORA-376 encountered when generating server alert SMG-3600
Sat May 30 13:40:35 2009
drop rollback segment "_SYSSMU1$"
Sat May 30 13:40:35 2009
Completed: drop rollback segment "_SYSSMU1$"
Sat May 30 13:41:16 2009
drop rollback segment "_SYSSMU2$"
Sat May 30 13:41:16 2009
Completed: drop rollback segment "_SYSSMU2$"
Sat May 30 13:41:18 2009
drop rollback segment "_SYSSMU3$"
Completed: drop rollback segment "_SYSSMU3$"
Sat May 30 13:41:21 2009
drop rollback segment "_SYSSMU4$"
Completed: drop rollback segment "_SYSSMU4$"
Sat May 30 13:41:25 2009
drop rollback segment "_SYSSMU5$"
Completed: drop rollback segment "_SYSSMU5$"
Sat May 30 13:41:28 2009
drop rollback segment "_SYSSMU6$"
Sat May 30 13:41:28 2009
Completed: drop rollback segment "_SYSSMU6$"
Sat May 30 13:41:30 2009
drop rollback segment "_SYSSMU7$"
Completed: drop rollback segment "_SYSSMU7$"
Sat May 30 13:41:36 2009
drop rollback segment "_SYSSMU8$"
Completed: drop rollback segment "_SYSSMU8$"
Sat May 30 13:41:38 2009
drop rollback segment "_SYSSMU9$"
Sat May 30 13:41:38 2009
Completed: drop rollback segment "_SYSSMU9$"
Sat May 30 13:41:40 2009
drop rollback segment "_SYSSMU10$"
Completed: drop rollback segment "_SYSSMU10$"
Sat May 30 13:43:12 2009
alter database recover automatic datafile 2,3
Sat May 30 13:43:12 2009
Media Recovery Start
 parallel recovery started with 3 processes
Sat May 30 13:43:13 2009
Media Recovery Log /home/oracle/app/oracle/oradata/rcat/archive/1_7192_663322987.arc
Sat May 30 13:43:13 2009
Media Recovery Complete (rcat)
Completed: alter database recover automatic datafile 2,3
Sat May 30 13:44:04 2009
alter tablespace undotbs1 online
Sat May 30 13:44:04 2009
Completed: alter tablespace undotbs1 online
Sat May 30 13:44:07 2009
alter tablespace sysaux online
Completed: alter tablespace sysaux online

恢复参数,重启

alter system reset "_offline_rollback_segments" scope=spfile sid='*';
alter system set undo_management=auto scope=spfile;
shutdown immediate
startup

至此, 恢复完成.



-fin-

Can multiblock-read span across extents 多数据块读能跨盘区吗

Can multiblock-read span across extents?
多数据块读能跨盘区吗?

通过测试展示了多数据块不能跨盘区, 但同时发现有时读取的块的数量不规则,不等于db_file_multiblock_read_count大小, 不知道为何



提问: 这个应该有影响因素吧？
如果我将db_file_multiblock_read_count设为64，一次读取64块，将近1m的数据，表的extent大小为65k，那么如果这16个extent都是一个表数据，全表扫描是否16个extent一次性读取；
而当这16个extent包含多个表数据并且互相交叉，是否要多次访问才能将某个表数据都取出：

回答: 多数据块读不能跨盘区, 即最多读取的数量等于: db_file_multiblock_read_count和extent两者取一个最大值(更正:应该是小)

提问: 我得学习学习了，需要确认一下你说的到底有没有问题，没有骗人吧？如果extent只有128k，那么db_file_multiblock_read_count设置再大也没有用？在哪记载？

回答: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm
10.3.4 db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'.


等我有时间再做个实验证实一下!


1. 补充说明

实际读取的大小还受操作系统的限制

DB_FILE_MULTIBLOCK_READ_COUNT
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

25 Using Parallel Execution - DB_FILE_MULTIBLOCK_READ_COUNT
This parameter determines how many database blocks are read with a single operating system READ call. The upper limit for this parameter is platform-dependent. If you set DB_FILE_MULTIBLOCK_READ_COUNT to an excessively high value, your operating system will lower the value to the highest allowable level when you start your database. In this case, each platform uses the highest value possible. Maximum values generally range from 64 KB to 1 MB.

WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
Oracle's ability to read multiple blocks is limited by the operating system's upper limit on the number of bytes which can be read in a single I/O call (MAX_IO_SIZE).
受到MAX_IO_SIZE参数的限制, 此参数跟操作系统相关


2. 多数据块读是否能跨盘区

参考资料1
Oracle wait interface By Richmond Shee, Kirtikumar Deshpande, K. Gopalakrishnan - Why Dose a Full Scan Operation Request Fewer Blocks than the MBRC
If you monitor a full table scan operation closely by repeatedly querying the V$SESSION_WAIT view in quick successions or by tracing the session with trace event 10046, you may see some db file scattered read events that request fewer blocks than the MBRC. This irregularity is due to any of the following reasons:
如果监视一个全表扫描(full table scan)时, 很快的连续的查询V$SESSION_WAIT视图, 或设置10046跟踪事件, 就会发现有些数据文件分散读(db file scattered read)事件请求的数据块数小于多数据块读的数量(MBRC). 这种不和常规的情况的发生是因为下面几种原因:
* The last set of blocks in an extents is less than the MBRC. If the MBRC is set to 8 and every extent has 10 blocks, Oracle will issue two multiblock read calls for each extent -- one read call for 8 blocks and the other read call for 2 blocks -- because the MBRC factor cannot span across extents.
* 一个盘区内的最后一组数据块数量小于MBRC. 如果MBRC设置为8, 每个盘区10个数据块, 对每个盘区Oracle会发起2个多数据块读 -- 一次读8个数据块, 另一次读2个数据块 -- 因为MBRC不能跨越盘区.
* One or more blocks in the multiblock read set is already in the buffer cache, so Oracle breaks the fetch into two or more reads, which may be comprised of a single or multiblock I/Os. For example, if the MBRC is 8 and blocks 3 and 7 are in the cache, Oracle will issue three read calls -- the first for blocks 1 and 2, the second for block 4 through 6, and the third for block 8. Since the third fetch is for a single database block, the wait event is db file sequential read. However, for the first two read calls, the wait event is db file scattered read because the number of blocks is greater than 1. Therefore, cached blocks can cause full table scans operations to perform more reads than required.
* 多数据块读中的一个或多个数据块已经在数据缓冲区内了, 因此Oracle将分2次或多次获取数据, 由一个单数据块或多数据块I/O组成. 比如, 如果MRBC是8, 数据块3和7在缓冲中, Oracle将发起3个读操作 -- 第1次读数据块1和2, 第2次读数据库4到6, 第3次读数据块8. 因为第3次读的是一个单数据块, 所以等待事件是数据文件连续读(db file sequential read). 而头2个读的等待事件则是数据文件分散读(db file scattered read), 这是因为读取的数据块数大于1. 因此, 被缓冲的数据块导致了全表扫描操作要读取更多次.

参考资料2
db_file_multiblock_read_count and different read rates
"We read maximally by EXTENT size (eg: a read never spans extents). perhaps your are being restricted by your extent size."
"we read in but not across extents, so yes, that small extent would cause the read size to go down."

----begin----
you can use the trace to find the MAX size, the optimal size is something entirely different :)

when you do a multi-block IO, some of the blocks may be in the buffer cache already. So, lets say
Oracle says "lets read 64 blocks starting from file 5, block 17"

it is saying "I need blocks 17 .. 80, but wait, block 17 is in the cache, lets read 63 blocks
starting at block 18". So it'll read 63 blocks

Later it says "I could read upto 64 blocks, but this extent only has 15 blocks left, lets read 15
of them"

Last it says "I could read upto 64 blocks, I want the 64 blocks from file 10 starting at block 1,
but blocks 2, 4, 6, 8, 10, ..... 64 are in the cache. I'll do 32 single block IO's to pick up the
rest"


So, you might (in order to find the max IO size on your system)

a) offline the tablespace (will flush cache of blocks for that tablespace)
b) online the tablespace
c) enable trace
d) full scan
e) exit sqlplus
f) scan trace file for max p3= value....

as long as your extents are bigger than your IO size, you'll have it.


the suggestion is to leave all parameters at a default setting unless you can really identify a
good reason for changing them
----end----

参考资料3
Managing Extents
If set incorrectly, when a full table or index scan is performed, an extra multiread block read will be required to read only the last remaining blocks of each extent. This happens because multiblock reads in Oracle never span extent boundaries, even if the extents happen to be contiguous. This behavior is also consistent within locally managed tablespaces.



3. 测试如下

创建测试表, 表空间是本地自动管理的

create tablespace ts_alloc_auto
 datafile '/home/oracle/app/oracle/oradata/test/ts_alloc_auto_01.dbf' size 100m
  autoextend on next 10m maxsize 500m
 extent management local autoallocate;
/

conn a/a
drop table t1;
create table t1 tablespace ts_alloc_auto
 as select * from all_objects;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;

查看盘区信息

select bytes/1024, count(*)
  from user_extents
 where segment_name = 'T1'
 group by bytes/1024
 order by 1
/

BYTES/1024   COUNT(*)
---------- ----------
        64         16
      1024         63
      6144          1
      8192          9

设置dfmbrc=16, 查看跟踪文件

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set db_file_multiblock_read_count=16;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #1 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214328880843122 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #1:c=40994,e=853947,p=527,cr=84,cu=0,mis=1,r=0,dep=0,og=1,tim=1214328880843114
BINDS #1:
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214328880843262
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328880843302
WAIT #1: nam='db file scattered read' ela= 66 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214328880843506
WAIT #1: nam='db file scattered read' ela= 78 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214328880843722
WAIT #1: nam='db file scattered read' ela= 71 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214328880843942
WAIT #1: nam='db file scattered read' ela= 94 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214328880844188
WAIT #1: nam='db file scattered read' ela= 8307 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214328880852641
WAIT #1: nam='db file scattered read' ela= 9519 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214328880862400
WAIT #1: nam='db file scattered read' ela= 8335 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214328880870940
WAIT #1: nam='db file scattered read' ela= 9318 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214328880880464
WAIT #1: nam='db file scattered read' ela= 8404 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214328880889128
WAIT #1: nam='db file scattered read' ela= 9423 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214328880898767
WAIT #1: nam='db file scattered read' ela= 9028 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214328880908032
WAIT #1: nam='db file scattered read' ela= 9220 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214328880917458
WAIT #1: nam='db file scattered read' ela= 7737 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214328880925600
WAIT #1: nam='db file scattered read' ela= 8927 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214328880934760
WAIT #1: nam='db file scattered read' ela= 11810 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214328880946829
WAIT #1: nam='db file scattered read' ela= 8946 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214328880955987
WAIT #1: nam='db file scattered read' ela= 18177 file#=12 block#=3979 blocks=16 obj#=62725 tim=1214328880974414
WAIT #1: nam='db file scattered read' ela= 2307 file#=12 block#=3995 blocks=2 obj#=62725 tim=1214328880977087
WAIT #1: nam='db file scattered read' ela= 2394 file#=12 block#=3998 blocks=16 obj#=62725 tim=1214328880979651
WAIT #1: nam='db file scattered read' ela= 17748 file#=12 block#=4014 blocks=16 obj#=62725 tim=1214328880997720
WAIT #1: nam='db file scattered read' ela= 18176 file#=12 block#=4030 blocks=16 obj#=62725 tim=1214328881016254
WAIT #1: nam='db file scattered read' ela= 18170 file#=12 block#=4046 blocks=16 obj#=62725 tim=1214328881034806
WAIT #1: nam='db file scattered read' ela= 20182 file#=12 block#=4062 blocks=16 obj#=62725 tim=1214328881055348
...
...
WAIT #1: nam='db file scattered read' ela= 18545 file#=12 block#=3831 blocks=16 obj#=62725 tim=1214328900218309
WAIT #1: nam='db file scattered read' ela= 2232 file#=12 block#=3847 blocks=2 obj#=62725 tim=1214328900220873
WAIT #1: nam='db file scattered read' ela= 18546 file#=12 block#=17421 blocks=16 obj#=62725 tim=1214328900239546
WAIT #1: nam='db file scattered read' ela= 18419 file#=12 block#=17437 blocks=16 obj#=62725 tim=1214328900258335
WAIT #1: nam='db file scattered read' ela= 18524 file#=12 block#=17453 blocks=16 obj#=62725 tim=1214328900277237
WAIT #1: nam='db file scattered read' ela= 24145 file#=12 block#=17469 blocks=16 obj#=62725 tim=1214328900301774
WAIT #1: nam='db file scattered read' ela= 34335 file#=12 block#=17485 blocks=16 obj#=62725 tim=1214328900336530
WAIT #1: nam='db file scattered read' ela= 18056 file#=12 block#=17501 blocks=15 obj#=62725 tim=1214328900354970
FETCH #1:c=1071837,e=19511954,p=16551,cr=17088,cu=0,mis=0,r=1,dep=0,og=1,tim=1214328900355290
WAIT #1: nam='SQL*Net message from client' ela= 221 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900355611
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214328900355665
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900355696
WAIT #1: nam='SQL*Net message from client' ela= 466 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214328900356187
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17088 pr=16551 pw=0 time=19511955 us)'
STAT #1 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17088 pr=16551 pw=0 time=9907650 us)'
=====================

前几个盘区大小是64k, 所以最多读8块
后面的盘区大小是1m, 最多读取16块, 跟db_file_multiblock_read_count相符

但是, 发现有时读取的块数不到16块, 是因为10g查询前的动态采样(Dynamic Sampling)

=====================
PARSING IN CURSOR #2 len=414 dep=1 uid=48 oct=3 lid=48 tim=1214328879996640 hv=508072983 ad='6d666888'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_T
UNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM
 "T1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "T1") SAMPLESUB
END OF STMT
PARSE #2:c=1000,e=862,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1214328879996633
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a974510b0  bln=22  avl=01  flg=09
  value=0
 Bind#1
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a97451068  bln=22  avl=01  flg=09
  value=0
...
EXEC #2:c=2000,e=2463,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1214328879999189
WAIT #2: nam='db file sequential read' ela= 16590 file#=12 block#=3997 blocks=1 obj#=62725 tim=1214328880015978
WAIT #2: nam='db file sequential read' ela= 10124 file#=12 block#=4933 blocks=1 obj#=62725 tim=1214328880026325
WAIT #2: nam='db file sequential read' ela= 11306 file#=12 block#=5071 blocks=1 obj#=62725 tim=1214328880037913
WAIT #2: nam='db file sequential read' ela= 4090 file#=12 block#=5204 blocks=1 obj#=62725 tim=1214328880042182
WAIT #2: nam='db file sequential read' ela= 3980 file#=12 block#=5548 blocks=1 obj#=62725 tim=1214328880046334
WAIT #2: nam='db file sequential read' ela= 7437 file#=12 block#=6317 blocks=1 obj#=62725 tim=1214328880053893
WAIT #2: nam='db file scattered read' ela= 31558 file#=12 block#=6586 blocks=16 obj#=62725 tim=1214328880085761
WAIT #2: nam='db file scattered read' ela= 25753 file#=12 block#=6614 blocks=16 obj#=62725 tim=1214328880111864
WAIT #2: nam='db file sequential read' ela= 3809 file#=12 block#=6653 blocks=1 obj#=62725 tim=1214328880115880
WAIT #2: nam='db file sequential read' ela= 5620 file#=12 block#=7095 blocks=1 obj#=62725 tim=1214328880121604
WAIT #2: nam='db file sequential read' ela= 4788 file#=12 block#=7407 blocks=1 obj#=62725 tim=1214328880126497
...
WAIT #2: nam='db file scattered read' ela= 18731 file#=12 block#=3319 blocks=16 obj#=62725 tim=1214328880802261
WAIT #2: nam='db file scattered read' ela= 18777 file#=12 block#=3584 blocks=16 obj#=62725 tim=1214328880821291
WAIT #2: nam='db file scattered read' ela= 19198 file#=12 block#=3624 blocks=16 obj#=62725 tim=1214328880840813
WAIT #2: nam='db file sequential read' ela= 1243 file#=12 block#=3734 blocks=1 obj#=62725 tim=1214328880842270
FETCH #2:c=35994,e=843145,p=526,cr=83,cu=0,mis=0,r=1,dep=1,og=1,tim=1214328880842375
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=83 pr=526 pw=0 time=843158 us)'
STAT #2 id=2 cnt=4899 pid=1 pos=1 obj=62725 op='TABLE ACCESS SAMPLE T1 (cr=83 pr=526 pw=0 time=26636 us)'
=====================

已经预先读取了一些数据块到缓存, 所以造成后来多数据块读的数量不规则了

关闭动态采样, 重新查询

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=16;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214329116866106 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #2:c=3000,e=2168,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1214329116866095
BINDS #2:
EXEC #2:c=0,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214329116866274
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214329116866313
WAIT #2: nam='db file sequential read' ela= 3424 file#=12 block#=3851 blocks=1 obj#=62725 tim=1214329116871313
WAIT #2: nam='db file scattered read' ela= 2536 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214329116874127
WAIT #2: nam='db file scattered read' ela= 6547 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214329116880948
WAIT #2: nam='db file scattered read' ela= 5220 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214329116886434
WAIT #2: nam='db file scattered read' ela= 2597 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214329116889331
WAIT #2: nam='db file scattered read' ela= 6248 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214329116895837
WAIT #2: nam='db file scattered read' ela= 5833 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214329116901972
WAIT #2: nam='db file scattered read' ela= 5119 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214329116907434
WAIT #2: nam='db file scattered read' ela= 6072 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214329116913780
WAIT #2: nam='db file scattered read' ela= 5649 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214329116919883
WAIT #2: nam='db file scattered read' ela= 5165 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214329116925362
WAIT #2: nam='db file scattered read' ela= 5220 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214329116930849
WAIT #2: nam='db file scattered read' ela= 6463 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214329116937547
WAIT #2: nam='db file scattered read' ela= 6555 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214329116944397
WAIT #2: nam='db file scattered read' ela= 5195 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214329116949877
WAIT #2: nam='db file scattered read' ela= 5224 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214329116955364
WAIT #2: nam='db file scattered read' ela= 5253 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214329116960861
WAIT #2: nam='db file scattered read' ela= 11883 file#=12 block#=3979 blocks=16 obj#=62725 tim=1214329116973055
WAIT #2: nam='db file scattered read' ela= 10444 file#=12 block#=3995 blocks=16 obj#=62725 tim=1214329116984032
WAIT #2: nam='db file scattered read' ela= 10553 file#=12 block#=4011 blocks=16 obj#=62725 tim=1214329116995017
WAIT #2: nam='db file scattered read' ela= 10757 file#=12 block#=4027 blocks=16 obj#=62725 tim=1214329117006276
WAIT #2: nam='db file scattered read' ela= 10090 file#=12 block#=4043 blocks=16 obj#=62725 tim=1214329117016866
WAIT #2: nam='db file scattered read' ela= 10533 file#=12 block#=4059 blocks=16 obj#=62725 tim=1214329117027851
WAIT #2: nam='db file scattered read' ela= 10565 file#=12 block#=4075 blocks=16 obj#=62725 tim=1214329117038829
WAIT #2: nam='db file scattered read' ela= 8118 file#=12 block#=4091 blocks=14 obj#=62725 tim=1214329117047387
WAIT #2: nam='db file scattered read' ela= 89876 file#=12 block#=4363 blocks=16 obj#=62725 tim=1214329117137664
...
...
WAIT #2: nam='db file scattered read' ela= 10611 file#=12 block#=3804 blocks=16 obj#=62725 tim=1214329128747190
WAIT #2: nam='db file scattered read' ela= 10603 file#=12 block#=3820 blocks=16 obj#=62725 tim=1214329128758184
WAIT #2: nam='db file scattered read' ela= 9949 file#=12 block#=3836 blocks=13 obj#=62725 tim=1214329128768526
WAIT #2: nam='db file scattered read' ela= 441 file#=12 block#=17421 blocks=16 obj#=62725 tim=1214329128769262
WAIT #2: nam='db file scattered read' ela= 112 file#=12 block#=17437 blocks=16 obj#=62725 tim=1214329128769687
WAIT #2: nam='db file scattered read' ela= 128 file#=12 block#=17453 blocks=16 obj#=62725 tim=1214329128770161
WAIT #2: nam='db file scattered read' ela= 467 file#=12 block#=17469 blocks=16 obj#=62725 tim=1214329128770919
WAIT #2: nam='db file scattered read' ela= 125 file#=12 block#=17485 blocks=16 obj#=62725 tim=1214329128771381
WAIT #2: nam='db file scattered read' ela= 22736 file#=12 block#=17501 blocks=15 obj#=62725 tim=1214329128794404
FETCH #2:c=690895,e=11928411,p=17078,cr=17093,cu=0,mis=0,r=1,dep=0,og=1,tim=1214329128794759
WAIT #2: nam='SQL*Net message from client' ela= 265 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795142
FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214329128795206
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795250
WAIT #2: nam='SQL*Net message from client' ela= 515 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214329128795790
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17093 pr=17078 pw=0 time=11928417 us)'
STAT #2 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17093 pr=17078 pw=0 time=11153729 us)'
=====================

读取还是不规则. 比如,读7个16块,1个14块. 为什么? 有时还有13块?

设置dfmbrc为更大的值, dfmbrc=4096, 查看跟踪文件
其实设置不到4096这么大, 最大只有128, 也就是最多一次读128*8k=1m

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16
SQL> alter session set db_file_multiblock_read_count=4096;

Session altered.

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
SQL>

conn / as sysdba
alter tablespace ts_alloc_auto offline;
alter tablespace ts_alloc_auto online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=4096;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t1) */ count(*) from t1;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214351910543864 hv=2516705452 ad='6f4bfb68'
select /*+ FULL(t1) */ count(*) from t1
END OF STMT
PARSE #2:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214351910543858
BINDS #2:
EXEC #2:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214351910544029
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214351910544071
WAIT #2: nam='db file sequential read' ela= 3416 file#=12 block#=3851 blocks=1 obj#=62725 tim=1214351910548894
WAIT #2: nam='db file scattered read' ela= 2408 file#=12 block#=3852 blocks=5 obj#=62725 tim=1214351910551595
WAIT #2: nam='db file scattered read' ela= 5218 file#=12 block#=3857 blocks=8 obj#=62725 tim=1214351910557073
WAIT #2: nam='db file scattered read' ela= 5215 file#=12 block#=3866 blocks=7 obj#=62725 tim=1214351910562563
WAIT #2: nam='db file scattered read' ela= 3839 file#=12 block#=3873 blocks=8 obj#=62725 tim=1214351910566707
WAIT #2: nam='db file scattered read' ela= 6344 file#=12 block#=3882 blocks=7 obj#=62725 tim=1214351910573312
WAIT #2: nam='db file scattered read' ela= 5167 file#=12 block#=3889 blocks=8 obj#=62725 tim=1214351910578795
WAIT #2: nam='db file scattered read' ela= 5271 file#=12 block#=3898 blocks=7 obj#=62725 tim=1214351910584326
WAIT #2: nam='db file scattered read' ela= 6626 file#=12 block#=3905 blocks=8 obj#=62725 tim=1214351910591250
WAIT #2: nam='db file scattered read' ela= 5182 file#=12 block#=3914 blocks=7 obj#=62725 tim=1214351910596721
WAIT #2: nam='db file scattered read' ela= 5227 file#=12 block#=3921 blocks=8 obj#=62725 tim=1214351910602211
WAIT #2: nam='db file scattered read' ela= 5221 file#=12 block#=3930 blocks=7 obj#=62725 tim=1214351910607696
WAIT #2: nam='db file scattered read' ela= 6848 file#=12 block#=3937 blocks=8 obj#=62725 tim=1214351910614795
WAIT #2: nam='db file scattered read' ela= 5128 file#=12 block#=3946 blocks=7 obj#=62725 tim=1214351910620259
WAIT #2: nam='db file scattered read' ela= 5253 file#=12 block#=3953 blocks=8 obj#=62725 tim=1214351910625751
WAIT #2: nam='db file scattered read' ela= 5245 file#=12 block#=3962 blocks=7 obj#=62725 tim=1214351910631248
WAIT #2: nam='db file scattered read' ela= 6596 file#=12 block#=3969 blocks=8 obj#=62725 tim=1214351910638089
WAIT #2: nam='db file scattered read' ela= 87504 file#=12 block#=3979 blocks=126 obj#=62725 tim=1214351910726610
WAIT #2: nam='db file scattered read' ela= 166722 file#=12 block#=4363 blocks=126 obj#=62725 tim=1214351910896271
WAIT #2: nam='db file scattered read' ela= 84797 file#=12 block#=4491 blocks=126 obj#=62725 tim=1214351910983974
WAIT #2: nam='db file scattered read' ela= 84803 file#=12 block#=4619 blocks=126 obj#=62725 tim=1214351911071708
WAIT #2: nam='db file scattered read' ela= 84780 file#=12 block#=4747 blocks=126 obj#=62725 tim=1214351911159631
WAIT #2: nam='db file scattered read' ela= 84584 file#=12 block#=4875 blocks=126 obj#=62725 tim=1214351911247331
WAIT #2: nam='db file scattered read' ela= 84883 file#=12 block#=5003 blocks=126 obj#=62725 tim=1214351911335008
WAIT #2: nam='db file scattered read' ela= 84960 file#=12 block#=5131 blocks=126 obj#=62725 tim=1214351911422732
WAIT #2: nam='db file scattered read' ela= 85093 file#=12 block#=5259 blocks=126 obj#=62725 tim=1214351911510735
WAIT #2: nam='db file scattered read' ela= 84723 file#=12 block#=5387 blocks=126 obj#=62725 tim=1214351911598233
WAIT #2: nam='db file scattered read' ela= 85103 file#=12 block#=5515 blocks=126 obj#=62725 tim=1214351911686015
WAIT #2: nam='db file scattered read' ela= 85149 file#=12 block#=5643 blocks=126 obj#=62725 tim=1214351911773861
WAIT #2: nam='db file scattered read' ela= 87201 file#=12 block#=5771 blocks=126 obj#=62725 tim=1214351911863783
WAIT #2: nam='db file scattered read' ela= 84943 file#=12 block#=5899 blocks=126 obj#=62725 tim=1214351911951679
WAIT #2: nam='db file scattered read' ela= 84879 file#=12 block#=6027 blocks=126 obj#=62725 tim=1214351912039312
WAIT #2: nam='db file scattered read' ela= 84608 file#=12 block#=6155 blocks=126 obj#=62725 tim=1214351912127094
WAIT #2: nam='db file scattered read' ela= 85062 file#=12 block#=6283 blocks=126 obj#=62725 tim=1214351912214894
WAIT #2: nam='db file scattered read' ela= 84816 file#=12 block#=6411 blocks=126 obj#=62725 tim=1214351912302607
WAIT #2: nam='db file scattered read' ela= 85147 file#=12 block#=6539 blocks=126 obj#=62725 tim=1214351912390407
WAIT #2: nam='db file scattered read' ela= 85150 file#=12 block#=6667 blocks=126 obj#=62725 tim=1214351912478262
WAIT #2: nam='db file scattered read' ela= 85169 file#=12 block#=6795 blocks=126 obj#=62725 tim=1214351912566093
WAIT #2: nam='db file scattered read' ela= 85125 file#=12 block#=6923 blocks=126 obj#=62725 tim=1214351912654005
WAIT #2: nam='db file scattered read' ela= 84563 file#=12 block#=7051 blocks=126 obj#=62725 tim=1214351912741721
WAIT #2: nam='db file scattered read' ela= 84846 file#=12 block#=7179 blocks=126 obj#=62725 tim=1214351912829444
WAIT #2: nam='db file scattered read' ela= 84862 file#=12 block#=7307 blocks=126 obj#=62725 tim=1214351912917050
WAIT #2: nam='db file scattered read' ela= 84933 file#=12 block#=7435 blocks=126 obj#=62725 tim=1214351913004811
WAIT #2: nam='db file scattered read' ela= 84942 file#=12 block#=7563 blocks=126 obj#=62725 tim=1214351913092502
WAIT #2: nam='db file scattered read' ela= 85132 file#=12 block#=7691 blocks=126 obj#=62725 tim=1214351913180347
WAIT #2: nam='db file scattered read' ela= 85049 file#=12 block#=7819 blocks=126 obj#=62725 tim=1214351913268080
WAIT #2: nam='db file scattered read' ela= 84877 file#=12 block#=7947 blocks=126 obj#=62725 tim=1214351913355804
WAIT #2: nam='db file scattered read' ela= 84951 file#=12 block#=8075 blocks=126 obj#=62725 tim=1214351913443520
WAIT #2: nam='db file scattered read' ela= 85081 file#=12 block#=8203 blocks=126 obj#=62725 tim=1214351913531319
WAIT #2: nam='db file scattered read' ela= 85028 file#=12 block#=8331 blocks=126 obj#=62725 tim=1214351913619047
WAIT #2: nam='db file scattered read' ela= 84962 file#=12 block#=8459 blocks=126 obj#=62725 tim=1214351913706849
WAIT #2: nam='db file scattered read' ela= 84855 file#=12 block#=8587 blocks=126 obj#=62725 tim=1214351913794578
WAIT #2: nam='db file scattered read' ela= 85190 file#=12 block#=8715 blocks=126 obj#=62725 tim=1214351913882474
WAIT #2: nam='db file scattered read' ela= 84566 file#=12 block#=8843 blocks=126 obj#=62725 tim=1214351913970201
WAIT #2: nam='db file scattered read' ela= 85003 file#=12 block#=8971 blocks=126 obj#=62725 tim=1214351914057894
WAIT #2: nam='db file scattered read' ela= 85174 file#=12 block#=9099 blocks=126 obj#=62725 tim=1214351914145869
WAIT #2: nam='db file scattered read' ela= 84784 file#=12 block#=9227 blocks=126 obj#=62725 tim=1214351914233373
WAIT #2: nam='db file scattered read' ela= 85096 file#=12 block#=9355 blocks=126 obj#=62725 tim=1214351914321154
WAIT #2: nam='db file scattered read' ela= 85037 file#=12 block#=9483 blocks=126 obj#=62725 tim=1214351914408912
WAIT #2: nam='db file scattered read' ela= 85018 file#=12 block#=9611 blocks=126 obj#=62725 tim=1214351914496710
WAIT #2: nam='db file scattered read' ela= 84800 file#=12 block#=9739 blocks=126 obj#=62725 tim=1214351914584616
WAIT #2: nam='db file scattered read' ela= 84961 file#=12 block#=9867 blocks=126 obj#=62725 tim=1214351914672285
WAIT #2: nam='db file scattered read' ela= 84974 file#=12 block#=9995 blocks=126 obj#=62725 tim=1214351914759998
WAIT #2: nam='db file scattered read' ela= 87049 file#=12 block#=10123 blocks=126 obj#=62725 tim=1214351914849805
WAIT #2: nam='db file scattered read' ela= 84919 file#=12 block#=10251 blocks=126 obj#=62725 tim=1214351914937507
WAIT #2: nam='db file scattered read' ela= 85094 file#=12 block#=10379 blocks=126 obj#=62725 tim=1214351915025324
WAIT #2: nam='db file scattered read' ela= 93487 file#=12 block#=10507 blocks=126 obj#=62725 tim=1214351915121494
WAIT #2: nam='db file scattered read' ela= 76708 file#=12 block#=10635 blocks=126 obj#=62725 tim=1214351915200896
WAIT #2: nam='db file scattered read' ela= 84945 file#=12 block#=10763 blocks=126 obj#=62725 tim=1214351915288673
WAIT #2: nam='db file scattered read' ela= 85123 file#=12 block#=10891 blocks=126 obj#=62725 tim=1214351915376507
WAIT #2: nam='db file scattered read' ela= 85206 file#=12 block#=11019 blocks=126 obj#=62725 tim=1214351915464493
WAIT #2: nam='db file scattered read' ela= 85099 file#=12 block#=11147 blocks=126 obj#=62725 tim=1214351915552304
WAIT #2: nam='db file scattered read' ela= 89897 file#=12 block#=11275 blocks=126 obj#=62725 tim=1214351915645016
WAIT #2: nam='db file scattered read' ela= 85050 file#=12 block#=11403 blocks=126 obj#=62725 tim=1214351915732738
WAIT #2: nam='db file scattered read' ela= 85161 file#=12 block#=11531 blocks=126 obj#=62725 tim=1214351915820585
WAIT #2: nam='db file scattered read' ela= 85294 file#=12 block#=11659 blocks=126 obj#=62725 tim=1214351915908549
WAIT #2: nam='db file scattered read' ela= 84876 file#=12 block#=11787 blocks=126 obj#=62725 tim=1214351915996265
WAIT #2: nam='db file scattered read' ela= 84860 file#=12 block#=11915 blocks=126 obj#=62725 tim=1214351916083966
WAIT #2: nam='db file scattered read' ela= 85020 file#=12 block#=12043 blocks=126 obj#=62725 tim=1214351916171686
WAIT #2: nam='db file scattered read' ela= 84694 file#=12 block#=12170 blocks=127 obj#=62725 tim=1214351916259509
WAIT #2: nam='db file scattered read' ela= 87660 file#=12 block#=12301 blocks=128 obj#=62725 tim=1214351916349970
WAIT #2: nam='db file scattered read' ela= 85014 file#=12 block#=12429 blocks=128 obj#=62725 tim=1214351916437808
WAIT #2: nam='db file scattered read' ela= 84923 file#=12 block#=12557 blocks=128 obj#=62725 tim=1214351916525495
WAIT #2: nam='db file scattered read' ela= 84929 file#=12 block#=12685 blocks=128 obj#=62725 tim=1214351916613175
WAIT #2: nam='db file scattered read' ela= 84899 file#=12 block#=12813 blocks=128 obj#=62725 tim=1214351916701065
WAIT #2: nam='db file scattered read' ela= 84868 file#=12 block#=12941 blocks=128 obj#=62725 tim=1214351916788797
WAIT #2: nam='db file scattered read' ela= 85047 file#=12 block#=13069 blocks=128 obj#=62725 tim=1214351916876575
WAIT #2: nam='db file scattered read' ela= 82152 file#=12 block#=13197 blocks=124 obj#=62725 tim=1214351916961500
WAIT #2: nam='db file scattered read' ela= 87154 file#=12 block#=13325 blocks=128 obj#=62725 tim=1214351917052060
WAIT #2: nam='db file scattered read' ela= 84967 file#=12 block#=13453 blocks=128 obj#=62725 tim=1214351917139905
WAIT #2: nam='db file scattered read' ela= 84905 file#=12 block#=13581 blocks=128 obj#=62725 tim=1214351917227592
WAIT #2: nam='db file scattered read' ela= 85613 file#=12 block#=13709 blocks=128 obj#=62725 tim=1214351917315952
WAIT #2: nam='db file scattered read' ela= 85091 file#=12 block#=13837 blocks=128 obj#=62725 tim=1214351917403834
WAIT #2: nam='db file scattered read' ela= 86421 file#=12 block#=13965 blocks=128 obj#=62725 tim=1214351917493241
WAIT #2: nam='db file scattered read' ela= 85098 file#=12 block#=14093 blocks=128 obj#=62725 tim=1214351917581064
WAIT #2: nam='db file scattered read' ela= 82232 file#=12 block#=14221 blocks=124 obj#=62725 tim=1214351917666031
WAIT #2: nam='db file scattered read' ela= 87473 file#=12 block#=14349 blocks=128 obj#=62725 tim=1214351917756500
WAIT #2: nam='db file scattered read' ela= 87166 file#=12 block#=14477 blocks=128 obj#=62725 tim=1214351917846538
WAIT #2: nam='db file scattered read' ela= 84952 file#=12 block#=14605 blocks=128 obj#=62725 tim=1214351917934223
WAIT #2: nam='db file scattered read' ela= 85422 file#=12 block#=14733 blocks=128 obj#=62725 tim=1214351918022378
WAIT #2: nam='db file scattered read' ela= 84502 file#=12 block#=14861 blocks=128 obj#=62725 tim=1214351918110063
WAIT #2: nam='db file scattered read' ela= 84870 file#=12 block#=14989 blocks=128 obj#=62725 tim=1214351918197783
WAIT #2: nam='db file scattered read' ela= 84973 file#=12 block#=15117 blocks=128 obj#=62725 tim=1214351918285618
WAIT #2: nam='db file scattered read' ela= 82231 file#=12 block#=15245 blocks=124 obj#=62725 tim=1214351918370666
WAIT #2: nam='db file scattered read' ela= 88454 file#=12 block#=15373 blocks=128 obj#=62725 tim=1214351918461802
WAIT #2: nam='db file scattered read' ela= 84889 file#=12 block#=15501 blocks=128 obj#=62725 tim=1214351918549513
WAIT #2: nam='db file scattered read' ela= 85044 file#=12 block#=15629 blocks=128 obj#=62725 tim=1214351918637377
WAIT #2: nam='db file scattered read' ela= 85250 file#=12 block#=15757 blocks=128 obj#=62725 tim=1214351918725354
WAIT #2: nam='db file scattered read' ela= 84693 file#=12 block#=15885 blocks=128 obj#=62725 tim=1214351918812915
WAIT #2: nam='db file scattered read' ela= 85098 file#=12 block#=16013 blocks=128 obj#=62725 tim=1214351918900749
WAIT #2: nam='db file scattered read' ela= 85060 file#=12 block#=16141 blocks=128 obj#=62725 tim=1214351918988614
WAIT #2: nam='db file scattered read' ela= 82203 file#=12 block#=16269 blocks=124 obj#=62725 tim=1214351919073551
WAIT #2: nam='db file scattered read' ela= 87833 file#=12 block#=16397 blocks=128 obj#=62725 tim=1214351919164104
WAIT #2: nam='db file scattered read' ela= 84953 file#=12 block#=16525 blocks=128 obj#=62725 tim=1214351919251812
WAIT #2: nam='db file scattered read' ela= 84994 file#=12 block#=16653 blocks=128 obj#=62725 tim=1214351919339701
WAIT #2: nam='db file scattered read' ela= 84984 file#=12 block#=16781 blocks=128 obj#=62725 tim=1214351919427846
WAIT #2: nam='db file scattered read' ela= 85027 file#=12 block#=16909 blocks=128 obj#=62725 tim=1214351919515687
WAIT #2: nam='db file scattered read' ela= 85656 file#=12 block#=17037 blocks=128 obj#=62725 tim=1214351919604120
WAIT #2: nam='db file scattered read' ela= 84927 file#=12 block#=17165 blocks=128 obj#=62725 tim=1214351919691946
WAIT #2: nam='db file scattered read' ela= 81989 file#=12 block#=17293 blocks=124 obj#=62725 tim=1214351919776786
WAIT #2: nam='db file scattered read' ela= 131724 file#=12 block#=13 blocks=128 obj#=62725 tim=1214351919911211
WAIT #2: nam='db file scattered read' ela= 85492 file#=12 block#=141 blocks=128 obj#=62725 tim=1214351919999413
WAIT #2: nam='db file scattered read' ela= 84817 file#=12 block#=269 blocks=128 obj#=62725 tim=1214351920087174
WAIT #2: nam='db file scattered read' ela= 84424 file#=12 block#=397 blocks=128 obj#=62725 tim=1214351920174816
WAIT #2: nam='db file scattered read' ela= 84965 file#=12 block#=525 blocks=128 obj#=62725 tim=1214351920262538
*** 2009-05-28 07:39:26.438
WAIT #2: nam='db file scattered read' ela= 85025 file#=12 block#=653 blocks=128 obj#=62725 tim=1214351920350373
WAIT #2: nam='db file scattered read' ela= 85233 file#=12 block#=781 blocks=128 obj#=62725 tim=1214351920438458
WAIT #2: nam='db file scattered read' ela= 82161 file#=12 block#=909 blocks=124 obj#=62725 tim=1214351920523410
WAIT #2: nam='db file scattered read' ela= 89403 file#=12 block#=1037 blocks=128 obj#=62725 tim=1214351920615466
WAIT #2: nam='db file scattered read' ela= 85092 file#=12 block#=1165 blocks=128 obj#=62725 tim=1214351920703258
WAIT #2: nam='db file scattered read' ela= 86077 file#=12 block#=1293 blocks=128 obj#=62725 tim=1214351920792731
WAIT #2: nam='db file scattered read' ela= 85274 file#=12 block#=1421 blocks=128 obj#=62725 tim=1214351920880847
WAIT #2: nam='db file scattered read' ela= 84926 file#=12 block#=1549 blocks=128 obj#=62725 tim=1214351920968580
WAIT #2: nam='db file scattered read' ela= 85055 file#=12 block#=1677 blocks=128 obj#=62725 tim=1214351921056417
WAIT #2: nam='db file scattered read' ela= 84898 file#=12 block#=1805 blocks=128 obj#=62725 tim=1214351921144107
WAIT #2: nam='db file scattered read' ela= 82178 file#=12 block#=1933 blocks=124 obj#=62725 tim=1214351921229184
WAIT #2: nam='db file scattered read' ela= 89427 file#=12 block#=2061 blocks=128 obj#=62725 tim=1214351921321285
WAIT #2: nam='db file scattered read' ela= 85163 file#=12 block#=2189 blocks=128 obj#=62725 tim=1214351921409182
WAIT #2: nam='db file scattered read' ela= 85052 file#=12 block#=2317 blocks=128 obj#=62725 tim=1214351921497029
WAIT #2: nam='db file scattered read' ela= 84847 file#=12 block#=2445 blocks=128 obj#=62725 tim=1214351921584852
WAIT #2: nam='db file scattered read' ela= 84866 file#=12 block#=2573 blocks=128 obj#=62725 tim=1214351921672513
WAIT #2: nam='db file scattered read' ela= 85162 file#=12 block#=2701 blocks=128 obj#=62725 tim=1214351921760449
WAIT #2: nam='db file scattered read' ela= 85226 file#=12 block#=2829 blocks=128 obj#=62725 tim=1214351921848473
WAIT #2: nam='db file scattered read' ela= 81282 file#=12 block#=2957 blocks=124 obj#=62725 tim=1214351921933222
WAIT #2: nam='db file scattered read' ela= 88830 file#=12 block#=3084 blocks=128 obj#=62725 tim=1214351922024750
WAIT #2: nam='db file scattered read' ela= 85072 file#=12 block#=3212 blocks=128 obj#=62725 tim=1214351922112530
WAIT #2: nam='db file scattered read' ela= 88051 file#=12 block#=3340 blocks=128 obj#=62725 tim=1214351922203314
WAIT #2: nam='db file scattered read' ela= 84971 file#=12 block#=3468 blocks=128 obj#=62725 tim=1214351922291095
WAIT #2: nam='db file scattered read' ela= 85209 file#=12 block#=3596 blocks=128 obj#=62725 tim=1214351922379076
WAIT #2: nam='db file scattered read' ela= 84185 file#=12 block#=3724 blocks=125 obj#=62725 tim=1214351922466023
WAIT #2: nam='db file scattered read' ela= 12697 file#=12 block#=17421 blocks=95 obj#=62725 tim=1214351922481138
FETCH #2:c=701893,e=11938796,p=17078,cr=17093,cu=0,mis=0,r=1,dep=0,og=1,tim=1214351922482897
WAIT #2: nam='SQL*Net message from client' ela= 273 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922483316
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214351922483375
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922483413
WAIT #2: nam='SQL*Net message from client' ela= 560 driver id=1650815232 #bytes=1 p3=0 obj#=62725 tim=1214351922484003
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=17093 pr=17078 pw=0 time=11938810 us)'
STAT #2 id=2 cnt=1238432 pid=1 pos=1 obj=62725 op='TABLE ACCESS FULL T1 (cr=17093 pr=17078 pw=0 time=23537745 us)'
=====================

一开始读7/8块, 读16次
后来读126/127块, 63次
最后读128块, 8次7*128+124, 1次5*128+125+95,
这跟盘区信息是相符的

BYTES/1024   COUNT(*)
---------- ----------
        64         16
      1024         63
      6144          1
      8192          9

不能解释为什么有时读取的块数不到dfmbrc, 而是5,7,124,127?
为什么读1m大小的盘区时, 只读126块?

重建表, 表空间是统一尺寸分配, 盘区大小为100m

conn / as sysdba
create tablespace ts_uniform_100m
 datafile '/home/oracle/app/oracle/oradata/test/ts_uniform_100m_01.dbf' size 200m
  autoextend on next 100m maxsize 500m
 extent management local uniform size 100m
/

conn a/a
drop table t2;
create table t2 tablespace ts_uniform_100m
 as select * from all_objects;
insert /*+ append */ into t2 select * from t2;
commit;
insert /*+ append */ into t2 select * from t2;
commit;
insert /*+ append */ into t2 select * from t2;
commit;

查看盘区信息

select bytes/1024, count(*)
  from user_extents
 where segment_name = 'T2'
 group by bytes/1024
 order by 1
/

BYTES/1024   COUNT(*)
---------- ----------
    102400          1

全部记录都处于1个100m的盘区内

设置dfmbrc=最大, 查看跟踪文件

conn / as sysdba
alter tablespace ts_uniform_100m offline;
alter tablespace ts_uniform_100m online;
conn a/a
alter session set optimizer_dynamic_sampling=0;
alter session set db_file_multiblock_read_count=4096;
alter session set events '10046 trace name context forever, level 12';
select /*+ FULL(t2) */ count(*) from t2;
alter session set events '10046 trace name context off';

跟踪文件

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=48 oct=3 lid=48 tim=1214353316531035 hv=2934957608 ad='789b3710'
select /*+ FULL(t2) */ count(*) from t2
END OF STMT
PARSE #2:c=2999,e=2167,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1214353316531022
BINDS #2:
EXEC #2:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1214353316531214
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1214353316531254
WAIT #2: nam='db file sequential read' ela= 3545 file#=11 block#=60 blocks=1 obj#=62731 tim=1214353316536247
WAIT #2: nam='db file scattered read' ela= 88963 file#=11 block#=61 blocks=128 obj#=62731 tim=1214353316626836
WAIT #2: nam='db file scattered read' ela= 91860 file#=11 block#=189 blocks=128 obj#=62731 tim=1214353316722135
WAIT #2: nam='db file scattered read' ela= 89679 file#=11 block#=317 blocks=128 obj#=62731 tim=1214353316814880
WAIT #2: nam='db file scattered read' ela= 91533 file#=11 block#=445 blocks=128 obj#=62731 tim=1214353316909059
WAIT #2: nam='db file scattered read' ela= 85837 file#=11 block#=573 blocks=128 obj#=62731 tim=1214353316997722
WAIT #2: nam='db file scattered read' ela= 84681 file#=11 block#=701 blocks=128 obj#=62731 tim=1214353317085472
WAIT #2: nam='db file scattered read' ela= 85087 file#=11 block#=829 blocks=128 obj#=62731 tim=1214353317173302
WAIT #2: nam='db file scattered read' ela= 87264 file#=11 block#=957 blocks=128 obj#=62731 tim=1214353317263270
WAIT #2: nam='db file scattered read' ela= 85293 file#=11 block#=1085 blocks=128 obj#=62731 tim=1214353317351353
WAIT #2: nam='db file scattered read' ela= 83874 file#=11 block#=1213 blocks=128 obj#=62731 tim=1214353317438788
WAIT #2: nam='db file scattered read' ela= 87989 file#=11 block#=1341 blocks=128 obj#=62731 tim=1214353317529645
WAIT #2: nam='db file scattered read' ela= 84940 file#=11 block#=1469 blocks=128 obj#=62731 tim=1214353317617455
WAIT #2: nam='db file scattered read' ela= 85153 file#=11 block#=1597 blocks=128 obj#=62731 tim=1214353317705479
WAIT #2: nam='db file scattered read' ela= 84574 file#=11 block#=1725 blocks=128 obj#=62731 tim=1214353317793068
WAIT #2: nam='db file scattered read' ela= 84848 file#=11 block#=1853 blocks=128 obj#=62731 tim=1214353317880767
WAIT #2: nam='db file scattered read' ela= 84992 file#=11 block#=1981 blocks=128 obj#=62731 tim=1214353317968596
WAIT #2: nam='db file scattered read' ela= 84968 file#=11 block#=2109 blocks=128 obj#=62731 tim=1214353318056353
WAIT #2: nam='db file scattered read' ela= 84690 file#=11 block#=2237 blocks=128 obj#=62731 tim=1214353318144047
WAIT #2: nam='db file scattered read' ela= 88591 file#=11 block#=2365 blocks=128 obj#=62731 tim=1214353318235508
WAIT #2: nam='db file scattered read' ela= 85007 file#=11 block#=2493 blocks=128 obj#=62731 tim=1214353318323290
WAIT #2: nam='db file scattered read' ela= 84633 file#=11 block#=2621 blocks=128 obj#=62731 tim=1214353318411145
WAIT #2: nam='db file scattered read' ela= 85179 file#=11 block#=2749 blocks=128 obj#=62731 tim=1214353318499112
WAIT #2: nam='db file scattered read' ela= 84844 file#=11 block#=2877 blocks=128 obj#=62731 tim=1214353318586863
WAIT #2: nam='db file scattered read' ela= 85204 file#=11 block#=3005 blocks=128 obj#=62731 tim=1214353318674810
WAIT #2: nam='db file scattered read' ela= 84841 file#=11 block#=3133 blocks=128 obj#=62731 tim=1214353318762359
WAIT #2: nam='db file scattered read' ela= 84941 file#=11 block#=3261 blocks=128 obj#=62731 tim=1214353318850057
WAIT #2: nam='db file scattered read' ela= 84890 file#=11 block#=3389 blocks=128 obj#=62731 tim=1214353318937820
WAIT #2: nam='db file scattered read' ela= 85100 file#=11 block#=3517 blocks=128 obj#=62731 tim=1214353319025678
WAIT #2: nam='db file scattered read' ela= 84955 file#=11 block#=3645 blocks=128 obj#=62731 tim=1214353319113389
WAIT #2: nam='db file scattered read' ela= 85105 file#=11 block#=3773 blocks=128 obj#=62731 tim=1214353319201352
WAIT #2: nam='db file scattered read' ela= 84542 file#=11 block#=3901 blocks=128 obj#=62731 tim=1214353319288753
WAIT #2: nam='db file scattered read' ela= 84724 file#=11 block#=4029 blocks=128 obj#=62731 tim=1214353319376214
WAIT #2: nam='db file scattered read' ela= 85027 file#=11 block#=4157 blocks=128 obj#=62731 tim=1214353319464026
WAIT #2: nam='db file scattered read' ela= 30083 file#=11 block#=4285 blocks=47 obj#=62731 tim=1214353319496682
FETCH #2:c=197970,e=2966232,p=4272,cr=4276,cu=0,mis=0,r=1,dep=0,og=1,tim=1214353319497521
WAIT #2: nam='SQL*Net message from client' ela= 313 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319497972
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1214353319498033
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319498072
WAIT #2: nam='SQL*Net message from client' ela= 565 driver id=1650815232 #bytes=1 p3=0 obj#=62731 tim=1214353319498664
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4276 pr=4272 pw=0 time=2966237 us)'
STAT #2 id=2 cnt=309616 pid=1 pos=1 obj=62731 op='TABLE ACCESS FULL T2 (cr=4276 pr=4272 pw=0 time=405861 us)'
=====================

这次没有出现124块的情况, 除了第一次(为什么?)和最后一次不是128块. 第1次只读了1块,好像每次查询都这样


4. 其它参数
_db_file_exec_read_count 和 _db_file_optimizer_read_count
_db_file_exec_read_count在多数据块读运行时使用
_db_file_optimizer_read_count则由优化器使用
修改db_file_multiblock_read_count参数后, 会自动修改这2个参数

col parameter for a40
col session_value for a20
col instance_value for a20
select substr(a.ksppinm,1,40) parameter,
       substr(b.ksppstvl,1,20) session_value,
       c.ksppstvl instance_value
  from x$ksppi a, x$ksppcv b, x$ksppsv c
 where a.indx = b.indx and a.indx = c.indx
   and a.ksppinm like '%db_file%';

PARAMETER                                SESSION_VALUE        INSTANCE_VALUE
---------------------------------------- -------------------- --------------------
_db_file_direct_io_count                 1048576              1048576
db_file_name_convert
db_files                                 200                  200
db_file_multiblock_read_count            128                  16
_db_file_exec_read_count                 128                  16
_db_file_optimizer_read_count            128                  16
_db_file_noncontig_mblock_read_count     11                   11
_db_file_format_io_buffers               4                    4

8 rows selected.

5. 其它参考资料
'DB_FILE_MULTIBLOCK_READ_COUNT' AND EXTENTS MANAGEMENT

Blocks read using index vs. full table scan
db_file_multiblock_read_count and different read rates
----begin----
Followup:
the table isn't full. we only read what we need -- and some of it can be in the buffer cache
(hence we won't read that necessarily).

It "looks" like you are using 3 of the 5 extents to hold data (this is probably, not 100%, caching
can affect this).

extent 1: blocks 867 .. 1042 (128+47)
extent 2: blocks 1302 .. 1562 (128+128+4)
extent 3: blocks 2082 .. 2249 (128+39)

the other two extents have nothing, two of the three that do have data are not full.
----end----

Sizing Extents for Performance

WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER?
How To Unset DB_FILE_MULTIBLOCK_READ_COUNT to Default Value?
Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows)
Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows)





-fin-