View the Exhibit and examine the partial output obtained when you issued the following query:
SQL> SELECT name,class,value FROM v$sysstat ORDER BY class;
Which two interpretations are correct about the information obtained from this view? (Choose two.)
A.
db block changes indicates the number of data blocks that have been changed.
B.
buffer is pinned count indicates the number of buffers that are currently pinned in the database
instance.
C.
Usage of CACHE and NOCACHE hints affects statistics for table scans (long tables) and table
scans (short tables).
D.
redo log spacerequests indicates the number of times a there were waits for disk space to be
allocated for the redo log entries.
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
FROM hr.employees hr_emp;
SELECT /*+ FULL (hr_emp) NOCACHE(hr_emp) */ last_name
FROM hr.employees hr_emp;
SELECT /*+ FULL (hr_emp)*/ last_name
FROM hr.employees hr_emp;
increase on every run
select name,value from v$sesstat natural join v$statname where sid=160 and name like ‘%short%’;
NAME | VALUE
—————————————————————-|———-
table scans (short tables) | 24
C and D
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm
The CACHE and NOCACHE hints affect system statistics table scans (long tables) and table scans (short tables), as shown in the V$SYSSTAT data dictionary view.
http://www.dba-oracle.com/m_redo_log_space_requests.htm
“The redo log space requests Oracle metric indicates the active log file is full and Oracle is waiting for disk space to be allocated for the redo log entries. Space is created by performing a log switch.
A is wrong. db block changes are related to update/delete that generates REDO, not all buffer block changes
B is wrong. buffer pinned count is the number of times that a buffer is pinned while being accessed, not number of buffers
C is right. CACHE make it short tables
D is right.