Your database instance is started using an SPFILE. You are connected to cdb$root, as a
DBA . You issue:
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;
Which two statements are true about the statistics level parameter?
A.
It is immediately set to all in the SPFILE and the CDB instance.
B.
It is immediately set to all in only those pluggable databases (PDBs) where the value is set
to typical.
C.
It is immediately set to all only for cd3Sroot.
D.
It is immediately set to all in all PDBs where the statistics_level parameter is not set.
E.
It is set to all for all PDBs only in the SPFILE.
A and B are fine for me
A,D (tested)
Thanks Siegfried!
A, D tested too
SQL> show parameter statistic
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL>
SQL>
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;
System altered.
SQL> show parameter statistic
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
SQL> alter session set container=pdborcl;
Session altered.
SQL> show parameter statistic
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
SQL>
When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system-wide state is not changed:
So I would say A,D
AD
AD is correct
B is wrong.
pb1
SQL> show parameter statis
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=TYPICAL SCOPE=BOTH;
System altered.
SQL> show parameter statis
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
root
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;
System altered.
SQL> show parameter statis
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 60
timed_statistics boolean TRUE
pdb1
SQL> show parameter statis
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL>
AD