Re: Do triggers cause a context switch between SQL & PL/SQL

I've seen different articles describing switching between the SQL & PL/SQL engines as 'context switches' - when SQL invokes a PL/SQL block, it has to pass data to the PL/SQL engine & vice versa. I haven't had time to try to test it or figure out how to trace this happening. I also haven't had the time to dig about in the manuals to get more info on how the 'engines' work under the covers.

Thanks for pointing out the uncommitted invisible data changes, by the way. I hadn't thought of that. Up to this point, I have yet to find any widows or orphans (I really expected to), but this argument will help me force some redesign down the application area's throats.... Which is what I've been trying to get them to do for quite some time now. If anyone wants an example of how NOT to build an application, from java/corba/toplink to hideous database design, I can send out blueprints.....

                                                                                                                          
                          "Jonathan Lewis"                                                                                
                          <jonathan_at_jlcomp.demon.  T                                                                      
                          co.uk>                   To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>      
                                                   cc:                                                                    
                          Sent by:                                                                                        
                          root_at_fatcity.com         bcc:                                                                   
                                                   Subject:                                          Re: Do triggers      
                                                   cause a context switch between SQL & PL/SQL                            
                                                                                                                          
                          04/04/03 02:23 PM                                                                               
                          Please respond to                                                                               
                          ORACLE-L                                                                                        
                                                                                                                          
                                                                                                                          

I have always been puzzled by the expression "context switch" when talking about sql and pl/sql.

To me, a context switch is something that happens at the O/S level when a process is suspended by the scheduler and a different process is resumed. If a trigger fires on an insert/update/delete, this is obviously not happening - the only process executing is your Oracle server (dedicated or MTS) - so what activity is being given the name "context switch" ? Surely the Oracle process is 'simply' calling a sub-routine somewhere else in its code path (and building a stack and acquiring sub-heaps and ...).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 01 April 2003 19:19

> Since you the action switches from SQL to PL/SQL to enforce the
trigger,
> it sounds like a context switch to me.
>
> FYI: This can be improved somewhat by adding "and rownum < 2 " into
the
> WHERE clauses of these. There will be noticable improvement when
there
> are many child rows.
>
> Jared
>
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: DEEDSD_at_Nationwide.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).