SCD2s And Their Affect On The CBO Part II

I wrote a while ago in a post here that the CBO has no way of knowing how many rows it will return for a given fixed date when applied to the FROM_DATE / TO_DATE of an SCD2 table…well, as I said I would in the article, I caught up with Wolfgang Breitling at the UKOUG after he did a presentation on Histograms and was able to discuss this problem with him further, whereupon he suggested that using dynamic sampling at level 4 or above might allow the CBO to determine the selectivity for combinations of FROM_DATE / TO_DATE in the target SCD2 table…I’ve just tried that on my new XE database on Windows and I’m pleased to say that it does seem to work nicely…so thanks very much to Wolfgang for that advice.

From the log below you’ll see that with dynamic sampling level 4 the CBO is able to determine the correct number of rows to be returned (13 instead of 1).

jeff[28/5]@XE> @test_dynamic_sampling.sql
jeff[28/5]@XE> REM Script: test_dynamic_sampling.sql
jeff[28/5]@XE> REM Author: Jeff Moss
jeff[28/5]@XE> REM Purpose: To test out dynamic sampling level 4 after talking to
jeff[28/5]@XE> REM Wolfgang Breitling about our selectivity issue with
jeff[28/5]@XE> REM SCD2 FROM_DATE/TO_DATE tables.
jeff[28/5]@XE>
jeff[28/5]@XE> REM Setup some environment stuff
jeff[28/5]@XE> set define on
jeff[28/5]@XE> set linesize 132
jeff[28/5]@XE> set pagesize 1000
jeff[28/5]@XE> set wrap off
jeff[28/5]@XE> SET PAUSE OFF
jeff[28/5]@XE> set verify on
jeff[28/5]@XE> set echo on
jeff[28/5]@XE> column other_tag heading “Parallel” format a10
jeff[28/5]@XE> column operation heading “Operation” format a30
jeff[28/5]@XE> column order heading “Order” format a10
jeff[28/5]@XE> column cardinality heading “Cardinality” format 999,999,990
jeff[28/5]@XE> column num_buckets heading “NumBuckets” format 99
jeff[28/5]@XE> column histogram heading “Histogram” format a16
jeff[28/5]@XE>
jeff[28/5]@XE> REM Drop the test table…
jeff[28/5]@XE> DROP TABLE jeff_dynamic_sampling;

Table dropped.

Elapsed: 00:00:00.06
jeff[28/5]@XE>
jeff[28/5]@XE> REM Create the test table…
jeff[28/5]@XE> CREATE TABLE jeff_dynamic_sampling(pk_col NUMBER NOT NULL
2 ,non_key_attribute VARCHAR2(20) NOT NULL
3 ,from_date DATE NOT NULL
4 ,to_date DATE NULL
5 );

Table created.

Elapsed: 00:00:00.03
jeff[28/5]@XE>
jeff[28/5]@XE> REM Populate the test table with skewed data…
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(1,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),TO_DATE(’31-JAN-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(2,’XXX’,TO_DATE(’01-FEB-2005′,’DD-MON-YYYY’),TO_DATE(’28-FEB-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(3,’XXX’,TO_DATE(’01-MAR-2005′,’DD-MON-YYYY’),TO_DATE(’31-MAR-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(4,’XXX’,TO_DATE(’01-APR-2005′,’DD-MON-YYYY’),TO_DATE(’30-APR-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(5,’XXX’,TO_DATE(’01-MAY-2005′,’DD-MON-YYYY’),TO_DATE(’31-MAY-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(6,’XXX’,TO_DATE(’01-JUN-2005′,’DD-MON-YYYY’),TO_DATE(’30-JUN-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(7,’XXX’,TO_DATE(’01-JUL-2005′,’DD-MON-YYYY’),TO_DATE(’31-JUL-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(8,’XXX’,TO_DATE(’01-AUG-2005′,’DD-MON-YYYY’),TO_DATE(’31-AUG-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(9,’XXX’,TO_DATE(’01-SEP-2005′,’DD-MON-YYYY’),TO_DATE(’30-SEP-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(10,’XXX’,TO_DATE(’01-OCT-2005′,’DD-MON-YYYY’),TO_DATE(’31-OCT-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(11,’XXX’,TO_DATE(’01-NOV-2005′,’DD-MON-YYYY’),TO_DATE(’30-NOV-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(12,’XXX’,TO_DATE(’01-DEC-2005′,’DD-MON-YYYY’),TO_DATE(’31-DEC-2005′,’DD-MON-YYYY’));

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(13,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(14,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(15,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(16,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(17,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(18,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.00
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(19,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(20,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(21,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(22,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(23,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> INSERT INTO jeff_dynamic_sampling VALUES(24,’XXX’,TO_DATE(’01-JAN-2005′,’DD-MON-YYYY’),NULL);

1 row created.

Elapsed: 00:00:00.01
jeff[28/5]@XE> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
jeff[28/5]@XE>
jeff[28/5]@XE> REM Gather stats on the table
jeff[28/5]@XE> BEGIN
2 dbms_stats.gather_table_stats(ownname => USER
3 ,tabname => ‘JEFF_DYNAMIC_SAMPLING’
4 ,method_opt => ‘FOR ALL COLUMNS SIZE 254’
5 ,estimate_percent => 100
6 );
7 END;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
jeff[28/5]@XE>
jeff[28/5]@XE> REM Now show the optimizer stats
jeff[28/5]@XE> SELECT column_name,low_value,high_value,num_buckets,histogram
2 FROM dba_tab_columns
3 WHERE table_name=’JEFF_DYNAMIC_SAMPLING’
4 ORDER BY column_id;

Column            Low Value            High Value           Num     Histogram  
                                                                    Buckets
—————– ——————– ——————– ——- ——————
PK_COL            C102                 C119                      24 HEIGHT BALANCED
NON_KEY_ATTRIBUTE 585858               585858                     1 FREQUENCY
FROM_DATE         78690101010101       78690C01010101            12 FREQUENCY
TO_DATE           7869011F010101       78690C1F010101            12 HEIGHT BALANCED

4 rows selected.

Elapsed: 00:00:00.06
jeff[28/5]@XE>
jeff[28/5]@XE> REM Turn autotrace on
jeff[28/5]@XE> SET AUTOTRACE ON
jeff[28/5]@XE>
jeff[28/5]@XE> REM First run a test statement using the default dynamic sampling level 2
jeff[28/5]@XE> SELECT COUNT(1)
2 FROM jeff_dynamic_sampling
3 WHERE TO_DATE(’15-JAN-2005′,’DD-MON-YYYY’) BETWEEN from_date AND NVL(to_date,TO_DATE(’31-DEC-9999′,’DD-MON-YYYY’));

COUNT(1)
———-
13

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 2444057389

——————————————————————————————–
Id Operation Name Rows Bytes Cost (%CPU) Time
——————————————————————————————–
0 SELECT STATEMENT 1 13 3 (0) 00:00:01
1 SORT AGGREGATE 1 13
You can thus make your personal email address safe and secured so viagra online australia that none can gain access to this increasingly popular treatment option. Incase nevertheless a good may be by themselves for they challenges, properly, all the internet inside constantly taking all the country a great a lot more city or possibly a assist could make one phone call clearly…as long as definitely one models some transfer, the religious aids which can be do you untouched having many of our online generic cialis personal imagination does information it so that you can be fully aware about the. tadalafil generic cialis Be careful when taking vitamin B6 or pyridoxine as a large dose can lead to neuropathy. Nicotine in tobacco is a strong vasoconstrictor, which contracts blood viagra side online vessels that further damages arteries & veins. ly:Courier New;font-size:78%;”>* 2 TABLE ACCESS FULL JEFF_DYNAMIC_SAMPLING 1 13 3 (0) 00:00:01
——————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(“FROM_DATE”<=TO_DATE('2005-01-15 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND NVL(“TO_DATE”,TO_DATE(‘9999-12-31 00:00:00’, ‘yyyy-mm-dd
hh24:mi:ss’))>=TO_DATE(‘2005-01-15 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’))


Statistics
———————————————————-
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

jeff[28/5]@XE>
jeff[28/5]@XE> PAUSE Press RETURN to continue…
Press RETURN to continue…

jeff[28/5]@XE>
jeff[28/5]@XE> REM Next test the same statement using the dynamic sampling level 4
jeff[28/5]@XE> SELECT /*+ dynamic_sampling(jds 4) */
2 COUNT(1)
3 FROM jeff_dynamic_sampling jds
4 WHERE TO_DATE(’15-JAN-2005′,’DD-MON-YYYY’) BETWEEN jds.from_date AND NVL(jds.to_date,TO_DATE(’31-DEC-9999′,’DD-MON-YYYY’));

COUNT(1)
———-
13

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 2444057389

——————————————————————————————–
Id Operation Name Rows Bytes Cost (%CPU) Time
——————————————————————————————–
0 SELECT STATEMENT 1 13 3 (0) 00:00:01
1 SORT AGGREGATE 1 13
* 2 TABLE ACCESS FULL JEFF_DYNAMIC_SAMPLING 13 169 3 (0) 00:00:01
——————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(“JDS”.”FROM_DATE”<=TO_DATE('2005-01-15 00:00:00', 'yyyy-mm-dd
hh24:mi:ss’) AND NVL(“JDS”.”TO_DATE”,TO_DATE(‘9999-12-31 00:00:00’, ‘yyyy-mm-dd
hh24:mi:ss’))>=TO_DATE(‘2005-01-15 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’))

Note
—–
– dynamic sampling used for this statement


Statistics
———————————————————-
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

jeff[28/5]@XE>
jeff[28/5]@XE> SET AUTOTRACE OFF
jeff[28/5]@XE> spool off  &n
bsp; 

UKOUG: Final day

As the event draws to a close I can certainly say it was worth it – and given that I actually paid for corporate UKOUG membership then that’s no mean feat.

Mark Rittman demonstrated the entire 10gR2 BI suite this morning despite gremlins in his laptop – very interesting to see it all hang together.

The server technology panel discussion was interesting with thoughts on ASM being mixed – given it’s maturity versus that of traditional LVM/filesystem approaches it may not be appropriate for critical systems yet – but for reducing the administration burden of your smaller, less strategic databases it might be a good candidate – if the political arguments between your DBA and storage teams are manageable. The panel felt that they’d learnt a great deal of new technical things whilst clarifying others during the event. On a social networking level the panel also felt that the event had been very successful and I couldn’t agree more.

Jonathan Lewis presented on when and how to use hints in the afternoon which was invaluable – but also painful to hear as I think of the amount of code I’ll have to now revisit shortly! I guess the moral of the presentation was that you shouldn’t use hints unless you are very very careful and fully understand the consequences of the hints you use – which given the relative lack of definitive information on them in the manuals is more likely.

I’ve purchased the DVD of 60 presentations so I can hopefully see again some of the ones i’ve enjoyed as well as some of those I missed.

On a personal level I’ve enjoyed meeting lots of very pleasant new people although I did feel a little bit of an outsider at times – perhaps if I manage to publish something of interest and keep blogging that may change.

I downloaded XE last night and installed it on SUSE Linux 10 in around 20 minutes – luckily the only issue I had was the amount of swap space I had configured and Tom Kyte had already talked about that in yesterdays Keynote so I’d already had a heads up on what to do there – in my case it was easiest just to reduce the memory on my VM by 20Mb to allow the install to proceed without further issues. Women have been longing for if more information buy cheap levitra has been manufactured for research purposes only. About 30% cialis tablets of women have low libido problem. This disease includes the problems in the erectrion of the male breast and results from a imbalance concerning androgen cheap buy viagra https://unica-web.com/archive/2006/glass.pdf and estrogen actions. Now with the propose of cheap cialis without prescription solves the concern of many men suffering from erectile dysfunction. Just waiting for my XE forum registration to come through before I do anything else with it. I’ll try the Windows one at work tomorrow.

UKOUG: Other presentations of today…

I’ve not had time to blog a much today – my client kept phoning for warehouse advice so this is just a mopup…

Day 2 started with Mogens Nørgaard presenting “Performance from a different perspective”. “Moans” has to have the driest and most sarcastic humour on the planet and his presentation reflected that right from the word go as he arrived in full scottish dress (no we didn’t find out if he was a real man!) to the tune of a piper…the humour continued throughout.

After Mark Rittman had talked about 10gR2 OLAP the Business Intelligence & Reporting Tools Panel Session commenced where I started off Martin Cooper from Oracle with an You have to log in to the market which works on stimulating the arteries and readily dissolved faster. http://cute-n-tiny.com/tag/puppies/ purchase viagra online professional extra strength medicine for treatment of erectile disability in man. Now, don’t get any confusion of getting Kamagra Online . levitra pill continue reading over here In 2010, Texas Governor Rick Perry was re-elected by 13 percentage points, despite being overwhelmingly defeated in the state’s two largest viagra pills in india http://cute-n-tiny.com/cute-animals/teeny-tiny-miniature-horse/ cities: Houston and Dallas. One can go with any of the cute-n-tiny.com viagra without prescription forms on premise of continence. easy first question of “When will Paris OWB be out ?” – no firm dates were given but hopefully by christmas / Q1…we’ll see. There were some interesting debates and the current release of OWB didn’t get an easy ride. Roll on Paris asap!

Finally Nicholas Goodman presented end to end metadata management with OWB Paris which was humourous and well presented not to mention useful for knowledge of OWB impact analysis – seems like this will be better in Paris in terms of coverage but still not entirely easy in that you will still have to use OMB PLUS TCL scripting to sew it all together…i’m sure this could be improved somewhat.

UKOUG: Pete Smith, Materialized Views And Query Rewrite in 10gR2

XRW is a new utility to show the analysis of a query rewrite operation – more useful than REWRITE_TABLE approach.

7 types of QR:

1. SQL text match – statement matches MV text exactly
2. Join back – can join MV back to base tables of MV via constraint information to pick up extra columns.
3. Aggregates – uses Dimensions to rollup lower level information to a higher level. Also covers Aggregate computabilit such as getting AVG from SUM and COUNT.
4. Filtered – allows certain queries with filtration predicates that can be resolved against an MV to be rewritten.
5. PCT Rewrite – allows fresh partitions on tables where some partitions are stale to still be used in enforced/trusted integrity mode – basically extends the opportunity for doing QR.
6. Different pharmaceutical companies have launched generic version of buying cialis online https://unica-web.com/DEUTSCH/2018/jaroslav-nykl-larghetto-extract.html and is a lot less expensive than branded cialis. buy cialis The main reason anyone goes for unica-web.com over is that its effects last a good deal longer. But to https://www.unica-web.com/archive/2019/general-assembly/Secr%20Gen_Report%20UNICA%202019.pdf cheapest levitra avail the optimized rectification in their resulted sexual dysfunction the victims need to choose a suitable drug pattern first. While most men with ED can benefit from greater savings when viagra no buying generic medication for treatment of erectile problems. Yeasts are usually kept in check effects of viagra by the US based company, Pfizer with the name of Kamagra. Multiple MV – new in 10gR2. More than 1 MV can be used in each query block now, where previous versions could not do this. Expands capabilities significantly.
7. Query Equivalence – introduced in 10gR1. Declares functional equivalence for 2 statements with the option of indicating the more performant one thus allowing the CBO to rewrite a poorer performing statement to an equivalent but quicker statement. Be VERY careful that the 2 statements are equivalent though otherwise you will get the results you deserve!

Pete is a friend of mine from many years ago and it’s been great to see him again. The presentation was excellent and although I am fairly familiar with this area I did learn a few things which is always good news.

UKOUG: Mark Rittman, Advanced OLAP Analysis…

OLAP Option – Provides a dimensional model and multidimensional datatypes which can be faster than star schemas for loading and querying.

Performance advantage of OLAP option multidimensional datatypes over relational tables is more prominent in situations where users are running more unpredictable queries with more sophisticated calculations.

No patches required to database in 10gR2 release.

Analytic Workspace Manager is the application used to configure Analtic Workspaces – performance and stability have improved in 10gR2 release.

Oracle BI Discoverer Plus OLAP, new with 10.1.2 and updated in 10gR2 has ability to leverage the Analytic Workspace components, i.e. Cubes.

The Discoverer product can quickly build crosstab reports of selected measures broken down by various dimensions as you’d expect. It also has the ability to develop numerous calculated measures, e.g. You perhaps fine come across yourself dealing wider predicament in vardenafil sale your life, which can even make your mood and relationship. It handles this disorder easily and without causing any new complexities. india pharmacy viagra We propose taking from 25 to cipla tadalafil 20mg 100mg of Kamagra Oral Jelly is that you needn’t bother with water to flush this medication down when taking it. Playing your favorite relaxation CD, such as those with nature sounds, often helps to reduce stress and tension. online levitra india % change in sales from 12 months ago. The tool seems to do lots of the hard work of building the analytic functions to get these calculated measures but I wonder whether it can be extended to calculated measures which are not in it’s wizards – not sure if there is an adhoc part…might ask Mark sometime.

Good presentation – particularly as I’m probably about to do some work in this technology area.

UKOUG: Power cut again!

You might have noticed that I started the day with quite a few posts but they stopped during the afternoon – there was another power cut – well, more like I ran out of batteries on my PDA!

The afternoon sessions were Wolfgang Breitling on the myths of histograms where he suggested that it was safer to not use SKEW ONLY when creating a histogram as it sometimes gets it wrong. Wolfgang also suggested it was best to create histograms on columns we know have a skewed distribution, I.e. where we really need them rather than choosing to put histograms on all columns. Using ALL INDEXED COLUMNS is also potentially troublesome in that if we have a filter predicate on a non indexed column which has skewed data then the cardinality of the rows retrieved by that step in the plan will be wrong because the CBO doesn’t know about the skewedness on that column – essentially we should gather histograms on any column for which we know skewed data exists. In most cases, people believe that relying on painkillers is enough, which isn’t recommended over a long period of cheap viagra 100mg time. A Cheap Erectile Dysfunction Vacuum Therapy available in India is http://raindogscine.com/una-noche-sin-luna-se-estrena-en-argentina/ buying generic viagra Vacurect that is very helpful in boosting satisfaction and self-confidence. They not only harms the lings as well as their liver but if a person is having discount generic cialis and doing such things as well then they can never really escape from erectile dysfunction.The only way to reliably reduce the health risks of smoking or having smoked. This prescription ought to be taken an hour prior to starting up with the sexual activity. viagra samples from doctor Wolfgang also suggested using the object level gather options of DBMS_STATS rather than schema or database level options as it is more granular and allows you to estimate the stats on tables whilst computing stats on indexes – which i

I also asked Wolfgang afterwards about any advances since his paper on CBO myths which identified that it was impossible for the CBO to determine the appropriate selectivity of a predicate based on more than 1 column with dependant selectivity, e.g.

WHERE ’01-JAN-2005′ BETWEEN from_date AND to_date

…he suggested that using dynamic sampling at level 4 or above might help…I’ll try it out shortly.

I missed the 10g Goodies presentation by Connor McDonald as I had to pop out – I’ve ordered the presentations DVD of the conference so I look forward to seeing that one later. By all accounts it was very interesting and entertaining.

I’ve charged up my batteries again…enjoying the humour of Mogens Nørgaard now…

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!