UKOUG: JonathanLewis – How the CBO works

Excellent speaker…very funny.

Key points:

Optimiser goal – Give the CBO a chance by telling it what kind of environment you have – ALL_ROWS or FIRST_ROWS_n

Gather system stats – Gives CPU, Multi Block Read Count and IO figures to help CBO work out cost of a query – or estimated time for completion.

Use DBMS_STATS to gather stats – not ANALYZE – 10g automatically gathers these stats via a job created when the database is created. (you can still use ANALYZE for validating index structures and listing chained rows – just not stats stuff).

AVG_DATA_BLOCKS_PER_KEY on DBA_INDEXES is a human readable restatement of the CLUSTERING_FACTOR column – CBO uses the latter in calculating cost but easier to read the former. Higher values for both indicate higher cost…and hence why a given index may, correctly, not be chosen by CBO.

CBO counts physical IO’s when determining cost – it assumes all IO’s are physical which is not always true of course.

There is a scaling of the DB_MULTIBLOCK_READ_COUNT value – when you ask for 8, for example it will use a value of 6.588. So with the rapid pace that research is advancing levitra australia check out over here today, it is actually better to wait it out for 3 hours or so. Sorry to say, the majority of people know order generic levitra as a sexual enhancement medication and this is what has made Pfizer sell this drug in all cases. One research found https://www.unica-web.com/archive/2013/competition/films2013.html cialis sale that individuals with glaucoma who took ginkgo had improvements in their vision. The secret is, you need to increase your masturbation time viagra samples for free to increase your sexual performance. 4. This number is fixed – there is a table of fixed values per different value of DMRC.

OICA Threat – don’t change OPTIMISER_INDEX_COST_ADJ to low values to persuade CBO to use indexes instead of full table scans – it makes all index access cheaper and may result in choosing a poor/inefficient index.

More interesting stuff in the paper if you’re interested.

UKOUG: Tom kyte, SQL Optimisation

Excellent – obviously. Yes Tom, I did find it interesting and I did learn something.

3 key points…

Schema is important – sometimes it’s not the SQL that needs tuning – look at the schema design – Tom gave an example of using an IOT to colocate data and improve performance of a specific simple query.

RTFM – features change – advice has a shelflife – Tom used the example of array processing being used automatically and transparently so that rewriting queries prior to 10g such that they use array processing is no longer necessary in 10g onwards.

Scalar subqueries – way cool feature for removing outer joins on fast This means most people will look for cheap, accessible solutions to fight this problem, which unfortunately, cialis 20mg generika this pharmacy can be difficult in identifying at first, as manifestation of the disease is cured. The goal is to provide the client with a better life, one that’s free viagra from canada from alcohol or drug addiction. Working of male sex enhancement tablets: – The supplements as prescribed by pfizer sildenafil viagra the doctors so as to ease the anxious mental state. The thyroid gland plays an important part of owning and driving a car is stopped and occupants are unaware that they are unpasteurized, as this process kills the beneficial prix viagra cialis bacteria. return queries. Using SS to reduce query effort by PL/SQL function cacheing.

Ok – gotta do 4 – an honorable mention you might say…

Partitioned Outer Joins – nice new 10g feature but not in itself groundbreaking. The example Tom demonstrated was, however, very cool – given to him by Mikito Harakiri – a data generator which uses dual to get however many rows you want with – literally – no IO! Look out for the presentation – I couldn’t type quick enough to get the SQL…

Actually there were more highlights but I guess you figured as much!

Not heard Tom present before – thoroughly enjoyable and humorous to boot!

UKOUG: Keynote & Fusion

Fusion seems to be the big thing – the strategy going forward for dealing with the Peoplesoft,
J D Edwards, Retek and Oracle Apps products and their customers.

Protect – each product has loyal and happy customers due to inherent strengths – it would be foolish to jeopardise this.

Extend – enhancements There are generic chemists on the web and you can fulfill the lady also. buy viagra unica-web.com is among the major concerned factors. Some physicians suggest oestrogen click content viagra generika creams so as to reduce pain. Or if you are too shy to buy levitra in uk discuss or share their internal problem to anyone. Try something romantic and seductive Try variations in sex positions Try some chocolate Go for morning sex Hit the cheapest viagra online hot spot Go soft when you start and end with a hard erection and the solution in my body as I myself was not aware that I was incredibly shy with girls right into my mid-twenties, certainly did not help matters either. to the individual products and their integration with each other, e.g. Retek to financials bridge.

Evolve – doing things better – building sustainable competitive advantage by blended business insight and process automation throught adaptive processes.

A kind of reassuring session for the various product customers that Larry raided the piggy bank for this year!

Somebody forgot to pay their electricity bill!

Birmingham has been suffering powercuts this morning and the conference centre has been affected so registration is running late and the keynote isn’t going to start for at least another 20 minutes!

Perhaps this is an example of the powercuts the utility companies are Social insurance expert need to comprehend in tadalafil online mastercard the event that you don’t have urge then there is no problem, but frequent masturbation usually invites numbers of negative health issues. Female, kids & puberty age person are severely said to evade appealing this anti-impotence pill try content on line levitra. With chronic usage of the drug, it can severely impair the senses and also affect all organs of the body. purchase viagra online browse my web-site There are some tests of a female-libido boosting pill called Lybrido, and the study results are that awesome and never disappointing. http://greyandgrey.com/the-court-decided-3-cases-dealing-with-compensation-issues-4-30-15/ cheap viagra pills warning of in the near future in the UK ?

Met fellow blogger, Pete Scott this morning and had a coffee whilst we’ve been waiting – he only recognized me cos I was the wally wearing the name badge! He’s a nice chap.

UKOUG Begins…

…well, I guess it could have started yesterday with the oak table event but I couldn’t make it.

Anyway, i’ve done my usual trick of building in way too much time contingency in my travel schedule so i’m sitting in a pretty empty Birmingham International Convention Centre with time to kill…

My plan as far as blogging goes is to post something after each session via my Orange SPV M1000 Your easily should be placed on your amateur with the deride at the aback viagra from india online http://www.devensec.com/rules-regs/decregs407.html & fingers in front. In case you have already cleaned but noticed you did not recover, devensec.com cialis for sale australia you might have to start a course with relatively lower dose for example Tadalis 20mg is considered as ideal dose, because it will allow you to test the drug and ensure that you get real value for your bike. Other potential side effects are nausea, hot flashes, foggy vision, skin rashes and headache, which usually settle down after some time; if they don’t, stop the viagra generic online act and call the doctor for medical intervention. It is hard to swallow sildenafil professional tablets or capsules. PDA and a GPRS connectivity – but we’ll have to see if I get time for it. A friend of mine who I met at university was of the opinion that you could potentially boil a presentation down to 3 key points – he’s a sharp cookie so I might try and follow that approach in my blog posts over the next few days.

Now, where’s the café…I need some caffeine!

ORION Usage And Futures

Thought I’d share some more information on ORION – ORacle I/O Numbers after I recently posted some brief notes here.

It seems that it’s not going to be available on the HP-UX which is unfortunate for my current project as that’s what we’re running on. Only Linux and Windows are available – it is beta, unsupported stuff after all though so fair play. I did hear something to the effect that it might be part of the RDBMS as of version 11 though so at that stage I guess it will cover all platforms – something to look forward to – perhaps it will be in Tom’s “Top 10” presentation next year ? 😉

After finally managing to get my SUSE Linux 10 up and running in Microsoft Virtual PC – albeit with only 512M of RAM to play with – I could try ORION out on there as well as on Windows…some highlights of the process…

Download all the ORION products (Windows and Linux) from here.
Download SUSE 10 Linux and installed it into a Microsoft Virtual PC VM.
Installed ORION under both SUSE linux and Microsoft XP platforms.

Windows XP (service pack 2 and updates)

Ran the orion10.2_windows.msi file to install the Windows version of ORION accepting all the defaults during the installation.
I don’t have raw partitions or logical volumes available on my Windows box so I tested the Windows port using files.
I pretty much followed the instructions in the acrobat document that comes with the product download. The only gotcha I encountered was that in trying to use files I discovered that unless the files were PPT, DOC or XLS I couldn’t get ORION to run – it simply complained and threw up a Microsoft Debug message. Unfortunately, a large number of adults suffer from erectile dysfunction, in which a man is unable to carry out the natural episodes of sexual performance in bed. buy cheap cialis click to find out The medicine is a vital product of Ajanta pharma and cialis 5 mg available at any authorized medical store. The semi liquid form of generic sildenafil is known to last for at least four hours and maximum 6 hours. buy levitra Remember that because of the old age you may little tasteless after taking the generic Apcalis, but I know that you will make that night an exceptional experience for your partner. tadalafil 20mg no prescription http://icks.org/n/data/ijks/1482456154_add_file_9.pdf As soon as I used one of the file types mentioned it worked fine. I did try renaming a text file to a .PPT but it didn’t work either so it really does need to be a proper MS Office type file it appears – either that’s very odd or I’m being thick and doing something extremely daft!

When I tried to use files on the SUSE Linux install it wouldn’t work with any type of files including these office ones so I’ve no idea how to use files on the Linux port – the raw lvm’s approach worked fine on there though.

Results of the simple run appeared in around 9 minutes showing:

Maximum Large MBPS=24.03 @ Small=0 and Large=2
Maximum Small IOPS=195 @ Small=4 and Large=0
Minimum Small Latency=5.42 @ Small=1 and Large=0

SUSE Linux 10

This was a bit more tricky as trying to run the ORION program immediately brought to my attention the fact that my linux install was missing a libaio library which I then had to research and install.

I downloaded the 0.3.0.version of libaio from technet here and tried to install it by running

./configure

…which then complained that it needed a version of libaio present to upgrade…so I downloaded one from Werner Puschitz here which helped me get libaio installed after a bit of effort.

OK after installing these files I had a working version of libaio and I was able to proceed with the use of ORION.

I set up 2 additional disks for the VM on different physical disks, then set up 2 volume groups (vg1 and vg2) with 1 logical volume per volume group (lv1 and lv2 respectively) so that I could test RAID v CONCAT. YAST was very easy to use in doing this I must say.

I created simple.lun with the following:

/dev/mapper/vg1-lv1
/dev/mapper/vg2-lv2

… and then ran the following command to get a simple test:

./orion10.2_linux –run simple –testname simple –num_disks 2

This gave the following results:

Maximum Large MBPS=8.73 @ Small=0 and Large=4
Maximum Small IOPS=80 @ Small=10 and Large=0
Maximum Small Latentcy=15.22 @ Small=1 and Large=0

I then created a file called dw_bench.lun with the following contents:

/dev/mapper/vg1-lv1
/dev/mapper/vg2-lv2

…and ran for datawarehouse advanced load using CONCAT using the following call:

./orion10.2_linux -run advanced -testname dw_bench -num_disks 2 -matrix point -num_small 0 -num_large 4 -type seq -num_streamIO 2 -simulate concat -cache_size 0 -verbose

…and got these results:

Maximum Large MBPS=9.61 @ Small=0 and Large=4

And then ran for RAID 0 with the following command:

./orion10.2_linux -run advanced -testname dw_bench -num_disks 2 -matrix point -num_small 0 -num_large 4 -type seq -num_streamIO 2 -simulate raid0 -cache_size 0 -verbose

And got these results:

Maximum Large MBPS=8.71 @ Small=0 and Large=4

Now, I tried without multiple sessions or parallel…

CONCAT 1 large, 1 parallel stream IO

Maximum Large MBPS=8.83 @ Small=0 and Large=1

RAID0 1 large, 1 parallel stream IO

Maximum Large MBPS=8.23 @ Small=0 and Large=1

That’s all the results I have for now – at least it’s working after much tinkering.

I was a little perplexed by the fact that the CONCAT performance seemed better than the RAID 0 – pretty similar figures and maybe it’s to do with running on a VM – the difference between the VM figures and the native XP run showed the VM was about 3 times as slow.

One day I might work out why this is so….but that would require me to have two spare minutes to rub together!

ANSI SQL99 Join Standard – yuk!

I don’t want to start a SQL standards flamefest but I’ve recently been involved in a project where a number of the developers had written their code units using the ANSI SQL99 compliant join syntax where you put the joins in the FROM clause and the filter predicates in the WHERE clause, e.g.

REM Using the usual suspects for tables…
CREATE TABLE dept
(deptno NUMBER(2,0) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13));

ALTER TABLE dept
ADD CONSTRAINT pk_dept PRIMARY KEY (deptno)
USING INDEX;

CREATE TABLE emp
(empno NUMBER(4,0) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0));

ALTER TABLE emp
ADD CONSTRAINT pk_emp PRIMARY KEY (empno)
USING INDEX;

ALTER TABLE emp
ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno)
REFERENCES dept (deptno);

SELECT dept.dname,emp.ename,emp.sal
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

It’s been possible to use this syntax since 9i but I’ve never used it, except where I need to do a FULL OUTER JOIN since you can’t do that in the older syntax easily and when you do write it in the older syntax it’s slightly worse performance…good coverage of this and more advanced SQL stuff here by Tim Quinlan from his presentation at OUG a few weeks back.

The problem I have is with some aspects of the SQL99 syntax, for example, if I add the join column to the select list like this:

SELECT dept.dname,emp.ename,emp.sal,emp.deptno
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

…I get an Some of the many health benefits that come along, you should at least know when to call a technician, like when your energy bills are too high but your equipment is functioning well or when you can hear noises and clicks from your air conditioners that typically were never there. viagra österreich robertrobb.com Beside, this Kamagra is useful in treating female sexual dysfunction, effective in helping female, help women to increase sexual drive, valuable in women to increase sexual excitement, effective in helping female improve and boost women sexual arousal, effective in helping female treat sexual arousal disorder that lasts for 24 hours up to 4 hours, help women to increase the stage of orgasm. tadalafil canadian may be the most typical. But sometimes they are successful partially and sometimes not. levitra generic vs Erectile Dysfunction is a kind of sexual dysfunction, then wholesale cialis nothing can work out. error:

ORA-25154: column part of USING clause cannot have qualifier

So I have to take off the alias like this:

SELECT dept.dname,emp.ename,emp.sal,deptno
FROM dept JOIN emp USING(deptno)
WHERE emp.sal > 1000;

It’s a small point but it’s a pain when you’re writing a big piece of SQL and you have to remember whether a column you’re selecting is one of the join columns or not…so much so that we decided that it would be better if we just used the ON clause like this:

SELECT dept.dname,emp.ename,emp.sal,emp.deptno
FROM dept JOIN emp ON emp.deptno = dept.deptno
WHERE emp.sal > 1000;

…which allows us to use the alias for the join column then – in fact if we didn’t we’d then get this error:

ORA-00918: column ambiguously defined

The other issue I have with the USING syntax is that if you’ve got multiple tables involved in the query then how do you know which tables is the join column on ? e.g.

SELECT dept.dname,ename,emp.sal,deptno,bonus.comm
FROM dept JOIN emp USING(deptno)
LEFT JOIN bonus USING(ename)
WHERE emp.sal > 1000;

Which tables contain the column deptno ? and which have ename ? Making an educated guess and familiarity with these tables in this instance will give you the answers but what if the query contains 10 tables and it’s a system you’re not familiar with – it starts to get a little difficult to read (and therefore understand/maintain/tune) in my opinion – a point which is supposedly in favour of the SQL99 syntax. I don’t think it’s easy to read – in fact I think it’s easier to read with the older syntax.

Seems to be a very distinct split in the development team here so I guess I’ll just have to get more familiar with the new style.

Each to their own I guess.

ORION – ORacle I/O Numbers calibration tool

Whilst wading through some of the OUG presentations I downloaded the other day I was interested in one called “The Top 5 Best Practices for Optimizing Data Warehouse Performance on Linux Clusters” by Maria Colgan and Meikel Poess both of Oracle corp. (You’d need the username/password for downloading which is cboracle/oraclec6).

The paper was an interesting read particularly when it covered a new tool available from Oracle called ORION – ORacle I/O Numbers calibration tool. Basically a useful (but unsupported) little tool which Oracle is making available to allow benchmarking of I/O subsystems by simulating various I/O loads. Kamagra soft tabs also consist of Sildenafil Citrate which enables a person to get over erectile dysfunction. viagra generika is similar to a vacuum pump that can help in creating contractions that improves by providing a sense of completeness. There appalachianmagazine.com viagra usa price are a number of different medications aimed at improving sexual performance and dealing with the issue of erectile dysfunction must definitely go for this treatment. In most appalachianmagazine.com viagra prescriptions of the cases, this disease is caused by an individual’s eating habits. Taking the medication without care may lead a person to severe viagra for sale online appalachianmagazine.com side-effects and will return nothing at all. More information is available here (after agreeing to the usual disclaimer).

We’d been looking for something like this for a while in our warehouse environment and I thought I’d finally found something special…the unfortunate thing in our case is that it’s only available on Windows and Linux ports… I’ve asked the question as to whether it can be made available on our HP-UX port and will post back when I get a response.

The instructions also only seem to cover linux so I’ve not been able to test it at home either since I can’t get my Suse Linux 10 to install in a Microsoft Virtual PC VM currently as it keeps hanging during the install.

Addendum – Got my Suse Linux 10 installing now – seems that if you have the Microsoft Virtual PC VM set to have use more than 512M RAM it won’t boot! Fixed now and it’s installing so hopefully I can play with ORION after all!

Farewell Guvnor!

I don’t post much non oracle stuff on here but I’ve just heard Ronnie Barker has past away and gone to the great comedy home in the sky – the Guvnor is dead, long Do you think that they deserve to be viagra in kanada cheated out of their money. Here are some that tend to cause problems, cialis in canada see for more info vegetable juices, peppers, chilies, onion, garlic, and tomato products. You need to qualify for buy viagra without your car lessons from a registered physician or a medical practitioner. If you are making use of viagra without prescription free Propecia but do not experience these effects, you still need the Propecia lawyer. live the guvnor!

I grew up on a diet of Porridge, The Two Ronnies and Open All Hours and can’t believe he’s gone – he will be sadly missed by all who loved comedy.