6-4 Performance Management - Gather statistics on a specific table without invalidating cursors

/* when gathering statistics using DBMS_STATS package, NO_INVALIDATE parameter can be used to not invalidate the cursors in the cache automatically. This parameter accepts 3 values: 1) TRUE doesn’t invalidate dependent cursors, 2) FALSE invalidate dependent cursors, 3) AUTO_INVALIDATE which is the default value that lets Oracle decide when to invalidate the cursors. */

 

 

 

난이도: ★★★☆☆

문제 1: shared pool을 flush하고, DBA_OBJECTS에 기반하여 새 테이블을 생성하고 table statistics를 gather하여라. 테이블을 두 번 쿼리하고 V$SQL 뷰에서 execution, invalidation 횟수를 추출하여라. 그리고 다시 한 번 statistics를 gather하고, cached cursor를 invalidate한 후에 V$SQL을 확인해 보아라. (15분)

 

SELECT dbms_stats.get_prefs('NO_INVALIDATE') FROM dual;

ALTER SYSTEM FLUSH SHARED_POOL;
CREATE TABLE tbl_invalidation_test
AS SELECT * FROM dba_objects;

SELECT count(*) FROM tbl_invalidation_test;
/

SELECT	child_number, parse_calls, executions, invalidations, object_status, sql_id
FROM	v$sql
WHERE	sql_text = 'SELECT count(*) FROM tbl_invalidation_test';

EXEC dbms_stats.gather_table_stats(NULL, 'TBL_INVALIDATION_TEST', NO_INVALIDATE=>FALSE);
SELECT count(*) FROM tbl_invalidation_test;

SELECT	child_number, parse_calls, executions, invalidations, object_status, sql_id
FROM	v$sql
WHERE	sql_id = 'cp85b0rhuvw84';



/* DBMS_STATS의 invalidation 관련 파라미터 설정 */
EXEC dbms_stats.set_param('NO_INVALIDATE', 'FALSE');
EXEC dbms_stats.set_param('NO_INVALIDATE', 'TRUE');
EXEC dbms_stats.set_param('NO_INVALIDATE', 'DBMS_STATS.AUTO_INVALIDATE');