14 Replies Latest reply on May 9, 2007 4:22 PM by BillC

    Install Questions

      Currently, we are a 10g Oracle Forms and Reports shop. We are planning to use HTML DB.

      We have a typical 10g environment:

      TEST10 and PROD10
      two 10g enterprise server databases one for development and production

      IASDEV2 and IASPROD2
      two 10g Oracle application servers: one for development and one for production

      Within the environment listed above, where do you recommend installing HTML DB? It will need to access the database schemas on the test10 and prod10 instances listed above.

      - create a new database instance on a new computer?
      - create a new database instance on the iAS servers?
      - install in the existing prod10 and test10 instances?

      After installing HTML DB, which web server should we use to support up to 1400 end-users. This would be for intranet use within our local area network (Windows).

      - do you recommend using the iAS web servers?
      - do you recommend using the HTML DB web server?

      Thanks in advance,

        • 1. Re: Install Questions
          If you don't want to use database links (you don't, trust me) then you should install in both. This is very common and reccomended. No reason to run 2 web servers, just use iAS. I wouldn't worry about the load on the web server side, as 99.999 of html db processor load will be on the database side, not the http server.

          Assuming your server is reasonably modern and has more than 1 processor, you will hardly notice 1400 end users. We run a 2 processor box internally that consistenly gets over 250,000 page views a day. You're apps are only as fast (or slow) as the queries you write + ~.07 seconds overhead per page view. Put another way, if your SQL takes 1/10 of a second in SQL Plus, expect the page view to be under 2/10 of a second.

          Good luck,
          • 2. Re: Install Questions
            HTMLDB is a simply a set of PL/SQL packages and tables. Installing this creates two new Oracle Schemas in your database. That is it - you have HTMLDB installed.

            To use HTMLDB you need to have an Apache Web Server (aka Oracle HTTP Application Server), configured with an custom Oracle build Apache module called MOD_PLSQL. Similar modules exist for PHP, Perl, LDAP and so on. MOD_PLSQL provides the interface between the Apache Web Server and the Oracle Database Server.

            You can thus access your HTMLDB applications (and design interface) via any MOD_PLSQL enabled Apache server.

            Why HTMLDB? Many reasons.

            Reduced hardware footprint. No need for a separate & dedicated Application Server. The HTMLDB "Application Server" resides as PL/SQL packages and tables in the Oracle Server.

            Scalability. Fact: Oracle Server is more scalable than any application server. Things like shared processing pools and so on existing in Oracle long before App Servers existed. Oracle RAC? Nothing comes close to it ito scalability.

            Performance & Architecture. (WARNING: rant follows)

            What does any business application deal with? DATA! That is the core. That is what drives the business. Applications come and go. Data is forever. Where does the data live? In the database. The database is the core. The database has been that since the 80's. Is still that. Focus on the core. Design for the core. Leverage the core.

            Think about it ito Excel. What is important? Excel or the spreadsheet data? And that is exactly what the J2EE prophets are claiming. That the spreadsheet is more important than the spreadsheet data. Which of course smells. A lot.

            Having established that, where and what and how is the best way to deal with the data? INSIDE the database. Not in Java Beans. Not outside the database where you have to re-invent the wheel ito concurrency controls and caching and all that. What the hell for!? Can a bunch of Java Beans provide better caching and concurrency than Oracle? Of course not!

            Thus the fastest and most optimal way to deal with DATA, the core of the bussiness, residing in your Oracle Data Server, is using PL/SQL.

            Thus your business applications should be in PL/SQL - INCLUDING business logic. I've often heard the argument for J2EE is business logic. BS! Business logic is code. Code is code is code. Java cannot do better business logic than PL/SQL. What does the business logic deal with? DATA! And where does that data reside? In the database! What is the fastest, flexible, the best darn way, to deal with that data? IN THE DATABASE. USING PL/SQL.

            What does web browsers deal with? Data. They are rendering data. And by now we know (I hope ;-) where the best place is to deal with data.

            HTMLDB does exactly that. It treats the web application as data. Pages. Contents of pages. Reports. Rendering details. Colours to use. Templates to apply. Image to display. Style sheets to use. All this is also data. And HTMLDB handles that for you. Using Oracle Tables and PL/SQL.

            Which is why the HTMLDB type architecture is flexible, fast, easy to use, easy to deploy, easy to maintain, can scale and can outperform most any other app server/app tier architecture out there.

            Comparing HTMLDB with the daily issues I have with JBOSS. We have both. HTMLDB simply works. JBOSS.. one darn problem after another.

            No contest. I recommend HTMLDB.
            • 3. Re: Install Questions

              1) You will be called a dinosaur for such archaic thinking.
              2) You rule.

              • 4. Re: Install Questions
                1) You will be called a dinosaur for such archaic
                Yeah.. but you know what is interesting.. thin client architecture.

                It is not new. It is exactly the SAME architecture I used back in the 80's. On mainframes. Sure, the name of the software components have changed. Instead of a Universal Transaction Monitor, we now have an App Server. Instead of an Interactive Format Generator, we have a web browser. Etc.

                But the architecture is software component for software component the same. Now the J2EE prophets are telling us that their frameworks and architectures are new and innovative and will revolutionise client-server? They are still in nappies. Which explains the smell.
                2) You rule.
                Thanks Joel. As agreed upon, the $50 is in the mail. :-)
                • 5. Re: Install Questions
                  Hi Billy,
                  I just see your comment via a link from Oracle WTF (no it is not against your post they are pros!) but just want to say the whole of Business Logic does not deal with persistence data so I agree with having "Data centric logic" in sort of PL/SQL but for other parts I (and guess many other mans) prefer something more scalable and really object oriented (not hyped later ;) such as Java.
                  • 6. Re: Install Questions

                    The theory sounded good.. until further thought...
                    Please let me know if this is incorrect because we want to continue with APEX.

                    with a high volume of users APEX seems to go to the database for EVERYTHING everytime anything is done. If the initial menu page is all static links it still does thousands of reads (according to STAT_PAK) just to build the simple page. Yes, this is all data but if you have the web server cache supplying the static pages, items, etc. avoiding the database hits doesn't that split the load up? and then when you add in a middle tier doesn't the calculations etc remove more proccessing from the database. In smal apps I see that everything on the database to create the page to be renderred would be much quicker. But when you have thousands of users on at atime then the more that can be done on the client and the other tiers would benifit all the wait locks on the database thus slowing down the app tremendously.

                    We had 2 sec response time until we opened the apps to another group of users, now 20% of the time it takes 500% more time to return the page content.

                    We want to go to RAC.. what experience do you have using it? you say you can't beat it.. we agree.. but we don't see much on RAC with APEX.

                    Any help would be appreciated.

                    • 7. Re: Install Questions
                      John Edward Scott

                      I recently did a presentation at Collaborate 07 precisely on issues such as this, namely how can you reduce some of the 'hits' against the database (this answer is 'in many different ways!').

                      APEX 3.0 provides some fantastic caching abilities that may be beneficial to you, there are also many improvements to can make to cache images and also you might want to look at using HTTP compression to reduce the load on your webservers too.

                      The whole area of application optimization is obviously not easily wrapped up into a simple series of steps, but I'm pretty sure there are lots of things that could be looked at which could improve things for you, but we would really need more detail about the actual application.

                      Feel free to contact me offline if you'd prefer help in a more 'commericial' way ;)
                      • 8. Re: Install Questions

                        Per your request from this thread:

                        How does APEX do it's clean-up of temp tables, temp files?

                        I'm not sure what feedback you're looking for.

                        I will say one thing, though. Per your statement of "We had 2 sec response time until we opened the apps to another group of users, now 20% of the time it takes 500% more time to return the page content." Something is wrong with your application.

                        Firstly, 2 second response time is not good. I usually shoot for 0.20 seconds or under. To me, it sounds like you have ample room for tuning opportunities in your application (that is, your application logic and queries, and not anything associated with the APEX engine itself).

                        • 9. Re: Install Questions
                          Anton Nielsen

                          In general I agree--if an application performs poorly in Apex, it will perform poorly in other technologies as well, assuming you do the same kinds of things. As Jes mentioned, though, there are a whole bunch of things you can do to improve an Apex app's performance.

                          Just as a note, though, there are things that show up in the documentation, or that the engine lets you do, that can cause problems. They sometimes seem obvious later, but when you click through the documents and follow the steps you might not realize what is going on. One instance comes to mind. When creating a report, you can edit a column and change the report column to "Display Only Based on LOV"--allowing you to select a code but display the meaning, for example. This seems like a great option and it shows up in the how-to's (at least it did). The problem is, it can kill performance as you end up building an LOV for every row of your report. The performance comes right back in line by adding the table with a join into the select statement.

                          As you said, this is not a problem with the Apex engine, it's the way the App is developed. If an application is running slowly, use the debug mode. It will often show you where the bottleneck is.

                          After reading over this, I'm not convinced I'm adding a lot to the discussion, but maybe that LOV issue will help someone, so I'm going to post it anyway.

                          • 10. Re: Install Questions
                            Hi All,

                            What I guess I am looking for is a knowledge base of how to set up a nice RAC/APEX environment. This would also of course have tuning of your APEX application and HTTP server(s) to work best with RAC.
                            :>) Any tech docs, books, etc.

                            We have a half terabit of data with about 2,000 uses that use the applications daily.

                            I can go to my books on how to tune the database which is very dependant on sooo.... many things. Just as Jes says about APEX...

                            Most documentation I have found on APEX has been higher level so I have to go into the forum and ask basic questions until I get enough knowledge to ask deeper questions.

                            I can say I understand allot more than I did a few days ago thanks to this forum and all your responses.
                            • 11. Re: Install Questions
                              Okay, Anton. You've got me on that one.

                              You did happen to pick an extreme (and I'll say isolated) case in Application Express where, if employed, performance could be sub-optimal. I specifically remembering when authoring that underlying function (text_from_lov) that it would not be ideal for performance, and the correct way for a customer to implement this would be as you suggest - joining to a table. In certain cases and with small data sets, this can be safely used. And for customers who know nothing about SQL or joins, this is more easily understood. But this sub-optimal case is certainly atypical for Application Express.

                              I can give an example which is similar in negative performance, but illustrates my broader point. A group wanted to rollout on our internally hosted server their application. They wanted to announce it on a Monday via e-mail to a couple thousand people. I had asked about what performance analysis was performed, and I repeatedly got back the answer that "it was perfectly tuned" - their page views were roughly 2 - 3 seconds. Finally, I went in and analyzed their application and some of their pages/queries. In about 5 minutes, I identified 3 separate queries which were employing multiple functions in the select clause, and each of these functions was performing another query. So for a given report, they thought they were performing a single query, when in fact, in some cases, they were performing over 12,000 queries - for a single report! That may give the illusion that it's acceptable for a single user, but when you have 2,000 people each trying to run reports which perform 12,000 queries apiece, performance will suffer for everyone and sparks will fly out of the data center. They eventually rewrote all of these reports and achieved < 0.25 second page view times.

                              My point? In general, I've found that most "Application Express performance problems" are usually application tuning problems and/or database tuning problems.

                              • 12. Re: Install Questions

                                I'm not trying to de-sell Oracle products, but why are you convinced that you need to go down the RAC route?

                                What is your maximum number of active database sessions for this 2,000 user population? Can anything be done to improve the response time (as I said before, 2 seconds is a very long time)?

                                There's a simplistic formula I cooked up for this Oracle Magazine article:


                                Basically, given the number of CPUs and the number of expected page views you expect per minute, you can calculate what your target page view time needs to be. Anything greater than this, and you'll have users waiting.

                                As an Oracle shareholder, I'm all for you going down the RAC route. But given the finite details of your situation, I believe your issues may be rectified through analysis and tuning.

                                • 13. Re: Install Questions
                                  Anton Nielsen

                                  I'm all about picking extreme cases! It may not always be obvious in my posts, but I'm also an extreme advocate of Apex.


                                  P.S. I tend to read any post I see your name on because it is sure to be valuable, so you get some of my most obscure concerns in return.
                                  • 14. Re: Install Questions
                                    Hi Joel!

                                    That was a good article! Yes, that will narrow down where to give our attension.

                                    As far as the RAC, it is already purchased and scheduled to be installed because they are scaling up on many applications, APEX, Oracle 6i, J2EE, many data loads, extracts to warehousing, etc.

                                    APEX is my area which I'm gaining more and more knowledge in this gleaning trying to answer the questions of the group I'm with at this time.

                                    Now, if there were articles on configuring multiple webservers, clustering webcache and RAC all based on APEX I would be in great shape!

                                    Thank you for your input! Bill