Re: Oracle Object Types and Java (migrating from mod_plsql)

In article <1108940944.315582.101500_at_l41g2000cwc.googlegroups.com>, Thanatos says...
>
>Dear Tom,
>
>I've been itching to talk to you about this, thanks for picking it up.
>
>> ahh, but what isn't scaling out?
>
>To be honest the server (some 8cpu Sun monster) is not running too
>badly.
>
>And admittedly there is still a lot of work that can be done to improve
>it's performance. We run alot of generic sql (via execute immediate)
>and it's also been a habbit to write every query as execute immediate.
>So I'm sure we could get more life out of the old dog yet.
>
>What executive management is worried about is that sometime in the
>future we will not be able to run any more sql or serve any more html
>from the server.
>

ahh, good old hypothetical fear. I'm afraid that an asteroid is going to hit earth some day wiping out life as you and I know it (including us).

But, I still have to get out of bed in the morning.

some of the things you mention above are huge scalability inhibitors -- the execute immediate for example -- that is downright "wrong" to have dynamic sql "the norm". dynamic sql in plsql should be the exception. You lose the parse once, execute many (you might pick SOME of it back up in 10g, execute immediate has an optimization or two to try and cache sql). You lose the dependency chain, the compile time checks -- you lose tons.

>They are fearful that we will simply have to buy a larger server, which
>they view as prohibitively expensive. Buying Oracle-AS is also seen as
>too expensive. So we have been exploring alternatives.

and human beings are cheap to pay? Ask them for the spreadsheet that compares a CPU of software with a person (include all of the person costs please -- not just their paycheck)

>
>One alternative is the j2ee framework and the company already has
>invested a lot into this project. The tag line of the project seems to
>be:
>
>"We will simply buy some more small, cheap computers to serve the
>application as we need too."

and pay dozens of j2ee architects to sit around and build something with the technology du-jour which will go out of date by the time we go production and they'll just rewrite it all :)

that is another way to look at it. How will have dozens of small cheap machines that eat your database like a swarm of pirannha help? You want to see a machine go under? let people who don't know the database throw database stuff at it.

>
>Given that the research and implementation of this project has taken
>over a year already, with the purchase of *5* extra computers for an
>application not 5% as large (or complex, or accessed) we seem to be
>heading in a very dangerous direction.

been there.

>
>>is it "we cannot run anymore sql", well, moving to java isn't going to
>make the
>>sql run any faster (in fact, my experience is the opposite is true,
>the java
>>programmers tend not to write the best sql, the database needs become
>larger
>>then when we had database programmers in there).
>>have you identified what isn't "scaling" about your situation? before
>you
>>propose a fix, you need to identify exactly what is "broken"
>
>I would say that the need for the server to generate and serve the
>entire application is what is "broken." We need to split the http
>serving and the data access.
>

why? I run really big stuff using htmldb, it scales rather "well". You want to see a database fall over -- throw billions of little queries at it from a "scalable middle tier" written with java in mind (not database performance) and see what happens.

How many times have I seen "well, we were afraid to join in the database, "everyone knows" joins are slow so we did it in the middle tier". "Well, we dont use order by because "everyone knows" that sorting is expensive so we pull all of the rows to the middle tier and sort them there"

those things *kill* the database. why? the fastest way to paginate through a result set is NOT to pull the entire result set to the middle tier, sort it and display it - it is to ask the database for the first 10 rows, then rows 10-20 and so on. The probability is that 99% of the time we'll never actually get the entire result set. The database does alot less work, not more.

I'm seriously skeptical that your scaling issues are with htp/htf -- first, you said above you don't really have any right now (it is "fear of the possible future"). Why don't you step back and test the machine to destruction (a quote from a book by Jonathan Lewis). See where this puppy breaks and what is breaking it (eg: I would guess your first major limiter will be the excessive soft parsing incurred by execute immediate!)

test it to destruction, see where this breaks, see if you'll ever get anywhere near it.

You know what is really expensive? Spending a year analyzing with a team of people.

>>> Re-write stored procedures as Oracle Object Types.
>> that doesn't make sense -- what do you mean by "rewrite stored
>procedures as object types"?
>
>Well currently we have a stored procedure which generates a page via
>htp.
>
>I am proposing we generate the page data in an object. This object can
>be initialized from a stored procedure that again uses htp to display
>the application *or* a java application could initialize the object and
>display the application using "print."

rather return XML and have something in the middle tier apply xslt to it to transform it, but then you are formatting xml (or putting into an object type or whatever).

bottom line, the htp stuff isn't hugely expensive here -- why not measure it? See what amount of time you spend doing

1. sql
2. plsql

And see what payback you would get by not using plsql "as much" (you'd still be using it alot to put the object together perhaps).

You might be surprised (and a rule of tuning is to know what to expect. before I travelled down the path of "lets remove plsql to make us scale more", i'd want to seriously understand what I'd be getting back in return - don't you?)

>
>The point being it would be easier to move pl/sql code into objects
>rather than rewrite everything in Java. I suppose I am suggesting the
>application has an API defined through Oracle Object Types.
>
>eg
>create or replace type result_t as object (
>result_list_html web_arr,
>member function display_summary return varchar2,
>member function display_results return varchar2)
>
>--pl/sql
>decalre
>
>my_results result_t := result_t();
>
>begin
>
>--display results
>htp.p(my_results.display_results);
>
>end;
>
>
>Or we could write this in another language that can access Oracle
>Objects (ie Java)
>
>I suggest the benefits of this are:
>Very simple Java component - that can be deployed on another server to
>handle the web serving of the application.
>
>Easier to move pl/sql from an application (stored procedures) into
>another pl/sql container (Oracle Object Type) the re-write the entire
>application (in Java, PHP or whatever.)
>
>
>Does this make anything clearer? As you probably can tell I'm
>grasping...
>
>Assume the server is at maximum capacity, what would Oracle say about
>our dilema? Buy a bigger server? Buy Oralce-AS? Both options would
>appear to be out of the quetsion for our company.
>
>Kind Regards,
>James
>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation