DBA Blogs

INTERVAL PARTITIONING with custom TABLESPACE names

Tom Kyte - Fri, 2017-02-24 08:46
We have a range partitioned table on daily basis. Every year, we are splitting the MAX partition into 365* days partitions such as: ALTER TABLE txn SPLIT PARTITION p_txn_max AT (to_date('01/02/2017','MM/DD/YYYY')) INTO (PARTITION ...
Categories: DBA Blogs

Update query using case and joins

Tom Kyte - Fri, 2017-02-24 08:46
<code>Table 1: select * from FV Table 2: select * From DB_FV_W UPDATE DB_FV_W Set FV_02 = (CASE WHEN db.FV_02 IS NULL THEN '0' Else fv.MD END) FROM DB_FV_W d LEFT OUTER JOIN FV f On...
Categories: DBA Blogs

Executing dynamic sql with

Tom Kyte - Fri, 2017-02-24 08:46
Hi Tom, I have a requirement wherein a sql saved in a table column (clob type) is executed in plsql . The results will then be used for further processing . SQLs to be executed will be selected based on some criteria. eg : Assume the table n...
Categories: DBA Blogs

Links for 2017-02-23 [del.icio.us]

Categories: DBA Blogs

Reading Execution Plans for Parallel DML

Pythian Group - Thu, 2017-02-23 20:36

Parallel execution in Oracle seems to be a topic of myths, mysteries and misunderstandings. In this post I hope to clear up at least one of them by diving into a discussion about parallel DML. Actually, I want to show you two things that can go wrong with it.

My demo setup and case are pretty simple, I just create two copies of dba_objects:

SQL> create table a as select * from dba_objects;

Table created.

SQL> create table b as select * from dba_objects;

Table created.

Now let’s delete from one of the tables with a subselect to the other one, and let’s make it parallel. When, if and how it makes sense to actually use parallel DML for a speedup is not the point of this post. In fact, this is a poor example as the overhead of extra communication and spinning up the processes just made the actual delete run slower.

SQL> delete /*+ PARALLEL(a 42) */ from a where object_id in (select object_id from b);

91277 rows deleted.

SQL> rollback;

And at first glance the execution plan for it seems to confirm that the operation was performed in parallel:

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	1q2m84vq594pj, child number 0
-------------------------------------
delete /*+ PARALLEL(a 42) */ from a where object_id in (select
object_id from b)

Plan hash value: 1475376193

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation	 	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT	   |	      |       |       |   855 (100)|	      |        |      | 	   |
|   1 |  DELETE 		   | A	      |       |       | 	   |	      |        |      | 	   |
|   2 |   PX COORDINATOR	   |	      |       |       | 	   |	      |        |      | 	   |
|   3 |    PX SEND QC (RANDOM)	   | :TQ10002 | 91277 |   891K|   855	(1)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  4 |     HASH JOIN SEMI BUFFERED|	      | 91277 |   891K|   855	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   5 |      PX RECEIVE 	   |	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   6 |       PX SEND HASH	   | :TQ10000 | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | P->P | HASH	  |
|   7 |        PX BLOCK ITERATOR   |	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | PCWC | 	   |
|*  8 | 	TABLE ACCESS FULL  | A	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | PCWP | 	   |
|   9 |      PX RECEIVE 	   |	      | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
|  10 |       PX SEND HASH	   | :TQ10001 | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,01 | S->P | HASH	  |
|  11 |        PX SELECTOR	   |	      |       |       | 	   |	      |  Q1,01 | SCWC | 	   |
|  12 | 	TABLE ACCESS FULL  | B	      | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,01 | SCWP | 	   |
--------------------------------------------------------------------------------------------------------------------

But a closer look reveals that the actual delete that we wanted to run in parallel is performed only after (above and to the left in the execution plan) the parallel part coordinator is done with the query. Also, because we only specified parallelism for Table A, the scan of Table B is being performed sequentially. The results are then distributed to parallel workers, and hash joined against the parallel query of A. Those results are buffered and returned to the coordinator which then serially performs the actual deletes. Sounds silly? It is. And the second issue is that even though the hint gets ignored for the DELETE part, it is still applied to the query and join, forcing a half-parallel operation.

Enable parallel DML mode

The issue to be aware of with parallel DML in Oracle is that it is disabled by default and you have to explicitly enable it in the session before running parallel DML operations.

And at least DBMS_XPLAN in 12c will also warn you about not using parallel DML in it’s output:

Note
-----
   - Degree of Parallelism is 42 because of hint
   - PDML is disabled in current session

So let’s try the same thing again, but enable parallel DML this time:

SQL> alter session enable parallel dml;

Session altered.

SQL> delete /*+ PARALLEL(a 42) */ from a where object_id in (select object_id from b);

91277 rows deleted.

SQL> rollback;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	1q2m84vq594pj, child number 1
-------------------------------------
delete /*+ PARALLEL(a 42) */ from a where object_id in (select
object_id from b)

Plan hash value: 2691386263

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT		 |	    |	    |	    |	438 (100)|	    |	    |	    |		 |
|   1 |  PX COORDINATOR 		 |	    |	    |	    |		 |	    |	    |	    |		 |
|   2 |   PX SEND QC (RANDOM)		 | :TQ10003 | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    DELETE			 | A	    |	    |	    |		 |	    |  Q1,03 | PCWP |		 |
|   4 |     PX RECEIVE			 |	    | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,03 | PCWP |		 |
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|
|*  6 |       HASH JOIN SEMI BUFFERED	 |	    | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,02 | PCWP |		 |
|   7 |        PX RECEIVE		 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
|   8 | 	PX SEND HASH		 | :TQ10000 | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | P->P | HASH	 |
|   9 | 	 PX BLOCK ITERATOR	 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
|* 10 | 	  TABLE ACCESS FULL	 | A	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
|  11 |        PX RECEIVE		 |	    | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,02 | PCWP |		 |
|  12 | 	PX SEND HASH		 | :TQ10001 | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,01 | S->P | HASH	 |
|  13 | 	 PX SELECTOR		 |	    |	    |	    |		 |	    |  Q1,01 | SCWC |		 |
|  14 | 	  TABLE ACCESS FULL	 | B	    | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,01 | SCWP |		 |
--------------------------------------------------------------------------------------------------------------------------

Now we see the DELETE operation below the PX COORDINATOR, which means it gets executed in parallel by the parallel workers. B is still read serially because we only specified table A in the hint. Let me just add this 100% parallel plan for the sake of completeness…

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID	02w21a0rgz02m, child number 1
-------------------------------------
delete /*+ PARALLEL(42) */ from a where object_id in (select object_id
from b)

Plan hash value: 149866034

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT		 |	    |	    |	    |	 23 (100)|	    |	     |	    |		 |
|   1 |  PX COORDINATOR 		 |	    |	    |	    |		 |	    |	     |	    |		 |
|   2 |   PX SEND QC (RANDOM)		 | :TQ10003 | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    DELETE			 | A	    |	    |	    |		 |	    |  Q1,03 | PCWP |		 |
|   4 |     PX RECEIVE			 |	    | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,03 | PCWP |		 |
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|
|*  6 |       HASH JOIN SEMI BUFFERED	 |	    | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
|   7 |        PX RECEIVE		 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
|   8 | 	PX SEND HASH		 | :TQ10000 | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | P->P | HASH	 |
|   9 | 	 PX BLOCK ITERATOR	 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
|* 10 | 	  TABLE ACCESS FULL	 | A	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
|  11 |        PX RECEIVE		 |	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
|  12 | 	PX SEND HASH		 | :TQ10001 | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | P->P | HASH	 |
|  13 | 	 PX BLOCK ITERATOR	 |	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | PCWC |		 |
|* 14 | 	  TABLE ACCESS FULL	 | B	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | PCWP |		 |
--------------------------------------------------------------------------------------------------------------------------
Conclusion

Keep in mind that parallel DML has to be enabled at the session level specifically. And when reviewing execution plans with parallel DML, make sure the update, insert or delete operation is below the PX coordinator or else you will serialize again before performing the actual operation.

Categories: DBA Blogs

LISTAGG not giving distinct values

Tom Kyte - Thu, 2017-02-23 14:26
Hi Guys, If I use LISTAGG for a single table it gives distance values in that column. But if I use it for by joining multiple tables it gives all the values even though they are duplicating. Scenario: CREATE TABLE TEMP1(ID1 NUMBER,TYPE1 CHAR...
Categories: DBA Blogs

REDO LOG growing for SMON Background process

Tom Kyte - Thu, 2017-02-23 14:26
Hello Tom, We experienced a continuous growing on redo log for background processes, specifically: SMON: 309 Gb Now, 300 Gb Last week, 291 two weeks ago DBW0: 141 Gb Now, 135 Gb Last week, 128 two weeks ago DBW1: 118 Gb Now, 110 Gb Last we...
Categories: DBA Blogs

Oracle CAST function conversion for BOOLEAN TO VARCHAR

Tom Kyte - Thu, 2017-02-23 14:26
Hi Tom, As per Oracle docs on CAST function, http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html <b>A BOOLEAN value can be cast explicitly to any of the string types. The result is 'true', 'false', or null. Conversely, string type...
Categories: DBA Blogs

REGARDING copy an table from existing table

Tom Kyte - Thu, 2017-02-23 14:26
How to create an table from existing table and vommiting particular columns with the data from the table
Categories: DBA Blogs

send e-mail notification through external

Tom Kyte - Thu, 2017-02-23 14:26
Hi Tom, below function is used to send mail. but recently I upgraded DB to 12c from 11gR1/R2, I am not getting body part of mail. I am getting notification mail with subject line but missing the body part. please help. function : ---------- ...
Categories: DBA Blogs

SQL client for soap web-service (parse MTOM response)

Tom Kyte - Thu, 2017-02-23 14:26
Good day, dear colleagues! Sorry for bad English. I'm from Russia. There is a client in oracle pl/sql that using UTL_HTTP makes a request to the Web service and parses the response. Everything works perfectly. But in the particular case of the...
Categories: DBA Blogs

Oracle not using literal value for partition pruning during query execution

Tom Kyte - Thu, 2017-02-23 14:26
Hi Tom, We have a table with about 700 partitions. Our test query is <code>select count(*) from fs_1d f where f.fs_date = '03-JAN-2017' and f.feed_code = 'E4F1'</code> When we explain plan for this query, the plan was correct. It goes dire...
Categories: DBA Blogs

impdp full database from solaris to windows database

Tom Kyte - Thu, 2017-02-23 14:26
Hi, I am new to Oracle DB. I am trying to import an exported database from a Solaris oracle 11.2.0.1 to a windows 2008 oracle 11.2.0.1. I installed Oracle 11.2.0 in a windows 2008 OS with a sample database name orcl. Below are the steps I follow t...
Categories: DBA Blogs

DB link - Access specified table

Tom Kyte - Thu, 2017-02-23 14:26
Hi, For instance, i have a dblink db123 in database1 and t2 table in database2 , i want to access only one table t2@database2 from database1 by using dblink db123 , except t2@database2 that dblink db123 should not accessible/valid for ...
Categories: DBA Blogs

how to return varhar type from clob

Tom Kyte - Thu, 2017-02-23 14:26
Hi, using dbms_lob.substr(), I can extract upto 4k chars only, But I want to extract all characters (6k out of 6k chars). Importantly I should return char or varchar type. Like below query dbms_lob.substr(x, 6000, 1). Thanks
Categories: DBA Blogs

Ravello CS with Big Data VM

Big Data Lite Virtual Machine is the most straight-forward way to start learning about Oracle Big Data Stack or even develop demos/proof of concepts on top of it (don't forget however that BD Lite...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Links for 2017-02-22 [del.icio.us]

Categories: DBA Blogs

Lateral Usage in 12c

Tom Kyte - Wed, 2017-02-22 20:06
Hi, I have learned that there is a new LATERAL keyword available in 12c Version. However, trying to understand the usage of the same. tried going through some links, but couldn't get complete picture. Syntax happy. Can you please help me.
Categories: DBA Blogs

convert CLOB data over 4K character to character

Tom Kyte - Wed, 2017-02-22 20:06
Hi, I am creating a materialized view, which needs to join multiple tables, and one of them (just only one table) giving me duplicate records in the view. When I used 'Distinct' keyword it was throwing error, knowing that when SELECT script has CL...
Categories: DBA Blogs

Analytics vs subquery

Tom Kyte - Wed, 2017-02-22 20:06
I have an example data set where I would like to count the number of RET status for a given event. This was a simple query, but now they would like the count reset when there is a different created_by. Barney in this example would reset the coun...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs