Guaranteed Method of Boosting your Oracle Skills May 21, 2015
Posted by mwidlake in Knowledge, Perceptions.Tags: 12Cbasics, behaviour, documentation, knowledge
7 comments
I can tell you how to be a better Oracle DBA, Developer, Designer, Architect – whatever your flavour of role or aspect of profession, if Oracle tech is part of your working world I can hand you the key to improvement. And it is easy.
I am totally assured(*) I can do this. It will work for every single one of you reading this post (except for you two, you know who you are). And you must send me $100 before I tell you how…
Hell, no you don’t! This is not some bull-droppings selling piece, it is just advice. And some advice aimed directly at myself too.
When did you last read the Oracle Server/Database Concepts manual? You know, that fairly short book (in fact, from 11G it is really short, with links through to other chapters in other books) that explains how Oracle TM (Copyright), actually does stuff? What it can do? It does not go into too many details but rather gives you a brief introduction of each concept and the fundamental “how it works” information.
Thanks to Kevin Fries who pointed out it would be nice if I linked to the online concepts manuals (as he did in his comment):
Here is the online 12C Database Concepts manual.
Here is the online 11GR2 Database Concepts manual for those still with 11GR2.
Here is the online 10GR2 Database Concepts manual for those trapped in the past with 10GR2.
Read it. Read it this week. I am confident that if you read the latest Oracle Database Concepts manual you will be ahead of the game by a massive step.
Oracle 7 instance diagram
Why am I so sure? Because we forget what Oracle has been able to do for years and we miss new abilities if our day-job has not touched on them since they came in. I think there is a growing move to learning only what you really need to know to get the job done (as we are all so busy) as we know we can search the web for the rest. My friend Neil Chandler came up with a name for it, JIT learning: “Just In Time” learning). Only, you can’t easily search for what you don’t know (or have forgotten) is possible with the tech you use. If you go through the concepts manual you will be reminded of stuff you forgot, things you missed or {and this is key to newbies} gain an outline understanding of what Oracle can do.
I became fascinated with how many people read the concepts manual about a decade ago and started asking a question when I presented on any Oracle topic. “Who has reads the concepts manual for the version of Oracle you mostly work with?”. In the last 10, 12 years the number of hands has decreased from well over 50%. In 2012, at a UK meeting, it hit the bottom of the barrel, no hands whatsoever. Oddly enough, a few weeks later I was in Slovenia (for none-European people, a lovely country bordering Italy and Austria – google it if you need more help) and the same question resulted in 40% of the audience raising a hand. When I was in the US 6 months later, no hands at all again. In the UK and Europe since, no hands or occasionally, one hand – and a few questions usually nailed down that it was a prior version of the manual they had read.
I took some time to ask this question again at a UK user group meeting about 4 months ago (no hands came up of course) and asked “why?”. The consensus was “we know most of what is in the concepts manual, we just need to know what has changed” – with an undercurrent of not having time to read the now-huge set of Oracle manuals. A few people suggested just reading the New Features. This was a crowd who did not know what a table cluster was (“Ha, look at ME! I know what a table cluster is! Hahahahaaaa – OK, no one uses them.”). (British ironic self-depreciation there).
Reading “New Features” is certainly better than nothing but I feel it is not enough as it does not remind us of the established stuff we have forgotten. I am on a bit of a personal Jihad to explain the basics of Oracle to any Newbie who cares to listen and I have to keep checking my facts with the concepts manual and some chosen expert friends (thank you VERY MUCH expert friends) and I keep stumbling over stuff I don’t know, misunderstood or forgot. And I have been an “expert” in Oracle since… Well, before the millennium bug or Android phones or iTunes had been invented. THAT LONG! I know my shit – and some of it is, well…. wrong.
Actually, I have a confession. I have not read the 11g or 12C concepts manual. I told you this advice was aimed at me too.
So, Go Read The Oracle 12C Concepts Manual. Go ON! Go and read it!!!! Oh. Still here? Well, I AM going to read it – as I last read the 10G concepts manual properly. And as part of my current push to present, talk and blog about the basics of Oracle, I will blog what jumps out at me. I know one thing – I will not be quiet any time until August if I follow my own advice, I will be posting blogs left, right and center about what I learn..
I’ll use the tag 12Cbasics.
Let the learning of basics begin.
Oracle 7 or Oracle 8, 1.5 ” of pure info
Thanks to Joel Garry for digging his old manuals out the basement and doing this shot for me 🙂
(*) it won’t work if you already read the latest concepts manual. But most people have not! Heck, I did not charge for the advice, so sue me if you read it already.
An Oracle Instance is Like An Upmarket Restaurant January 28, 2015
Posted by mwidlake in Architecture.Tags: Architecture, documentation
11 comments
I recently did an Introduction to Oracle presentation, describing how the oracle instance worked – technically, but from a very high level. In it I used the analogy of a restaurant, which I was quite happy with. I am now looking at converting that talk into a set of short articles and it struck me that the restaurant analogy is rather good!
Here is a slide from the talk:
Simple partial overview of an Oracle Instance
As a user of the oracle instance, you are the little, red blob at the bottom left. You (well, your process, be it SQL*Plus, SQL*Developer, a Java app or whatever) do nothing to the database directly. It is all done for you by the Oracle Sever Process – and this is your waiter.
Now, the waiter may wait on many tables (Multi-threaded server) but this is a very posh restaurant, you get your own waiter.
You ask the waiter for food and the waiter goes off and asks the restaurant to provide it. There are many people working in the restaurant, most of them doing specific jobs and they go off and do whatever they do. You, the customer, have no idea who they are or what they do and you don’t really care. You don’t see most of them. You just wait for your food (your SQL results) to turn up. And this is exactly how an Oracle Instance works. Lots of specific processes carry out their own tasks but they are coordinated and the do the job without most of us having much of an idea what each bit does. Finally, some of the food is ready and the waiter delivers the starter to you – The server process brings you the first rows of data.
Let’s expand the analogy a bit, see how far we can take it.
When you arrived at the restaurant, the MaĂ®tre d’ greets you and allocates you to your waiter. This is like the Listener process waiting for connection requests and allocating you a server process. The Listener Process listens on a particular port, which is the front door to the restaurant. When you log onto an oracle database your session is created, ie your table is laid. If someone has only just logged off the database their session might get partially cleared and re-used for you (you can see this as the SID may well get re-used), as creating a session is a large task for the database. If someone had just left the restaurant that table may have a quick brush down and the cutlery refreshed, but the table cloth, candle and silly flower in a vase stay. Completely striping a table and relaying it takes more time.
The restaurant occupies a part of the building, the database occupies part of the server. Other things go in the server, the restaurant is in a hotel.
The PMON process is the restaurant manager or Head of House maybe and SMON is the kitchen manager, keeping an eye on the processes/staff and parts of the restaurant they are responsible for. To be candid, I don’t really know what PMON and SMON do in detail and I have no real idea how you run a large kitchen.
There are lots of other processes, these are equivalent to the Sous-chef, Saucier, commis-chef, Plonger (washes up, the ARC processes maybe?), Ritisseur, Poissonier, Patissier etc. They just do stuff, let’s not worry about the details, we just know there are lots of them making it all happen and we the customer or end user never see them.
The PGA is the table area in the restaurant, where all the dishes are arranged and provided to each customer? That does not quite work as the waiter does not sit at our table and feed us.
The SGA is the kitchen, where the ingredients are gathered together and converted into the dishes – the data blocks are gathered in the block buffer cache and processed. The Block Buffer Cache are the tables and kitchen surfaces, where all the ingredients sit. The Library cache is, yes, the recipes. They keep getting re-used as our kitchen only does certain recipes, it’s a database with a set of standard queries. It’s when some fool orders off-menu that it all goes to pot.
Food is kept in the larder and fridges – the tablespaces on disc. You do not prepare the dishes in the larder or fridge, let alone eat food out of them (well, some of the oracle process might nick the odd piece of cooked chicken or chocolate). everything is brought into the kitchen {the SGA} and processed there, on the kitchen tables.
The orders for food are the requests for change – the redo deltas. Nothing is considered ordered until it is on that board in the kitchen, that is the vital information. All the orders are preserved (so you know what was ordered, you can do the accounts and you can re-stock). The archived redo. You don’t have to keep this information but if you don’t, it’s a lot harder to run the restaurant and you can’t find out what was ordered last night.
The SCN is the clock on the wall and all orders get the time they were place on them, so people get their food prepared in order.
When you alter the ingredients, eg grate some of the Parmesan cheese into a sauce, the rest of the cheese (which, being an ingredient is in the SGA) is not put back into the fridge immediately, ie put back into storage. It will probably be used again soon. That’ll push it up the LRU list. Eventually someone will put it back, probably the Garçon de cuisine (the kitchen boy). A big restaurant will gave more then one Garçon de cuisine, all with DBW1 to x written on the back of their whites, and they take the ingredients back to the larder or kitchen when they get around to it – or are ordered to do so by one of the chefs.
Can we pull in the idea of RAC? I think we can. We can think of it as a large hotel complex which will have several restaurants, or at least places to eat. They have their own kitchens but the food is all stored in the central store rooms of the hotel complex. I can’t think what can be an analogy of block pinging as only a badly designed or run restautant would for example only have one block of Parmesan cheese – oh, maybe it IS a lot like some of the RAC implementations I have seen 🙂
What is the Sommelier (wine waiter) in all of this? Suggestions on a post card please.
Does anyone have any enhancements to my analogy?
Friday Philosophy – The Tech to Do What You Need Probably Exists Already November 30, 2012
Posted by mwidlake in Friday Philosophy, Perceptions.Tags: documentation, knowledge, perception, system development
4 comments
How many of you have read the Oracle Concepts manual for the main version you are working on?
This is a question I ask quite often when I present and over the last 10 years the percentage number of hands raised has dropped. It was always less than 50%, it’s been dropping to more like 1 in 10 and Last year (at the UKOUG 2011 conference) was the nadir when not a single hand was raised. {Interestingly I asked this at the Slovenian User Group 3 months ago and something like 40% raised their hand – impressive!}.
Why do I feel this is important? Well, do you know all the technology solutions available across just the core RDBMS with no cost options?. No, you don’t, you (and I) really don’t. If you read the concepts manual, even just skimming it, you will be reminded of a whole load of stuff you have only dim memories of and perhaps you will even see some features that passed you by when they were first introduced.
Of course, you would need to read a few more manuals to get the full picture, such as the PL/SQL Packages and Types References, as so much good stuff is introduced via built-in packages, and the SQL Language Reference, as SQL has been extended quite a lot over the last couple of versions. Dull reading indeed but I’d estimate that if you read those three you would be aware of 90%+ of the Oracle technologies that are available to you out-of-the-box and considerably more than all but a handful of Oracle Experts. You’d know more than I as (a) I have not skimmed the PL/SQL one for years and (b) I have a rotten memory.
My point it, you can’t consider using Oracle technologies you don’t know about or remember – and they could be just what you need to fix the problem you see in front of you.
I’ll give a couple of examples.
Problem, physical IO is too high, your storage system is bottle-necked.
Answers, Reducing Physical IO:
-First up, Index Organized tables. Some of you will be aware that I am very keen on IOTs and the reason I am is that I’ve used them to physically group data that the application needed to select over and over again. It can make a massive improvement to that sort of system. They are rarely used.
-Clustered Tables. Even less used, in fact has anyone reading this used them in anger in the last 10 years? Great for situations where you need parent-children or parent-children-type-1+parent-children-type-2 data. I confess, I have not used them in anger for years.
-Move table (and order them as you do it!) and re-build indexes to remove “dead” space. This one got a bad name, especially the index rebuilds, as people were doing it needlessly without appreciating what the intention was, but now I hardly ever see it done – even when it is of benefit.
-Compress your tables and indexes. With normal Oracle compress (no need for HCC). In tests I’ve done I pretty much always see a drop in physical IO and run time. Being candid, I can’t remember doing any tests and NOT seeing an improvement but I usually only test when I expect and improvement and I don’t want to give anyone the impression it will always help.
All the above were available in Oracle 7 or 8 and all have improved over the versions.
Problem, you want to carry out some long,complex data processing in PL/SQL and if a step fails, be able to handle it and carry on.
Answers:
– Savepoints. You can rollback to a savepoint, not just to the last commit. In this way you can break the task into chunks and, if something towards the end fails, you roll back one step (or several, your choice) and call an alternative routine to handle the exception.
– Autonomous Transactions. You want to record that an error occurred but not fail the original action or not save anything it has done to date. An autonomous transaction runs in it’s own sub-session and commits in it do not effect the calling session.
– Temporary Tables. You can put your working information in them as you progress and if you need to bomb out (or some evil DBA kills your session for running too long) the temp table contents just disappear. No clean up needed.
Maybe the above is not so fair, I have not been a proper PL/SQL developer for a while now, but I hardly see the above used. Especially Savepoints. I can’t remember not having savepoints available (hmm, maybe Oracle 6) and Autonomous Transactions and Temporary tables are Oracle 8 (I thought Temporary tables might be 9 but Tim Hall’s OracleBase says 8)
Another thing I have noticed over the years is that so often I will read up on some oracle feature I know little about, only for it to come up in the next few weeks! There is a psychological aspect to this, that we only remember these “coincidences” and not the more common situations where we read up on something which does not subsequently come up before we forget about it, but I think that it is also that we tend to use only a few solutions to solve the problems we see and adding another solution to our list means the chances are high it will be suitable for something soon.
OK, so it would help us all to read the manuals (or other Oracle technical books) more. Now the big problem is finding the time.
Friday Philosophy – Whatever Happened to Run Books? July 27, 2012
Posted by mwidlake in Friday Philosophy.Tags: documentation, Management
3 comments
I realised recently that it is many years since I saw what used to be called a Run Book or System Log Book. This was a file – as in a plastic binder – with sheets of paper or printouts in it about a given system. Yes, this was a while back. It would often also have diagrams {occasionally drawn by hand on scraps of paper – so that would be the database ERD then}, hand-written notes and often the printed stuff would have scribbles against it.
{BTW I asked a colleague if he remembered these and when he said he did, what he used to call them – “err, documentation???”. Lol}
There was one book per key system and you could tell if a system was key (that is, Production, or a development system where a large development manager would punch you in the eye for losing anything, or any system the DBAs wanted) as it had a run book. It held information that was important about the system and, although you could look up most of it when logged onto the system itself, was useful to grab and just check something. However, it was vital if you had to recover the system.
Being a DBA-type, the run books I used to see and use were database focused. The front page would have the SID, name, host name (and even the spec of the host), version, tnsnames info, block size, backup strategy and schedule and, very importantly, the system owner. Yes, the big guy who would be upset if you lost the system. In there you would have printouts of the tablespaces, datafiles and sizes, the backup script, users (and passwords, very often), reference data tables, filesystem layout, OS user details and anything else
needed to recover the system.
This was an evolving and historical set of data. I mentioned above that you would have maybe scraps of paper from when a design session had come up with an alteration to the system. Corrections would often be done by hand. When you printed off the tablespace sizes on Monday, you did not throw the old one away but just added the new one, so you had information about the growth of the DB going back in time. Once in a while you might thin out the set but you kept say one a month.
It was actually that which got me to thinking about runbooks. At a site recently one of the DBAs was asking me if I knew of a screen in OEM that showed the growth of space used over time and my immediate thought was “well look in the run book” {I was very tired that day and losing my grip on reality}. Not being able to find a screen for what he wanted and knowing the data in OEM/AWR was only going back a month anyway, I suggested a simple spreadsheet that he could maintain. With the run book you could flip to the printouts of tablespace sizes, grab a piece of paper and do something lo-tech like this:
This would take less time than firing up Excel, typing the figures in, getting the graph wrong 3 times and then printing it out. Though if you had to go show Managers how the data was growing, you invested that time in making it pretty {why do high level managers insist on “pretty” when what they really want is “informative”?}
So why have Run Books gone {and does anyone out there still use them, in physical or electronic format}? It certainly seemed standard practice across IT in the 80’s and 90’s. I suspect that the reason is that most of the information that used to go into them is now available via online GUI admin tools and looking at them is actually faster than going and grabbing a physical book. Besides, if your DBA or Sys Admin team is split between UK, India and Australia, where do you keep a physical book and allow everyone to check it? I have vague memories of electronic Run Book applications appearing but they never seemed to get traction.
That is one of the drawbacks of using GUI admin tools. No, this is not just some tirad by a bitter old lag against GUI tools – they are generally a massive improvement on the old ways – but they are not perfect. Most of them only hold a short history and printing out the data is often tricky or impossible. All you can really do is screen dumps. No one has those little scripts for listing out basic information anymore {except us bitter old lags} as they have GUIs to do all that and, heck, I can’t go printing off a load of stuff on paper and sticking it in a binder – that is so 20th century!
Maybe I’m being unfair and OEM has a “run book” section I have simply never seen – but I’ve never seen it. If it is/was there, how many people would use it?
I do miss the Run Book though. Especially the ease with which I could look up all those passwords…
Oracle documentation on a Kindle January 18, 2012
Posted by mwidlake in publications.Tags: documentation, kindle
7 comments
WARNING! Some time since 12c came out, Oracle have stopped supporting the Mobi/ePub formats, even HTML it seems. You can only get the documentation in PDF format as far as I can see…
Anyway, as a historical note…..
I recently bought myself a Kindle – the keyboard 3G version. Keyboard as I know I will want to add notes to things and the 3G version for no better reason than some vague idea of being able to download things when I am away from my WiFi.
So, how about getting Oracle documentation onto it? You can get the oracle manuals as PDF versions (as opposed to HTML) so I knew it was possible and that others have done so before. A quick web search will show a few people have done this already – one of the best posts is by Robin Moffat.
Anyway, this is my take on it.
1) Don’t download the PDF versions of the manuals and then just copy them onto your kindle. It will work, but is not ideal. PDF files are shown as a full page image in portrait mode and parts are unreadable. Swap to landscape mode and most text becomes legible and you can zoom in. In both modes there is no table of contents and none of the links work between sections. All you can do is step back and forth page by page and skip directly to pages, ie goto page 127. This is not so bad actually as quite often the manual states the page to go to for a particular figure or topic.
2) Do download the MOBI format of the manuals you want, if available. Oracle started producing it’s manuals in Mobi and Epub format last year. I understand that Apple’s .AZW format is based on .MOBI (Mobipocket) format. As such text re-flows to fit the screen of the Kindle. I’ve checked a few of the DBA_type manuals for V10 and V11 and Mobi files seem generally available, but not a couple I checked for 10.1. If there is no Mobi, you can still revert to downloading the PDF version.
3) You cannot download a set of manuals in this format and you won’t see an option to download an actual manual in MOBI format until you go into the HTML version of the document.
I can understand that it would be a task for someone in Oracle to go and create a new downloadable ZIP of all books in these formats or, better still, sets to cover a business function (like all DBA-type books and all developer-type books), but it would be convenient.
Anyhow, go to OTN’s documentation section, pick the version you want and navigate to the online version of the manual.
Here I go to the 11.2 version – note, I’m clicking on the online set of manuals, not the download option.
Select the HTML version of the document you want, in this case I am grabbing a copy of the performance tuning guide. As you can see, this is also where you can choose the PDF version of the manual
Once the first page comes up, you will see the options for PDF, Mobi and Epub versions at the top right of the screen (see below). I wonder how many people have not realised the manuals are now available in new ebook formats, with the option only there once you are in the manual itself?
I’ve already clicked the Mobi link and you can see at the bottom left of the screen-shot, it has already downloaded {I’m using Chrome, BTW}. Over my 4Mb slightly dodgy broadband connection it took a few seconds only.
4) I don’t like the fact that the files are called things like E25789-01.mobi. I rename them as I move them from my download directory to a dedicated directory. You then attach up your kindle to your computer and drag the files over to the kindle’s “documents” folder and, next time you go to the main menu on the kindle, they will appear with the correct title (irrespective of you renaming them or not)
5) If you download the PDFs I would strongly suggest you rename these files before you move them to the kindle as they will come up with that name. I have a booked called e26088 on my kindle now – which manual is that? {don’t tell me, I know}. I have not tried renaming the file on the kindle itself yet.
6) You don’t have to use a PC as an intermediate staging area, you can directly download the manuals to your kindle, if you have a WiFi connection. Go check out chapter 6 of the kindle user guide 4th edition for details, but you can surf the web on your kindle. Press HOME, then MENU and go down to EXPERIMENTAL. click on “Launch Browser” (if you don’t have wireless turned on, you should get prompted). I’d recommend you flick the kindle into landscape mode for this next bit and don’t expect lightning fast response. If it does not take you to the BOOKMARKS page, use the menu button to get there and I’d suggest you do a google search for OTN to get to the site. Once there navigate as described before. When you click on the .Mobi file it should be downloaded to your kindle in a few seconds. Don’t leave the page until it has downloaded as otherwise the download will fail.
There you go, you can build up whatever set of oracle manuals you like on your ebook or kindle and never be parted from them. Even on holiday…
I’ve obviously only just got going with my Kindle. I have to say, reading manuals on it is not my ideal way of reading such material. {story books I am fine with}. I find panning around tables and diagrams is a bit clunky and the Kindle is not recognising the existence of chapters in the Oracle Mobi manuals, or pages for that matter. However, the table of contents works, as do links, so it is reasonably easy to move around the manual. Up until now I’ve carried around a set of Oracle manuals as an unzipped copy of the html download save to a micro-USB stick but some sites do not allow foreign USB drives to be used. I think I prefer reading manuals on my netbook to the kindle, but the kindle is very light and convenient. If I ever get one of those modern smart-phone doo-dahs, I can see me dropping the netbook in favour of the smartphone and this kindle.
Of course, nothing beats a big desk and a load of manuals and reference books scattered across it, open at relevant places, plus maybe some more stuff on an LCD screen.
Lack of Index and Constraint Comments November 24, 2011
Posted by mwidlake in Architecture, database design, development.Tags: Architecture, data dictionary, design, documentation, sql*plus
12 comments
Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.
Here is an example of adding comments to tables and columns:
set pause off feed off drop table mdw purge; create table mdw(id number,vc1 varchar2(10)); comment on table mdw is 'Martin Widlake''s simple test table'; comment on column mdw.id is 'simple numeric PK sourced from sequence mdw_seq'; comment on column mdw.vc1 is'allow some random text up to 10 characters'; -- desc user_tab_comments Name Null? Type ----------------------------------------------------- -------- ------------------------------------ TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) COMMENTS VARCHAR2(4000) -- select * from dba_tab_comments where table_name='MDW' / OWNER TABLE_NAME TABLE_TYPE ------------------------------ ------------------------------ ----------- COMMENTS ---------------------------------------------------------------------------------------------------- MDW MDW TABLE Martin Widlake's simple test table select * from dba_col_comments where table_name='MDW' order by column_name / OWNER TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------- COMMENTS ---------------------------------------------------------------------------------------------------- MDW MDW ID simple numeric PK sourced from sequence mdw_seq MDW MDW VC1 allow some random text up to 10 characters -- now to add a big comment so need to use the '-' line continuation character in sqlplus -- comment on table mdw is 'this is my standard test table.- As you can see it is a simple table and has only two columns.- It will be populated with 42 rows as that is the solution to everything.' / select * from dba_tab_comments where table_name='MDW' OWNER TABLE_NAME TABLE_TYPE ------------------------------ ------------------------------ ----------- COMMENTS ---------------------------------------------------------------------------------------------------- MDW MDW TABLE this is my standard test table. As you can see it is a simple table and has only two columns. It w ill be populated with 42 rows as that is the solution to everything. -- /
Adding comments on tables, views and columns seems to have dropped out of fashion over the years but I think it is still a very useful feature of oracle and I still do add them (though I am getting a little slack about it myself over the last 3 or 4 years, which I must stop).
Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table.
But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference.
When you design a database, do a schema diagram or an ERD, you label your relationships between entities/tables. It tells you exactly what the relationship is. You might create an index to support a specific method of access or particular business function. You might alter the index in a way not immediately obvious to the casual observer, such as to allow queries that use the index to avoid having to visit the table. All of those things will, of course, be fully documented in the maintained project documentation in the central repository, available and used by all…
If I was able to add comments to constraints and indexes within the database then they would there. You move the system from one platform to the other, they are there. If for any wildly unlikely reason the central documentation lets you down, the information is always there in the database and easy to check. You may not be able to track down the original design documents but you have the database in front of you, so comments in that will persist and be very easy to find.
Lacking the ability to add comments on indexes and constraints, I have to put them at the table level, which I always feel is a kludge. I might actually raise an enhancement request for this, but as Oracle 12 is already nailed down, it will have to wait until Oracle 14. (A little bird told me Larry said there would be no Oracle 13…).
Friday Philosophy – PowerPoint Picture Pain July 21, 2011
Posted by mwidlake in Friday Philosophy, humour.Tags: documentation, Presenting
16 comments
The title does not refer to the agony of sitting through a presentation with loads of pointless graphics and zip-all content. Rather, it encompasses the agony of trying to produce some decent graphical content for PowerPoint. Or Word or any other software-based method of presenting information. I think we generally had better presentations when we had overhead projectors or flip charts and I go “powerpoint naked” to present sometimes.
However, I do present quite often using the ubiquitous PowerPoint. I spend hours on my slides, both trying to put good textual content into them for both the actual presentation and also for anyone who downloads them later. I also spend ages trying to put together graphics as we humans are visual creatures and a good {let’s pause for a second and stress the word good} picture or diagram can help enormously. I did a little animated graphic a few years back to show how DNA is unzipped and duplicated, allowing the near perfect transmission of our DNA as cells divide. Modesty prevents me saying it was good but {drat, I just said it} but several people told me it really helped them understand something that text descriptions struggle to put across.
{Hmm, OK, I’ll see if I can get this to work. Try this how-dna-duplicates powerpoint show. It shows how the two strands of DNA are “matched”. A, C, T and G are the 4 letters of the DNA and each one is associated with its “pair” in the second strand. A to T, T to A, C to G and G to C. Split the pair into two strands and you can make each a perfect copy of the original by adding back the pairs. Understand? Oh, and there are very, very, very rare copy mistakes, in this case a C is matched to a T rather than an A. Go on, fire up the slide.}
The reality is though that most of my presentations have very average slides. When you present for the UK Oracle User group they are good enough to solicit feedback from the audience and feed it back to the presenter. You get scored on topic, content, style, slides, charisma, sex appeal… The lowest part of my score is always, Always, ALWAYS the slides. This started getting on my nerves so one year I tried really hard on the slides. It was still my weakest point.
The thing is, I have never had a graphics team (let alone a whole marketing department) to help me out. Either I have worked for a company with no such thing or I have been a lone consultant doing what I can in my spare time. It is especially frustrating as large companies that do have access to teams of graphic artists seem to present with a large number of very pretty, very well drawn slides with zip-all content and the graphics add almost nothing to understanding (and we are back to the first line of this posting). In fact, I often find that slides with lots of pretty icons representing part of the technology stack with chunky arrows sweeping across them showing flow to be {cough} pointless to my understanding. One thing I have picked up from all those pretty pictures though is that a feeling of depth and texture is very effective in altering how a slide looks.
So, I decided for my posts on Index Organised Tables I would spend some time messing around with such things in PowerPoint. My Graphic of the Thread is:
I’m quite pleased with it, especially as in doing so I found out quite a bit about how to use the gradient fills, shadowing, 3D formating and rotating of simple shapes. It is certainly better than my previous stock graphic for an index:
I think most people would agree, it is an improvement. But is it two days worth of effort improvement. Yes, that is how long I spent messing around with the image options in PowerPoint to replace a flat, uninspiring green blob with 3 lines on it to represent an index into a nicely shaped, pseudo-3D image with a tad more information. *sigh*.
I guess I can now convert other flat pictures made up of boxes, triangles and lines into something that look a bit more like a professional diagram than something knocked up by a database geezer late one evening the day before the presentation. But if it does not help me produce a diagram that aids understanding rather than just presentation fluff, like those marketing slides I’ve just wasted a lot of time.
I tell you what though, having spent so long on that diagram, I’m going to use it to death 🙂
COMMAND_TYPE Values December 10, 2009
Posted by mwidlake in internals.Tags: data dictionary, documentation, SQL
21 comments
Follow-up post on finding most of the COMMAND_TYPES in the data dictionary is here and thanks to Christian Antognini who’s comment led me in that direction.
Spoiler – See end for getting a full list of COMMAND_TYPE values.
If you are in the habit of looking at V$SQLAREA and V$SQL to see what is going on in the database {for those of use who occasionally step out of the “GUI DBA Tool” environment} you will see a lot of stuff in there. I tend to look for the most demanding SQL so I am not interested in PL/SQL, I just want to see the SQL executed via it.
To do this, you can ignore those entires in V$SQL/V$SQLAREA with a “COMMAND_TYPE=47” filter.
This COMMAND_TYPE is useful. 3=SELECT, the most common thing you probablly see and look at, 6=UPDATE, 7=DELETE. 50=EXPLAIN.
If you google/bing/whatever COMMAND_TYPE you get many hits that list of some of the commands, all looking very similar. But very partial lists.
So I decided to amalgamate these partial lists, verrify what COMMAND_TYPE links to what commands in the databases I have access to and publish this fuller, verified list. I quickly found some commands do not stay in the SGA after issuing, so I could not confirm them {“drop index” being one}.
I got this far:
-- mdw 10/12/09 -- check for cmd types not seen before, as lists on web are not complete -- * = I have verified col sql_txt form a60 select command_type,sql_id,substr(sql_text,1,60) sql_txt from gv$sqlarea where command_type not in ( 1 -- create table * ,2 -- INSERT * ,3 -- SELECT * ,6 -- UPDATE * ,7 -- DELETE * ,9 -- create index * ,11 -- ALTER INDEX * ,26 -- LOCK table * ,42 -- ALTER_SESSION (NOT ddl) --two postings suggest 42 is alter session ,44 -- COMMIT ,45 -- rollback ,46 -- savepoint ,47 -- PL/SQL BLOCK' or begin/declare * ,48 -- set transaction * ,50 -- explain * ,62 -- analyze table * ,90 -- set constraints * ,170 -- call * ,189 -- merge * ) and rownum < 20 /
Please feel free to add to it.
Or save yourself the bother and check out the list provided in the Oracle documentation. Not under the descriptions for V$SQL or V$SQLAREA, which would have been the obvious place Mr Larry Ellison thank you, but under V$SESSION. . Further, the fact that in the V$SESSION table the column is called just COMMAND and not COMMAND_TYPE does not assist in locating this information (don’t google COMMAND and ORACLE, you get many millions of hits…). Just click the below.
This is the full table of values and meanings.
(This is the listing for 10.2 and 11.1 is very similar).
But it does not include 189 – MERGE, so that is one up for practical testing than just reading the manual 🙂
Back to the day job…
Friday Philosophy – A Comment on Comments September 25, 2009
Posted by mwidlake in development, internals.Tags: data dictionary, design, documentation, system development
3 comments
This blog is not about blog comments. It’s about table and column comments in the data dictionary.
Some of you may well be going “huh?”. Others are probably going “Oh yes, I remember them?”. Table and column comments appear to be suffering the same fate as ERDs, who’s slow demise I bemoaned a couple of weeks ago. They are becoming a feature not known about or used by those with “less personal experience of history” {ie younger}.
It’s a simple principle, you can add a comment against a table or a column,up to 4000 characters. {you can also add comments against index types, materialized views and operators (huh?), at least in 10.2}.
comment on table widlakem.person is 'Test table of fake people for training purposes, approx 50k records' Comment created. select * from dba_tab_comments where owner='WIDLAKEM' and table_name = 'PERSON' OWNER TABLE_NAME TABLE_TYPE ---------- ------------------------------ ----------- COMMENTS -------------------------------------------------------------------------- WIDLAKEM PERSON TABLE Test table of fake people for training purposes, approx 50k records -- comment on column widlakem.person.second_forename is 'null allowed, second or middle name. If more than one, delimited by / character' select * from dba_col_comments where owner='WIDLAKEM' and table_name = 'PERSON' and column_name='SECOND_FORENAME' OWNER TABLE_NAME COLUMN_NAME ---------- ------------------------------ ------------------------------ COMMENTS ------------------------------------------------------------------------------- WIDLAKEM PERSON SECOND_FORENAME null allowed, second or middle name. If more than one, delimited by / character
So you can stick basic information into the data dictionary where it will remain with the object until the object is dropped or the comment is updated. (You can’t drop a comment, you can only update it to ”:
>comment on table widlakem.person is ”;
It’s simple, it’s sensible, it’s solid.
And it seems to be dying out. In fact, I had stopped adding comments to my tables and columns as no one else seemed to bother. Probably as a consequence of them not being added, no one ever seemed to think to look at them to get hints about the database structure and table/column use.
But Raj sitting next to me is as old a hand at this game as myself and I “caught” him adding comments to the little schema we are working on together. Well, if he is bothering, so will I!
How about Oracle Corp? How do they manage on this front? After all, the Oracle Reference manual has all these short descriptions of tables and columns in the data dictionary {some helpful, some utterly unhelpful}:
select owner,count(*) from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')
group by owner
OWNER COUNT(*)
---------- ----------
SYSTEM 151
SYSMAN 472
SYS 3894
3 rows selected.
Heyyyy, nice Oracle.
select owner,table_name,comments
from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')
OWNER TABLE_NAME
---------- ------------------------------
COMMENTS
-----------------------------------------------------
SYS ICOL$
SYS CON$
SYS FILE$
SYS UET$
SYS IND$
SYSTEM MVIEW_RECOMMENDATIONS
This view gives DBA access to summary recommendations
SYSTEM MVIEW_EXCEPTIONS
This view gives DBA access to dimension validation results
SYSTEM AQ$_QUEUE_TABLES
SYS SEG$
SYS COL$
SYS CLU$
SYSTEM SQLPLUS_PRODUCT_PROFILE
SYSTEM PRODUCT_PRIVS
SYSTEM HELP
SYSMAN MGMT_NOTIFY_QTABLE
SYSMAN AQ$MGMT_NOTIFY_QTABLE_S
Oh. Lots of blanks. Not so nice Oracle. No, scrub that, several lines are not blank, so Not a bad attempt Oracle.
Why all the blanks? Why have Oracle set blank comments? That’s because a blank table comment gets created when you create a table, and a blank column comment is created per column.
create table mdw_temp (col1 number); Table created. select * from dba_tab_comments where table_name = 'MDW_TEMP'; OWNER TABLE_NAME TABLE_TYPE ---------- ------------------------------ ----------- COMMENTS ---------------------------------------------------------------------- WIDLAKEM MDW_TEMP TABLE 1 row selected. select * from dba_col_comments where table_name='MDW_TEMP'; OWNER TABLE_NAME COLUMN_NAME ---------- ------------------------------ --------------- COMMENTS ------------------------------------------------------------------------- WIDLAKEM MDW_TEMP COL1 1 row selected.
So what populated system-like comments do we have?
select owner,count(*) from dba_tab_comments
where owner in (‘SYS’,’SYSTEM’,’SYSMAN’)
and comments is not null
group by owner
OWNER COUNT(*)
———- ———-
SYSTEM 73
SYSMAN 15
SYS 944
OK, there are some, and as you can see below, some are more useful than others…
OWNER TABLE_NAME
---------- ---------------
COMMENTS
----------------------------------------------------------------------------------------------------
SYS SYSTEM_PRIVILEG
E_MAP
Description table for privilege type codes. Maps privilege type numbers to type names
SYS TABLE_PRIVILEGE
_MAP
Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type
numbers to type names
SYS STMT_AUDIT_OPTI
ON_MAP
Description table for auditing option type codes. Maps auditing option type numbers to type names
SYS RESOURCE_MAP
Description table for resources. Maps resource name to number
SYS SESSION_PRIVS
Privileges which the user currently has set
SYS SESSION_ROLES
Roles which the user currently has enabled.
SYS ROLE_SYS_PRIVS
System privileges granted to roles
SYS ROLE_TAB_PRIVS
Table privileges granted to roles
SYS ROLE_ROLE_PRIVS
Roles which are granted to roles
Oracle_DatabaseInstance contains one entry for each Oracle Instance that is
centrally managed. A Real Application Cluster has one entry for each of the
instances that manipulate it. Instances of Oracle_DatabaseInstance are created
using the database instances that are known to the Oracle Enterprise Manager
repository.
SYS DBA_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYS USER_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYSMAN ORACLE_DATABASESTATISTICS
Oracle_DatabaseStatistics provides current information about the statistics for
a database. Database statistics pertain to the database and have the same
value regardless of the database instance that is used.
SYSMAN ORACLE_DBINSTANCESTATISTICS
Oracle_DBInstanceStatistics contains statistics for a database instance. These
are retrieved from the Oracle Managment Repository that is managing the
database upon request from a managment client.
If you don’t add comments to tables and comments, you will just have blank entries for them in the data dictionary.
So why not pop a few real comments in there, especially for any tables or comments where the name is not really telling you what that column or table is for? It’s free and easy, and it might just prove useful. And if you add them to your tables, I’ll add them to mine.
Martin Widlake's Yet Another Oracle Blog 