| Select query taking time even after using PARALLEL hint [message #596611] |
Wed, 25 September 2013 03:18  |
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  |
John Watson
Messages: 8964
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 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
