Home » RDBMS Server » Performance Tuning » Unable to Understand Explain Plan (Oracle 10g, red hat)
Unable to Understand Explain Plan [message #328075] Wed, 18 June 2008 20:37 Go to next message
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 #328087 is a reply to message #328075] Wed, 18 June 2008 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

Why do you expect anyone to tune a SQL statement they have NEVER seen?

Are statistics current for objects involved in this query?
Re: Unable to Understand Explain Plan [message #328093 is a reply to message #328075] Wed, 18 June 2008 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Performance Tuning Guide
Chapter 19 Using EXPLAIN PLAN

Regards
Michel
Re: Unable to Understand Explain Plan [message #329919 is a reply to message #328075] Fri, 27 June 2008 01:22 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
like I sed, oracle sux, it can work fast.
Re: Unable to Understand Explain Plan [message #329937 is a reply to message #329919] Fri, 27 June 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question
Did you randomly pick up words in the dictionary? There are much more beautiful ones you know.

Regards
Michel
Re: Unable to Understand Explain Plan [message #329947 is a reply to message #328075] Fri, 27 June 2008 02:15 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
words are not fine,
but it is pure truth.
do you worked with other rdbms?
if you do not tried anything else oracle, your word do not counts!
Re: Unable to Understand Explain Plan [message #329948 is a reply to message #328075] Fri, 27 June 2008 02:16 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Kriptas wrote on Fri, 27 June 2008 08:22
like I sed, oracle sux, it can work fast.



sed ?
Isn't sed a unix stream editor

sux?
Doesnt sux mean Suxamethonium chloride (also known as succinylcholine, scoline, or colloquially as sux) is a medication widely used in emergency medicine and anesthesia to induce muscle relaxation.


Re: Unable to Understand Explain Plan [message #329961 is a reply to message #329947] Fri, 27 June 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kriptas wrote on Fri, 27 June 2008 09:15
words are not fine,
but it is pure truth.
do you worked with other rdbms?
if you do not tried anything else oracle, your word do not counts!

I simply didn't understand what you said... and still don't what you say now.

Regards
Michel

Re: Unable to Understand Explain Plan [message #329970 is a reply to message #328075] Fri, 27 June 2008 03:03 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
fine...
lets say "you do not want to understand me" Wink
Re: Unable to Understand Explain Plan [message #329976 is a reply to message #329970] Fri, 27 June 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kriptas wrote on Fri, 27 June 2008 10:03
fine...
lets say "you do not want to understand me" Wink

No I REALLY don't understand your words and even less your point.
Maybe if you post it in english... my Collins contains neither "sed" nor "sux".

Regards
Michel

Re: Unable to Understand Explain Plan [message #329978 is a reply to message #328075] Fri, 27 June 2008 03:18 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
oh right Wink
what I want to say is :
Last 1.5 years while I working with oracle rdbms, i had only problems. WORST RDBMS I never sow!
Re: Unable to Understand Explain Plan [message #329990 is a reply to message #329978] Fri, 27 June 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're surely not working with it correctly.
1.5 year is very very short to get experience.

Regards
Michel
Re: Unable to Understand Explain Plan [message #329997 is a reply to message #328075] Fri, 27 June 2008 04:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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 Go to previous messageGo to next message
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 #330024 is a reply to message #330018] Fri, 27 June 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
they have oracle 10g sertificates

Laughing

Quote:
show about different 10 queries where oracle more then 2-10 times is slower

And the opposite.

Quote:
you need 10 times more "experience" in oracle to do simple things.

Most of the time you just need to read Database Concepts.

Read all the posts that come here and you'll see that most of the time the only thing to do is to search in the doc.

You're happy with MS SQL, good.
If you want to read more, have a look at:
What don't you like about Oracle?

Regards
Michel
Re: Unable to Understand Explain Plan [message #330033 is a reply to message #328075] Fri, 27 June 2008 07:07 Go to previous messageGo to next message
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?



Re: Unable to Understand Explain Plan [message #330053 is a reply to message #330033] Fri, 27 June 2008 08:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Stop feeding the trolls
Re: Unable to Understand Explain Plan [message #330058 is a reply to message #330053] Fri, 27 June 2008 08:22 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Already done Wink

Regards
Michel
Previous Topic: Tuning the query - Not using the index
Next Topic: performance of database , please help
Goto Forum:
  


Current Time: Fri Dec 09 03:44:52 CST 2016

Total time taken to generate the page: 0.07130 seconds