Home » RDBMS Server » Performance Tuning » Select query taking time even after using PARALLEL hint (Oracle 11.2.0.3,Linux x86-64)
Select query taking time even after using PARALLEL hint [message #596611] Wed, 25 September 2013 03:18 Go to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi All,
Need help to tune a query,

 select 
serialnumber from product where productid in 
	(select /*+ full parallel(producttask 16) */productid from producttask where 
	startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
	and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
	and producttasktypeid in 
		(select producttasktypeid from producttasktype 
		where producttaskname='SHIPPING-SETTINGS' or producttaskname='SHIPPING-SETTINGS-OQC' or producttaskname='NON-UIRESTORE'
		)
	and modelid in (select modelid from modeltable where familyid in (select familyid from family where familytypecode='N48' or familytypecode='N49') )   
	and rownum < 100
	);


Explain plan output:

 Plan hash value: 2779236890

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				   | Name			| Rows	| Bytes | Cost (%CPU)| Time	| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			   |				|     1 |    29 |  9633M  (8)|999:59:59 |	|	|
|*  1 |  FILTER 				   |				|	|	|	     |		|	|	|
|   2 |   PARTITION RANGE ALL			   |				|   738M|    19G|  6321K  (1)| 21:04:17 |     1 |  6821 |
|   3 |    TABLE ACCESS STORAGE FULL		   | PRODUCT			|   738M|    19G|  6321K  (1)| 21:04:17 |     1 |  6821 |
|*  4 |   FILTER				   |				|	|	|	     |		|	|	|
|*  5 |    COUNT STOPKEY			   |				|	|	|	     |		|	|	|
|   6 |     NESTED LOOPS			   |				|	|	|	     |		|	|	|
|   7 |      NESTED LOOPS			   |				|     5 |   235 |    13   (8)| 00:00:01 |	|	|
|   8 |       NESTED LOOPS			   |				|     2 |    56 |     7  (15)| 00:00:01 |	|	|
|   9 |        VIEW				   | VW_NSO_1			|     1 |    13 |     4  (25)| 00:00:01 |	|	|
|  10 | 	NESTED LOOPS			   |				|     1 |    18 |     3   (0)| 00:00:01 |	|	|
|  11 | 	 TABLE ACCESS BY INDEX ROWID	   | MODELTABLE 		|     1 |     9 |     2   (0)| 00:00:01 |	|	|
|* 12 | 	  INDEX UNIQUE SCAN		   | MODELTABLE_MODELID 	|     1 |	|     1   (0)| 00:00:01 |	|	|
|* 13 | 	 TABLE ACCESS BY INDEX ROWID	   | FAMILY			|     4 |    36 |     1   (0)| 00:00:01 |	|	|
|* 14 | 	  INDEX UNIQUE SCAN		   | FAMILY_FAMILYID		|     1 |	|     0   (0)| 00:00:01 |	|	|
|* 15 |        TABLE ACCESS STORAGE FULL FIRST ROWS| PRODUCTTASKTYPE		|     2 |    30 |     3   (0)| 00:00:01 |	|	|
|  16 |       PARTITION RANGE SINGLE		   |				|     2 |	|    64   (0)| 00:00:01 |   918 |   918 |
|  17 |        PARTITION HASH ALL		   |				|     2 |	|    64   (0)| 00:00:01 |     1 |    32 |
|* 18 | 	INDEX RANGE SCAN		   | PT_RESULT_TASKTYPE_START_P |     2 |	|    64   (0)| 00:00:01 | 29345 | 29376 |
|  19 |      TABLE ACCESS BY LOCAL INDEX ROWID	   | PRODUCTTASK		|     2 |    38 |    67   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (<not feasible>)
   4 - filter("PRODUCTID"=:B1)
   5 - filter(ROWNUM<100)
  12 - access("MODELID"=:B1)
  13 - filter("FAMILYTYPECODE"='N48' OR "FAMILYTYPECODE"='N49')
  14 - access("FAMILYID"="FAMILYID")
  15 - storage("PRODUCTTASKNAME"='NON-UIRESTORE' OR "PRODUCTTASKNAME"='SHIPPING-SETTINGS' OR
	      "PRODUCTTASKNAME"='SHIPPING-SETTINGS-OQC')
       filter("PRODUCTTASKNAME"='NON-UIRESTORE' OR "PRODUCTTASKNAME"='SHIPPING-SETTINGS' OR
	      "PRODUCTTASKNAME"='SHIPPING-SETTINGS-OQC')
  18 - access("PRODUCTTASKTYPEID"="PRODUCTTASKTYPEID" AND "STARTEDTIMESTAMP">TO_DATE(' 2013-07-04 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss') AND "STARTEDTIMESTAMP"<TO_DATE(' 2013-07-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement 

[Updated on: Wed, 25 September 2013 03:22]

Report message to a moderator

Re: Select query taking time even after using PARALLEL hint [message #596619 is a reply to message #596611] Wed, 25 September 2013 03:30 Go to previous messageGo to next message
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
Is it possible that someone has already tried to tune this? You have a rather odd looking hint in there, and a SQL profile: you are not giving the optimizer much choice.
I would begin by disabling the profile; remove the hint; gather statistics for the objects; make sure that you have a usable index on product.productid.
Then let the optimizer have another try at it.

[Updated on: Wed, 25 September 2013 03:31]

Report message to a moderator

Re: Select query taking time even after using PARALLEL hint [message #596627 is a reply to message #596619] Wed, 25 September 2013 04:24 Go to previous message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi John,
Thanks for the reply. I had made the query to accept the sqlprofile recommended by sql autotune feature i.e..,"execute dbms_sqltune.accept_sql_profile". Can you please let me know how to remove that profile?

The original plan before accepting the recommended sqlprofile was,
 Plan hash value: 3234874388

--------------------------------------------------------------------------------
------------------------------------------------------
| Id  | Operation				 | Name 		     | R
ows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------
------------------------------------------------------
|   0 | SELECT STATEMENT			 |			     |
   1 |	  29 |	  74G  (2)|999:59:59 |	     |	     |
|*  1 |  FILTER 				 |			     |
     |	     |		  |	     |	     |	     |
|   2 |   PARTITION RANGE ALL			 |			     |
 738M|	  19G|	6321K  (1)| 21:04:17 |	   1 |	6821 |
|   3 |    TABLE ACCESS STORAGE FULL		 | PRODUCT		     |
 738M|	  19G|	6321K  (1)| 21:04:17 |	   1 |	6821 |
|*  4 |   FILTER				 |			     |
     |	     |		  |	     |	     |	     |
|*  5 |    COUNT STOPKEY			 |			     |
     |	     |		  |	     |	     |	     |
|*  6 |     HASH JOIN				 |			     |
   2 |	 158 |	 101   (2)| 00:00:02 |	     |	     |
|*  7 |      TABLE ACCESS STORAGE FULL FIRST ROWS| PRODUCTTASKTYPE	     |
   3 |	  45 |	   5   (0)| 00:00:01 |	     |	     |
|   8 |      NESTED LOOPS			 |			     |
  97 |	3104 |	  95   (2)| 00:00:02 |	     |	     |
|   9 |       VIEW				 | VW_NSO_1		     |
   1 |	  13 |	   4  (25)| 00:00:01 |	     |	     |
|* 10 |        FILTER				 |			     |
     |	     |		  |	     |	     |	     |
|  11 | 	NESTED LOOPS			 |			     |
   1 |	  18 |	   3   (0)| 00:00:01 |	     |	     |
|  12 | 	 TABLE ACCESS BY INDEX ROWID	 | MODELTABLE		     |
   1 |	   9 |	   2   (0)| 00:00:01 |	     |	     |
|* 13 | 	  INDEX UNIQUE SCAN		 | MODELTABLE_MODELID	     |
   1 |	     |	   1   (0)| 00:00:01 |	     |	     |
|* 14 | 	 TABLE ACCESS BY INDEX ROWID	 | FAMILY		     |
   1 |	   9 |	   1   (0)| 00:00:01 |	     |	     |
|* 15 | 	  INDEX UNIQUE SCAN		 | FAMILY_FAMILYID	     |
   1 |	     |	   0   (0)| 00:00:01 |	     |	     |
|  16 |       PARTITION RANGE ITERATOR		 |			     |
  97 |	1843 |	  91   (0)| 00:00:02 |	 KEY |	 KEY |
|  17 |        PARTITION HASH ALL		 |			     |
  97 |	1843 |	  91   (0)| 00:00:02 |	   1 |	  32 |
|  18 | 	TABLE ACCESS BY LOCAL INDEX ROWID| PRODUCTTASK		     |
  97 |	1843 |	  91   (0)| 00:00:02 |	 KEY |	 KEY |
|* 19 | 	 INDEX RANGE SCAN		 | PT_PRODUCT_TASKTYPE_KEY_P |
   1 |	     |	   2   (0)| 00:00:01 |	 KEY |	 KEY |
--------------------------------------------------------------------------------
------------------------------------------------------

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

   1 - filter( EXISTS (<not feasible>)
   4 - filter("PRODUCTID"=:B1)
   5 - filter(ROWNUM<:SYS_B_9)
   6 - access("PRODUCTTASKTYPEID"="PRODUCTTASKTYPEID")
   7 - storage("PRODUCTTASKNAME"=:SYS_B_4 OR "PRODUCTTASKNAME"=:SYS_B_5 OR "PROD
UCTTASKNAME"=:SYS_B_6)
       filter("PRODUCTTASKNAME"=:SYS_B_4 OR "PRODUCTTASKNAME"=:SYS_B_5 OR "PRODU
CTTASKNAME"=:SYS_B_6)
  10 - filter(TO_DATE(:SYS_B_0,:SYS_B_1)<TO_DATE(:SYS_B_2,:SYS_B_3))
  13 - access("MODELID"=:B1)
  14 - filter("FAMILYTYPECODE"=:SYS_B_7 OR "FAMILYTYPECODE"=:SYS_B_8)
  15 - access("FAMILYID"="FAMILYID")
  19 - access("STARTEDTIMESTAMP">TO_DATE(:SYS_B_0,:SYS_B_1) AND "STARTEDTIMESTAM
P"<TO_DATE(:SYS_B_2,:SYS_B_3))


Thanks!
Previous Topic: Please help me to tune this query
Next Topic: Split Data separated by comma ; then create a collection with data mapped from other columns
Goto Forum:
  


Current Time: Wed Oct 22 11:36:25 CDT 2014

Total time taken to generate the page: 1.44646 seconds