Short overview about most popular topics as "task-answer" format.
CHAPTER 6. "Hints"
CHAPTER 7. "Reading execution plan"
CHAPTER 6. "Hints"
Task 6.1. Hints. Part 1.
1. What is a hint?
2. Why we should use hints?
3. Where in query we can use hints and how? What can be used as parameters hints?
4. How to find using hints in execution plan? How to see join order in execution
plan?
5. List popular hints categories.
6. What is view stores info about names, classes of hints?
Solution:
--1
Comment in SQL statement to force optimizer make specific action in execution
plan.
--2
1) If statistics old, then optimizer can create bad execution plan.
2) Want choose another index access path.
3) Want choose another join method.
--3
A statement block can have only one comment containing hints, and that comment
must follow the SELECT, UPDATE, INSERT or DELETE keyword. Comment can be
multiline /*+ hint */ or single line --+ hint
Table names or its aliases or index names (if query have table aliases, then must
use table aliases in hints) can be as parameters divided by space.
--4
Since 19c use DBMS_XPLAN. May see join order in OUTLINE section in line with
word "LEADING" after getting execution plan for query with aliases by command:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'TYPICAL +OUTLINE'));
--5
1) Optimization goals and approaches;
2) Access path;
3) Join order;
4) Join operation;
5) Parallel execution;
6) Query Transformation;
7) Other.
--6
SELECT * FROM V$SQL_HINT
Task 6.2. Hints. Part 2.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 NUMBER,
COL3 VARCHAR2(100),
CONSTRAINT PK PRIMARY KEY (COL1),
CONSTRAINT UN UNIQUE (COL2));
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(i, i*2, i||'text');
END LOOP;
COMMIT;
END;
/
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL2 NUMBER,
CONSTRAINT PK2 PRIMARY KEY (COL2));
INSERT INTO TAB2(COL2) VALUES(500000);
INSERT INTO TAB2(COL2) VALUES(600000);
INSERT INTO TAB2(COL2) VALUES(700000);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB1',
CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB2',
CASCADE => TRUE);
END;
Then do next:
1. Create 2 queries (multline and single line hints using alias) for display COL1
where COL1 <= 100000 and demonstrate FULL TABLE SCAN. What is predicate will be:
filter or access?
2. Create query display all columns from TAB1 where COL1 <= 999999 and use PK.
What will be the types of predicate, index scan, table access?
3. Explain using multiple hints in query. Create query where display columns from
TAB1 that have INNER JOIN to TAB2 and using hints force Oracle use HASH JOIN and
FULL TABLE SCAN for tables.
Solution:
--1
--with FULL hint will be FILTER predicate
SELECT /*+ FULL(t1) */
t1.COL1
FROM TAB1 t1
WHERE t1.COL1 <= 100000
SELECT --+ FULL(t1)
t1.COL1
FROM TAB1 t1
WHERE t1.COL1 <= 100000
--2
--ACCESS predicate. INDEX RANGE SCAN. TABLE ACCESS BY ROWID
SELECT /*+ INDEX(t1 PK) */
t1.*
FROM TAB1 t1
WHERE t1.COL1 <= 999999
--3
--Hints must produce different actions and do not conflict with each other
SELECT /*+ FULL(t1) FULL(t2) USE_HASH(t1 t2) */
t1.*
FROM TAB1 t1
INNER JOIN TAB2 t2
ON t1.COL2 = t2.COL2
Task 6.3. Hints. Part 3.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 NUMBER,
COL3 VARCHAR2(100),
CONSTRAINT PK PRIMARY KEY (COL1),
CONSTRAINT UN UNIQUE (COL2));
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(i, i*2, i||'text');
END LOOP;
COMMIT;
END;
/
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL2 NUMBER,
CONSTRAINT PK2 PRIMARY KEY (COL2));
INSERT INTO TAB2(COL2) VALUES(500000);
INSERT INTO TAB2(COL2) VALUES(600000);
INSERT INTO TAB2(COL2) VALUES(700000);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB1',
CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB2',
CASCADE => TRUE);
END;
Then do next:
1. Using only TAB1 and TAB2 demonstrate query with PUSH_PRED hint. Explain. What
does for PUSH_PRED we will see in execution plan?
2. Rerwite query from point 1, but instead TAB2 use DUAL with UNION ALL.
3. Explain situation when PUSH_PRED does not work.
4. Rerwite query from point 2 to demonstrate OLD_PUSH_PRED and explain.
Solution:
--1
--PUSH_PRED instructs the optimizer to push a join predicate into the view.
--It is mean if you push to view`s indexed column, then in view uses index and
--does not read all rows. In explain plan we may see UNION ALL PUSHED PREDICATE.
SELECT /*+ NO_MERGE(vw) PUSH_PRED(vw) */
--or write /*+ NO_PUSH_PRED(vw) */ to see huge cost
t2.*
,vw.*
FROM TAB2 t2
JOIN(
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
UNION
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
) vw
ON t2.COL2 = vw.COL2 --vw.COL2 have unique index from constraint UN
--2
SELECT /*+ NO_MERGE(vw) PUSH_PRED(vw) */
t2.*
,vw.*
FROM(
SELECT '500000' COL2 FROM DUAL
UNION ALL
SELECT '600000' FROM DUAL
UNION ALL
SELECT '700000' FROM DUAL
) t2
JOIN(
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
UNION
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
) vw
ON t2.COL2 = vw.COL2
--3
PUSH_PRED(some_view) MERGE(some_view). When some_view merges into its
containing query block, the optimizer cannot apply the PUSH_PRED hint because
some_view is unavailable.
ALso can be pushed only access predicates, not filter predicates.
--4
--Oracle may use pushing with hint OLD_PUSH_PRED, you may see it in the section
--"Outline data". And in section "Hint Report" PUSH_PRED hint will be marked as
--U (unused):
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'+OUTLINE'))
--or SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+OUTLINE'))
SELECT /*+ NO_MERGE(vw) PUSH_PRED(vw) */
--or write NO_PUSH_PRED(vw) to remove OLD_PUSH_PRED and increase cost
t2.*
,vw.*
FROM(
SELECT REGEXP_SUBSTR(st, '[^,]+', 1, LEVEL) COL2
FROM (SELECT '500000,600000,700000' st FROM DUAL)
CONNECT BY REGEXP_INSTR(st, '[^,]+', 1, LEVEL) > 0
) t2
JOIN(
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
UNION
SELECT t1.COL1, t1.COL2, t1.COL3 FROM TAB1 t1
) vw
ON t2.COL2 = vw.COL2
Task 6.4. Hints. Part 4.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 NUMBER,
COL3 VARCHAR2(100),
CONSTRAINT PK PRIMARY KEY (COL1),
CONSTRAINT UN UNIQUE (COL2));
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(i, i*2, i||'text');
END LOOP;
COMMIT;
END;
/
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL2 NUMBER,
CONSTRAINT PK2 PRIMARY KEY (COL2));
INSERT INTO TAB2(COL2) VALUES(500000);
INSERT INTO TAB2(COL2) VALUES(600000);
INSERT INTO TAB2(COL2) VALUES(700000);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB1',
CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB2',
CASCADE => TRUE);
END;
Then do next:
1. Demonstrate MERGE hint using TAB1 and TAB2. Explain restrictions.
2. Demonstrate NO_MERGE hint using TAB1 and TAB2.
Solution:
--1
--MERGE hint instruct Oracle transform outer query that have view (inner query)
--to single query instead of execute view as separate query. It is improve
--performance, because Oracle may not read all rows from view, but use join
--methods with index access on condition columns or use other transformations.
--Restrictions (merging not allowed) depends on type of merging:
--SIMPLE VIEW MERGING, for simple select-project-join views:
-- 1) The view contains constructs other than select, project, join, including:
-- GROUP BY, DISTINCT, Outer join, MODEL (spreadsheet clause), CONNECT BY, Set
-- operators, Aggregation.
-- 2) The view appears on the right side of a semijoin or antijoin.
-- 3) The view contains subqueries in the SELECT list.
-- 4) The outer query block contains PL/SQL functions.
--OUTER-JOIN VIEW MERGING, for outer-joined views restrictions:
-- 1) more than one table from view have left join with same table from outer
-- query;
-- 2) outer query have left join with view and this view have more than
-- one table (BUT merging allowed if view have 2 and more tables, but these
-- tables must be joined with each other before left join to outer query).
--COMPLEX VIEW MERGING, for distinct and group by views:
-- 1) optimizer cannot delay evaluation of GROUP BY or DISTINCT until joins
-- reduce amount of data (for example joins have not filters);
-- 2) outer query table does not have a rowid or unique column;
-- 3) view: appears in a CONNECT BY or contains GROUPING SETS, ROLLUP, or PIVOT;
-- 4) view or outer query block contains the MODEL clause.
SELECT /*+ MERGE(t1) */
t1.*
FROM TAB2 t2
JOIN (SELECT --or write here /*+ MERGE */
t1.*
FROM TAB1 t1
WHERE t1.COL3 = '250000text') t1
ON t2.COL2 = t1.COL2
--2
SELECT /*+ NO_MERGE(t1) */
--or write to get huge cost: /*+ NO_MERGE(t1) NO_PUSH_PRED(t1) */
t1.*
FROM TAB2 t2
JOIN (SELECT --or here /*+ NO_MERGE */ or /*+ NO_MERGE NO_PUSH_PRED */
t1.*
FROM TAB1 t1
WHERE t1.COL3 = '250000text') t1
ON t2.COL2 = t1.COL2
CHAPTER 7. "Reading execution plan"
Task 7.1. Reading execution plan. Part 1.
1. What is a STATISTICS COLLECTOR in plan?
2. What is a BUFFER SORT in plan?
3. What happens if SQL developer try execute long query and freezes.
4. What is VIEW operator in execution plan? When might we not see it in the plan?
Solution:
--1
Indicates that is adaptive plan which control optimizations: nested loops and
hash join selection, star transformation bitmap pruning, adaptive parallel
distribution method. It is mean WHILE EXECUTING QUERY Oracle decides that
statistics not correct and Oracle scan a small random sample of a table blocks to
estimate predicate selectives and use it if it`s generates better plan.
Adaptive plans are enabled by default.
--2
BUFFER SORT operation indicates that the database is copying the data blocks
obtained by the scan of my_index from the SGA to the PGA. This strategy avoids
multiple scans of the same blocks in the BUFFER CACHE, which would
generate many logical reads and permit resource contention. This may or may not
involve sorting.
--3
Even if you close SQL Developer it will work and DBA must kill your session.
--4
For outer query with inner query in it and if optimizer cannot merge that
inner query, then Oracle names that block (inner query) and displays with VIEW
operator in OPERATION column of an execution plan. And this inner query will be
executed as separate query and it may decrease performance.
We may not see VIEW operator if Oracle transforms outer query with inner query to
single query (merges inner query (view) to outer query).
Task 7.2. Reading execution plan. Part 2.
1. How to clear RESULT CACHE area for user?
2. Which is a view stores info about objects in RESULT_CACHE? What is a status
means that result is available for use?
3. Explain RESULT_CACHE with table annotation. Which is a view stores info about
enabled RESULT_CACHE for table or not?
4. Explain the RESULT_CACHE hint and what we will see in execution plan?
5. Explain the use of the RESULT_CACHE hint for a table with RESULT_CACHE
annotation.
6. Explain the NO_RESULT_CACHE hint and what we will see in execution plan?
Solution:
--1
--by SYS user
ALTER SESSION SET CONTAINER=ORCLPDB;
EXEC DBMS_RESULT_CACHE.FLUSH;
--2
--Status "Published" means that result is available for use.
--CACHE_ID = value from execution plan from column NAME (or OBJECT_NAME).
SELECT * FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID = 'my_cache_id_from_plan'
--3
--We can create or alter table with different RESULT_CACHE modes:
--DEFAULT means that table not will be stored in cache if not using hint.
CREATE TABLE TAB1 (COL1 NUMBER) RESULT_CACHE (MODE DEFAULT);
--FORCE means that results of all single-table queries (with this table only)
--will be stored in cache.
ALTER TABLE TAB1 RESULT_CACHE (MODE FORCE);
SELECT RESULT_CACHE
FROM USER_TABLES
WHERE TABLE_NAME = 'TAB1'
--4
Hint instructs the database to cache the results of a query block and to use the
cached results in future executions of this query. After storing result in cache
will see phrase 'RESULT CACHE' after table/index access in execution plan.
SELECT /*+ RESULT_CACHE */ COUNT(*) FROM TAB1; --run it to place result in cache
--5
Hint instruct Oracle ignore table annotation and store result in cache.
--6
NO_RESULT_CACHE hint disables caching for the current query if RESULT_CACHE_MODE
initialization parameter is set to FORCE (or in table annotation), otherwise it
does not work.
Task 7.3. Reading execution plan. Part 3.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 NUMBER,
COL3 VARCHAR2(100),
CONSTRAINT PK PRIMARY KEY (COL1),
CONSTRAINT UN UNIQUE (COL2));
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(i, i*2, i||'text');
END LOOP;
COMMIT;
END;
/
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL2 NUMBER,
CONSTRAINT PK2 PRIMARY KEY (COL2));
INSERT INTO TAB2(COL2) VALUES(500000);
INSERT INTO TAB2(COL2) VALUES(600000);
INSERT INTO TAB2(COL2) VALUES(700000);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB1',
CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB2',
CASCADE => TRUE);
END;
Then do next:
1. List SORT operations from execution plan, also write alternatives to increase
performance. Write examples.
Solution:
--1
--SORT ORDER BY. Sorts row source after TABLE ACCESS FULL.
SELECT * FROM TAB1 ORDER BY COL3 DESC
--SORT UNIQUE (alternative HASH UNIQUE). If use UNION set statement, then sorts
--final row source. If use MINUS, INTERSECT then sorts both row sources.
SELECT COL1 FROM TAB1
UNION
SELECT COL2 FROM TAB2
--SORT UNIQUE NOSORT. When use MINUS for row sources after INDEX FULL SCAN and
--not require sort.
SELECT COL2 FROM TAB2
MINUS
SELECT COL2 FROM TAB2
--SORT AGGREGATE. It is not real sort operation. Uses for COUNT, MIN, MAX, AVG.
SELECT COUNT(COL1), AVG(COL1), MIN(COL1), MAX(COL1) FROM TAB1
--SORT JOIN. Oracle sorts row sources when use SORT MERGE JOIN method.
SELECT /*+ USE_MERGE(t1 t2) */
t1.COL2
FROM TAB1 t1
JOIN TAB2 t2
ON t1.COL2 = t2.COL2
--SORT GROUP BY (alternative HASH GROUP BY). When Oracle use GROUP BY with MEDIAN
--function (or functions that does not support HASH GROUP BY) to sort data to
--separate groups. Or if uses ORDER BY for sorting also.
SELECT t2.COL2
,MEDIAN(t2.COL2)
FROM TAB2 t2
GROUP BY t2.COL2
SELECT t2.COL2
,SUM(t2.COL2)
FROM TAB2 t2
GROUP BY t2.COL2
ORDER BY t2.COL2 DESC
--BUFFER SORT. Indicates that Oracle stores row source (usually smallest) in SORT
--AREA in PGA for fast reading.
SELECT t1.*, t2.*
FROM TAB1 t1
CROSS JOIN TAB2 t2
Task 7.4. Reading execution plan. Part 4.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 NUMBER,
COL3 VARCHAR2(100),
CONSTRAINT PK PRIMARY KEY (COL1),
CONSTRAINT UN UNIQUE (COL2));
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(i, i*2, i||'text');
END LOOP;
COMMIT;
END;
/
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL2 NUMBER,
CONSTRAINT PK2 PRIMARY KEY (COL2));
INSERT INTO TAB2(COL2) VALUES(500000);
INSERT INTO TAB2(COL2) VALUES(600000);
INSERT INTO TAB2(COL2) VALUES(700000);
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB1',
CASCADE => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TAB2',
CASCADE => TRUE);
END;
Then do next:
1. Demonstrate INLIST iterator. Explain. How to ignore INLIST iterator?
2. Demonstrate COUNT (STOPKEY) operator. Explain. What is a bad alternative?
3. Demonstrate CURSOR DURATION MEMORY. Explain. What memory area is used?
Solution:
--1
--INLIST iterator uses in execution plan to search values in indexed column using
--IN, OR clauses. Oracle search values from IN separately (feeds the "=" clause)
--and union to single result. If IN clause have too many values, then optimizer
--may use FULL SCAN.
SELECT t1.* FROM TAB1 t1 WHERE t1.COL2 IN (2, 6, 12)
SELECT t1.* FROM TAB1 t1 WHERE t1.COL2 = 2 OR t1.COL2 = 6 OR t1.COL2 = 12
--to ignore INLIST iterator must be too many values in IN clause or use hint
--USE_CONCAT(OR_PREDICATES(32767))
SELECT /*+ USE_CONCAT(OR_PREDICATES(32767)) */
t1.*
FROM TAB1 t1 WHERE t1.COL2 IN (2, 6, 12)
--2
--COUNT - an operation that counts the number of rows selected from a table.
--COUNT STOPKEY - a count operation where the number of rows returned is limited
--by the ROWNUM expression in the WHERE clause.
SELECT COUNT(*) FROM TAB1 WHERE ROWNUM < 3
--bad alternative is (because firstly reads all rows from index or table):
SELECT COUNT(*) FROM TAB1 FETCH FIRST 3 ROWS ONLY
--3
--If subquery uses multiple times in complex query, then Oracle to improve
--performance may store result of subquery in PGA (or if PGA memory not
--available - write temporary data to disk) using cursor-duration temporary
--table. In plan CURSOR DURATION MEMORY appears.
WITH TEMP2 AS (SELECT MAX(t2.COL2) MX
,MIN(t2.COL2) MN
FROM TAB2 t2)
SELECT tt2.MX FROM TEMP2 tt2
UNION ALL
SELECT tt2.MN FROM TEMP2 tt2
--
SELECT COUNT(COUNT(*))
FROM TAB1 t1
GROUP BY GROUPING SETS(t1.COL1
,t1.COL2)