Thanks for the question, Philip.

Asked: August 02, 2017 - 3:28 pm UTC

Last updated: August 05, 2017 - 3:12 am UTC

Version: Exadata Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Dear Tom,

if I execute in Toad a query like:
select *
from SOMESCHEMA.TABLE_A A
join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID
join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID
join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID
where C.C_ID = 1234;

the query results return in less than a second.
If I now execute the query:
select * from SOMESCHEMA.TABLE_A A
join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID
join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID
join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID
where C.C_ID = 1234;

the query results return in about 45 seconds. The only difference between the two queries is the line break between * and from.

The results are reproducible.

Do you have any idea why this could happen?

Thank you!

and Connor said...

Try this - run each with the hint as below

select /*+ gather_plan_statistics */ * 
from SOMESCHEMA.TABLE_A A
join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID
join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID
join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID
where C.C_ID = 1234;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

select /*+ gather_plan_statistics */ * from SOMESCHEMA.TABLE_A A
join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID
join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID
join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID
where C.C_ID = 1234;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

There might be different plans being generated.

Post the output of the dbms_xplan here with code tags and we can take a look

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Execution Plans

Philip, August 03, 2017 - 6:49 am UTC

Dear Tom,

unfortunately, the phenomenon disapeared. Maybe some statistics changed?

I checked already yesterday, in V$SQL and V$SQL_PLAN, there were different plans used. One plan used MERGE JOIN CARTESIAN twice, the other plan didn't. However, I am wondering why the optimizer can be affected by adding a Newline (!) to a query?

Best regards,
Philip

August 04, 2017 - 1:48 am UTC

Different sql text = a new parse = potentially a new plan.

And in 12c, even the same sql might yield a new plan with statistics feedback in play.

Just a guess

Thomas Brotherton, August 03, 2017 - 2:20 pm UTC

This is just a guess, but the difference could have been something that Toad is doing under the covers, like checking your syntax, looking for bind variables, etc.

To Philip

J. Laurindo Chiappa, August 03, 2017 - 4:32 pm UTC

Hello, Philip :

first things first, let´s rememeber - inside the RDBMS Oracle, by default PARSING is based on the TEXT of the SQL : so, ANY CHANGES in the SQL text (no matter how small) WILL imply in a NEW parse, a NEW entry in SQL cache... See :

ORCL12C@ORCL12C> select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180;

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
181 Fleaur 3100
182 Sullivan 2500
183 Geoni 2800
184 Sarchand 4200
185 Bull 4100
186 Dellinger 3400
187 Cabrio 3000
188 Chung 3800
189 Dilly 3600
190 Gates 2900
191 Perkins 2500

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
192 Bell 4000
193 Everett 3900
194 McCain 3200
195 Jones 2800
196 Walsh 3100
197 Feeney 3000
198 OConnell 2600
199 Grant 2600
200 Whalen 4400
201 Hartstein 13000
202 Fay 6000

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
203 Mavris 6500
204 Baer 10000
205 Higgins 12008
206 Gietz 8300

26 linhas selecionadas.

ORCL12C@ORCL12C>

==> Let´s check the SQL cache :

ORCL12C@ORCL12C> ed
Gravou file afiedt.buf

1* select sql_id, CHILD_NUMBER, CHILD_ADDRESS, OPEN_VERSIONS, LOADED_VERSIONS, PARSE_CALLS, ROWS_PROCESSED, KEPT_VERSIONS, sql_text from v$sql where sql_text l
T%'
ORCL12C@ORCL12C> /

SQL_ID CHILD_NUMBER CHILD_ADDRESS OPEN_VERSIONS LOADED_VERSIONS PARSE_CALLS ROWS_PROCESSED KEPT_VERSIONS
------------- ------------ ---------------- ------------- --------------- ----------- -------------- -------------
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
24j394hvvmc65 0 00007FFCE67E2930 1 1 1 26 0
select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180

ORCL12C@ORCL12C>

==> just ONE entry, with ONE executin plan, NO CHILDs, ok... Let´s execute in the SAME session the SAME EXACT SQL TEXT, without ANY changes :

ORCL12C@ORCL12C> select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180;

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
181 Fleaur 3100
182 Sullivan 2500
183 Geoni 2800
184 Sarchand 4200
185 Bull 4100
186 Dellinger 3400
187 Cabrio 3000
188 Chung 3800
189 Dilly 3600
190 Gates 2900
191 Perkins 2500

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
192 Bell 4000
193 Everett 3900
194 McCain 3200
195 Jones 2800
196 Walsh 3100
197 Feeney 3000
198 OConnell 2600
199 Grant 2600
200 Whalen 4400
201 Hartstein 13000
202 Fay 6000

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
203 Mavris 6500
204 Baer 10000
205 Higgins 12008
206 Gietz 8300

26 linhas selecionadas.

ORCL12C@ORCL12C> select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180;

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
181 Fleaur 3100
182 Sullivan 2500
183 Geoni 2800
184 Sarchand 4200
185 Bull 4100
186 Dellinger 3400
187 Cabrio 3000
188 Chung 3800
189 Dilly 3600
190 Gates 2900
191 Perkins 2500

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
192 Bell 4000
193 Everett 3900
194 McCain 3200
195 Jones 2800
196 Walsh 3100
197 Feeney 3000
198 OConnell 2600
199 Grant 2600
200 Whalen 4400
201 Hartstein 13000
202 Fay 6000

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
203 Mavris 6500
204 Baer 10000
205 Higgins 12008
206 Gietz 8300

26 linhas selecionadas.

ORCL12C@ORCL12C>

==> Let´s check the SQL area :

ORCL12C@ORCL12C> select sql_id, CHILD_NUMBER, CHILD_ADDRESS, OPEN_VERSIONS, LOADED_VERSIONS, PARSE_CALLS, ROWS_PROCESSED, KEPT_VERSIONS, sql_text from v$sql where sql_text like 'select /* TEST%';

SQL_ID CHILD_NUMBER CHILD_ADDRESS OPEN_VERSIONS LOADED_VERSIONS PARSE_CALLS ROWS_PROCESSED KEPT_VERSIONS
------------- ------------ ---------------- ------------- --------------- ----------- -------------- -------------
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
24j394hvvmc65 0 00007FFCE67E2930 1 1 3 78 0
select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180

ORCL12C@ORCL12C>

==> Just one entry yet : the TEXT being the same (and no bind involved, etc) we got a REUSE of the same SQL and (of course) with the same PLAN....
Now I will exec the VERY SAME query but changing the text - will put some LINE BREAKs just like you did :

ORCL12C@ORCL12C> select /* TEST */ employee_id,
2 last_name,
3 salary
4 from employees where employee_id > 180
5 /

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
181 Fleaur 3100
182 Sullivan 2500
183 Geoni 2800
184 Sarchand 4200
185 Bull 4100
186 Dellinger 3400
187 Cabrio 3000
188 Chung 3800
189 Dilly 3600
190 Gates 2900
191 Perkins 2500

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
192 Bell 4000
193 Everett 3900
194 McCain 3200
195 Jones 2800
196 Walsh 3100
197 Feeney 3000
198 OConnell 2600
199 Grant 2600
200 Whalen 4400
201 Hartstein 13000
202 Fay 6000

EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
203 Mavris 6500
204 Baer 10000
205 Higgins 12008
206 Gietz 8300

26 linhas selecionadas.

ORCL12C@ORCL12C>

==> Same results, of course, BUT let´s check the SQL cache :

ORCL12C@ORCL12C> /

SQL_ID CHILD_NUMBER CHILD_ADDRESS OPEN_VERSIONS LOADED_VERSIONS PARSE_CALLS ROWS_PROCESSED KEPT_VERSIONS
------------- ------------ ---------------- ------------- --------------- ----------- -------------- -------------
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------
129w4d5a3pxkn 0 00007FFCE645F910 0 1 1 26 0
select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180

24j394hvvmc65 0 00007FFCE67E2930 1 1 3 78 0
select /* TEST */ employee_id, last_name, salary from employees where employee_id > 180

ORCL12C@ORCL12C>

===>> NO REUSE, see ? As I said, ANY DIFF (small or big, matters not) in the SQL text WILL cause ANOTHER PARSE, adding ANOTHER entry in the SQL area, and POTTENTIALLY we COULD GET another SQL EXECUTION PLAN.... Yes ?? NO REASON for your surprise... The point is , when you change the TEXT of a given SQL things CAN change : the "problem" here as I see is the fact the SQL text is changing, and not the line breaks per se.....
Now your work is to find the REASON for the SQL execution plan after changing the SQL text - could be due to SQL Profiles, due to new statistics being collected, due to some session setting changed when you send the new SQL text, among many other possible reasons...
Search here (and in google!!) for ORACLE SQL PLAN CHANGED and you will find a LOT of refs and articles....

regards,

J. Laurindo Chiappa

Question Solved

Philip, August 04, 2017 - 6:55 am UTC

Dear TOMs,
Thank you very much for your answer - that indeed explains the riddle that we had.

On this regards, may I ask why Oracle uses the SQL Text as Cache / identifier, and not a parsed version of the query?

Thank you,
Philip

August 05, 2017 - 3:12 am UTC

Its about performance. Isn't it always :-)

The reason we have the concept of re-use of SQL is to reduce the overall cost of parsing an SQL statement. So if the cost of *working out* if we can re-use statement was high, we'd defeat the purpose of re-use at all.

Some of the tools inside Oracle do *some* cleansing work to help things. For example, SQL within PL/SQL is case normalized before being presented to the database, so if you had two plsql programs where

pgm1: select ename from emp
pgm2: select ENAME from EMP

then by the time these SQL's made it to the database, they would both be:

SELECT ENAME FROM EMP

and would be shared.

Whilst it seems a *simple* thing to see if two statements are the same, its actually pretty hard. Think of this example:

YOU: SELECT ENAME FROM EMP
ME: SELECT ENAME FROM EMP

They can be shared right ? Well...maybe not.

EMP might be a synonym for you, and a table for me. One might be view. You might have a row level security predicate. It might be a public synonym. I might not have access to the table, etc etc etc.

There's a LOT of stuff going on even to the do the simplest of checks - "are two SQL's the same"

Possible causes...

J. Laurindo Chiappa, August 04, 2017 - 12:39 pm UTC

Hi : with "parsed version" you mean a NORMALIZED VERSION of the SQL text, ie, with non-quoted words uppercase-converted, removing unquoted extra whitespaces, with non-printing/control characters (such as TABs and alike) removed, and transformed in a single-line text replacing the new-lines/line breaks to single whitespace, right ?
Maria and/or Connor could answer it much better than I (let´s wait for these answers), but afaik it is due to compatibility reasons : a RDBMS engine can receive dozens and dozens of SQL texts simultaneously and needs to interpret all of them very very quickly, in ancient times servers do not had the horsepower to do it, so no normalization.. The approach was to do the normalization (and not a full one, just uppercase and space-trimming) in the development tools, Oracle Forms was an example of it...
Nowadays I think the common-ground hardware where the RDBMS runs already have the horsepower enough to full normalize the received SQL text before Parsing but this is not implemented to avoid profound changes in the Parsing sub-routines, that´s it, afaik....

Regards,

J. Laurindo Chiappa

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Execution Plans

Just a guess

To Philip

Question Solved

Possible causes...

More to Explore

Performance

SQL Execution Speed depending on Line Breaks in Query

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Execution Plans

Just a guess

To Philip

Question Solved

Possible causes...

More to Explore

Performance