tag:blogger.com,1999:blog-12214002.post1713205007019094542..comments2024-03-09T06:56:57.099-05:00Comments on Let's Wreck This Together...with Oracle Application Express!: Make all of your APEX applications run a bit fasterJoel R. Kallmanhttp://www.blogger.com/profile/01915290758512999160noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-12214002.post-40879290224348807822015-12-05T22:41:44.050-05:002015-12-05T22:41:44.050-05:00Nice, I tried it on APEX 5. DB 11.2.3 where APEX h...Nice, I tried it on APEX 5. DB 11.2.3 where APEX has become remarkably faster..<br /><br />alter session set plsql_optimize_level = 2;<br />alter session set plsql_code_type = interpreted;<br />exec dbms_utility.compile_schema('APEX_050000');Fatehhttps://www.blogger.com/profile/16188541271419542725noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-45106195528034368992013-06-13T16:13:50.628-04:002013-06-13T16:13:50.628-04:00Hi Dean,
Good question. To be honest, we have no...Hi Dean,<br /><br />Good question. To be honest, we have not enabled it on apex.oracle.com because I've gun been shy ever since the issues we had in 2009, when we had to disable it.<br /><br />In talks with the PL/SQL group, they indicated that of all of the permutations (native code calling interpreted, interpreted code calling native, etc.), the most expensive is for native code to call interpreted. And thus...they recommended that all PL/SQL in the database be natively compiled. There is a (probably unsupported) script in $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql that can be used to facilitate this.<br /><br />So....do we recommend this? I personally don't have enough evidence to recommend one way or the other. But I'd consider trying it again.<br /><br />JoelJoel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-78777455124262845522013-06-12T19:03:43.655-04:002013-06-12T19:03:43.655-04:00Joel
Is it now ok or recommened to use Native com...Joel<br /><br />Is it now ok or recommened to use Native compile on Apex 11.2g databases?<br /><br />Thanks<br />DeanAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-6873346192608886302009-12-02T22:43:00.851-05:002009-12-02T22:43:00.851-05:00@Drew - it's possible, but it may be slightly ...@Drew - it's possible, but it may be slightly more involved than the instructions I've given here. I personally haven't tried it on 10gR2.<br /><br />JoelJoel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-66540000990926817122009-11-30T14:33:54.176-05:002009-11-30T14:33:54.176-05:00Is this possible with 10gr2?Is this possible with 10gr2?Drewhttps://www.blogger.com/profile/05810185785379857057noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-25346078785794730612009-10-06T10:06:23.704-04:002009-10-06T10:06:23.704-04:00@Kris - sorry, I really don't have any guidanc...@Kris - sorry, I really don't have any guidance there. This is why I tried this in 11g, as it used to not be so simply prior to 11g.Joel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-60867065183431690382009-09-23T10:48:16.336-04:002009-09-23T10:48:16.336-04:00Joel,
I followed your steps, and I too was unable...Joel,<br /><br />I followed your steps, and I too was unable to get the TYPES to compile. When I run the additional script as shown below, I get the error message also below.<br /><br />This is running on an Oracle 10g Enterprise 10.1.0.5.<br /><br />begin<br /> for c1 in (select type_name, owner from dba_types where owner = 'FLOWS_030100') loop<br /> execute immediate 'alter type ' || c1.owner || '.' || c1.type_name || ' compile';<br /> end loop;<br />end;<br /><br /><br />--select type_name, owner from dba_types where owner = 'FLOWS_030100';<br />Error at line 22<br />ORA-02311: cannot alter with COMPILE option a valid type with type or table dependents<br />ORA-06512: at line 3<br /><br />Also, when just running the compile_schema code sample, my apex applicaitons wouldn't run at all.<br /><br />Any ideas?Kris Hennemanhttps://www.blogger.com/profile/10909651220229130141noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-85950459262776718552009-07-19T12:56:03.534-04:002009-07-19T12:56:03.534-04:00i think it does not work on Oracle XE.
I got a lot...i think it does not work on Oracle XE.<br />I got a lot of invalid APEX views and APEX is not running any moreسليم هليّلhttps://www.blogger.com/profile/06359663432177409545noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-36443241855019679442009-07-06T23:51:41.235-04:002009-07-06T23:51:41.235-04:00Joel,
Any news on what caused the ora-600 errors? ...Joel,<br />Any news on what caused the ora-600 errors? I'm interested in seeing how much native compiling would benefit APEX applications.<br />ToddAravehttps://www.blogger.com/profile/10050725129064532069noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-67932871306931979212009-03-24T16:27:00.000-04:002009-03-24T16:27:00.000-04:00Excellent post, did for myself a complete change o...Excellent post, did for myself a complete change of the DB from interpreted to native, but from the Oracle documentation at: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#insertedID10 <BR/>I found the following:<BR/><BR/>"During the conversion to native compilation, TYPE specifications are not recompiled by dbmsupgnv.sql to NATIVE because these specifications do not contain executable code.<BR/>Package specifications seldom contain executable code so the run-time benefits of compiling to NATIVE are not measurable. You can use the TRUE command-line parameter with the dbmsupgnv.sql script to exclude package specs from recompilation to NATIVE, saving time in the conversion process."<BR/><BR/>So these objects in the db that do not contain executable code don't profit from conversion from interpreted to native.Gerbenhttps://www.blogger.com/profile/14046684546070522013noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-46825646771954023812009-03-10T10:12:00.000-04:002009-03-10T10:12:00.000-04:00Joel,after i run "exec dmbs_utility.compile_schema...Joel,<BR/><BR/>after i run "exec dmbs_utility.compile_schema..." more than 700 objects (views, triggers) are invalid.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-61014962112628979942009-03-10T10:11:00.000-04:002009-03-10T10:11:00.000-04:00Hi Carsten,Short of doing it myself with your spec...Hi Carsten,<BR/><BR/>Short of doing it myself with your specific APEX Version and database version, I'm not sure at this time.<BR/><BR/>JoelJoel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-35087805343226006582009-03-10T09:27:00.000-04:002009-03-10T09:27:00.000-04:00Hi Joel,if i run: "exec dbms_utility.compile_...."...Hi Joel,<BR/><BR/>if i run: "exec dbms_utility.compile_...." the files *.c are saved to the "plsql_native_library_dir" correctly.<BR/><BR/>The optimization level is 2, the same as yours example.<BR/><BR/>CarstenAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-55040374419802788142009-03-10T09:07:00.000-04:002009-03-10T09:07:00.000-04:00@Carsten,I don't really know. What was your optim...@Carsten,<BR/><BR/>I don't really know. What was your optimization level? What do you mean by "The interpreted files are saved in the correct dir."?<BR/><BR/>JoelJoel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-45595863030330643262009-03-10T09:03:00.000-04:002009-03-10T09:03:00.000-04:00Hello Joel,i've try this. The interpreted files ar...Hello Joel,<BR/><BR/>i've try this. The interpreted files are saved in the correct dir.<BR/><BR/>But if i access my apex-application an error 404 result. The apache-error-log says: <BR/><BR/>....mod_plsql: /pls/htmldb/f HTTP-404 \nf: PROCEDURE DOESN'T EXIST<BR/><BR/>Oracle 10.2.0.4, Apex 3.1.0.00.32<BR/><BR/>Whats wrong?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-5857899028139837032009-03-05T15:45:00.000-05:002009-03-05T15:45:00.000-05:00Excellent post Joel. I would be very interested in...Excellent post Joel. I would be very interested in any metrics you have on the performance increase. Thanks for posting this though.<BR/><BR/>Duncsruncsmeduncshttps://www.blogger.com/profile/06299973847200066992noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-71764607992134200882009-03-04T10:59:00.000-05:002009-03-04T10:59:00.000-05:00I had the same issue with the types being left as ...I had the same issue with the types being left as interpreted. My machine was 11.1.0.6 on Enterprise Linux in the cloud. Your workaround worked perfectly for me.Jason Straubhttps://www.blogger.com/profile/12627913070109819002noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-9427889028711530052009-02-28T17:28:00.000-05:002009-02-28T17:28:00.000-05:00It works now Joelall of them compiled.Thank youIt works now Joel<BR/>all of them compiled.<BR/>Thank youسليم هليّلhttps://www.blogger.com/profile/06359663432177409545noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-91452598132745186942009-02-26T12:14:00.000-05:002009-02-26T12:14:00.000-05:00You had me worried that maybe I did "fake" my quer...You had me worried that maybe I did "fake" my query results, but I checked it again - I assure you, nothing faked here. I wonder why DBMS_UTILITY.COMPIILE_SCHEMA isn't touching those TYPEs. I didn't have to do anything separately before. Either way, try this:<BR/><BR/>alter session set plsql_optimize_level = 2; <BR/>alter session set plsql_code_type = native; <BR/>begin<BR/>for c1 in (select type_name, owner from dba_types where owner = 'APEX_030200') loop<BR/>execute immediate 'alter type ' || c1.owner || '.' || c1.type_name || ' compile';<BR/>end loop;<BR/>end;<BR/>/Joel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-2533605918007309212009-02-26T12:04:00.000-05:002009-02-26T12:04:00.000-05:00Same with me.4 types are still interpreted.Maybe t...Same with me.<BR/>4 types are still interpreted.<BR/><BR/>Maybe the difference between apex 3.1 and 3.2, since Joel did get them natively compiled? Unless off course Joel faked his query results ;-)<BR/><BR/>Anyway, on my "try what you like, I don't care databse" I notice a very pleasing increase in performance.<BR/>Both developing and using the application<BR/><BR/>I'm definately going to sugest to do the same for the 'real' databases.<BR/><BR/>ErikAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-942952453761347332009-02-25T05:35:00.000-05:002009-02-25T05:35:00.000-05:00Georges,I am getting the same exactly.COUNT(*)OBJE...Georges,<BR/><BR/>I am getting the same exactly.<BR/><BR/>COUNT(*)OBJECT_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE<BR/>--------------------------------------------------------<BR/>12 FUNCTION 2 NATIVE<BR/>358 PACKAGE 2 NATIVE<BR/>350 PACKAGE BODY 2 NATIVE<BR/>19 PROCEDURE 2 NATIVE<BR/>1 TABLE 2 NATIVE<BR/>243 TRIGGER 2 NATIVE<BR/>4 TYPE 2 INTERPRETEDسليم هليّلhttps://www.blogger.com/profile/06359663432177409545noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-46958139642967428122009-02-20T19:58:00.000-05:002009-02-20T19:58:00.000-05:00Joel,I followed all steps and here is my result :C...Joel,<BR/><BR/>I followed all steps and here is my result :<BR/><BR/>COUNT(*)OBJECT_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE<BR/>--------------------------------------------------------<BR/>12 FUNCTION 2 NATIVE <BR/>358 PACKAGE 2 NATIVE <BR/>350 PACKAGE BODY 2 NATIVE <BR/>19 PROCEDURE 2 NATIVE <BR/>1 TABLE 2 NATIVE <BR/>243 TRIGGER 2 NATIVE<BR/>4 TYPE 2 INTERPRETED<BR/><BR/>I wonder why Types are still Interpreted. <BR/>I use Oracle 11G.<BR/><BR/>Thanks.<BR/>GeorgesAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-12214002.post-77748566995312694662009-02-20T15:29:00.000-05:002009-02-20T15:29:00.000-05:00John,I don't. I know that seems like an obvious q...John,<BR/><BR/>I don't. I know that seems like an obvious question, but I was a little under the gun today. Once APEX 3.2 goes out the door, I'll modify this post with actual numbers (on 11gR1 on OEL on VMWare on Vista x-64).<BR/><BR/>JoelJoel R. Kallmanhttps://www.blogger.com/profile/01915290758512999160noreply@blogger.comtag:blogger.com,1999:blog-12214002.post-87299260788653817552009-02-20T13:49:00.000-05:002009-02-20T13:49:00.000-05:00Joel,Excellent post.Do you have any figures to han...Joel,<BR/><BR/>Excellent post.<BR/><BR/>Do you have any figures to hand of the sort of difference in timings this made?Anonymousnoreply@blogger.com