OraFAQ Forum: Performance Tuning » ANSI

Home » RDBMS Server » Performance Tuning » ANSI - SQL or Traditional SQL (oracle 9.2.0.6 on AIX 5.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ANSI - SQL or Traditional SQL [message #340269]

Tue, 12 August 2008 02:53

sandhyaa
Messages: 79
Registered: February 2007

Member

Dear All,
I have been using SQL Server for a while and so I am more comfortable with Ansi SQL than the traditional Oracle SQL, however I feel there is a performance gain if we use oracle traditional sql. Is it true?

To check this i did the following:

CREATE TABLE EMP_TEST(EMP_ID NUMBER, NAME VARCHAR2(30));

CREATE TABLE SUB1(EMP_ID NUMBER, SCORE NUMBER);

CREATE TABLE SUB2(EMP_ID NUMBER, SCORE NUMBER);

CREATE TABLE SUB3(EMP_ID NUMBER, SCORE NUMBER);

INSERT INTO EMP_TEST(EMP_ID, NAME)
SELECT OBJECT_ID, OBJECT_NAME FROM ALL_OBJECTS

INSERT INTO SUB1(EMP_ID, SCORE)
SELECT OBJECT_ID, dbms_random.random from all_objects

delete from sub1 where score <0 ; -- so that i can do some outer join

INSERT INTO SUB2(EMP_ID, SCORE)
SELECT OBJECT_ID, dbms_random.random from all_objects

delete from sub2 where score <0 ; -- so that i can do some outer join

INSERT INTO SUB3(EMP_ID, SCORE)
SELECT OBJECT_ID, dbms_random.random from all_objects

delete from sub3 where score <0 ;   -- so that i can do some outer join

commit;

USING ANSI SQL

sqlplus>set timing on

sqlplus>set autotrace traceonly

sqlplus>SELECT E.EMP_ID, E.NAME, S1.SCORE AS SUB1, S2.SCORE AS SUB2, S3.SCORE
FROM EMP_TEST E
LEFT OUTER JOIN SUB1 S1 ON E.EMP_ID=S1.EMP_ID
LEFT OUTER JOIN SUB2 S2 ON E.EMP_ID=S2.EMP_ID
LEFT OUTER JOIN SUB3 S3 ON E.EMP_ID=S3.EMP_ID;

Elapsed: 00:00:10.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1460 Card=316090 Bytes=34137720)
   1    0   HASH JOIN (OUTER) (Cost=1460 Card=316090 Bytes=34137720)
   2    1     HASH JOIN (OUTER) (Cost=841 Card=316090 Bytes=25919380)
   3    2       HASH JOIN (OUTER) (Cost=362 Card=316090 Bytes=17701040)
   4    3         TABLE ACCESS (FULL) OF 'EMP_TEST' (Cost=2 Card=316090 Bytes=9482700)
   5    3         TABLE ACCESS (FULL) OF 'SUB1' (Cost=2 Card=269801 Bytes=7014826)
   6    2       TABLE ACCESS (FULL) OF 'SUB3' (Cost=2 Card=260135 Bytes=6763510)
   7    1     TABLE ACCESS (FULL) OF 'SUB2' (Cost=2 Card=285911 Bytes=7433686)


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
      19007  consistent gets
       1507  physical reads
          0  redo size
   12790852  bytes sent via SQL*Net to client
     176773  bytes received via SQL*Net from client
      16013  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     240179  rows processed

sqlplus>

USING TRADITIONAL SQL

sqlplus>SELECT E.EMP_ID, E.NAME, S1.SCORE AS SUB1, S2.SCORE AS SUB2, S3.SCORE
FROM EMP_TEST E,
SUB1 S1,
SUB2 S2,
SUB3 S3
WHERE E.EMP_ID=S1.EMP_ID(+)  
AND E.EMP_ID=S2.EMP_ID(+)
AND E.EMP_ID=S3.EMP_ID(+);

621848 rows selected.

Elapsed: 00:00:06.82

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     MERGE JOIN (OUTER)
   3    2       MERGE JOIN (OUTER)
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'EMP_TEST'
   6    3         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'SUB3'
   8    2       SORT (JOIN)
   9    8         TABLE ACCESS (FULL) OF 'SUB2'
  10    1     SORT (JOIN)
  11   10       TABLE ACCESS (FULL) OF 'SUB1'


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2848  consistent gets
       2065  physical reads
          0  redo size
   12789080  bytes sent via SQL*Net to client
     176773  bytes received via SQL*Net from client
      16013  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     240179  rows processed

If you see the statistics, it is very evident that traditional sql is doing better than ansi sql. What could be the issue?

I am using oracle 9.2.0.6 version.

Thanks
Sandi

[Updated on: Tue, 12 August 2008 02:54]

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340275 is a reply to message #340269]

Tue, 12 August 2008 03:18

gintsp
Messages: 118
Registered: February 2007

Senior Member

There is difference in your execution plans (although autotrace may lie, to be 100% sure you can check either v$sql_plan or run trace for your session).
This is the first reason why the execution time is different.
Second reason is that you need to do several mesurements to get rid of recursive sqls building execution plan, checking rights and dependencies. Also usually the first query needs to do some physical reads, but for next ones data are cached (if data are not too much of course). So when you force both queries to the same execution plan (of course it is interesting why they are different) then you'll see that there is no difference.
BTW there are few weird things in your code:
1) why the first explain plan has cardinalities and cost, but the second one not? I bet because for traditional join syntax choose defaults to rule, and effectively you are using rule based optimizer, but for ansi sql rule doesn't work, so choose defaults to all_rows and that's why you have cost and cardinalities. So you are not comparing apples to apples. Calculate statistics for all tables and look then.
2) this row 621848 rows selected. is in contradiction with 240179 rows processed. Are you sure you copied the right data?

Also look at your 16013 SQL*Net roundtrips to/from client. This means you have default arraysize 15. See what you get when you say set arraysize 100, so reducing network roundtrips.

So I'd say if you are used to ANSI joins then use them. And if something could be faster then look at execution plan to find out why they are slow.

Gints Plivna

[Updated on: Tue, 12 August 2008 05:31] by Moderator

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340279 is a reply to message #340275]

Tue, 12 August 2008 03:30

sandhyaa
Messages: 79
Registered: February 2007

Member

Sorry, that was a copy-paste error. The number of rows returned are exactly same.

Regards,
Sandi

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340284 is a reply to message #340269]

Tue, 12 August 2008 03:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is obvious that the first query use CBO and second one RBO, so you didn't execute in the same conditions and so it is not comparable.

Oracle optimizer doesn't care you use ANSI or Oracle syntax, if they are equivalent, the plan is the same.

Regards
Michel

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340285 is a reply to message #340275]

Tue, 12 August 2008 03:34

sandhyaa
Messages: 79
Registered: February 2007

Member

Hi,
Thanks for your suggestions. Can you please clarify these two suggestions:
1)Optimizer issue
I am actually in oracle apps environment which has OPTIMIZER_MODE = CHOOSE,
so I'll check after analyzing the tables and post the difference.

2)Also look at your 16013 SQL*Net roundtrips to/from client. This means you have default arraysize 15. See what you get when you say set arraysize 100, so reducing network roundtrips.

Can you please suggest how to increase the arraysize?

Regards,
Sandi

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340286 is a reply to message #340275]

Tue, 12 August 2008 03:37

gintsp
Messages: 118
Registered: February 2007

Senior Member

And here are my results:

SQL> exec dbms_stats.gather_table_stats(user, 'EMP_TEST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.65
SQL> exec dbms_stats.gather_table_stats(user, 'SUB1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL> exec dbms_stats.gather_table_stats(user, 'SUB2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL> exec dbms_stats.gather_table_stats(user, 'SUB3');

PL/SQL procedure successfully completed.

SQL> SELECT E.EMP_ID, E.NAME, S1.SCORE AS SUB1, S2.SCORE AS SUB2, S3.SCORE
  2  FROM EMP_TEST E
  3  LEFT OUTER JOIN SUB1 S1 ON E.EMP_ID=S1.EMP_ID
  4  LEFT OUTER JOIN SUB2 S2 ON E.EMP_ID=S2.EMP_ID
  5  LEFT OUTER JOIN SUB3 S3 ON E.EMP_ID=S3.EMP_ID;

68628 rows selected.

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=206 Card=68628 Bytes=4117680)
   1    0   HASH JOIN (RIGHT OUTER) (Cost=206 Card=68628 Bytes=4117680)
   2    1     TABLE ACCESS (FULL) OF 'SUB1' (TABLE) (Cost=42 Card=34708 Bytes=381788)
   3    1     HASH JOIN (RIGHT OUTER) (Cost=161 Card=68628 Bytes=3362772)
   4    3       TABLE ACCESS (FULL) OF 'SUB3' (TABLE) (Cost=42 Card=34272 Bytes=376992)
   5    3       HASH JOIN (RIGHT OUTER) (Cost=116 Card=68628 Bytes=2607864)
   6    5         TABLE ACCESS (FULL) OF 'SUB2' (TABLE) (Cost=42 Card=34046 Bytes=374506)
   7    5         TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=71 Card=68628 Bytes=1852956)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1543  consistent gets
          0  physical reads
          0  redo size
    2654846  bytes sent via SQL*Net to client
       8058  bytes received via SQL*Net from client
        688  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68628  rows processed

SQL> SELECT E.EMP_ID, E.NAME, S1.SCORE AS SUB1, S2.SCORE AS SUB2, S3.SCORE
  2  FROM EMP_TEST E,
  3  SUB1 S1,
  4  SUB2 S2,
  5  SUB3 S3
  6  WHERE E.EMP_ID=S1.EMP_ID(+)  
  7  AND E.EMP_ID=S2.EMP_ID(+)
  8  AND E.EMP_ID=S3.EMP_ID(+);

68628 rows selected.

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=206 Card=68628 Bytes=4117680)
   1    0   HASH JOIN (RIGHT OUTER) (Cost=206 Card=68628 Bytes=4117680)
   2    1     TABLE ACCESS (FULL) OF 'SUB1' (TABLE) (Cost=42 Card=34708 Bytes=381788)
   3    1     HASH JOIN (RIGHT OUTER) (Cost=161 Card=68628 Bytes=3362772)
   4    3       TABLE ACCESS (FULL) OF 'SUB3' (TABLE) (Cost=42 Card=34272 Bytes=376992)
   5    3       HASH JOIN (RIGHT OUTER) (Cost=116 Card=68628 Bytes=2607864)
   6    5         TABLE ACCESS (FULL) OF 'SUB2' (TABLE) (Cost=42 Card=34046 Bytes=374506)
   7    5         TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=71 Card=68628 Bytes=1852956)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1543  consistent gets
          0  physical reads
          0  redo size
    2654846  bytes sent via SQL*Net to client
       8058  bytes received via SQL*Net from client
        688  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68628  rows processed

So as you see the same execution plan, the same stats, the same cardinalities, and as a result the same execution time and consistent gets as expected Smile

Gints Plivna

[Updated on: Tue, 12 August 2008 05:30] by Moderator

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340288 is a reply to message #340285]

Tue, 12 August 2008 03:47

gintsp
Messages: 118
Registered: February 2007

Senior Member

sandhyaa wrote on Tue, 12 August 2008 11:34

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721
CHOOSE
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

So gather stats and you'll use CBO. However you should gather stats for all involved tables otherwise Oracle may fall down to some defaults or do some dynamic sampling. It is controlled by parameter optimizer_dynamic_sampling see more here:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1135.htm#REFRN10140
and here
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#11792

Quote:

2)Also look at your 16013 SQL*Net roundtrips to/from client. This means you have default arraysize 15. See what you get when you say set arraysize 100, so reducing network roundtrips.
Can you please suggest how to increase the arraysize?
Regards,
Sandi

For SQL*Plus it is set arraysize <number>, for all other clients somehow different. Actually the idea here was to show you that other factors may quite increase or decrease performance and in case your app fetches single row (or just few rows) each time then this might hurt quite much. But you should look in your used environment how to set how many rows are fteched each time.

Gints Plivna

[Updated on: Tue, 12 August 2008 05:29] by Moderator

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340328 is a reply to message #340288]

Tue, 12 August 2008 06:03

sandhyaa
Messages: 79
Registered: February 2007

Member

Hi,
After gathering statistics, the picture is:

with ansi sql

Elapsed: 00:00:14.98

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1062 Card=240179 Bytes=13209845)
   1    0   HASH JOIN (OUTER) (Cost=1062 Card=240179 Bytes=13209845)
   2    1     HASH JOIN (OUTER) (Cost=724 Card=240179 Bytes=10567876)
   3    2       HASH JOIN (OUTER) (Cost=426 Card=240179 Bytes=7925907)
   4    3         TABLE ACCESS (FULL) OF 'EMP_TEST' (Cost=161 Card=240179 Bytes=6244654)
   5    3         TABLE ACCESS (FULL) OF 'SUB1' (Cost=91 Card=120204 Bytes=841428)
   6    2       TABLE ACCESS (FULL) OF 'SUB3' (Cost=91 Card=120172 Bytes=1321892)
   7    1     TABLE ACCESS (FULL) OF 'SUB2' (Cost=91 Card=120628 Bytes =1326908)

with traditional sql

Elapsed: 00:00:04.75

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1062 Card=240179 Bytes=13209845)
   1    0   HASH JOIN (OUTER) (Cost=1062 Card=240179 Bytes=13209845)
   2    1     HASH JOIN (OUTER) (Cost=724 Card=240179 Bytes=10567876)
   3    2       HASH JOIN (OUTER) (Cost=426 Card=240179 Bytes=7925907)
   4    3         TABLE ACCESS (FULL) OF 'EMP_TEST' (Cost=161 Card=240179 Bytes=6244654)
   5    3         TABLE ACCESS (FULL) OF 'SUB1' (Cost=91 Card=120204 Bytes=841428)
   6    2       TABLE ACCESS (FULL) OF 'SUB3' (Cost=91 Card=120172 Bytes=1321892)
   7    1     TABLE ACCESS (FULL) OF 'SUB2' (Cost=91 Card=120628 Bytes  =1326908)

with ansi sql

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10821  consistent gets
       2822  physical reads
          0  redo size
   12332857  bytes sent via SQL*Net to client
     176773  bytes received via SQL*Net from client
      16013  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     240179  rows processed

SQL>

with traditional sql

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10821  consistent gets
       1048  physical reads
          0  redo size
   12332857  bytes sent via SQL*Net to client
     176773  bytes received via SQL*Net from client
      16013  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     240179  rows processed

SQL>

Please comment your opinion.

Regards,
Anand

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #340342 is a reply to message #340328]

Tue, 12 August 2008 07:17

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Different physical reads => cache effect.

Regards
Michel

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #342754 is a reply to message #340269]

Sun, 24 August 2008 21:41

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

You understand what Michel means when he says "CACHE EFFECT" right?

Try running each statement twice twice in a row, then look at the results of each statments second run.

It should be noted however, that although in theory there should be no difference between semantically equivalent queries written in ANSI-SQL vs. ORACLE SQL, some people feel that ANSI-SQL has a few advantages of ORACLE SQL from a syntax perspective.

I don't think I will ever use ANSI-SQL because I have been doing oracle since 1985 and am pretty used to the ORACLE syntax. But I know people who say there are a few places where ANSI syntax is superior. Certain potential ambiguities of outer-join with the ORACLE sytanx that can not exist in the ANSI syntax, come to mind as I recall.

Good luck, Kevin

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #343166 is a reply to message #342754]

Tue, 26 August 2008 09:07

Olivia
Messages: 519
Registered: June 2008

Senior Member

Quote:

Different physical reads => cache effect.

I didn't get you!

Regards,
Oli

Report message to a moderator

Re: ANSI - SQL or Traditional SQL [message #343188 is a reply to message #343166]

Tue, 26 August 2008 10:08

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

All the statistics are same but "physical reads" this just means that the second query gets more blocks inside the cache and so did not have to make physical reads.
I bet if OP executed the query in the opposite order it will get the opposite statistics (and led to the opoosite conclusion).

Regards
Michel

Report message to a moderator

Previous Topic:	NOT IN Vs. NOT EXISTS
Next Topic:	Dead lock

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 14:57:44 CST 2024