| Explain plan depends on Oracle client? [message #643102] |
Tue, 29 September 2015 05:56  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Hello,
I have run into a problem. The warehouse ETL script (executed with sqlplus) went into productional environment and started to perform very slowly (test environment was ok). The problem is with CTAS statement (see attachment), which after ~72h of execution has completed only ~4% of total work. It was stuck on HASH JOIN operation on the 21st row of explain plan. (see attachment "bad explain plan").
Strangely, if I manually execute the same CTAS script (connect with the same Oracle user) using PLSQL Developer 9, Oracle generates another execution plan (see attachment "good explain plan"), which executes CTAS statement in ~30minutes (which is acceptable).
1. Why does Oracle generate different execution plans for the same statement?
2. How do I force Oracle to use "good explain plan"?
|
|
|
|
|
|
|
|
| Re: Explain plan depends on Oracle client? [message #643110 is a reply to message #643106] |
Tue, 29 September 2015 08:43  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Quote:How did you generate the bad plan?
Using this query (had to find sid=122 of problematic session at first):
--explain plan of executing SQL:
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM (
SELECT id, parent_id, operation, options, object_name
FROM v$sql_plan sqpl, v$session ses, v$sql sq
WHERE
sqpl.address = sq.address
AND sqpl.hash_value = sq.hash_value
AND sqpl.child_number = sq.child_number
and ses.SQL_ADDRESS=sq.ADDRESS
and ses.sid = 122
)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
Anyway, it seems I have solved the problem by splitting the CTAS statement into 2 smaller CTAS statements, and the problem is gone.
Sorry for disturbing your time.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
