Sep 01 2010
This is a short note to point out that I just added to the Public Appearances page the next conference organized by the Deutsche ORACLE-Anwendergruppe (DOAG) in Nürnberg. It will take place on November 16-18. My talk, entitled “Transaktions-Management Internas”, will be the German version of then one I will give at the Michigan OakTable Symposium 2010 in two weeks.
Sep 01 2010
Few days ago the OOW content team informed me that they changed the location of my presentation from “Rm 200″ to “Rm 304″. This is very good news! Why? Because the capacity has more than doubled. They probably noticed that the previous one was almost full… So, right now there still are plenty of free seats.
Because of that change, the very unpleasant situation of last year (not all people willing to attend my presentation were able to enter the room and, in addition, for many of the attendees it was simply not possible to see the projected slides…) should not be repeated.
I’m looking forward to seeing you in San Francisco!
Sep 01 2010
As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release 2, there is a feature that provides parallel processing capabilities in the Standard Edition as well. This feature is available through the DBMS_PARALLEL_EXECUTE package.
For example, let’s say that you have to execute an UPDATE statement on all rows of a table containing a huge amount of data. If you can take the table “offline”, provided you have enough space doing a CTAS statement instead of an UPDATE statement is probably much faster. However, if you cannot put the table “offline”, doing it in parallel might be a sensible way to speed-up the execution. Hence, if you are using the Enterprise Edition you can take advantage of the parallel processing features integrated in the SQL engine. Thus, you can execute something like that:
ALTER SESSION ENABLE PARALLEL DML; UPDATE /*+ parallel(t,4) */ t SET col = expr;
Since such a possibility is not available with the Standard Edition, as of Oracle Database 11g Release 2 you might execute a PL/SQL block like the following one to perform the same operation in parallel:
DECLARE
l_task_name user_parallel_execute_tasks.task_name%TYPE;
l_sql_stmt user_parallel_execute_tasks.sql_stmt%TYPE;
BEGIN
l_task_name := 'px_update';
l_sql_stmt := 'UPDATE t SET col = expr';
dbms_parallel_execute.create_task(task_name => l_task_name);
dbms_parallel_execute.create_chunks_by_rowid(
task_name => l_task_name,
table_owner => user,
table_name => 'T',
by_row => FALSE,
chunk_size => 128
);
dbms_parallel_execute.run_task(
task_name => l_task_name,
sql_stmt => l_sql_stmt,
language_flag => dbms_sql.native,
parallel_level => 4
);
WHILE (dbms_parallel_execute.task_status(task_name => l_task_name)
NOT IN (
dbms_parallel_execute.finished,
dbms_parallel_execute.finished_with_error,
dbms_parallel_execute.crashed
))
LOOP
dbms_lock.sleep(1);
END LOOP;
dbms_parallel_execute.drop_task(task_name => l_task_name);
END;
Note that using the DBMS_PARALLEL_EXECUTE package is not limited to the Standard Edition, though. I see at least two situations where it can be handy with the Enterprise Edition:
Both situations are relevant if, as of Oracle Database 11g Release 2, you plan to perform such an operation during an online application upgrade by taking advantage of edition-based redefinition. I guess that the package was implemented for that purpose…
Aug 10 2010
This is a short note to point out that I just added to the Public Appearances page the next conference organized by the Slovenian Oracle User Group (SIOUG) in Portoroz. It will take place on September 27-29. My talk, entitled “Join Techniques”, is based on chapter 10 of my book. It will be a shorter version of then one I will give at Oracle OpenWorld the week before.
Aug 09 2010
When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the next couple of months.
In part 2 I pointed out that Oracle Database is not able to offload the processing of all datetime functions. This fact, to my surprise, was also referenced by Netezza in a recent paper entitled Oracle Exadata and Netezza TwinFin Compared. The essential thing to understand is that this limitation is due to bug 9682721. The fix is expected to be part of 11.2.0.2. According to my test cases (that Greg Rahn was so kind to execute against an early release of 11.2.0.2), offloading works correctly for all datetime functions but for the following three predicates.
Note that the MONTHS_BETWEEN function can basically be offloaded. The problem in these cases is that the offloading does not work when, for example, SYSDATE is used as parameter.
To have a full list of the functions supporting offloading, the “official reference” is available through the V$SQLFN_METADATA view. Here is a simple query to summarize the current situation.
SQL> SELECT offloadable, count(DISTINCT name) 2 FROM v$sqlfn_metadata 3 GROUP BY offloadable; OFF COUNT(DISTINCTNAME) --- ------------------- NO 511 YES 319
Another thing I would like to point out about offloading is that the feature can be controlled through the CELL_OFFLOAD_PROCESSING initialization parameter. By default it is set to TRUE and, therefore, offloading is used whenever possible. It goes without saying that offloading is disabled when it is set to FALSE. Note that it can not only be set at the instance and session level, but also at the statement level. The following example illustrate this (notice that only the first query uses offloading).
SQL> ALTER SESSION SET cell_offload_plan_display = always;
SQL> ALTER SESSION SET cell_offload_processing = true;
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id = 101;
SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL | |
|* 2 | TABLE ACCESS STORAGE FULL| T |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ID"=101)
filter("ID"=101)
SQL> EXPLAIN PLAN FOR SELECT /*+ opt_param('cell_offload_processing' 'false') */ * FROM t WHERE id = 101;
SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION RANGE ALL | |
|* 2 | TABLE ACCESS STORAGE FULL| T |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=101)
Another initialization parameter that controls offloading is CELL_OFFLOAD_DECRYPTION. This parameter is relevant for encrypted tablespaces only. With it you can specify whether the keys necessary to decrypt the data can be shipped to the cells. By default it is set to TRUE and, therefore, the keys are shipped. For security reasons you might want to set it to FALSE and disable offloading for encrypted tablespaces. Note that this parameter can only be changed at the instance level.