Home » RDBMS Server » Performance Tuning » Explain plan depends on Oracle client? (Oracle 11g)
Explain plan depends on Oracle client? [message #643102] Tue, 29 September 2015 05:56 Go to next message
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 #643103 is a reply to message #643102] Tue, 29 September 2015 06:47 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Please format your execution plans using
select * from table(dbms_xplan.display);

and copy them into the post, enclosed within [code] tags. It is really hard to read anything from what you have posted.
Re: Explain plan depends on Oracle client? [message #643106 is a reply to message #643103] Tue, 29 September 2015 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
How did you generate the bad plan?
Re: Explain plan depends on Oracle client? [message #643110 is a reply to message #643106] Tue, 29 September 2015 08:43 Go to previous messageGo to next message
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.
Re: Explain plan depends on Oracle client? [message #643113 is a reply to message #643110] Tue, 29 September 2015 08:57 Go to previous message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
for furure reference, it would have been easier (and better formatted) to have used
select * from table(dbms_xplan.display_cursor(&sql_id,&child));
Previous Topic: Tuning
Next Topic: Can we manually assign certain query transaction between RAC nodes ?
Goto Forum:
  


Current Time: Tue Mar 19 04:24:26 CDT 2024