| Unable to Understand Explain Plan [message #328075] |
Wed, 18 June 2008 20:37  |
ultimatejiten
Messages: 1
Registered: June 2008
Location: Melbourne
|
Junior Member |
|
|
Hi,
I am new to tuning oracle queries and I have this query that takes ages in Oracle to run, the same query runs in seconds in Postgres. The indexes, table straucture and data are the same as Postgres database. I am trying to decipher the explain plan but am new to this and will appreciate if someone can point out the probable cause. The explain plan is given below:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2124 | 549 (3)| 00:00:07 |
| 1 | HASH UNIQUE | | 1 | 2124 | 549 (3)| 00:00:07 |
| 2 | HASH GROUP BY | | 1 | 2124 | 549 (3)| 00:00:07 |
| 3 | NESTED LOOPS OUTER | | 1 | 2124 | 548 (3)| 00:00:07 |
| 4 | NESTED LOOPS | | 1 | 118 | 68 (2)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 108 | 59 (2)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 90 | 58 (2)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | IX_CORE_CASE | 1 | 72 | 56 (2)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | IX_CASE_ASSIGNMENT | 1 | 18 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | CASEKEY_IDX | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | IX_ORG_USER | 1 | 18 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | SYS_C00238171 | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | IX_CASE_DIARY | 13 | 130 | 9 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | CORECASEKEY_IDX | 13 | | 1 (0)| 00:00:01 |
| 14 | VIEW PUSHED PREDICATE | | 1 | 2006 | 481 (3)| 00:00:06 |
|* 15 | HASH JOIN SEMI | | 7 | 749 | 481 (3)| 00:00:06 |
|* 16 | TABLE ACCESS BY INDEX ROWID | IX_SMARTFORM_RESULTS | 1 | 78 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 7 | 658 | 26 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| IX_SMARTFORM_PARTICIPANTS | 7 | 112 | 6 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | PARTICIPANTS_PARTKEY | 7 | | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | RESULTS_PARTICIPANTKEY | 10 | | 1 (0)| 00:00:01 |
| 21 | VIEW | VW_NSO_1 | 917 | 11921 | 454 (3)| 00:00:06 |
| 22 | SORT GROUP BY | | 917 | 19257 | 454 (3)| 00:00:06 |
|* 23 | HASH JOIN | | 917 | 19257 | 453 (3)| 00:00:06 |
|* 24 | VIEW | index$_join$_013 | 917 | 8253 | 396 (3)| 00:00:05 |
|* 25 | HASH JOIN | | | | | |
|* 26 | INDEX RANGE SCAN | RESULTS_DATAELEMENTKEY | 917 | 8253 | 2 (0)| 00:00:01 |
| 27 | INDEX FAST FULL SCAN | RESULTS_PARTICIPANTKEY | 917 | 8253 | 392 (2)| 00:00:05 |
|* 28 | TABLE ACCESS FULL | IX_SMARTFORM_PARTICIPANTS | 15520 | 181K| 56 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("IX_CORE_CASE"."STATUS"='Investigation' AND
LOWER("IX_CORE_CASE"."CATEGORY")<>'complaints' AND LOWER("IX_CORE_CASE"."SUBCATEGORY")<>'pre 85' AND
LOWER("IX_CORE_CASE"."SUBCATEGORY")<>'external party search' AND
CURRENT_DATE-"IX_CORE_CASE"."RECEIVEDDATE">1 AND "IX_CORE_CASE"."DELETED"=0)
8 - filter("IX_CASE_ASSIGNMENT"."DELETED"=0)
9 - access("IX_CORE_CASE"."CORECASEKEY"="IX_CASE_ASSIGNMENT"."CASEKEY")
11 - access("IX_CASE_ASSIGNMENT"."USERKEY"="IX_ORG_USER"."ORGUSERKEY")
13 - access("IX_CORE_CASE"."CORECASEKEY"="IX_CASE_DIARY"."CORECASEKEY")
15 - access("IX_SMARTFORM_RESULTS"."SMARTFORMPARTICIPANTKEY"="$nso_col_1")
16 - filter("IX_SMARTFORM_RESULTS"."DATAELEMENTKEY"=61)
18 - filter("IX_SMARTFORM_PARTICIPANTS"."DELETED"=0)
19 - access("IX_SMARTFORM_PARTICIPANTS"."PARTICIPANTKEY"="IX_CORE_CASE"."CORECASEKEY")
20 - access("IX_SMARTFORM_RESULTS"."SMARTFORMPARTICIPANTKEY"="IX_SMARTFORM_PARTICIPANTS"."SMARTFORMPAR
TICIPANTKEY")
23 - access("IX_SMARTFORM_RESULTS"."SMARTFORMPARTICIPANTKEY"="IX_SMARTFORM_PARTICIPANTS"."SMARTFORMPAR
TICIPANTKEY")
24 - filter("IX_SMARTFORM_RESULTS"."DATAELEMENTKEY"=61)
25 - access(ROWID=ROWID)
26 - access("IX_SMARTFORM_RESULTS"."DATAELEMENTKEY"=61)
28 - filter("IX_SMARTFORM_PARTICIPANTS"."DELETED"=0)
59 rows selected
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to Understand Explain Plan [message #329997 is a reply to message #328075] |
Fri, 27 June 2008 04:39  |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
really ?
1.5 years is short time?
if the version of oracle changes about 3-5 years, so it is enought time.
everything you doing on oracle it takes longer : 2-10 times longer to write source, 1-5 times longer to execute, 10 times longer for DBA to maintain RDBMS.
And all that is if you doing all right.
And it is very costly.
I working in company, where are 5 senior oracle programers.
So what? they code is ugly, they do not coding in ANSI SQL,
all code is slow. I optimizes about 10 queries, and gain was from 40h to 1h, but in MS SQL it is done in 15 minutes.
And all of them (programers) told me, that 40h runing time is the best time evah, that oracle ROOLES, that all queries is optimal.
so never say that oracle is state of the art if you do not tried other systems.
If oracle optimiser is dumb, it is not my problem, I do not need read zilion books, ant put in the query anothet zilion hints to make query run normaly.
If I buy car, I want to ride on it, but i do not want lay down under the car all the time and repair everything after every 5 miles.
|
|
|
|
| Re: Unable to Understand Explain Plan [message #330012 is a reply to message #329997] |
Fri, 27 June 2008 05:45 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | everything you doing on oracle it takes longer :
2-10 times longer to write source
|
Lack of experience, I wrote at the same speed in any language I master.
| Quote: | 1-5 times longer to execute
|
Prove it.
| Quote: | 10 times longer for DBA to maintain RDBMS
|
Lack of experience.
| Quote: | And all that is if you doing all right.
|
How can you estimate this if you're lacking of experience?
| Quote: | I working in company, where are 5 senior oracle programers.
So what? they code is ugly, they do not coding in ANSI SQL,
|
Maybe it is the programmer that is to blame not Oracle itself.
I also work with senior programmer that still programmed as they did in version 7.
Most of the programmers I encountered do not know the basic in RDBMS, just know the basic in SQL, just learn PL/SQL looking at the code the previous ones wrote.
| Quote: | I optimizes about 10 queries, and gain was from 40h to 1h, but in MS SQL it is done in 15 minutes.
|
Prove that better can't be written.
If you have the same code in Oracle and MS SQL then it is obvious that it is bad code for one of the RDBMS.
Even with a car you have to learn how to drive it and it is the same way for a car, a truck, a limousine.
Do you think with your car license you are able to drive a hundred yards long australian trailer truck?
Regards
Michel
|
|
|
|
| Re: Unable to Understand Explain Plan [message #330018 is a reply to message #328075] |
Fri, 27 June 2008 06:09 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
so...
your only argument is "Lack of experience."
20 best oracle programers (which I know in real life) can do nothing better, they have oracle 10g sertificates and so on.
3 DBA cant do nothing with optimizer (yes, I think you right in this case, becuouse they think like 10 years ago, and on production system optimizer is set to RULE becouse they do not know COST optimizer at all) but I cant find any better ORACLE DBA in real life.
I can prove about speed, but only in real life.
I can present identical servers, identical OS and instal on one ORACLE 10g R2, and on another MS SQL2005sp2, pump identical data to the tables, and show about different 10 queries where oracle more then 2-10 times is slower. and lots of ordinary queries where oracle 2 times slower.
like there is another post about slow oracle performance, where 3 dba cand do nothing about faster access to table using indexes or rowid.
oracle have a lot of features which ms sql do not have.. but you can live with out them...
so... about leak of experience...
you need 10 times more "experience" in oracle to do simple things.
experience is the most costly thing.
so to code simple applications with oracle you need to spend about 100 times more money compare to ms sql server.
|
|
|
|
|
|
| Re: Unable to Understand Explain Plan [message #330033 is a reply to message #328075] |
Fri, 27 June 2008 07:07 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
yep, I know...
oracle, ant orher oracle "guru" have zilinion $ income for selling books about nothing, and true oracle fan must buy them.
it is true success story, build dumb and complex (where complexity do not need) system, sell it for bilions $, sel "support" for nothing also for biliosn $, ans sell lots of king books like "how to open PL/SQL developer 10 times faster" for bilions $...
and can't put basic applications in one package, all you need to buy separately..
nice..
realy.. do you tried ever MS SQL SERVER with about 1TB database and more time then 1 month?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
