Uncategorized

Top Ten Travel hints and Tips

Well, let me be honest right at the top here.  These are not travel hints Smile  These will not help you in any way.

This is me having a whine and a rant about a minority of people that I occasionally encounter when travelling.

Yes, this can probably be best described as me and my first world problems, but I need to expunge these so that next time I travel, I don’t lose my head, and stuff some poor unsuspecting innocent passenger“under the seat in front of me or in the overhead locker” Smile

DIY parallel task execution

We had a question on AskTOM recently, where a poster wanted to rebuild all of the indexes in his schema that had a status of UNUSABLE.  Running the rebuild’s in serial fashion (one after the other) seemed an inefficient use of the server horsepower, and rebuilding each index with a PARALLEL clause also was not particularly beneficial because the issue was more about the volume of indexes rather than the size of each index.

An obvious solution was to use the DBMS_PARALLEL_EXECUTE facility.  Our poster was pleased with our response but then came back asking for help, because they were stuck languishing on an old release for which DBMS_PARALLEL_EXECUTE was not yet present.

It’s just bad code or bad design … most of the time

Some years ago I wrote an article for the UKOUG magazine called “Want a faster database – Take a drive on the M25”.  For those not familiar with the United Kingdom, the M25 is one of its busiest roads (M = “motorway”) and because it moves so much traffic, and runs so close to capacity, it has often been referred to as “the world’s largest car park”.  Many people have probably spent a good part of their lives on the M25 Smile  I used the M25 as a metaphor for how database professionals can focus on the wrong things when trying to solve a performance problem, such as:

“I’m stuck in traffic…perhaps a faster car will help”

ie, throwing CPU at a problem that is not CPU bound will not help things, or

“I’m stuck in traffic…it must be the width of the paint on the lane markings”

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace

OTN Yathra 2017

The amazing 6 city set of events is just around the corner, with all of the dates and details available at http://otnyathra.in/

image

Here are my thoughts on why you should be attending

Instrumentation … not just for debugging

Yeah I know.  You’re probably thinking “Here’s another blog post from someone telling us how important it is to instrument our code, so we can get better debugging, better performance, blah blah blah”.

The village idiot

If you are not familiar with the term Village Idiot, then Wikipedia provides a sufficient definition from which I can base this blog post.

https://en.wikipedia.org/wiki/Village_idiot

“The village idiot … is a person known for ignorance or stupidity”

Over the past couple of weeks I’ve been flying a bit.  First was OpenWorld and OracleCode in New Delhi in India, and from there, I was heading straight from there to Cleveland, Ohio for the GLOC users conference for the first time.  Being a fairly seasoned traveller, this should have been a relatively straightforward affair.

Well… things didn’t turn out that way. 

opt_estimate

The opt_estimate hint is one of many that shouldn’t be used in end-user code and isn’t officially documented. Nevertheless – like so many other hints – it’s a hint that is hard to ignore when you see it floating around the code generated by the Oracle software. This note is prompted by a twitter question from fellow Oak Table member Stefan Koehler asking the about working of the hint’s index_filter parameter. Checking my library I knew the answer was yes – so after a quick exchange on twitter I said I’d write up a short note about my example, and this is it.

Quick tip–identity columns

Lets say I’ve been reading about schema separation, and thus I am going to have a schema which owns all of my objects, which I’ll call APP_OWNER, which will have no connection privilege and a separate schema called APP_ADMIN which will take care of all of the DDL tasks.

Here’s my setup:

RTFM

Imagine you’re fairly new to Oracle and don’t have a lot of background information at your fingertips; then one day someone tells you to read the manual pages for the view dba_free_space. Look carefully at this sentence:

Note that if a data file (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information.

Can you spot the error ? Did you spot the error when you first read the sentence – or did you fill in the gap without noticing what you were doing ?

Let’s demonstrate the accuracy of the statement (simple cut-n-paste from an SQL*Plus session on 12.1.0.2 running in archivelog mode, and with a locally managed tablespace consisting of 4 (oracle managed) files on a filesystem):