---
title: WWDC2004 Session 620
framework: wwdc
role: article
path: wwdc/wwdc2004-620
---

# WWDC2004 Session 620

## Transcript

Kind: captions Language: en oon my name is Matt Sturgis on a global alliance manager at Apple who manages the Oracle relationship and I like to thank everybody for coming here it's been a very exciting week for Apple and Oracle start off with a keynote address mentioned by Steve Jobs on Monday followed up a little bit later that morning with some additional messaging about some products availability coming out from Oracle in the coming months with 10g oracle 10g and jdeveloper their development ide for Apple we're very very excited about our relationship with Oracle moving forward I know for some of you out there in the audience this has been a long time coming we're very excited to be bringing this technology to you today's presentation i think is going to be a great great learning experience for everybody here we've got some incredible presenters from oracle introduced in a second but we're very excited again about oracle 10g coming onto the platform today they release in press announcements and it's going to be available this fall again with jdeveloper their java development tools so again i like to thank you for coming today we look forward to to a great event with oracle i like to now as for speakers tom kite is the vice president of core technologies out of Oracle's rest in office responsible for government education and healthcare is going to go ahead and kick off the presentation followed by mark Pierre marini senior technologist out of that office he's going to give us a great demonstration of the power of HTML DB their application development platform so I like to thank Tom and introduce them and get this kicked off thank you [Applause] good afternoon so he said my name is Tom kite I've been with Oracle going on about 11 years now but have been using the database software for about 16 knots just curious just to get a feel in the audience how many of you have developed applications using oracle is the database excellent so I don't have to explain what SQL and other things like that stands for perfect one of the things I spend way too much my time doing at Oracle is working on a website called a spa morecambe that's one of the many resources out there for you to get answers to technical questions and things like that another resource that I'd like to sort of point out get out of there for second and go over into the web browser otn oracle com this is the Oracle technology Network and just to prove that this stuff actually exists you can go there today this afternoon if you like to product centers download for the database and featured right down there we have the Oracle 10g developers release for the mac OS so you can go get it the same software that we're running on these Xers over here for the for the demonstration is what you guys would be downloading and installing in your own environment so what you see is doing are things you'd actually be able to do yourself later this afternoon and back to the slide so we have a really short agenda I'm going to talk about to database things the two main things about oracle 10g the g in 10 G stands for the grid so we're going to take a look at that briefly then we're going to look at the the other half of 10 gb what's in it for all the rest of us the manageability features that they add it to the database 10g has over 350 new things new functional areas added to it over 9i release 2 which had about 150 things over 9i release one which had about a hundred and ninety things over a tie every year we've been coming out with more and more inside the database 10g is our current production relief we started rolling it out on various platforms in the middle of this winter the februari time frame available on the Macintosh now and a developer's release consider that sort of late beta and we're looking at the fall time to go production and then we're going to close up with HTML BB we its marked or marinis going to sort of walk you through and show you what this this development tool is so beginning very simply with the grid this is sort of a picture that describes the sort of systems I've worked on him I've been doing this since the mid-1980s and I started as a peel one programmer on an IBM mainframe using this thing that not many people heard about because I was the newest kid there I got to play with the coolest new stuff so they set me about programming answer this thing called db2 and sequel des and that was really started you know where I got all my my sequel background but pretty much a lot of our systems back then we're mainframe base very reliable centralized these things never went down when I first came to Oracle in the early 90s there was a lot of shift away from a mainframe source centralized environment into more of a departmental server so sovak was what we all ran on I had a VT 100 connected to fax machines everything with character mode we had literally hundreds of servers all over the world running things in the mid-90s we went through this sort of I call it a faux pas this is client-server face I remember 1995 we rolled out to 20,000 sales rep laptops with client server software on it it was called sales force automation is going to make everybody so super productive this would be great the only thing we really got out of this was on Monday we had 20,000 people with the same installation and by Friday we had 20,000 sort of unique installations out there trying to keep the software and thinking keep the patches going was just impossible fortunately in 1995 this internet thing started coming along as well and we migrated all of our applications to a more internet centric approach we sort of got back to a lot of the attributes of the mainframe the browser became our 3278 sort of screen if you will but we went back through a major reconsolidation phase where we took a lot of our systems and clump them together into single systems instead of running 187 email servers we went down to single email servers one of the things that allowed us to do this to sort of consolidate these systems so I'm going to be talking about next this grid architecture so there's a lot of people who study this and they look at systems and they say you know what the way we're doing it is wrong I've got this system that for most of the years it's their Idol or underutilized and they may be one or two months out of the year the thing is really busy I'll use one of my customers as an example the IRS I know they have a lot of systems that in January start to ramp up by April fifteenth at midnight they're going gangbusters and then after that those systems are pretty much idle for the rest of the year but then they have other systems to pick up at the end of April to start processing the data and what they've done is what most of us would do they put somebody in charge of system one and you went out you got his budget you said you know I need to size for April fifteenth they gave another system to somebody else the data warehouse well I need 25 my system for may and so they go out and they buy these mega sort of machines everything is configured for their peak ululation the availability is fairly good not as good as it could be but it's pretty much a poor way to utilize this because there's any point in time if you added up all the free cycles you probably have three empty computer sitting out there because one of the machines is fully utilized and the others are not doing much at all so we've come along along with a lot of other people in Oracle it's not trying to say we invented the grid or the grid is Oracle or Oracle is the grid there's many ways to approach grid computing for us is our very simple definition coordinated use of many small servers acting together as one large computer like a bunch of Xers inside of Iraq this is different from say IBM's programmatic approach to grid computing they have a grid tool kit where someone can take their problem basically decompose it paralyze it use the grid tool kit and come up with a very scalable solution for their particular problem we took it from a different perspective we wanted to take applications that already existed and be able to plug them into this grid environment without having to change them so what we've done is sort of our grid tool kit is our database our application servers you have an application that you'd like to run in a grid sort of environment you don't have to program specifically and build a briton you just take a database application and deploy it in a grid capable database so one of the examples this is sort of what I was talking about before I've got two systems order entry and financials well things go very cyclical this is May 31st at Oracle May 31st is the end of our fiscal year we booked most of our business in the last couple of hours for the year on that particular day those order entry systems are totally maxed out the very next day for the next month the system is really busy is our financial system so we started doing is consolidating these instead of having separate stove pipe systems put our applications into a single database with multiple servers in front of it so we have an order entry financial database single database single set of discs but three or four Wow ever many computers we need able to access that information in a read/write fashion and we can load balance across those so one month two out of three nodes could be order entry the next month to Ebbetts green nodes could be a financial system so we're able to allocate and reallocate these resources very quickly very a MacLean the technology that lets us do this is something we call real application clusters you're all familiar with raid 4 discs that just makes sense at this point you understand how that works maybe you mirror your disks and if you lose one that's okay you still have a mirror of it maybe you're using raid 5 so you have five discs working together and if I lose one that's ok as well Rack is raid for your computer system in a clustered environment like this i'm going to have one database so those four disks represent a single database if you will I'm going to have four instances of Oracle in this particular example so I have four computers that have full read/write access to this single database image I can log in the node 1 and run a query I can log in to node 4 run the same exact query it's going against the same data in fact I can update the same information in node 1 deduction update in node 2 3 and 4 this bodice two things first and foremost it by this much higher availability I have to lose three out of four computers before I'm in danger of having the system become totally unavailable so it gives me the ability to reduce unplanned downtime if I lose a power supply cpu goes bad something happens to a ram chip it's okay one of those nodes can go down the other nodes will pick up the workload the other thing he gave me was the ability to scale my application out I don't know how big my application is going to ultimately be all I know is initially i'm going to have 50 users it might grow to five hundred it might grow to five thousand it's really hard to spy systems for three or four years out sometimes because you don't know how popular some of them are going to be you know the website you're building it to be the coolest thing since sliced bread or is it going to be a ho-hum event well you don't want to have to buy a super-large server to serve up something that becomes a whole arm of event eventually down the road be much nicer to start small with the hardware start drawing it out over time is needed we did this with our email we consolidated all of our email in Oracle from 187 servers down to one we started with a two node rack cluster we needed additional capacity we're now running a three node wrap cluster on our email email for Oracle is a mission-critical sort of application if we don't have email up and running we actually can't process orders we can't do order fulfillment we can't do our job basically so our email system being able to scale it out horizontally by adding additional capacity as well as having it highly available so that it's a single node or two nodes evening sale we still have access to our email systems so what was new in 10g Rack is something we introduced in oracle 9i in oracle a time before you might have known it as oracle parallel server in 10g we made it infinitely easier to install configure and manage so for example one of the things we did was added integrated clustering software in the past so i was using oracle 9i and i wanted to run rack what i have to do is is get the oracle database from oracle i'd have to get clustering software from some vendor I have to get an operating system run this on and then I need some sort of volume management software to manage my disk and that infrastructure you have these four pieces of software probably coming from four different companies or maybe two or three but four different pieces of software are complex software you install them onto the the hardware and you start it up and the first thing that always seem to happen was nothing would work so you call up vendor one and they'd say we need this OS patch apply the OS patch that break the vendor to vendor to set so if you apply that patch we have to have this as well and then that Briggs vendor three and then vendor cerises off if you apply this patch everything's going to be great everything is great but you've got this environment it's pretty fragile you're sort of afraid to apply a patch at that point because what if the patch has a bad interaction say some Veritas software or the security patch causes the networking layer to behave differently and Oracle doesn't respond the same way anymore so you've got this very fragile environment that you're afraid to touch with this integrated clusterware and as we'll see in a minute stories management software you have two pieces of interacting software now we're going to have oracle running just enough clustering software to make your database cluster just that a database cluster I've got 2x serves I want to pull them together have two instances of Oracle accessing one set of disks the only thing I need is the mac OS and the oracle database now if i install it all and something doesn't work i have one place to call i call oracle and they say up do you have this required OS patch no install it if it still doesn't work i still only have one place to call you're not calling veritas for support on their file system software you're not calling whomever for support on their clustering software it's just a database on top of an operating system it's as easy to support and manage as a single database when the single server would have been so the clustered environment now becomes pretty much synonymous with a single machine environment the other thing it gives us new and 10g is automated server provisioning so what I was talking about with the order entry financial systems we're one month two of them order entry in one of them's financials and then later on is that load changes two of them become financials one of them becomes order entry the software can run in one of two modes one is sort of a trust me mode where Enterprise Manager are our grid control piece of software is watching the system and saying you know what the loads been changing over the last couple of hours we're lowering this sort of a service we need more financial services right now order entry it's sort of dying off we're going to take one of the nodes in the cluster and we're going to stop redirecting order entry request to it just start directing financial requests to it the other one is is a mode where you come in and you do this manually we know until May 31st order entry is going to be jamming come April first or april may june june first the financial system needs the resources so the dba's would set up a policy that would facilitate this at midnight or 2 a.m. or during some downtime they would change these services around we also have the ability to slide servers in and out of a cluster just think about it you've got a whole bunch of machines out there kind of them are sitting there is app servers two of them are sitting there as databases come along I don't need as many application servers I'm not I'm not processing orders from the web at this point in time I'm doing more data warehouse things we can actually reallocate some of our application servers to become database servers temporarily so I'll go eight and four and then later on when I need the application servers back we can repurpose those nodes in the cluster if you will they stop being databases and they start being application servers again this is something we can do online without having to shut down and restart and reconfigure the database instances in the cluster which is what we used to have to do in the past the next part is taking a look at storage so storage has the same problem as our servers did if I was asked to five out of system or build a particular system and say oh I need 500 gigawatts of storage and I need a machine dis big in order to run this during that one peak day so if you go out and look at a lot of your storage out there you're going to find that some systems way oversized from system headway under sized industry came up with a fairly nice solution for this call it a storage area network or network tap storage instead of each individual system having its own direct attached storage we pull all those resources the problem with storage area networks and network attached storage in a database environment is that it really made the job of the system greater who is responsible for slicing and dicing up data much easier but did absolutely nothing for the database administrator who's got to work with the storage because what happens I I request 500 gigabytes of storage for my database that's great and a year give me two hundred fifty gigabytes more for next year in a storage area network environment what happened is the system administrator would slice out a mount point for me give me another 250 gig at storage but it's basically this big empty set of stuff over there as a DBA I have to come in and figure out okay what files am I going to move what data am I going to split up what's the best way to sort of utilize this new resource I have spend a week doing that and we'll spend a weekend actually implementing it and the reason it's a weekend is because this sort of movement encouraged downtime in order to move a physical operating system files from you know / v 0 12 / d0 to Oracle can't be writing to that file at that point so this is a downtime sort of thing then next week what happened well we spend next week figuring out how many mistakes we made introducing a hotpoint over here or contention over there and coming up with another plan so that we can spend the next weekend sort of coming back and rebalancing the IL this is a continual process DBA spend a lot of time looking at disks moving files around trying to eke out the best performance they can by making sure that we achieve this Nirvana of sort of even io distribution so in 10g what we did was introduce a database file system so much like you would install your operating system and say use a UNIX file system driver or something like that to build a ufs you're using automatic storage management or ASM to build a database file system this is a file system that does one thing it holds files that Oracle ingest and Oracle processes so it holds redo log files it holds data well it can hold database backup anything that Oracle sort of produces are consumed it can hold what you're not putting here are your your documents your HTML files your spreadsheets unless of course you're actually putting them inside the database this is purely for database file and one thing that it does is it's achieving that goal of even IL across every single device possible so here using that same scenario we started the year with a couple hundred gig of storage we get an additional allocation to us we would be adding these devices into what's known as a disk group this group is what the DBA sees physically at the operating system level you still have all the devices you can manage and monitor it at the OS level but the DBA sees this disk group we just doubled the size of this disk group what you would observe at the operating system level if you cared to watch as you add at these discs would have been a whole lot of read activity on the top and write activity on the bottom Oracle would be going out there and picking up extensive objects and moving them around rebalancing the i/o conversely if you needed to take some storage away from the database I doubled your size but that was fifty percent too much I need to take some of that back for this there's other application over here you can go in and actually remove devices from these fish groups what you'd see then is a whole lot of read activity on the devices you remove and it's spreading out of the data across the remaining devices here so instead of working with files and mount points and moving things around and trying to determine the optimal IO layout yourself the database is now doing it at the database level using the equivalent of a database file system so in short ASM is a portable high-performance database file system it eliminates the need for conventional volume management so for example is if you get oracle standard edition this thing rack comes as a feature of that so you could get oracle standard edition put it on a two node cloth with two CPUs in each box and what you'd be running is our clustering software ASM to manage the disk that's all just the Oracle database and the operating system itself there's no other software to sort of x or integrate into this mix and in fact with the standard edition we don't even support it if you attempt to do that because it makes it too hard to support in that particular environment having it all come from a single place the database managing the database made everything much easier so in short that was a quick peek at some of our grid features gridded is not just rack grid is many things part of its ASM being able to take the storage and allocate it and reallocate it part of the grid is being able to slide things into Iraq cluster and slide things out of a rack cluster part of the grid is being able to just move data around in your network but what we looked at today briefly was predominantly storage related being able to very easily manage storage in an Oracle environment database clustering being with the slide servers in and out of Iraq cluster we didn't really take a look at the application servers but they have the same capability I sort of used that in an example where I had 10 out servers and two databases I can steal from the app servers unplug those components if you will from that grid environment and slide them over into the database environment and then one thing we will be taking a look at is the grid control how do we manage for Oracle instances as if it were a single Oracle instance because that's really what you want to do at the end of the day you don't want to be having to think I've got these four different instances I need to remember to do this over here and that over there I just want to manage it as though it were one now onto manageability oracle 10g could have been called Oracle 10m and sometimes I wish it had been but when Larry Ellison sat down and looked at it you know there's manageability it's hard to spell big it's long and then there's it's very short so we went with 10g but of the 350 new things added to the database over one-third of them were manageability features if you just even look at for example ASM seems like a grid feature seems like something for big databases it's equally appropriate for the small installation I've got I'm the Oracle DBA I've been given six correct attached scuzzy disk and I've been told to make it perform optimally what do I do where do I start but with sick disks I really don't have much of an opportunity so give those to ASM and let ASM implement this striping and mirroring of everything it's using the best overall general iOS group put on this I don't have to worry about this thing so ASM is actually a manageability feature in disguise it's not just a grid feature so I stuck this slide in here and introducing oracle 10g and I colorized the last bullet points because I think many people gave us or conceded the first bullet points yeah it's Oracle's pretty much runs all your applications it's fairly open that way it stores any information you have from rows and columns to spatial to text to images is scalable available reliable pretty secure but what I don't think people would have conceded in the past was it was it was really easy to manage you know it is easy as firing up Microsoft Word and you know put in an ABC and I've got a document that i can print out oracle had a reputation of being able to scale up really large but maybe not scaled down as easily if you will so this is something that we really focused on in the release of this software it was a single biggest effort not just for 10g in a microcosm but in my years at Oracle it was the single biggest effort software-wise that I've ever seen they took this seriously over fifty percent of the architects and the engineers within the server technologies division were focused on manageability related projects this is a widespread effort not just in the server technology on the database but throughout the entire application stack and the application server as well so for example the overriding goal was reduce the cost of managing simple small Oracle instances by at least fifty percent how can we do that well one thing we can do is reduce administration costs we made many things automatic versus manual marc was trying to get oracle going on his laptop which is somewhat memory starved and he's like well how big should make the block buffer cache and how big should i make the java pool and how big should i make the large pool what about this shared pull on the redo log buffers you know i could go through all the pools all the dba's would be glad to know we added five more in oracle 10g so you have like 13 of them that you could five when we took a look at this system and I said you know you got X amount of memory let's get half of it Oracle you can set a single memory parameter SGA underscore target we set that to 200 mega or whatever it was and Oracle come up with the best allocation of memory based on that what it does is sets a relatively small size pool for everything and throw the rest of the memory into those into the buffer cache as the other pools become starved for memory Oracle will come and steal from the buffer cache and give it to these other pools but it made it automatic I didn't have to sit down and think about how to size these 13 caches I just said Oracle take 200 megs of ram and divvy it up the best way possible intelligence vs data I call this one giving the database a long-term memory the dba's out there now that the database is very good at telling you two things about performance can tell you what's happening right now right I can log in I can see what queries are running I can see how many zehrs are connected i can see who's running a long-running query i can see how long the queries have been running for i can see how much work the queries that are still running have done the other thing that can tell me is well since you start at me up two months ago I did X number of transactions I ran this query so many billions of times I've done this over and over again what it can't tell you is at noon the performance problem that was being experienced by your end-users was caused by George that information just doesn't exist in the database anywhere in a very short term memory what's happening right now and then it has this averaged long term memory but they had no real information couldn't log in and find out anything truly useful about the immediate path because it always happened that way the DBA goes out to lunch she comes back the red light is blinking on the phone they pick it up it's George saying hey at noon the system is really really slow I need to know two things from you why and what are you going to do about it when the past unless you had accidentally been sitting there watching the system admin you'd really have no way of knowing the database is now constantly benchmarking itself so when the DBA comes back at one o'clock slide a little window back in the in the database console drop it down they can see what was happening at you know inside the database and for the first time they'll be able to call George back and say we've told you so many times don't hit the stop and the reload button just let the page finish because the performance problem was you submitting the same monster queries 15 times and we fixed it by revoking your access to the system but the DBA never had that information before it's always been guesswork I've always been you know afraid of answering that phone calls if it's like I don't know what was going on at 3am and I have no way of telling you what was going on at 3am reduce capital expenditures is we're talking about by sort of right size in the system adaptive versus oversized I'll add capacity as I need I don't have to buy a machine that can support up to 128 CPUs which is a quite expensive machine I can start with smaller machines and add more of them over time integrated versus third party I want to do this cluster thing I need to buy two pieces of software not 40 on I've cut out sort of this volume management software that I don't need and this clustering software that i don't need the last part here is reducing the failure cost so proactive versus corrective that SGA target that i was talking about one of the very important pools inside of an oracle databases to sink a shared pool shared pools where we store parsed representations of equal and PL sequel and other pretty important stuff a very common error is an aura 4030 out unable to allocate X number of bytes in the shared pool used to be that the end users would just get that message back not very useful have to call the DBA DBA to us or to figure out what went wrong and fix the problem if i'm using this automatic memory tuning before returning an or 40 30 back to an end user the database is going to look and say can I can I sort of feel something from somebody else at this point in time maybe I can shrink this pool and take it and use it over here because we were using this memory over there but we're not using it right now so let's give it up from that purpose and move it over here so it's proactively fixing itself rather than reactive we waiting for DBA to correct itself or correct it and the other one is acting succeed versus trial and error I know a lot of dba's I've been guilty of this myself systems running slow on the past you know we did this and it sort of made things better we rebuilt an index so we'll go and try that is it better yet no okay reorg incapable that'll make things faster so we'll rebuild some tables is it faster yet know the database because it's now benchmarking itself can actually come back and give you advice hey I installed this database console my 10g database was running great one day it came back and said your database is running five times slower today than it was in the past we're running five hundred percent slower things are taking five times as long we've identified that there's needs new queries that have been introduced in the system they never existed before and if you push this button over here we'd actually like to take these queries and tune them for you we can suggest indexes that you could add to it to make them faster materialized use or even help to simply rewrite the query the database is sort of watching itself waiting for its performance characteristics change trying to figure out why and then notifying you so we're not coming in and sort of guessing what can fix the system the system telling us yeah there is a problem and here is the probable cause and solution for that problem so take a look at the life of the DBA see where they spend all their time about eighteen percent of it is in install creating configure so this came out of a survey from the international oracle user group dba's what do you actually do so eighteen percent install creating configure that sort of boggled my mind a little bit I thought that that was a lot you install a database Swanson and it's over it's forgotten right well no because I install the database for this developer or so that he can build as prototype and I install it for this development team so they can take the prototype a little bit further than I need to move it over to this QA system and do it over here again and then I need to put it into a production environment so every every database might actually have five six seven installed behind it and people are actually creating and destroying the database instances relatively frequently so one of the things we did in Kenji was too make the database fast and light weight as far as installation we got it down to a single disk if you will it used to be three to five CDs to actually install the database it's now one CD no we didn't cheat we didn't put it on a DVD you know it's actually a CD what we did was going on back and took a look and said what is this file it's been here since version 5 what does it mean well we don't need that one anymore and we cleaned out a lot of the stuff one back and we have one single base image CD now you stick this in 15-20 minutes later you have a database installed up and running with the seed database the starter database in everything and out of the box database console so it used to be I install the database now I want to manage it oh go get the CD back so we can install Enterprise Manager and set it up and get figure it and everything else like that we've taken the enterprise manager database functionality and actually moved it inside the database itself so now when i install the database and i started up comes with a very small java web server sitting outside of it the java web servers just making stored procedure calls basically into the database and the database is got the enterprise manager tool in it so no longer am i installing the database and then hunting around for CD to figure out how to install the tool to manage the database install the database the last step is hey go to this URL and start managing me this is how you're going to interact with me six percent in loading data so we took a tool we've had this tool called export and import forever and it's been virtually unchanged forever and every DBA knows exactly what Export Import is and other limits they know how to kick it and make it do what they want but it's not very flexible fast is something people might not have attributed to it so for the first time in many years we rewrote this are now called import DP and export DP for for data pump the export is after but not as faster as the import is the export was already pretty fast using a direct path to export there was only so much we could do we're now pretty much gated by the speed of the disk as far as getting data out of the database but the import we did a lot to made it use direct path operations so it's bypassing the sequel engine so it's able to format database blocks and write them directly automatic parallelization of the input stream so if you've ever trying to import a hunger gigabyte database dump file you know that was a long process because it was insert row after row after row and it would create an index create an index create an index now it's going to do all those things in parallel as much as possible so it's blown as much data in as it can as fast as it can then creating indexes in parallel as fast as they can basically utilizing the entire machine it's restartable one of the boat points I don't have on here that I think is nice one is I can actually start an export sort of a server process on one machine stream it over the network to an import process running someplace else and it sort of piping it into my database so i can actually stream my information from say an HP ux box across the network and import it into my database running on a mac or take it from the mac and stream it over to linux or wherever i need the information to go so it's a little bit like using database links but doing direct path operations in a massively parallel fashion here's the big one fifty-five percent of the time ongoing system maintenance so I've got DBA names Fred he manages 14 Oracle instances and his morning is pretty boring because it's always the same thing over and over again it's a little bit like Groundhog Day comes in logs in the server one hi how you doing are you up or you run out of space the backups work am i running out space in the filesystem looking at everything did anybody reporting problems against you then he logs out and goes to the next server and sort of repeats the whole process and hopefully it's a blurring morning that he doesn't find out any problems we went on now with the database sort of watching itself when he comes in the databases tell them everything was okay you don't even need to look at me we're running fine we're not out of space nothing bad happened the backup succeeded or there'd be a red blinking light saying help i need help over here performance was going through the tubes at three o'clock in the morning here's what was wrong here's what I think you need to do about it and then Fred who's a professional DBA looks at that advice and treats it as advice from a junior DBA and either does it or refines it or ignore it entirely so this diagram is supposed to represent what we have today there's a database and then each one of these red boxes is sort of a point solution outside the database my developers have a tool that they use to do their application and sort of sequel management and there they're tuning of sequel queries and rewrites and things like that my system administrators have system resource management tool how's my system do anything helps my disk the memory on a box the CPUs the dba's have their backup and recovery tools and they'll get another tool to do some space management hey go to look for fragmentation in my database suggest objects that need to be fixed help me reorg and things like that and then lastly there's storage management software like a Veritas or something like that our goal was 10g to actually move that all into the database so if I want to tune sequel the databases are under the sequel the database knows better than anybody else what could actually help the sequel go better because it's got this thing called an optimizer inside of it it could say you know what if we had an index on these columns how would how would the sequel probably perform given that that's the entire goal or job of an optimizers to look at all the possible you know combinations the query plans given the tables and indexes it have it can now hypothesize and say you know what if we had these extra two indexes over here we have to make this query would consume one system resources it is right now or if you created this materialized view we would be able to execute this query with one-tenth of illogical iOS instead of the millions that you're doing right now same with system resource management and space management the database sort of knows as you're gathering statistics and things like that this object is fifty percent white space well once I've identified there's an object is fifty percent white space because I sort of delete it every other row or something like that the next step is when I need to reorganize is that the object is in need of a reorg step one and step two it actually has the command to shrink segments now so I could say hey take the table uncompacted it's going to do things like read the table from the bottom up and then find the last row and sort of delete it and reinsert it at the top of the table so that the table will become fifty percent full at the top 50% empty at the bottom and it will redraw the high water mark for that segment we would give us all that space back our goal was to take all these things that we always had outside the database and move them inside so for example automatic tuning notes of sequel we have bad sequel in a packaged application this is sort of a list of steps you not expected to read these of what you would do in 9i vs 10 g and 9i you'd have to first identify that there is a problem this query has run for like five hours there's the problem then you'd have to try and figure out well could the query actually run in less than five hours and I play around with it my tune it and I find hate you know if I rewrote it like this or hinted adore I did this the query runs really fast but it's in a third-party application so you can't change the code you can't change the query so you call up their support you spend a couple hours convincing them that there is a problem and then maybe a couple of days later a couple of weeks later they develop a patch and then you you install this patch which is sort of down time as you're patching your system and one of three things will happen one thing that could happen is nothing changes doesn't fix the problem on your particular box the other one is it could actually fix the problem but the more probable outcome is the installation of that patch destabilizes 50 other modules inside of this system and you end up worse off than you were in the beginning in 10g what's going to happen or does happen is the database identifies bad sequel automatically pops to the top and it says you've got this sequel it is a problem we need to take a look at it it's got many things that could do to it it could advise you on indexes materialized use it could tell you how to rewrite it but those aren't always achievable in this third party software environment the last thing it can do is actually analyze a query now much like dba's have been able to analyze table I'm going to take that query is going to say look it's a ten table joint problem is we don't understand the relationship between table 1 and table 2 let's understand the relationship between table 1 and table 2 given this predicate that you're using and the database analyzes this query stores extended statistics in the data dictionary and then the next time you parse an optimized that query it says you know when I join these two tables together without this extended statistics i thought i'd get five thousand rows but now i know because of these extras statistics i'm going to get five million rows out of this joint so i'm going to come up with a totally different query plan the DBA can take any query analyze it store that information in the data dictionary so the next time that query is parched and comes up with the right plan all by itself it cuts out that loop of go back to the developers who wrote the query in the first place and try to figure out a better way to run this query give the optimizer more information out of memory errors is this the one that I used before hey i'm running a query and i get this or 40 31 or 40 30 out of out of memory what do i do well I call the DBA the DBA has to look and say okay yeah the buffer pool is a little bit bigger than we need it let's shrink the buffer pool and will grow the shared pool and now the problems gone away if I'm using the automatic memory management inside the database in 10g that space is intentionally left blank because that's not going to happen in that particular installation if the shared pool runs out of memory we're automatically going to look and identify we can get some from and slide it over there last example mistakenly drop table you know it's the problem of right table wrong database you know it's that late at night if the pinky leaves the enter key you get that cold feeling in your stomach just fall through and you go I did a bad thing 9i we'd prepare an auxiliary instance we'd use our man to restore the tablespace we do a point-in-time recovery of that table space to right before the drop and then we transport that data out and move it over this is probably at least an hour because we're going to tapes we're going to get back up we're going through this process that we don't go through every day in order to recover this 10g we now treat tables as though they're like recycle bin objects and I drag something into my trash it doesn't actually delete it sort of hides it I can go back and unhide it later when you drop a table we don't actually drop it anymore we sort of hide it from you and we give you the ability to unwrap this object at a later point in time so instead of your system being down for an hour or so while you're recovering this table it's back up immediately because as soon as you drop it you unwrap it and bring it back and then the last part we're going to look at here before getting in an HTML DB is the install creating to figure again but also the ongoing software maintenance this is a quarter of their time is spent dealing with software related issues not even data related issues really Enterprise Manager version for this is what we also call our grid control its main purpose in life is managing groups of systems and in these grids if you will they took the database functionality out of enterprise manager many dba's looked at enterprise I understand that's the tool I used to like create users and things like that administer my database we moved out all into the database consult the database has that functionality now Enterprise Manager needed to find something else to do a different purpose in life so it came up with I'm going to manage your systems I'm going to manage your software I'm going to fix the problem that we have of this create configure install patch maintain two topics that I picked out as the to the intrigue me most one is automated software cloning that DBA is that hey install this database for this prototype installed this database for this development install this database for QA install the database for production they're doing the same thing five six seven times over and over again they can now install the software I'm going to installing to create and configure an OLTP ish database then I'm going to install and create and configure sort of a data warehousing database it's going to have partitioning and other things on it I'm going to have these reference installed so that when somebody comes in provisions of database for me I don't have to get the cds out anymore I don't have to go to the machine I don't run the installation process I point at an existing Oracle install and then either just clone the software or I call in the software and the data along with it so instead of being an hour long process to say install create and configure database becomes a 30-second operation because you're setting this up through enterprise manager and it's happening in the background true you have to come back in an hour and verify that everything worked but you're not sitting there watching a blue bar go across hitting a next prompt filling in the default it's an automated process the other thing if this is the only thing it did it would still be cool is enterprise manager is maintaining an inventory of what you've got installed it knows how many Oracle instance is you have running out there what versions of the application software that you're running the application server the applications themselves storing this in a repository it's not only doing that just remembering what operating system you're running on what patches are installed on it how many CPUs you have how fast they are what the disk looks like how much RAM you have this is all in one centralized repository takes this information now and turn it around and goes to metal inc which is our support website and says this is this guy's finger print this is what he's actually running this is what he's actually using what applies given these inputs not using partitioning you won't get bulletins and patch that are partitioning related you're simply not using that feature you are using advanced security well the security bulletin just came out last night is something that would pop up on your screen immediately so Enterprise Manager is taking an inventory finding out what applies notifying you about it and then allowing you to download that information into a repository as well so get to the patch puts it into a managed location so that you can come back and say yeah server1 apply the patch right now shut down by the patch bring it back up verify that everything work register that in in the repository server 2 3 p.m. on saturday I'd like you to schedule that patch upgrade to take place and send the alert as to the success or failure to this email address so that can page me or whatnot and let me know how it went but I don't want to do it right now on Friday I want to wait till tomorrow afternoon and these other servers out here I can schedule them as well so for the first time the DBA can look and say I've got 10 Oracle instances nine out of ten have had the patch applied and the tenth one is scheduled to have this patch applied saturday afternoon at this point I'm going to turn it over to mark he's going to spend about 20 minutes talking about HTML DB and then we're going to have a quick Q&A session so mark alright so HTML DB is something that's built out of the Reston Development Center we really kind of started as a small little project to build a calendar and turn into a sizable component of 10g I mean I've got a couple slides in real just going to get into the demo so I'll switch over there in a minute what is HTML DD ok I'm gonna watch the clock watch us try to look at this I'm really bad at reading like you all do so what HTML DD is a declarative web based development environment what does that mean well that means you build it with the browser you don't have to install on a client software you run it from a browser where you build it is where you run it you don't have to do that but you can do that if you wish it's also a feature of the Oracle 10g database you can stick it in older 9i 09 I are two databases with a loss on functionality for instance the recycle bin Tom talked about is exposed in HTML DV so what does the offer I'm not going to go over that because Tom talked about all the LEDs the secure ability of the integrity scalability we know all that it just exposes all that for you and again it's built for the web so if you're like a lot of organizations you've got your spreadsheets out there you've got your microsoft access databases your file servers you get it all spread all over and you can read all the studies let's say oh it takes 13 man hours to find a file and the server I've heard some companies have one web page one website for two different people and all that good stuff so this is about consolidation you use HTML DV you take your spreadsheets and we will you take your desktop databases which we won't do in this example for time and your files you can put them all on a 10g database and then you can look at them with your standard browsers it's not IE specific god knows how many times I've gone to a web page where it says you've got to run IE 6 we don't do that you can run all of those browsers right there works just fine the development team is a small bunch but they care about the browsers out there they don't want to isolate anybody so there's a four major components to it there's the application builder there's the sequel workshop the data workshop and administration I'll go over each one of those tools and a little more detail we're going we're going well am I going too fast anyone might learn all right great so the application builder is best explained the seagull workshop is what you think it is you can go create sequel edit sequel save sequel create sequel from objects say you've created a database tables 10 table of indexes functions triggers and you misplace that file you're like now going to go rebuild that script you can point HTML DB to the table and say rebuild the ddl and it'll spit it all back out we'll be able to look at all the attributes of a database to trigger a function look at the source code do all that good stuff with the sequel workshop the data workshop allows you to load data in via XML the cut and paste with the spreadsheet upload the export-import that's all right there and then administration so this is a view by your rack of X herbs and you've got 20 development environments and you don't want to give them their own database you want to take one day they spread across all those excerpts and just slice them up so there's a provisioning aspect to it so Tom gets is 20 megabytes because he's special I get my two megabytes because I'm a not as special and then what I see is completely independent of what Tom sees he won't be able to touch my stuff I won't be able to touch his stuff and that's part of using the virtual private database part of 10g all right the good stuff like that stuff I want to go over here ok cool thank you for that so i've already logged into HTML DB what I'm going to do is I'm gonna take a spreadsheet I'm going to put it into the database I might have it converted into a table I'm going to make a couple lookup columns on them then I'm going to build an application based on that table i've already logged in so let's go create let's go first go to the data actually where's my spreadsheet where's my spreadsheet i'm so glad i didn't know that would have not made a good demo so what this is is a spreadsheet of a bunch of hardware we've got I want to just copy that go back to Safari when i import the spreadsheet data i'm going to create a new table and these tables are routing you real tables like anybody else are not second-class citizens they're not some special objects as metadata the whole environment runs off metadata these are real tables and will pop over to sequel and a prove that because people ask though you're putting this in some freaky repository aren't you no we're not it's just regular so we go over here paste the data don't worry if it looks funky you it'll come good so it took the columns and make a good good guess as to what they are well the first one is a text field so the second one cpu speed we're pretty good pretty sure that that's a number the purchase date we're real real sure that that's a date and then so on and so forth if we wanted we could go on and do some other stuff with it then it gives you a couple columns here and if you want to you will if you don't want it column you can say no I'm not going to say this is going to be better than sequel loader if you're familiar with that tool but it beats having to try to get a log into the server so a web-based it will name this table hardware continue on and right here what it's going to do is going to put a primary key on the table I was talking with the gentleman yesterday I believes we wanted to convert from one take one system to another and he said well I've got all these indexes I got this Web object out there that's maintaining the top level sequence number for all the tables and that's how I know what's index is to make and I said all you got to do is create an index on that table give it a sequence i d-didn't you kind of looked amazing with the sequence I said it's an automatic number that's generated by the database for you and what HTML DB is doing is doing all that for you so it's automatically giving you a sequence number so you don't have to try to manage your own index numbers for collisions I've done it myself I've tried to do that I've had a key violation myself trying to do it not here tom I learned that before so and then it'll create primary key so there's all the good stuff that it gives you so we're going to go ahead and create the index let it do that create the sequence name is already use I forgot to drop that before let me go back give it a different table name hardware too so can we cut that out of the video so there we go 37 row succeeded 0 failed woohoo so we're going to go back over to the application builder and you can see you can do is already a sample application here when you install it the way to deploy sample application is you click a button and boom I'll get playing time click a button boom and it just goes runs you don't have to go deploy anything it's just a bunch of sequel that it runs to create the database create the application for you and that's what they do when you you a provision a user so we're going to go create a new application we're going to have a based on existing tables this user has a schema associated with it called apple if i wanted to that could have multiple schemas this is the default schema that I told it when I provisioned this user just going to look in this schema for the table pop up right here and you'll see hardware to you also see over here kind of a breadcrumb trail of you know this is a wizard there's a lot of wizards in there you can make your own wizard so there's actually a wizard wizard and you can see over there on the left just how far you are into the process the nice thing is is as a developer and I don't have to manage state if you're familiar with me in maintaining session state it does it all for you you don't have to worry about oh when I go from page a two-page be collect up all the information in and the form fields and save it off into some memory space it does it all for you so it really makes developing applications pretty quickly but I'm just going to finish it's good chug cusco run it and see what we got we've got basic security on it you as a workspace manager can pick what you want right now this is using database users and as it works as manager you can create those yourself if you want you can use ldap based authentication you can use single sign-on authentication I don't think most to any of you hopefully have Microsoft Active Directory but you can you can use that as an authentication source so you don't have to convert all this data and move it over and keep it in sync it'll do it so we login oh so this is what it generated generated an application with a couple tabs and a couple of components to it so let's go look at the hardware we go analyze it it's through and some charting by default we're going to it took all the columns in the table and said okay these are all the columns you you have let's go so show a chart will do that and since it's an SVG chart so you can do all that skin zooming and whatever if you want you can click on one of the components and it'll bring you up to it will show you a sequel query of just that data and literally all this was is I just typed in each types in that Atlanta you saw me it types into select star from hardware to wear what did I say brand equals del great and if I wanted to I could go do it myself I can go edit these if you've done web based development you're always familiar with the create update delete functions this takes care of all that it's kind of the Oh entity relationship builder that it has in there it went out there and did it it knew that this is a days so it said oh I'm going to prefer a pop-up date picker right there then I go apply the changes I don't have to worry about commits or anything like that it takes care of that I can't worry about it if I want but I don't have to this is just a sample application you can you can go to town I have a couple example applications of what some people have built with it I mean you were just using straight tables here would you be spatial data there's an example of that down the road and then so we wanted to we can as a debugger we can flip it in that load will show you everything that's doing as it's rendering but right here you can see all the session data you're like well what is that value I've got a bug I maybe maybe I don't but you can see the value right here see what it is what it thinks it is so it's a nice way to just keep tracking without having to do if you're familiar with see print a spell over the place or system that out there print lens you've got all that right there the other thing that I wanted to do is go back and edit the application and I told myself I was going to forget and I sure enough did I forgot to create the the lookup table on that so let me go over to the sequel there's the recycled and I'm going to go browse the table at the table find my hardware table for someone like me she doesn't live in the sequel world I love it Tom Tom live in the sequel world he's a sequel plus jockey among other things so he's a he loves that I love this because I can just drill in here and start looking at stuff and have to worry about this is the trigger that I put on there automatically so I'm going to go create a lookup table for this and I'm going to pick the brand looks good we take that brand I'm going to say next it's going to make a new table out of its going to soak up all that info and put it in the index in table and then replace that so if you wanted to you could see where was it view the data model and you can see that brand looked up is now related to hardware to this isn't a really fancy a referential integrity but you can get this point when it gets larger it can be pretty a pretty useful so we'll just go back over here to the Builder let's just do this one more time for a reinforcement go back pick hardware too mornin so where was that hardware too so right there you see the brand ID now turns into a picklist and as smart enough to know that look at the referential integrity see the relationship and create an appropriate type for that that's pretty much HTML DB in a nutshell from getting your spreadsheets out of everybody's desk tops off everybody's desktop consolidated into a database I did a project for a boat racing group and they had spreadsheets all over the place files here and there spreadsheets everywhere we told him use this tool before wasn't even produce and they they were quite happy with it with eight literally sailors out there developing HTML DV applications Who am I looking on time seven minutes ok oh I'll let that run little well it's not even showing anymore can you float back to that one real quickly apologized so anyways this was a spatial data thing you can see that somebody is we track the vehicle from I guess their home they parently went down to the airport and then once somewhere else this is all data running out of the database the map is generated by the spatial data system and it's just making requests you can pretty much drop these type of objects on there for 10 we could zoom you can see this person tends to speed a little bit I think that's a 65 mile an hour toll road though so that's the type of stuff you can make anyways this another little bullet point of what what gee whiz stuff there is available so if we could flip back to the presentation thank you so we talked about how it works a little bit you can see that it's centrally deployed and managing your single database be running on you know one laptop like I have or a rack of X serves and x x rayed systems I've got one of those in the lab I thoroughly enjoy it access anywhere via browser we literally do on the plane with our browsers take your application put it back up into the system and just as easy as moving applications like that and it's declarative I don't have to go out there and say if this condition is that then do this you can just say you know show me this text field only when I'm an administrator don't show it when Tom logs in show an administrative tab only when the person who logged in as a member of this ldap specified church hierarchy so it's a little simpler for some people but you can also go to town and create normal people procedures Java stored procedures anything that's callable from the databases is a fair game this is what it looks like that's pretty much it as they say that's all folks with that you've got the little yellow box that can be one machine it doesn't have to be but basically you've got your database runs HTML DB you don't have a middle tier you don't have edge servers and cache servers and all this kind of stuff it's can be one machine we typically do that you can split it out if you want to have your HTTP server running somewhere else in a real word real world you probably would because you want to have a firewall and all that good stuff with the load balancers and then that's pretty much it so the Collette via installation for the companion CD that's available on otn has it it's really simple install you don't have to sit there and say okay now where does this go how's that hook up invariably somebody always says well are you trying to get rid of your portal product or you how this is you know going to fit the j2ee stuff and we're not trying to displace j2ee by any means suspicions we have our own product but you can look at it you can see that j2ee standards-based this is HTML deviate run PL sequel that's what is built in it's taking advantage of all those of wonderful 10g features that Tom talked about the development approach is a little more ad hoc somebody says hey I got the spreadsheet can you do this you know build an application track something do whatever it doesn't mean you've got a lot of silly little applications are one time used you can but it's um it's more leverages of stuff for that as opposed to j2ee where if you wouldn't do a right j2ee application you got to know what you're doing you have to design it well and scale it so then the development environment j2ee the Eclipse the Xcode the jdeveloper those are all phenomenal hi des but if you just got a web browser you have to go somewhere and they don't allow you to bring your computer with you like the CIA you a web browser you use theirs and you're good to go and then it's were really the non professional developers out there like I said sailors have been coding applications non-trivial applications i might add they literally got the to sequel in 21 days type of book and they were ready to go so that's a little flavor of what HTML DB is and isn't and how it relates to the j2ee if you want to try it out you can go download it install it yourself and and go to town if you want to just get started in 10 minutes just go to this website HTML DV oracle com it's free you log in and you can put you'll you ask for a workspace little it'll get provisions automatically within an hour so depending on if the guys are still there then you can talk there's a discussions on OT informs what did oh how do i do that's how i do that you can also go to the HTML DV studio where there's examples of how to do this how to do the if you don't want to build that common look and feel from the application builder how do you make your own master detail relationships how do you put a multiple row edit all these days view looks out there you can look at and it shows you how to do it and you're going to also go to HTML db2 oracle com / o TN is where the official homepage is for that product