/* When running a query in Oracle, an optimizer determines the most efficient sequence of operations and chooses the best access and join method by considering the cost and cardinality of each operation, partition and parallel execution to run the statement. */
/* You might be asked to check the explain plan of a query and modify it to run more efficiently. The first thing that you have to check is cardinality, which is the estimate number of rows that come out for each operation. The cardinality is either determined by the complex set of formulas or from the column level statistics. */
/* If the PLAN_TABLE is missing, it can be created anytime by running utlxplan.sql file. It is also possible to use different plan table for EXPLAIN PLAN FOR command using INTO clause as follows: */
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'tableryu');
난이도: ★☆☆☆☆
문제 1: EXPLAIN PLAN FOR을 사용해서 explain plan을 생성해 보아라. (4분)
CREATE TABLE tableryu
AS
SELECT * FROM dba_objects WHERE rownum <= 10000;
EXPLAIN PLAN FOR
SELECT count(*) FROM tableryu;
SELECT * FROM table(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
난이도: ★★☆☆☆
문제 2: 특정 쿼리의 SQL_ID를 사용해서 그 쿼리의 explain plan을 조회해 보아라. (6분)
SELECT /* ddquery */ count(*) FROM tableryu;
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%ddquery%';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('g2xw02b09b42d',NULL,'ALLSTATS LAST'));
난이도: ★☆☆☆☆
문제 3: SET AUTOTRACE 커맨드를 사용하여 다음을 표시하여라. (6분)
- Only execution plan
- Only statistics
- Both execution plan and statistics
- Execution plan with output of query
SET AUTOTRACE ON EXP
SET AUTOTRACE ON STAT
SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
