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
As always happens from time to time, we had the following request on AskTom today:
Could you list down 2 reasons why sql server is better than oracle?
Then 2 counter reasons as to why why oracle is better than sql server
And I thought I’d reproduce my response here
I’ll answer it slightly differently…
Q: When is SQL Server better then Oracle ?
A: When you have good, intelligent, knowledgeable SQL Server people on your workforce.
Q: When is Oracle better then SQL Server ?
A: When you have good, intelligent, knowledgeable Oracle people on your workforce.
When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run.
AskTom has been answering questions from the user community for nearly 16 years.
But what if that service could have an even more personal touch ? We are pleased announce the largest advance in AskTom service since it started way back in 2000.
See all the details in the video below for the new service we are launching on April 1st.