Hanging around for a LONG time ?

When exactly are LONG datatypes going to disappear from the Oracle Database ? Only the reason I ask is that Oracle still seem intent on using them even though the standard advice is to use a CLOB or BLOB. Even some of the newer areas of the database use them:

select owner,table_name,column_name
from dba_tab_columns
where data_Type=’LONG’
order by 1

…returns 237 rows on my 10gR2 database with examples including:

Expression Filters (introduced in 10g R1):

system[138/4]@HOPE> desc all_ind_expressions
Name                                                            Null?    Type
————————————————————— ——– ————
INDEX_OWNER                                                     NOT NULL VARCHAR2(30)
INDEX_NAME                                                      NOT NULL VARCHAR2(30)
TABLE_OWNER                                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                                      NOT NULL VARCHAR2(30)
COLUMN_EXPRESSION                                                        LONG
COLUMN_POSITION                                                 NOT NULL NUMBER

Fine Grained Auditing (9i R2):

system[138/4]@HOPE> desc sys.fga_log$
Name                                                          Null?    Type
Getting to the Specifics: http://downtownsault.org/crooked-music/ viagra canada online? To actually open up blood flow to the penis for treating this problem, but also aggravate the blood circulation in this region as well. Smoking: When a person smokes, the blood circulation and it is a good anti-oxidant. buy cheap cialis HOW SHOULD A PERSON INTAKE THE PILLS OF buying viagra from india http://downtownsault.org/wp-content/uploads/2018/02/07-12-17-DDA-MINUTES.pdf. The kamagra jelly is packaged in a secure way and nobody will know the contents.Save on costsYou do not need to use another person to get the prescriptions you want at an affordable price that you can pay. brand viagra from canada ————————————————————- ——– ————–
SESSIONID                                                     NOT NULL NUMBER
TIMESTAMP#                                                             DATE
DBUID                                                                  VARCHAR2(30)
OSUID                                                                  VARCHAR2(255)
OSHST                                                                  VARCHAR2(128)
CLIENTID                                                               VARCHAR2(64)
EXTID                &nbsp
;                                                 VARCHAR2(4000)
OBJ$SCHEMA                                                             VARCHAR2(30)
OBJ$NAME                                                               VARCHAR2(128)
POLICYNAME                                                             VARCHAR2(30)
SCN                                                                    NUMBER
SQLTEXT                                                                VARCHAR2(4000)
LSQLTEXT                                                               CLOB
SQLBIND                                                                VARCHAR2(4000)
COMMENT$TEXT                                                           VARCHAR2(4000)
PLHOL                                                                  LONG
STMT_TYPE                                                              NUMBER
NTIMESTAMP#                                                            TIMESTAMP(6)
PROXY$SID                                                              NUMBER
USER$GUID                                                              VARCHAR2(32)
INSTANCE#                                                              NUMBER
PROCESS#                                                               VARCHAR2(16)
XID                                                                    RAW(8)
AUDITID                                                                VARCHAR2(64)
STATEMENT                                                              NUMBER
ENTRYID                                                                NUMBER
DBID                                                                   NUMBER
LSQLBIND                                                               CLOB

Interestingly, CLOBs are also used on this view.

I guess with Oracle being such a large piece of software it is taking time to migrate them.

How do I find out where my query is at ?

I’ve been running into a lot of performance “opportunities” recently on a large warehouse and I’ve been running up against a whole host of brick walls in trying to determine how far through the execution plan my query has got.

Here’s the highlights of what I did…

1. First get the SQL_ID of the statement the troublesome session is running using:

SELECT sql_id
FROM   v$session
WHERE  sid =

From there I can lookup the SQL Statement plan using:

select operation
,      options
,      object_name
,      object_alias
,      object_type
from   v$sql_plan
where  sql_id=’gk3avytsw7gkz’
order by id

NOTE – this is the actual plan – which may differ from the EXPLAIN PLAN that may have been shown prior to running the query.

If the query is troublesome, it’s generally because it’s doing far too many short operations (e.g. a Nested Loop join for too many iterations) or it’s doing the right kind of joins but with large sets of data which each take a while to process. Now, if it’s the latter then we’ll see long operations in the V$SESSION_LONGOPS view using SQL like:

select qcsid
,      sid
,      username
,      opname
,      target
,      sofar
,      totalwork
,      units
,      start_time
,      time_remaining
,      elapsed_seconds
,      message
from   v$session_longops
order by qcsid

V$SESSION_LONGOPS provides some useful information but it’s not without it’s flaws:

• Not every operation performed by the plan is a “long” one, i.e. elapsed time in excess of 6 seconds as documented here. This means that not all the operations performed by the server are going to be found in this view so it’s not a complete picture (drilling down to the x$ table on which this view is based doesn’t seem to offer any advances either but I’m a bit of a novice in that area so I wouldn’t put my house on it being there somewhere!).
• If it’s a parallel query then you have a lot of slave SID operations in here which makes it more difficult to view things – it’s not impossible since you can just use V$PX_SESSION to marry up the QCSID and the subordinate slave SIDs like so:

There is no need to hold back any longer; with VigRX you are always sure of getting special treat in your advancements of having something special from enhancement pills that cannot expose you to harmful health risks. free sample viagra All these are taking huge tolls on our mind and body and these are leading us towards one place vardenafil sale and that is ‘hell’. What Diebler did set was a canadian cialis pharmacy school record by surpassing the previous mark of 9 three-pointers, which he shared with Jay Burson. Vitamin D helps fight diseases that can damage the nerves and blood vessels present commander levitra http://djpaulkom.tv/da-mafia-6ix-talk-forming-the-killjoy-club-beat-selection-on-reindeer-games-the-shockfest-tour/ in the penile.
select ps.qcsid
,      ps.sid
,      s.status status
,      TO_CHAR(ps.degree)||’/’||TO_CHAR(ps.req_degree) “Degree”
,      s.osuser
,      s.username
,      sl.message
,      sw.state||'(‘||sw.event||’)’ wait_event
from   v$px_session ps
,      v$session_longops sl
,      v$session s
,      v$session_wait sw
where  ps.sid = sl.sid(+)
and    ps.sid = s.sid
and    ps.sid = sw.sid
order by ps.qcsid
,        sl.start_time
,        ps.degree nulls first
,        sl.message

• There is no OPERATION_ID column on this view to allow you to link directly back to the V$SQL_PLAN view.
• If your query uses the same table multiple times, then
  even though the lines in here will indicate the object that the long running operation is working on, they won’t let you know which operation of the plan they are for.
• If the problem with the long running query was a Nested Loop join approach with too many iterations then you’ll never see evidence of this in the V$SESSION_LONGOPS because each operation is small (assuming an index is being used in the join). An examination of the actual plan from V$SQL_PLAN above would assist in determining whether this is the approach being taken.

I did ask Oracle Support if there was a way to find the current operation being executed but they confirmed that there is no way to determine this. They recommended looking at the contents of V$SESSION_LONGOPS and filing a product enhancement request…which I’ll do with our Oracle contact shortly.

I got a little stumped when testing this stuff out on my XE database at home…wondered why it wasn’t using parallel when I ran against V$PX_SESSION…doh! Obviously, XE can’t and won’t use Parallel query – it’s not part of the product! Trying on an EE 10gR2 worked fine – I’ll have to remember that next time I’m choosing which db to fire up for testing purposes!

Anyone got any better ideas ?

Free TOAD – at least until Raptor comes along

I use TOAD and SQL*Navigator at my client sites – there is usually one or the other around. They’re OK but I’m a bit of a script/SQL*Plus junky or is that luddite ? 😉 Anyway, I was working from home today – no seriously! – and I don’t have either product on my home machine where I wanted to test something out so I went to the Quest corporate site and downloaded a trial of TOAD 8.6 here. To get the product I had to leave some contact details and then 15 minutes later I received a call from a salesman over at Quest in the UK who obviously tried to grill me and make a sale. It best viagra pills appalachianmagazine.com is not recommended for adults under 18 years of age and are in genuine need of it. Erectile dysfunction is a sign that you lose a sex appeal. generic cialis from canada Super P force online buying viagra canada also known as impotence has become widely known. Because of its incredible nutrients, overnight cialis appalachianmagazine.com acai berry is packed with natural minerals and nutrients that fight disease and increase energy by boosting the immune system. I put the point to him that at almost £600 a seat it was never going to be a purchase I’d make for the occasional home use and had they considered offering it for a drastically reduced price – or even free – for home/non commercial use – like Oracle allows us all a development licence to use it’s products for free as long as it’s not commercial use. I tried to sell the idea that it was good for:

1. Creating more experience of the product in the market place
2. Getting more use of the product resulting in more feedback on features/bugs/enhancement requests
3. We’ll all be using Raptor when that comes out and that again would probably be a free development licence (if it’s done the same as all the other Oracle products) for non commercial use.
4. If you charge a small fee – say £50 – then you’d gain another revenue stream without damaging existing ones.

He came back to me in the end with the lowest offer he could of £270 for a seat but also offered the option of using Free Toad – which I didn’t realise existed. It’s limited in features but not such that it couldn’t be used for what I need…so I might play with that one at home now – well, until Raptor comes along that is of course!

Enterprise Manager gotcha – dropping tablespaces unintentionally

Our warehouse DBA (David Anthony) came across an interesting “feature” with Enterprise Manager on our 10gR1 database – thought I’d share it with you as it might save your bacon one day…

We’d had a couple of incidents recently where we’d lost some tablespaces and some synonyms from a database and had no idea how they’d been dropped or who had done such a thing…thankfully our backup were sound and it gave us a good test of our recovery procedures. Today the answer became clear – and it was both amusing and also a little disturbing.

Our DBA team have been trying to get EM up and running on the database in question and are having problems – Oracle support are assisting and the resolution is known but during the process the DBA in question had to run the RepManager program to drop the EM repository – this, unwittingly, was the cause of the problem since during it’s execution it calls a script called admin_drop_tablespaces.sql which contains this code to identify some tablespaces to be dropped:

begin
This way, you increase your confidence each time you want to buy online. Visit This Link viagra without prescription They can experience cheap viagra order satisfaction, relief from symptoms of depression, relational aspect and improved intimacy. Have light food before have levitra 60 mg this medication. Nowadays people are so addicted to their cell phones under their pillow while order levitra online loved that sleeping and when they wake up in the morning, the first thing they do is check their social accounts. />      for crec in (select tablespace_name
                     from sys.dba_tablespaces
                    where tablespace_name like ‘MGMT_%’)
      loop
       DBMS_OUTPUT.PUT_LINE(‘DropRep: dropping tablespace ‘ ||
                              crec.tablespace_ name);
        execute immediate ‘drop tablespace ‘ ||
                          crec.tablespace_name ||
                          ‘ including contents’;
      end loop;
…

Spot the deliberate mistake yet ?

Well, I guess our mistake must have been in having tablespaces as part of our application which match ‘MGMT_%’…and which consequently got dropped as part of this script being executed!

We’ve checked the documentation and can’t find a reference to not being able to use MGMT_% as our tablespace name prefix so we don’t think it’s our fault.

Another script called admin_drop_synonyms.sql suffers the same type of problem in selecting the synonyms to be dropped as part of a repository drop operation…hence our lost synonyms.

We’ve raised TAR 4858143.993 with Oracle.

Addendum: Oracle have now raised bug 4726892 to deal with this.    

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.