How accurate is the Oracle EXPLAIN PLAN?

Question

Are there any good ways to objectively measure query performance in Oracle 10g? There is one specific request that I have been setting up for several days. I got a version that works faster (at least based on my initial tests), but the cost of EXPLAIN is about the same.

• How likely is EXPLAIN to be missing?
• Are there any special situations where the cost of EXPLAIN is disproportionately different from the actual query performance?
• I used the first_rows hint for this query. It affects?

Answer 1

, EXPLAIN ?

. , 1:)

, EXPLAIN, . , , .

EXPLAIN PLAN , , .

, EXPLAIN PLAN :

SELECT  *
FROM    table
START WITH
        id = :startid
CONNECT BY
        parent = PRIOR id

id, parent, FULL TABLE SCAN, , , .

STORED OUTLINE , .

- , EXPLAIN ?

, .

CBO (, ) .

JOIN 's, .. , , , .

, : HASH JOIN, , probe table, - pga_aggregate_table, Oracle 10g , - CBO.

, 2 .

first_rows . ?

: , , .

NESTED LOOP HASH JOIN.

NESTED LOOP , ( ).

, . .

Answer 2

Q: - Oracle 10g?

• Oracle - . Oracle . SQLPlus AUTOTRACE.

http://asktom.oracle.com/tkyte/article1/autotrace.html ( )
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671636641855

Oracle .

Q: , . , ( , ), EXPLAIN .

• - , . EXPLAIN PLAN , .

Q: > 1. , EXPLAIN ?

• , , EXPLAIN PLAN , .

Q: > 2. - , EXPLAIN ?

• : . , EXPLAIN PLAN . , EXPLAIN PLAN , . EXPLAIN PLAN , Oracle.

Q: > 3. first_rows . ?

• (, /*+ FIRST_ROWS */) , .

---

"", PLAY EXPLAIN, . , . .

, PLUS EXPLAIN, 1, " ", PLUS EXPLAIN . .

, , - ( , , ..). , .

, , EXPLAIN PLAN, , . , Oracle8, -, .

, . - SQLPlus AUTOTRACE.

[http://asktom.oracle.com/tkyte/article1/autotrace.html][4]

SQLPlus Oracle:

    alter session set timed_statistics = true;
    alter session set tracefile_identifier = here_is_my_session;
    alter session set events '10046 trace name context forever, level 12'
    --alter session set events '10053 trace name context forever, level 1'
    select /*-- your_statement_here --*/ ...
    alter session set events '10046 trace name context off'
    --alter session set events '10053 trace name context off'

user_dump_dest . . ( udump)

    select value from v$parameter where name like 'user_dump_dest'

, dba, . (Dba script, .trc tkprof tkprof. trcanlzr. .

Answer 3

AFAIK, EXPLAIN , .

Answer 4

In my experience, EXPLAIN was accurate and useful. If this were not so, it would not be a useful tool. When was the last time you analyzed the tables? I saw where the Explain plan was almost the same before and after the analysis, but the analysis made a huge performance boost.