QUESTION 1
Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)
A. Cursor cache
B. Stored outline
C. SQL Tuning Set
D. Automatic Workload Repository (AWR) snapshots
Answer: AC
解析： AWR snapshot 需要先導(dǎo)入到sql tuning set;
To load plans from a SQL tuning set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package. To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before using the LOAD_PLANS_FROM_SQLSET function as described in this section. To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package.
QUESTION 2
Which two statements about workload capture and replay operations are true? (Choose two.)
A. The clients must be created manually on the test machines to perform more realistic testing.
B. Restart the database in RESTRICTED mode before beginning workload replay to enable a clean state for workload replay.
C. Restart the database in RESTRICTED mode before beginning workload capture to enable a clean state for capturing the workload.
D. The application state of the capture system must be identical to the application state of the replay system when the workload replay begins.
Answer: CD
QUESTION 3
View the Exhibit exhibit1 to examine the series of SQL commands
View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.
The baseline in the first row of the Exhibit is created when OPTIMIZER_MODE was set to FIRST_ROWS.
Which statement is true if the SQL query in exhibit1 is executed again when the value of
OPTIMIZER_MODE is set to FIRST_ROWS?
A. The optimizer uses a new plan because none of the plans in the exhibit2 are fixed plans.
B. The optimizer uses the plan in the second row of the exhibit2 because it is an accepted plan.
C. The optimizer uses the plan in the first row of the exhibit2 because it is the latest generated plan.
D. The optimizer uses the plan in the first row of the exhibit2 because OPTIMIZER_MODE was set to
FIRST_ROW during its creation.
Answer: B
解析：Oracle 在執(zhí)行SQL語句時，有兩種優(yōu)化方法：即基于規(guī)則的RBO和基于代價的CBO赴涵。 在SQL執(zhí)教的時候媒怯，到底采用何種優(yōu)化方法，就由Oracle參數(shù) optimizer_mode 來決定髓窜。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
optimizer_mode string CHOOSE
optimizer_mode 參數(shù)值共有以下四個：
第一：CHOOSE
這個是Oracle的默認(rèn)值扇苞。采用這個值時，Oracle即可以采用基于規(guī)則RBO寄纵，也可以采用基于代價的CBO鳖敷，到底使用那個值，取決于當(dāng)前SQL的被訪問的表中是不是有可以使用的統(tǒng)計信息擂啥。
如果有多個被訪問的表哄陶，其中有一個或多個有統(tǒng)計信息，那么Oralce會對沒有統(tǒng)計信息的表進行采樣統(tǒng)計（即不全部采樣）哺壶，統(tǒng)計完成后屋吨，使用基于代價的優(yōu)化方法CBO。
如果所有被訪問的表都沒有統(tǒng)計信息山宾，Oracle就會采用基于規(guī)則的優(yōu)化方法RBO至扰。
第二：ALL_ROWS
不管是不是有統(tǒng)計信息，全部采用基于成本的優(yōu)化方法CBO资锰。
第三：FIRST_ROWS_n
FIRST_ROWS_n后面的n值可以為1,100,1000
不管是不是有統(tǒng)計信息敢课，全部采用基于成本的優(yōu)化方法CBO，并以最快的速度，返回前N行記錄直秆。
第四：FIRST_ROWS
使用成本和試探法相結(jié)合的方法濒募，查找一種可以最快返回前面少數(shù)行的方法；這個參數(shù)主要用于向后兼容圾结。
第五：RULE
這個參數(shù)正好和ALL_ROWS相反瑰剃，不管是不是統(tǒng)計信息，全部采用基于規(guī)則的優(yōu)化方法筝野。
如何更改 optimizer_mode 的參數(shù)呢晌姚？可以用以下的方法。
SQL> alter session set optimizer_mode='RULE';
會話已更改歇竟。
QUESTION 4
Which statement describes the effect of table redefinition on the triggers attached to the table?
A. All triggers on the table remain valid.
B. All triggers on the table are invalidated and are automatically revalidated with the next DML execution on
the table.
C. All triggers on the table are invalidated and must be manually recompiled before the next DML execution
on the table.
D. Only triggers that are affected by the changes to the structure of the table are invalidated and
automatically revalidated with the next DML execution on the table.
Answer: B
參考
QUESTION 5
USER_DATA is a nonencrypted tablespace that contains a set of tables with data. You want to convert all existing data in the USER_DATA tablespace and the new data into the encrypted format. Which methods would you use to achieve this? (Choose all that apply.)
A. Use Data Pump to transfer the existing data to a new encrypted tablespace.
B. Use ALTER TABLE..MOVE to transfer the existing data to a new encrypted tablespace.
C. Use CREATE TABLE AS SELECT to transfer the existing data to a new encrypted tablespace.
D. Enable row movement for each table to be encrypted and then use ALTER TABLESPACE to encrypt the tablespace.
E. Encrypt the USER_DATA tablespace using the ALTER TABLESPACE statement so that all the data in the tablespace is automatically encrypted.
Answer: ABC
QUESTION 6
Evaluate the following block of code:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (acl => 'mycompany-com-permissions.xml',
principal => 'ACCT_MGR',
is_grant => TRUE,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'mycompany-com-permissions.xml',
host => '*.mycompany.com');
END;
What is the outcome of the above code?
A. It produces an error because a fully qualified host name needs to be specified.
B. It produces an error because the range of ports associated with the hosts has not been specified.
C. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT and RESOLVE privileges.
D. It creates an access control list (ACL) with the user ACCT_MGR who gets the CONNECT privilege but not the RESOLVE privilege.
Answer: C
參考：http://blog.csdn.net/rlhua/article/details/15029727
創(chuàng)建訪問控制列表(ACL)挥唠。
ACL 是在XML 文件中保存的用戶和權(quán)限的列表。以acl參數(shù)命名的XML 文檔與XML DB中的/sys/acl/文件夾相關(guān)焕议。在題中例中宝磨，ACCT_MGR被授予connect權(quán)限。ACL 中的用戶名區(qū)分大小寫盅安，而且必須與會話的用戶名相匹配懊烤。只有resolve和connect權(quán)限，connect權(quán)限包含resolve權(quán)限宽堆。可選參數(shù)可以指定這些權(quán)限的開始和結(jié)束時間戳茸习。要將更多用戶和權(quán)限添加到此ACL 中畜隶，可使用ADD_PRIVILEGE過程。
QUESTION 7
Which steps are mandatory to enable Direct NFS?
Mount all required file systems using the kernel NFS driver.
Create an oranfstab file containing the attributes for each NFS server to be accessed using Direct NFS.
Replace the ODM library libodm11.so_stub with libodm11.so. A. 2 and 3
B. 1 and 3
C. 1 and 2
D. 1, 2 and 3
Answer: B
解析：
參考：http://czmmiao.iteye.com/blog/1965839
Enabling Direct NFS
You must follow these steps to enable Direct NFS:
- You must mount all NFS mount points with your kernel NFS client. You must make sure you mount any file systems you plan on using through ODM NFS and make the file systems available to Oracle over regular NFS mounts.
- If you want to specify Oracle-specific options to Direct NFS, you’ll need an oranfstab file. This is an optional step. The oranfstab file must have the following attributes so the database can access all NFS servers through Direct NFS:
- You must replace the standard ODM library, libnfsodm10.so, with the ODM NFS library, as shown here:
$ cd $ORACLE_HOME/lib
$ cp libodmll.so libodmll.so_stub
$ ln -s libnfsodm11.so libodm11.so
You can disable the Direct NFS client by using any of the following three methods.
Delete the oranfstab file.
Replace the ODM NFS library with the stub libodm11.so file.
Modify the oranfstab file by deleting the specific NFS server or the network paths to the NFS server.
If the database can’t open the NFS server using Direct NFS, it will use the operating system kernel client instead.
QUESTION 8
Which statements are true regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database 11g? (Choose all that apply.)
A. The problem key is the same as the incident number.
B. Every problem has a problem key, which is a text string that describes the problem.
C. The database makes an entry into the alert log file when problems and incidents occur.
D. The database sends an incident alert to the Oracle Enterprise Manager Database Home page.
E. Only the incident metadata and dumps are stored in the Automatic Diagnostic Repository (ADR).
Answer: BCD
解析：
http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag001.htm#ADMIN11008
QUESTION 9
You are managing an Oracle Database 11g database. You want to take a backup on tape drives of the USERS tablespace that has a single data file of 900 MB. You have tape drives of 300 MB each. To accomplish the backup, you issued the following RMAN command:
RMAN>BACKUP
SECTION SIZE 300M
TABLESPACE users;
What configuration should be effected to accomplish faster and optimized backups by using the above command?
A. The SBT channel must be configured, with the MAXPIECESIZE set to 300 MB.
B. The SBT channel must be configured, with the parallelism（平行） setting for the SBT device set to 3.
C. The COMPATIBLE initialization parameter for the database instance must be set to at least 10.0.
D. The SBT channel must be configured, with the default parallelism setting for the SBT device set to 1.
Answer: B
解析：
參考：
根據(jù)Note: You cannot use SECTION SIZE with MAXPIECESIZE or with INCREMENTAL LEVEL 1.這句話号胚，說明A錯誤
根據(jù)RMAN channels can process each step independently and in parallel籽慢，應(yīng)該選擇B，可以并行備份猫胁。
Oracle 數(shù)據(jù)文件最大為128 TB箱亿。
在以前的版本中，RMAN 備份的最小單位是整個文件弃秆。這對于如此大的文件不適用届惋。在Oracle Database 11g中，將在多個并行服務(wù)器進程之間分配各個文件的工作量菠赚。如果指定了SECTION SIZE選項脑豹，則RMAN 可以將一個大文件分解為多個部分，然后獨立備份和還原這些部分衡查。
換句話說瘩欺，RMAN 可以對每個文件使用多個通道。每個通道備份一個文件部分。每個文件部分都是文件中一系列連續(xù)的塊俱饿「柚啵可按串行或并行方式獨立處理每個文件部分。以多個單獨部分的形式備份文件拍埠，既可以提高性能又可以重新啟動大型文件備份失驶。多部分備份作業(yè)可生成一個多片段備份集。每個片段都包含一個文件部分械拍。多部分備份的所有部分的大小都相同（最后一部分可能除外）突勇。每個文件最多有256 個部分。提示：在備份駐留在少量磁盤上的大型文件時坷虑，不宜使用很高的并行度甲馋。此功能已內(nèi)置到RMAN 中。除Oracle Database 11g的常規(guī)安裝外迄损，不需要其它安裝定躏。因為早期版本無法還原多部分備份，所以必須至少將COMPATIBLE設(shè)置為11.0芹敌。
QUESTION 10
When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics.
Which two statements describe the implications of this? (Choose two.)
A. It produces less accurate results of the comparison analysis.
B. It automatically calls the SQL Tuning Advisor for recommendations.
C. It shortens the time of execution and reduces the impact on system resources.
D. Only the changes in the execution plan, and not performance regression, are detected.
Answer: AC
