It’s sad that I could not capture in pictures the amazing sights and sounds from both inside the conference and outside in the streets of these amazing places.
I was too busy just absorbing it myself and neglected to take enough pictures
But again, a truly wonderful couple of days.
Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care.
Let’s look at the following example
SQL> create table T ( 2 x varchar2(20) , y varchar2(100)); Table created. SQL> insert into T 2 select 'x' , rpad('z',100) from all_objects; 94117 rows created. SQL> insert into T 2 select 'X' , rpad('z',100) from all_objects; 94117 rows created. SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true) PL/SQL procedure successfully completed. SQL> create index TX on T ( upper(x) ) ; Index created.
So you can see that in reality, ALL of the rows have a single value for UPPER(X), namely “X”. So let’s look at an execution plan.
For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.
SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select column_name from user_ind_columns 2 where index_name = 'BLAH_IX' 3 / COLUMN_NAME ------------------------------ SYS_NC00002$
Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.
Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist
Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).
However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:
If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.
Oaktable World Las Vegas is happening at Collaborate 2016! Many thanks to Tim Gorman, Alex Gorbachev and Mark Farnham for organizing!
Free Oaktable World t-shirts available at Delphix booth 1613 on Tuesday and at the Oaktable World talks on Wednesday. Also available at the Delphix booth is free copies of Mike Swing’s “the little r12.2.5 upgrade essentials for managers and tema members”. Mike will be doing Q&A at the Delphix booth Tuesday 1:15-2:00 and book signing on Wednesday 2:00- 2:45.
Oaktable World all day Wednesday 9:15-6:15 Mandalay Bay Ballroom I
With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data