Home » RDBMS Server » Performance Tuning » ANSI - SQL or Traditional SQL (oracle 9.2.0.6 on AIX 5.3)
ANSI - SQL or Traditional SQL [message #340269] |
Tue, 12 August 2008 02:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #340285 is a reply to message #340275] |
Tue, 12 August 2008 03:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: ANSI - SQL or Traditional SQL [message #340286 is a reply to message #340275] |
Tue, 12 August 2008 03:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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](images/smiley_icons/icon_smile.gif)
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
sandhyaa wrote on Tue, 12 August 2008 11:34 | 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.
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
Re: ANSI - SQL or Traditional SQL [message #343188 is a reply to message #343166] |
Tue, 26 August 2008 10:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
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
|
|
|
Goto Forum:
Current Time: Wed Sep 11 20:59:34 CDT 2024
|