UNIT 5 Expensive SQL Statements

Question 1

What can make an SQL statement expensive?

Answer 1

Statement that causes the database to read many blocks

Question 2

We can see estimated ______ and estimated _______ in the explain plan for an SQL statement.

Answer 2

Cost and rows

Question 3

What are the transactions that you can use to show the Explain Plan for an SQL?

Answer 3

ST04

ST05

ST12

Question 4

What is the general solution for a full table scan?

Answer 4

Create index with selective field

Question 5

If you saw many work processes performing

sequential read or direct read for too long, what could you check?

Answer 5

Analyze the database

Question 6

How are optimizer statistics kept up to date?

Answer 6

Schedule periodic refreshes

Question 7

What info is taken into account when executing an SQL statement for an optimal access strategy?

Answer 7

1) Where cause fields*
2) Availability of index’s *
3) Statistics of the table