CODEWITHSUNDEEP
performanceoracle-databasequery-optimizationexplainsql-execution-plan
Avinash KP
Avinash KP

Reputation: 67

Analysing ORACLE EXPLAIN PLAN

Browser is getting hanged while generating jasper report[PDF format] which runs a query whose explain plan is given below.

Please help to anlyse the query, is this query taking too much time? We also noticed stuck thread while generating this report.

Explain Plan of the report query

Upvotes: 1

Views: 1957

Answers (2)

pahariayogi
pahariayogi

Reputation: 1163

Agree with APC. i also got surprised looking at expected time (45 sec). To add, explain plan is an 'expected plan' by CBO. Sometime, 'Actual' vs 'Expected' differs after real execution.

So, its better to check actual plan as well. Following can be used get the actual plan:

1) Using dbms_xplan 

explain plan for <SELECT ...>
select * from table(dbms_xplan.display);            --estimated plan
select * from table(dbms_xplan.display_cursor);     --actual plan

2) Trigger '10046 trace' and TKPROF 

alter session set tracefile_identifier = 'something-unique'
alter session set sql_trace = true;
alter session set events '10046 trace name context forever, level 8';

Upvotes: 0

APC
APC

Reputation: 146239

is The EXPLAIN PLAN is the optimizer's informed guess at how your query will run, including how long it will take. The opt imizer bases this guess on many things, including what statistics it has regarding data volumes and system characteristics.

These gueses are usually pretty good, especially in the later versions of Oracle. But they can still be out, especially if your statistics are stale, if your data distribution is skewed or because of ambient system conditions.

In your particular case the optimizer is guessing that your query returns one row: does that sound right? If not your statistics are inaccurate and need refreshing.

As for time, the optimizer is guessing that your query will take 45 seconds to run. Is that too long? Only you can tell?

Bear in mind that database tuning is a complex science. It requires lots of detailed information. People forge entire careers by tuning slow-running queries. Tuning is even more complicated in web applications because there are so many points where the architecture or bad coding can introduce bottlenecks. It is very difficult to get a profile of the performance of the whole system.

Upvotes: 7

Related Questions