Friday, September 21, 2007

Before the iPod there was...

Mister Disc :)

What a concept, carry a bunch of 45s around (no, not the gun for those of you - well- too young to know what a 45 is). True, you could carry a bunch of 33's (and 1/3) but that doesn't sound as fun.

1983, I was graduating high school.  Compact Disc was just being released in the US.  I went to college with some really big speakers and a turntable.  No computer.  Rotary phone.

Very different back then...

Thursday, September 20, 2007

Sql_trace=true, Part Two...

I got a few questions on my last entry Tuning with sql_trace=true.  The two I'll address here are:

  1. Ok, I'm using 9ir2 not 10g so the sql_id bit doesn't exist and DBMS_XPLAN doesn't have the entry points you used - so how do we do this in 9i?
  2. Ok, so we identified the cause (sql_trace causing a new child cursor, bind variable peeking being the ultimate culprit) - what do we do then?

Using 9iR2

The following code snippet is what you would use in 9iR2 - this picks up from the previous example (following the create table, autotrace demonstration to show different plans, and the turning on and off of sql_trace).  We read v$sql_plan to populate the plan_table with our two child cursors - and then dbms_xplan can display that easily.  We query V$SQL_SHARED_CURSOR using an address instead of sql_id.  We also note that the column queried from v$sql_shared_cursor is different in 9i than in 10g:

ops$tkyte%ORA9IR2> delete from plan_table;
5 rows deleted.

ops$tkyte%ORA9IR2> insert into plan_table
2 ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION,
3 OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,
4 OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
5 POSITION, COST, CARDINALITY, BYTES, OTHER_TAG,
6 PARTITION_START, PARTITION_STOP, PARTITION_ID,
7 OTHER, DISTRIBUTION, CPU_COST,
8 IO_COST, TEMP_SPACE )
9 select 'QUERY_'||child_number,
10 sysdate, null, operation, options,
11 object_node, object_owner, object_name,
12 optimizer, search_columns, id, parent_id,
13 position, cost, cardinality, bytes, other_tag,
14 partition_start, partition_stop, partition_id,
15 other, distribution, cpu_cost, io_cost,
16 temp_space
17 from v$sql_plan
18 where (address,child_number) in
19 ( select address, child_number
20 from v$sql
21 where sql_text = 'select * from t where id = :id' );
5 rows created.

ops$tkyte%ORA9IR2> select * from
table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_0' ) );

PLAN_TABLE_OUTPUT
--------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 30353 | 2964K| 43 |
--------------------------------------------------------------------
Note: cpu costing is off
9 rows selected.

ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_1' ) );

PLAN_TABLE_OUTPUT
--------------------------

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 100 | 2 |
| 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 |
---------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.

ops$tkyte%ORA9IR2> select address, OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar = ( select address
4 from v$sql
5 where sql_text =
6 'select * from t where id = :id'
7 and rownum = 1 );

ADDRESS O
-------- -
5BA2A6A4 N
5BA25B18 Y



So, that is the answer to number 1...


So, what the heck do we do about it?


Well, fortunately (for me) I address this in the "All about binds" talk as well.  There are a number of things you can do - this is the slide I talk to during the seminar (click to read):



binds


 


 Most of the time, almost all of the time in fact - bind variable peeking works as the designers of the feature intended - in a positive, helpful manner.  The query in the application uses bind values that drive it to one plan or the other consistently.  It is only when the plan flip flops between two radically different execution paths and for some segment of users - there exists a really bad plan.


So, when it doesn't work 'nicely', what can you do to get around it?  I'll go through the ideas one by one...


Don't bind that query, that is a possibility


Yes, you read that correctly, don't use a bind variable there (and I wrote that - do not use a bind variable :) ).  Look at my example above - ID has two values - 1 and 99.  Let's say ID wasn't ID but rather was "processed_flag" and had two values - Y and N.  Most of the records are processed_flag=Y, some of them are N.  Some people query the processed records (Y=yes).  They want a full scan.  Others query the unprocessed records in order to process them (N=no).  They would like an index range scan.


Here, my suggestion would be do not bind against processed_flag, use literals.  That way we have two queries, two plans.


This is also why I hate cursor_sharing=force, because when a programmer does this (uses literals) and the DBA turns on cursor sharing = force, we are back to the original problem!!! Bind variable peeking comes into play once again - there is one plan for all and it depends on who runs the query first. (cursor_sharing similar would not have this side effect by the way, it would choose to not bind against processed_flag based on the statistics).


Don't gather statistics that cause the plan to flip flop


If there is only one plan that the optimizer would ever choose - then bind variable peeking will have no effect.  Consider the following, notice that I gathered NO HISTOGRAMS in this case - so the plan would never flip flop.  We still have the child cursors (sql_trace will do that) but the plan is consistent:


ops$tkyte%ORA9IR2> create table t
2 as
3 select case when rownum = 1 then 1 else 99 end id, a.*
4 from all_objects a
5 /
Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(id);
Index created.

ops$tkyte%ORA9IR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all columns size repeat' );
5 end;
6 /
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t where id = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=15177 Bytes=1456992)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=15177 Bytes=1456992)

ops$tkyte%ORA9IR2> select * from t where id = 99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=15177 Bytes=1456992)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=15177 Bytes=1456992)

 


Notice the plan however!  One plan for ID=1, one plan for ID=99.  The optimizer doesn't know anything about the values in the column this time - guesses "about half of the data" (we have 30,000 rows in T in this 9i example).  It'll always full scan.  This is why we have histograms (column statistics) and further, why we do bind peeking - without histograms, without bind peeking - the only plan for this query would be FULL SCAN.  

ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> variable id number
ops$tkyte%ORA9IR2> set autotrace traceonly statistics
ops$tkyte%ORA9IR2> exec :id := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> select * from t where id = :id;
30353 rows selected.
... 2422 consistent gets ....

ops$tkyte%ORA9IR2> exec :id := 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

ops$tkyte%ORA9IR2> alter session set sql_trace=true;
Session altered.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

ops$tkyte%ORA9IR2> alter session set sql_trace=false;
Session altered.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

...
ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_0' ) );
PLAN_TABLE_OUTPUT
-----------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 15177 | 1422K| 43 |
--------------------------------------------------------------------

ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_1' ) );
PLAN_TABLE_OUTPUT
-------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 15177 | 1422K| 43 |
--------------------------------------------------------------------

ops$tkyte%ORA9IR2> select address, OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar = ( select address
4 from v$sql
5 where sql_text =
6 'select * from t where id = :id'
7 and rownum = 1 );

ADDRESS O
-------- -
5BA2A6A4 N
5BA25B18 Y


Use your domain knowledge of the data


If you know that when a certain query is executed with a date within the last 30 days - the volume of data is small and "indexes would be used" and that if the date is way in the past - the volume of data is large and "full scans are us" is true - then just use an if/then/else


if ( p_date > sysdate-30 )
then
open l_cursor for
select * from t less_than_30 where ......
else
open l_cursor for
select * from t more_than_30 where ...
end if;
loop
fetch l-cursor into ....;
.....


This works when you have a good knowledge of your data.  If the code is in ('a','b','c' ) then open this cursor, else open that cursor - and so on.


You can use cursor_sharing=similar


And let Oracle decide what to bind and what not to bind.  Consider the following (we'll use the above create table T, the index T_IDX and the original gather stats with method_opt=> for all indexed columns size 254.


ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_object_id where id = 1 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_object_id where id = 1 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 1 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 2 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 3 and object_id = 100;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 3
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"


 


Notice here how there are three copies of the sql in v$sql when we vary the ID - Oracle decided it was not safe to bind against ID - it looks like it used a bind, but it really didn't - the three copies are one each for ID=1, 2 and 3. When we vary the object_id, Oracle has no statistics that would cause it to consider alternate plans, so it was safe to bind that value - and it did, resulting in a single child cursor.


 

ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_id where id = 1 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 2 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 3 and object_id = 101;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 3
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"

 


Notice here how there are three copies of the sql in v$sql still! That is because we already had the plans for ID=1,2,3 - regardless of what the object_id was, however if we change ID again:

ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_id where id = 4 and object_id = 101;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 4
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"


You see a fourth copy pop into v$sql - for ID=4.


Hence, for every unique value of ID we use - there will be a new child cursor.


Note that with cursor_sharing=force, there would be one plan - but remember bind variable peeking - there would be on plan that could flip flop over time.


You could use stored outlines


Also known as query plan stability.  In a test environment, you would exercise the application using "appropriate" or "representative" inputs.  Additionally you would be capturing stored outlines into the outline tables.  You would then move these into production and have the application issue the alter session to use these stored outlines.


You in effect freeze the plans (you may stop gathering statistics now as well, you just froze the plans...)


You can disable the bind peeking feature


Using an undocumented init.ora parameter - but, since this really only affects you if you have statistics that cause plans to flip flop - I would say stop gathering those statistics, save the time and resources that takes and you'll have achieved basically the same goal.

Wednesday, September 19, 2007

Tuning with sql_trace=true...

My all time favorite seminar topic is my talk on "All about Binds".  It takes about 3 hours to do the entire talk from start to finish and covers performance, memory utilization, scalability, security, bind mismatches, bind variable peeking and cursor sharing.

Today, I got a question on asktom that I answer - no, I actually demonstrate the answer to - in that presentation.  During the presentation - I say

"I'm going to show you how to tune by setting sql_trace=true.  You are probably all thinking that I'm going to run a query, observe it running slow, trace it and then tune it.  Well, I'm not going to do that - all I'm going to do is set sql_trace=true and we'll observe the query performance and resource utilization to be dramatically affected - all for the better!"

The question on asktom is identical to what I demonstrate - they wrote:

"I have a query that runs slow (2 minutes) So what do I do? Turn on tracing (both sql_trace=true and event 10046) before running the query. But consistently, 100% of the time, without changing anything else, when I enable tracing, the query comes back instantly!!"

I know exactly what is happening...  It is a combination of two things

  • The fact that when you set sql_trace=true, you set up a new 'parse' environment (made up term).  You have changed your session in such a way that you will not share any existing SQL that was not parsed with sql_trace enabled.  So, it is highly likely that you will either hard parse a new version of the query - or use some existing child cursor that is different from the one you would use with sql_trace disabled.
  • Bind variable peeking happens at hard parse time and may affect the plan chosen by the optimizer.

So, first for the setup script to demonstrate this phenomena - I'll set up a very skewed set of data for emphasis:

ops$tkyte%ORA10GR2> create table t
2 as
3 select case when rownum = 1 then 1 else 99 end id, a.*
4 from all_objects a
5 /
Table created.

ops$tkyte%ORA10GR2> alter table t modify object_id null;
Table altered.

ops$tkyte%ORA10GR2> create index t_idx on t(id);
Index created.

ops$tkyte%ORA10GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all indexed columns size 254' );
5 end;
6 /
PL/SQL procedure successfully completed.


So, it is quite clear that WHERE ID=1 will return one record and WHERE ID=99 will return all of the rest (about 50,000 records). Also, the optimizer is very aware of this fact due to the histograms in place and if we parse using literals - we can definitely see different plans for different inputs:


ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("ID"=1)

ops$tkyte%ORA10GR2> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50244 | 4710K| 235 (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 50244 | 4710K| 235 (4)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=99)

ops$tkyte%ORA10GR2> set autotrace off


But, what happens when we issue "select * from t where id = :id" - when we bind the ID input?  Well, on the first hard parse - Oracle will wait till the cursor is opened to do the actual optimization of the query - it will wait for the bind variable value to be supplied by the application before figuring out the right way to optimize the query.  That is called "bind variable peeking", when the optimizer looks at the bind values and then optimizes the query.  In this case however, depending on what inputs are used to first run the query - we'll either get a full scan or an index range scan plus table access by index rowid - and that is the plan that will be used to execute the query "select * from t where id = :id" regardless of the bind values from now on.


What is happening to the poster on asktom is that



  1. Someone hard parsed the query in question and when they did so, the inputs they used resulted in "plan A"
  2. Plan A was the best plan for that person given their inputs - it was the most efficient plan
  3. Later, someone else executed the same query, using shared sql they reused the plan generated by the other person - but their bind variable inputs where different and the plan that was generated for #1 was not the best plan for these inputs, in fact the plan was miserable for these inputs
  4. They turned on sql_trace and executed the query with inputs from #3 again - but because sql-trace was on - they did not share the sql statement - they hard parsed - with their inputs - and this resulted in a very different plan

We can observe this easily, consider:


ops$tkyte%ORA10GR2> variable id number
ops$tkyte%ORA10GR2> set autotrace traceonly statistics
ops$tkyte%ORA10GR2> exec :id := 99
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t where id = :id;
50254 rows selected.

Statistics
----------------------------------------------------------
....
4031 consistent gets
....
50254 rows processed

 


So, we started off with id=99 as the bind, and a full scan was chosen (you can prove that via TKPROF by looking at the row source operation if you wish; I did...).  That means regardless of the bind value from now on - we will full scan - for example:


ops$tkyte%ORA10GR2> exec :id := 1
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t where id = :id;

Statistics
----------------------------------------------------------
...
720 consistent gets
...
1 rows processed
It is unlikely that was the index range scan/table access by index rowid - we'd expect a lot less logical IO, 3 or 4 IO's against an index and 1 against the table - we did a full scan for id=1.  That represents our "poorly performing query".  We turn on SQL_TRACE to find the performance characteristics of that and observe:
ops$tkyte%ORA10GR2> alter session set sql_trace=true;
Session altered.

ops$tkyte%ORA10GR2> select * from t where id = :id;

Statistics
----------------------------------------------------------
...
4 consistent gets
...
1 rows processed

 


That apparently just setting SQL_TRACE=TRUE is our tuning mechanism!  It isn't really - it is bind peeking and hard parsing that is causing this, but unless you know that a) sql_trace sets up a child cursor and b) bind peeking happens - this looks very mysterious, especially if you turn it off:

ops$tkyte%ORA10GR2> alter session set sql_trace=false;
Session altered.

ops$tkyte%ORA10GR2> select * from t where id = :id;

Statistics
----------------------------------------------------------
...
720 consistent gets
...
1 rows processed

 


See it run slow and turn it on again:

ops$tkyte%ORA10GR2> alter session set sql_trace=true;
Session altered.

ops$tkyte%ORA10GR2> select * from t where id = :id;

Statistics
----------------------------------------------------------
...
4 consistent gets
...
1 rows processed


And see it go fast...


It is not magic, it is bind peeking.  To see the real plans - you can query v$sql_plan / v$sql_plan_statistics or use DBMS_XPLAN to dump the plans for each child cursor:


ops$tkyte%ORA10GR2> select sql_id 
from v$sql
where sql_text = 'select * from t where id = :id';

SQL_ID
-------------
8s40hfjcbmxzk
8s40hfjcbmxzk

ops$tkyte%ORA10GR2> select *
from table( dbms_xplan.display_cursor( '8s40hfjcbmxzk', 0 ) );

PLAN_TABLE_OUTPUT
---------------------
SQL_ID 8s40hfjcbmxzk, child number 0
-------------------------------------
select * from t where id = :id

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 235 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50250 | 4710K| 235 (4)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=:ID)


18 rows selected.

ops$tkyte%ORA10GR2> select *
from table( dbms_xplan.display_cursor( '8s40hfjcbmxzk', 1 ) );

PLAN_TABLE_OUTPUT
---------------------------
SQL_ID 8s40hfjcbmxzk, child number 1
-------------------------------------
select * from t where id = :id

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("ID"=:ID)


19 rows selected.



You can use v$sql_shared_cursor to see why you have multiple child cursors as well - for example:


ops$tkyte%ORA10GR2> select child_number, stats_row_mismatch
2 from v$sql_shared_cursor
3 where sql_id = '8s40hfjcbmxzk'
4 /

CHILD_NUMBER S
------------ -
0 N
1 Y


That is what you would look for to verify you were having a "must be sql_trace creating a new child cursor and a hard parse with different binds" problem.

Tuesday, September 18, 2007

Pop, Sizzle - what is that awful smell...

Man, am I glad I work at home now.  If I didn't, I might not have one anymore!  I was at my desk this morning - prepping for a web seminar I'm doing in about 15 minutes when I heard this loud POP and sizzle noise.  Then, the smell wafted in - the smell of electricity and burning.  I'm walking around trying to figure out what it is - when I spy a battery pack my son left plugged in to charge while he is at school, well, what was left of it anyway:

 

RIMG0002

 That green bubbly looking bit - that is the battery pack - the words "fly model set" - used to be "straight and all the same size".  It was smoking, it was hot, it was sizzling.  I unplugged it and threw it outside on the concrete porch to cool off.

After it cooled off - I read the back:

RIMG0003

It reads

Refreshing time pleases according to the enactment of the manual refreshing, the person pleases do not leave.  When refresh to complete, asking real time to pull out the power supply, avoiding refreshing time over long, damage battery.

Damage battery indeed - damage battery, burn down house is more like it.  It was only plugged in for 30 minutes!

I guess a new rule of life is "if the back of the plug is 100% gibberish - DO NOT PLUG IT IN".

When I'm leaving my house empty for a while, I gotten into the habit of unplugging stuff - I'm definitely going to be more conscious of this in the future - I leave for days at a time, I'll be unplugging lots of stuff now.

I sort of wish US plugs were like those in the UK - with the little on/off switches on them, I'll have to search around and see if they exist - I'd replace all of my plugs with those.

Monday, September 17, 2007

Search Terms Redux...

About a year ago, I wrote about some search terms that were used to find content on my blog.  Many of the terms from a year ago are still there - "areas of improvement" being a huge one (hmmm, now I know what people use google for - self improvement).

I revisited the search logs to see what sort of google/yahoo searches (ok, google searches, 97+ % come from there, the rest come from yahoo) were happening a year later.  Some are amusing:

Anyway, it is really sort of fascinating to see what people search for - and what it finds - and how far up the page rank your own material might be.  Some are surprising (1st page for porn.com), others are not.  But the terms are always interesting.

Saturday, September 15, 2007

Stumbling about...

Stumble upon, I've written about it before - and this page sums it up rather nicely.

"StumbleUpon has the unique ability to instantly give its users horrible, horrible insomnia. You will, undoubtedly, spend an entire night stumbling at some point in the near future, if you haven’t already."

It is true, but you find lots of gold nuggets in the meantime. (Stumble upon is perfect for those long conference calls :) )

How to teach...

I found this article to be a classic bit of humor.  Now, if I could only find a way to apply that technique to teaching about bind variables!  I need to find a way to make "not using bind variables" as distasteful (all pun intended) as the maintenance person in that article did with his problem...

Really bad advertising...

I remember DOS 5.  I am glad I never had a PC back then capable of running video.  Because, if I had - I might have seen this video.  And, had I seen that video way back when, I might have changed careers :)

This looks very cool...

I might buy one of these - just because of the coolness factor.  Imagine, an infinitely renewable power source where ever you go.  In an airport, finding an outlet can be very challenging - something like that might come in very handy in a pinch.

Toot Toot...

Ok, I did not find this link via stumble upon...  AskTom - the written column - won an award (again :)).  Second year running.

And in closing...

Some good presentation tips here.  And probably the only set of tips to include a section on "Grade A Speaking With a Hangover"! 

Ahh, Travel...

I've updated the page of upcoming events - adding a couple of links and another event (Nashville TN in October).  When I get a link with details for the Nashville event, I'll update the page again.  Lots of travel coming up in the next couple of months. 

Travel can be fun, but it is also tiring and can push the limits of patience sometimes :)  This last week was a case in point.  I started out on the road Monday evening - heading down to Dallas.  There were some serious storms in the Dallas area on Monday - enough that American Airlines was canceling flight after flight.  That was OK because I was traveling on USAIR - until USAIR said they would be delaying my flight by many hours.  Resigned to being stuck at the airport for a while, I sat down and started to do some work - fortunately I did not leave the gate area because a few minutes later - they un-delayed my flight.  That is something that never happens -- it was delayed by 2 hours and we left about 15 minutes after the original delay announcement.  I was glad I didn't leave the area - I would have easily missed the flight otherwise.  Dallas and Phoenix were pretty uneventful travel wise - did a full day seminar in both locations. 

Wednesday night was a heavy travel night though - after finishing in Phoenix at 4:30, I had to fly to LAX and from there take a red-eye to Philadelphia, where I was speaking the next morning at 9am.  We landed early in Philadelphia (good news), but a plane was at our gate (bad news), so we arrived on time after sitting on the tarmac for a long time.  Going to the car rental - the driver dropped me off and said my name would be on the board, but of course it wasn't - and at 6am - there was no one around. 

Finally I get a car, drive to the hotel and ask for my room (which was paid for the night before by someone staying at the hotel).  The clerk says "we were oversold last night - we have no rooms" - which doesn't sit very well with me (since we actually paid for the room, with a credit card).  How can there not be a room when we actually checked in and paid for it?  Anyway - eventually they conceded that there might be a mixup and got me a room - on the 15th floor (the top of the building).  I got a shower, changed, did my talk.  After the talk - as some of us are standing around doing some questions and answers - every light in the building goes off, total darkness (not a good thing since we had just started the day and had 3 more hours to go, with about 200 people).  The lights came back on - but went off again shortly thereafter.  A transformer blew out nearby - no power.  And now it was near 11am, checkout time - the time when my key would cease functioning, and my luggage, computer and such were in the room - on the 15th floor, without any elevators.  I found a security guard to walk up with me - so they could let me in - and that was when I realized that my room actually has no windows (it was a 'theme room', all of the rooms on the 15th floor where - my rooms theme was "Pirates", the strangest hotel room I've had to date).  No power, no lights, no windows - but they did give me one of those green glowing sticks.  Made it hard to pack :)

Ahh, it is stuff like that that makes traveling interesting...  You just have to roll with it though, if you got overly upset at all of this stuff - you would spend all of your time traveling upset.  I've written about what I call "travel mode" before  - a necessary state of mind for anyone that travels :)

Wednesday, September 12, 2007

Podcast #2 and Analogies...

First - the part 2 of my 2 part 11g podcast has been released... 

Second, I hit upon this blog entry that I found interesting.  I definitely can relate to bits and pieces of it - I don't agree it will "ruin" your life (programming), just changes it.  I really liked this paragraph (emphasis mine):

A program is highly malleable. You can make a nearly unlimited number of changes. You can re-implement. You can optimize. You can run the compile-test-debug cycle ad infinitum. Make a change, see a result. Life is not like this. Every action you take is followed by a commit and the transaction cannot be rolled back. You can continue to make changes and optimizations as you move forward but the effects of these will not be immediately apparent. The instant feedback of development is sorely lacking in real life. Furthermore, your changes might simply be ignored. Data will be skipped. Blocks will not be executed. Optimizations will go unnoticed. The world is resistant to your tinkering.

Hah - if only life had "rollback" - wouldn't that be cool....

I've used a similar analogy in some of my talks - for example:

Flashback database - how to best describe it.  Ok, say you were at your companies Christmas party and had a bit too much to drink, you are talking with your manager and blurt out the most absurd thing you could think of saying....  Flashback database would be the amazing ability to "un-say what you just said, to take it all back".

In short, flashback database in real life would be the ability to rollback your actions :)

Ahh, Oracle as a virtual reality world :)

Friday, September 07, 2007

A little fun... Part II

Many of the thoughts as to why my response "didn't help" were very close.  The reason my response didn't help.....

 

 

 

 

they did not ask how to do what they needed to do, they asked a different question instead.

 

 

Here was the full response they gave me

 

 

Hello Thomas,

Thanks for answering me.

This doesn’t help me, since the indexes are not sequential. (they could be : 123298, 123400,122000), and I needed some more conditions.

So I used that one instead:

select * 
from (select ...,
(lead(id,1) over (order by id)) as nextTB,
id as TB,
(lag(id,1) over (order by id)) as preTB
from JPTALKBACK
where ARTICLEREF in (select articleref
from JPTALKBACK
where id=?)
and status ='AP'
order by id asc)
where id=:id
/

 


Now, the comment about the not sequential is not relevant - the approach I took did not rely on them being sequential. I would find the max(id) less than the one of interest and get three records starting there.


But, the bit about lots of predicates and such - totally missing.


This happens a lot - questions that are so ill specified or just not even close to what they wanted to ask - it wastes both of our time (my time, their time).


One commenter hit it on the head:


http://tkyte.blogspot.com/2007/09/little-fun-with-you-all.html#c4289852638992909470


Writing specifications, asking what you mean, being concise yet complete - it is so important, yet seems to be the thing most lacking in many respects.  I think maybe this "agile" programming technique - the one where specifications and such are purposely "not firmed up" leads to this. 

A little fun with you all...

Ok, this will be a two parter - I'll introduce you to the frustration that is "answer my question".

Part 1 - the question and my response and their initial response.

So, first the question - written in to me as a letter to the editor regarding a column I wrote on using rownum:

Hi,

I have a question about rownum which is not mentioned.

Lets say that I want to extract a 3 records out of a result where the middle one is the query. For example I have records:

ID Value
1 Hello
2 Hi
3 Wow
4 Shrek
5 Shus
6 What?

And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.

Is there a way to do it in Oracle?

Seems straightforward, concise, clear - and a question I've received numerous times before.  Looks like someone trying to emulate an ISAM library - porting some code to Oracle.  So - I respond:

This presumes that ID is "unique"

select * 
from
( select *
from t
where id >= (select nvl(max(id),0)
from t
where id < :id)
order by id)
where rownum <= 3;

 


For example, if you copy all_Objects into a table T and add a unique/primary key constraint on object_id:

select * 
from ( select *
from t where object_id >=
(select nvl(max(object_id),0)
from t
where object_id < :id)
order by object_id)
where rownum <= 3
order by object_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.00 0 5 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 3

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
3 COUNT STOPKEY (cr=7 pr=0 pw=0 time=333 us)
3 VIEW (cr=7 pr=0 pw=0 time=307 us)
3 TABLE ACCESS BY INDEX ROWID T (cr=7 pr=0 pw=0 time=270 us)
3 INDEX RANGE SCAN T_PK (cr=5 pr=0 pw=0 time=282 us)(object id 55414)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=154 us)
1 FIRST ROW (cr=2 pr=0 pw=0 time=104 us)
1 INDEX RANGE SCAN (MIN/MAX) T_PK (cr=2 pr=0 pw=0 time=85 us)(object id 55414)


Basically, we start by finding the row in 'front' of the one we need in the middle - the select max(id) does that bit for us and then starting with that record - retrieve in sorted order that record and the next two (rownum <= 3). 


A logical extension of the article - pretty efficient approach and answers the question.  (note; I added nvl(max(id),0) - the NVL bit after the fact this morning.... so that it 'works' for the first record as well - presuming that ID is a whole number)


OR DOES IT?


Apparently, it does not - the response was:



Hello Thomas,

Thanks for answering me.

This doesn’t help me, ....


Anyone want to guess why this didn't help them? It can be summarized in a single sentence - which I'll publish later. 

Tuesday, September 04, 2007

Podcast...

Listen to a just released podcast I recorded last month.  It is part 1 of 2 (it was supposed to be a one parter - but I tend to ramble on and on and on...)

 

I'll post when part 2 is released..

I'm back...

After a brief hiatus, I'm back.

I took off some time in the month of August - worked here and there, even flew out to California for a few days to speak at the Southern California Oracle User Group with Ken Jacobs and recorded a podcast.  But all in all, mostly just spent time with my kids (drove around a lot, even got to see the B52's with Megan in a park at Coney Island) and getting ready for September (back to school and all).

Now, it is back to work for me as well :)

I start a new position within Oracle today.  I no longer work in sales - after 14 years there.  I now work for the Database Server Technologies division (the people that actually make the database software).  It won't make a huge change in what I do at Oracle - just whom I do it for.  Truth be told, a lot of what I did in the past wasn't officially part of my job, more like a hobby.  So the seminars, talking at user groups, writing the column and answering questions on AskTom, blogging - that was all "on the side".  Now it is officially what I do - and I can do more of it.

So, I'm back after a brief respite from writing and such - I have a busy lineup coming in the near future:

 

Time to brush off the frequent flyer card and book some flights ;)