oracle ocp 052-153

 
   
  
QUESTION 153
In your database instance, the STATISTICS_LEVEL initialization parameter is set to BASIC.
What is the impact of this setting?
A. Optimizer statistics are collected automatically.
B. Only timed operating system (OS) statistics and plan execution statistics are collected.
C. The snapshots for the Automatic Workload Repository (AWR) are not generated automatically.
D. Snapshots cannot be collected manually by using the DBMS_WORKLOAD_REPOSITORY package.
E. The Oracle server dynamically generates the necessary statistics on tables as part of query optimization

解析：
statistics_level 引數是oracle9.2開始引入的一個控制系統統計引數收集的一個開關.一共有三個值:basic,typical,all.支援alter session,alter system 動態修改.如果要用statspack或者AWR收集系統效能統計資料.那麼這個引數的值必須為typical或all.通常all是一個全面收集,包括 OS以及sql執行路徑方面的一些統計資訊,除非遇見非常嚴重的效能問題或在一些特殊的效能掙斷方面才會用到statistics_level=all, 平常statistics_level=typeical已經足夠診斷99%的效能問題了.
example: seting statistics_level
 
alter system set statistics_level=basic;
alter system set statistics_level=typical;
alter system set statistics_level=all;
or
alter session set statistics_level=basic;
alter session set statistics_level=typical;
alter session set statistics_level=all;

statistics_level=basic的情況下,oracle關閉了所有效能資料的收集,也就是如果要關閉AWR或statspack收集,只要設定alter system set statistics_level=basic;就行了;
statistics_level=typical的時
候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集這個兩項,必須設定statistics_level=all;

如果當statistics_level=ALL,系統收集所有的統計資訊.