999 ?
Not even close.
How about 9 billion ?
select to_number('9e9') from dual;
TO_NUMBER('9E9')
--------------------
9,000,000,000
Friday, May 28, 2010
A quick quiz for a Friday.
What is the largest number you can get from a three character string ?
999 ?
Not even close.
How about 9 billion ?
Any advance on that ?
999 ?
Not even close.
How about 9 billion ?
Ambiguity resolved.
Another case where the order of the tables within the FROM clause has an impact.
Two tables, with the same column names (and data types).
> desc t2
Name Null? Type
--------- -------- ---------------
1 ID NUMBER
2 VAL VARCHAR2(10)
> desc t1
Name Null? Type
--------- -------- ---------------
1 ID NUMBER
2 VAL VARCHAR2(10)
Put together a SELECT * query. Now, which table has the value 'Blue' and which is 'Red'.
> select * from (select * from t2 join t1 on t1.id = t2.id);
QCSJ_C000000000400000 QCSJ_C0000 QCSJ_C000000000400001 QCSJ_C0000
--------------------- ---------- --------------------- ----------
1.00 Blue 1.00 Red
It appears to be the order of the tables in the where clause (so the first two columns are from T2 and the latter from T1). Sensibly, that happens irrespective of the path the optimizer takes, so a change in plans won't suddenly change the results.
Yes, it is another reason NOT to use a SELECT *.
That said, do you notice the automatic column aliasing ?
SQL*Plus has truncated some of the column headers, and the actual column names (and datatypes) are :
It only happens with the ANSI join, with the old-style join throwing an error.
Two tables, with the same column names (and data types).
> desc t2
Name Null? Type
--------- -------- ---------------
1 ID NUMBER
2 VAL VARCHAR2(10)
> desc t1
Name Null? Type
--------- -------- ---------------
1 ID NUMBER
2 VAL VARCHAR2(10)
Put together a SELECT * query. Now, which table has the value 'Blue' and which is 'Red'.
> select * from (select * from t2 join t1 on t1.id = t2.id);
QCSJ_C000000000400000 QCSJ_C0000 QCSJ_C000000000400001 QCSJ_C0000
--------------------- ---------- --------------------- ----------
1.00 Blue 1.00 Red
It appears to be the order of the tables in the where clause (so the first two columns are from T2 and the latter from T1). Sensibly, that happens irrespective of the path the optimizer takes, so a change in plans won't suddenly change the results.
Yes, it is another reason NOT to use a SELECT *.
That said, do you notice the automatic column aliasing ?
SQL*Plus has truncated some of the column headers, and the actual column names (and datatypes) are :
QCSJ_C000000000400000 NUMBER
QCSJ_C000000000400002 VARCHAR2(10)
QCSJ_C000000000400001 NUMBER
QCSJ_C000000000400003 VARCHAR2(10)
QCSJ_C000000000400002 VARCHAR2(10)
QCSJ_C000000000400001 NUMBER
QCSJ_C000000000400003 VARCHAR2(10)
It only happens with the ANSI join, with the old-style join throwing an error.
select * from (select * from t2 , t1 where t1.id = t2.id)
*
ERROR at line 1:
ORA-00918: column ambiguously defined
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Tuesday, May 25, 2010
Word for today is FAILSAFE.
FAILURE IS NOT AN OPTION…IT'S INCLUDED AS STANDARD
Failsafe does NOT mean that the item cannot fail. What it does mean is that, in the event of a failure, it won't go BOOM. As far as databases go, it means that, when a statement fails, any work done by that statement is undone. When a transaction fails, any work done by that transaction is undone. When a recovery is done, it leaves the database in a consistent state (ie no transactions partly done).
Oracle rates pretty highly on the failsafe scale. When a SQL statement, or a top-level anonymous PL/SQL block, fails it will rollback all the TRANSACTIONAL data changes made. It won't reset non-transactional items, such as PL/SQL package level variables or any stuff done outside the database (OS files, web service calls, FTP transfers etc). It doesn't work miracles.
Failures are a fact of life. The "not an option" declaration can only be valid at a system level, where multiple components are present to cover for the failure of individual components. The only way to mitigate against failure is to anticipate and understand that anything and everything can fail, and to have put in place steps to remediate the situation.
The power can fail and the server crash, so we have the redo logs. The media can fail, so we can backup the data files and multiplex the redo logs. Servers fail, so we have RAC and DataGuard.
Your software will fail. The question you've got to ask yourself is, will it fail safe ?
And if you're not sure, well punk, do you feel lucky.
Thursday, May 20, 2010
Sydney Oracle Developer Day Review
Last week, I attended the Developer Day in Sydney.
One of the presentations was about Apex, and I was hoping for more information on Apex 4.0. I was disappointed as there wasn't a lot of detail, and nothing new to me. While I've had a Workspace in the EAs, I haven't really had a chance to use it. I'll have to wait for an installable version, so I can play with it on the train home.
However one of the other talks was about the TimesTen / In-Memory Database Cache. While I'd heard of it, it hasn't had the same level of spruiking as Exadata (or Iron Man, come to that ). Since it had previously escaped my attention, it was the presentation from which I gained the most.
Firstly, it is an Enterprise Edition feature. That's sort of okay, since it is aimed at very high throughput environments. I know not all of those are awash with money, but I don't expect Oracle to give everything away for free and it is reasonable to equate demanding requirements with a preparedness to pay for them.
Secondly, it gives Oracle scale-out capability. For most Oracle techies, scaling means either a bigger database box (scaling up) or RAC. All those mid-tier happy developers just want the ability to throw boxes at a problem, and RAC isn't that simple. TimesTen would fit their mindset better. Think of it as half proxy/half cache. Most importantly, the developers treat a TimesTen node as the database, and Oracle will keep all the tricky stuff hidden from them.
In some ways it should be simpler than RAC, as the "hardcopy" database (the one with real disks) would treat the cache as a client rather than as an integrated component. That means that it would be easier to throw up TimesTen caches as and when required (very cloud-like).
Multiple TimesTen caches can duplicate data or shard it (eg one for images, one for audio). A duplicate would be good for reducing latency issues by having a cache co-located on an app-server. That is "on" not just "near". Apparently the removal of the network is part of the performance boost.
Sharding could be effective in 'boosting' memory. If you've got a machine maxxed out on 32 GB, then having 20 Gb of 'Northern' data in one shard and 20 Gb of 'Southern' data in another means the hardcopy database can offload the frequently queried read-only stuff to the caches.
It isn't just read-only though. There's capability for the data to be written, either directly to the hardcopy database, or with 'batching' done by the cache for improved performance.
Of course, I was just sitting through the sales pitch. I've had a flick through the manuals too. SQL and PL/SQL both work in the cache layer, though with some bits missing. Still all the general stuff that you use for applications is there. I'm going to have to dig around and see what real-life experiences have had to say about it. And see if I can throw up an instance (though I suspect VirtualBox on a net book might be somewhat challenging).
In some ways there is an overlap with RAC. There would be a set of problems where either additional RAC nodes or TimesTen caches would be a valid solution. There's also definitely issues where the cache would benefit where RAC would not (eg co-location with an app server).
[PS. The whole TimesTen / In Memory Database Cache terminology is a bit clunky. Oracle on speed or CrackOracle might have a bit more life in it. ]
There was a further talk on the GoldenGate product also acquired by Oracle a while back. More on that later….
Wednesday, May 19, 2010
It's probably quantum
Today I'm talking uncertainty. Not the "I don't know" uncertainty, but the "If I look at it then I get a different answer than if I don't look at it" type. And that becomes important when trying to put together automated tests which work by looking at things.
Here's a simple table and a procedure to insert into it.
create table test_1 (val number(2,0));
create or replace procedure proc_ins (p_val in number) is
begin
insert into test_1 (val) values (p_val);
insert into test_1 (val) values (p_val * 100);
end proc_ins;
/
What is the state of test_1 after I execute proc_ins with a value of 5 ?
(Assuming no other session activity, hidden triggers, magic doves)
The answer is, it depends.
From SQL*Plus
call proc_ins(5);
select * from test_1;
Empty.
The first insert into test_1 (which would have succeeded) was rolled back. This is called a statement level rollback. The documentation states that "An anonymous block is a SQL statement." and that an SQL statement that causes an error during execution will execute a statement level rollback and "The effect of the rollback is as if the statement had never been run."
An automated test
So what happens if I want to create an automated test to confirm the results.
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
proc_ins(1);
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
Because the statement (the anonymous block) now includes the exception handler, the statement itself doesn't fail and the first insert is still there. By testing for the exception, I seem to change the behaviour. I say "seem" because the statement rollback is not explicitly part of the code I am testing, but is an artifact of the way the statement is called.
If proc_ins were to be part of a larger PL/SQL program, the tested behaviour would represent the actual run time behavior. But if it is to be called as a top level procedure, for example directly from a client application, the tested behaviour would be inaccurate.
Changing the results
I can change the unit test so that it represents a 'independent statement' rather than 'embedded call' using dynamic SQL. A dynamic SQL call will set a new level of SQL scope.
truncate table test_1;
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
execute immediate 'call proc_ins(2)';
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
Within the scope of the SQL statement (the CALL) in which the exception occurs, there is no exception handler. The SQL statement is rolled back, as if it never happened. When we re-enter the outer scope, the exception is handled, but the statement rollback has already happened.
It doesn't make a difference whether we use the CALL statement (which is SQL), or a BEGIN...END; PL/SQL construction.
truncate table test_1;
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
execute immediate 'begin proc_ins(3); end;';
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
I will own up and say this revisits a post a few months back but I felt the automated test aspect of it needed an emphahsis. Also I was prompted by the PL/SQL Challenge of May 13th.
The answer states that "Exceptions do not roll back DML statements unless the exception goes unhandled to the host environment, in which case most such environments (like SQL*Plus) will automatically perform a rollback in your session."
I didn't like the statetment about host environments as it is the database engine which performs the statement-level rollback whenever an SQL call raises an exception. The host can do whatever it likes (though it is generally safer to rollback when you get an exception you don't expect).
Here's a simple table and a procedure to insert into it.
create table test_1 (val number(2,0));
create or replace procedure proc_ins (p_val in number) is
begin
insert into test_1 (val) values (p_val);
insert into test_1 (val) values (p_val * 100);
end proc_ins;
/
What is the state of test_1 after I execute proc_ins with a value of 5 ?
(Assuming no other session activity, hidden triggers, magic doves)
The answer is, it depends.
From SQL*Plus
call proc_ins(5);
select * from test_1;
Empty.
The first insert into test_1 (which would have succeeded) was rolled back. This is called a statement level rollback. The documentation states that "An anonymous block is a SQL statement." and that an SQL statement that causes an error during execution will execute a statement level rollback and "The effect of the rollback is as if the statement had never been run."
An automated test
So what happens if I want to create an automated test to confirm the results.
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
proc_ins(1);
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
Because the statement (the anonymous block) now includes the exception handler, the statement itself doesn't fail and the first insert is still there. By testing for the exception, I seem to change the behaviour. I say "seem" because the statement rollback is not explicitly part of the code I am testing, but is an artifact of the way the statement is called.
If proc_ins were to be part of a larger PL/SQL program, the tested behaviour would represent the actual run time behavior. But if it is to be called as a top level procedure, for example directly from a client application, the tested behaviour would be inaccurate.
Changing the results
I can change the unit test so that it represents a 'independent statement' rather than 'embedded call' using dynamic SQL. A dynamic SQL call will set a new level of SQL scope.
truncate table test_1;
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
execute immediate 'call proc_ins(2)';
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
Within the scope of the SQL statement (the CALL) in which the exception occurs, there is no exception handler. The SQL statement is rolled back, as if it never happened. When we re-enter the outer scope, the exception is handled, but the statement rollback has already happened.
It doesn't make a difference whether we use the CALL statement (which is SQL), or a BEGIN...END; PL/SQL construction.
truncate table test_1;
declare
e_too_big exception;
PRAGMA EXCEPTION_INIT(e_too_big, -1438);
begin
execute immediate 'begin proc_ins(3); end;';
exception
when e_too_big then
for c_rec in (select val from test_1) loop
dbms_output.put_line(c_rec.val);
end loop;
end;
/
I will own up and say this revisits a post a few months back but I felt the automated test aspect of it needed an emphahsis. Also I was prompted by the PL/SQL Challenge of May 13th.
The answer states that "Exceptions do not roll back DML statements unless the exception goes unhandled to the host environment, in which case most such environments (like SQL*Plus) will automatically perform a rollback in your session."
I didn't like the statetment about host environments as it is the database engine which performs the statement-level rollback whenever an SQL call raises an exception. The host can do whatever it likes (though it is generally safer to rollback when you get an exception you don't expect).
Wednesday, May 05, 2010
How many rows in a view ?
It depends on the order of the tables in the FROM clause....sometimes.
Okay, it's a trick question. the real one is
"How many rows does the CBO estimate when querying a view over a DB link ?"
Lets see it in action.
create table small as
select level id, 'small' val from dual connect by level <= 10;
create table large as
select level id, 'large' val from dual connect by level <= 10000;
create or replace view small_large as
select s.id small_id, l.id large_id, s.val small_val, l.val large_val
from small s, large l where s.id = l.id;
create or replace view large_small as
select s.id small_id, l.id large_id, s.val small_val, l.val large_val
from large l, small s where s.id = l.id;
begin
dbms_stats.gather_table_stats (ownname => user, tabname => 'SMALL',
estimate_percent=> 100, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true, no_invalidate => false);
dbms_stats.gather_table_stats (ownname => user, tabname => 'LARGE',
estimate_percent=> 100, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true, no_invalidate => false);
end;
/
Simple Select
explain plan for select * from small_large;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 10 | 180 | 13 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SMALL | 10 | 90 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LARGE | 10000 | 90000 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
Gives an estimate of 10 rows as we'd expect (and we can see the source tables).
Select over a database link
Lets add in the database link (in this case a loopback link to the same account and database).
create database link lb connect to ****** identified by ****** using 'db11gr2';
explain plan for select * from small_large@lb;
select * from table(dbms_xplan.display);
Still ten rows, but the query is fully resolved at the remote end, so there's no real challenge (and we still see the source tables).
Distributed select with local and remote objects
Now with a query that hits both sides of the fence.
explain plan for select * from small_large@lb, dual d;
select * from table(dbms_xplan.display);
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 |
| 1 | NESTED LOOPS | | 10000 |
| 2 | TABLE ACCESS FULL| DUAL | 1 |
| 3 | REMOTE | SMALL_LARGE | 10000 |
--------------------------------------------------
Now it says 10,000 rows.
explain plan for select * from large_small@lb, dual d;
select * from table(dbms_xplan.display);
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 110 |
| 1 | NESTED LOOPS | | 10 | 110 |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 |
| 3 | REMOTE | LARGE_SMALL | 10 | 90 |
----------------------------------------------------------
Just 10 rows estimated there.
Distributed select with local and remote objects
The only difference is the join order in the view definitions and that difference only comes into play with a distributed query over the DB link.
Anyone with a eidetic memory may realise I'm just reprising a post from my blog a few years back. [That was one of my first - I haven't been going quite as long as Tom or Doug.] Really I just wanted to see if anything had changed in the past couple of database releases. It hadn't, and 11gr2 does the same thing.
To take it one step further, I tried a trace. It isn't easy picking trhough the recursive SQL, but there was one query that looked for object dependencies for the view. It queried sys.dependency$ with an order by on a column order# (which isn't exposed in the dba_dependecies view). As long as the dependency is in the FROM clause (and I'm still working on how it determines that) it seems the entry with the highest order# is the one used to determine the cardinality used by the optimizer.
So, sometimes, the order of tables in the FROM clause is important. But not very often.
Okay, it's a trick question. the real one is
"How many rows does the CBO estimate when querying a view over a DB link ?"
Lets see it in action.
create table small as
select level id, 'small' val from dual connect by level <= 10;
create table large as
select level id, 'large' val from dual connect by level <= 10000;
create or replace view small_large as
select s.id small_id, l.id large_id, s.val small_val, l.val large_val
from small s, large l where s.id = l.id;
create or replace view large_small as
select s.id small_id, l.id large_id, s.val small_val, l.val large_val
from large l, small s where s.id = l.id;
begin
dbms_stats.gather_table_stats (ownname => user, tabname => 'SMALL',
estimate_percent=> 100, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true, no_invalidate => false);
dbms_stats.gather_table_stats (ownname => user, tabname => 'LARGE',
estimate_percent=> 100, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true, no_invalidate => false);
end;
/
Simple Select
explain plan for select * from small_large;
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 10 | 180 | 13 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SMALL | 10 | 90 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LARGE | 10000 | 90000 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
Gives an estimate of 10 rows as we'd expect (and we can see the source tables).
Select over a database link
Lets add in the database link (in this case a loopback link to the same account and database).
create database link lb connect to ****** identified by ****** using 'db11gr2';
explain plan for select * from small_large@lb;
select * from table(dbms_xplan.display);
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 10 | 180 |
|* 1 | HASH JOIN | | 10 | 180 |
| 2 | TABLE ACCESS FULL | SMALL | 10 | 90 |
| 3 | TABLE ACCESS FULL | LARGE | 10000 | 90000 |
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 10 | 180 |
|* 1 | HASH JOIN | | 10 | 180 |
| 2 | TABLE ACCESS FULL | SMALL | 10 | 90 |
| 3 | TABLE ACCESS FULL | LARGE | 10000 | 90000 |
--------------------------------------------------------
Still ten rows, but the query is fully resolved at the remote end, so there's no real challenge (and we still see the source tables).
Distributed select with local and remote objects
Now with a query that hits both sides of the fence.
explain plan for select * from small_large@lb, dual d;
select * from table(dbms_xplan.display);
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 |
| 1 | NESTED LOOPS | | 10000 |
| 2 | TABLE ACCESS FULL| DUAL | 1 |
| 3 | REMOTE | SMALL_LARGE | 10000 |
--------------------------------------------------
Now it says 10,000 rows.
explain plan for select * from large_small@lb, dual d;
select * from table(dbms_xplan.display);
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 110 |
| 1 | NESTED LOOPS | | 10 | 110 |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 |
| 3 | REMOTE | LARGE_SMALL | 10 | 90 |
----------------------------------------------------------
Just 10 rows estimated there.
Distributed select with local and remote objects
The only difference is the join order in the view definitions and that difference only comes into play with a distributed query over the DB link.
Anyone with a eidetic memory may realise I'm just reprising a post from my blog a few years back. [That was one of my first - I haven't been going quite as long as Tom or Doug.] Really I just wanted to see if anything had changed in the past couple of database releases. It hadn't, and 11gr2 does the same thing.
To take it one step further, I tried a trace. It isn't easy picking trhough the recursive SQL, but there was one query that looked for object dependencies for the view. It queried sys.dependency$ with an order by on a column order# (which isn't exposed in the dba_dependecies view). As long as the dependency is in the FROM clause (and I'm still working on how it determines that) it seems the entry with the highest order# is the one used to determine the cardinality used by the optimizer.
So, sometimes, the order of tables in the FROM clause is important. But not very often.
Subscribe to:
Posts (Atom)