Hi,
NOTE: first, I thought sql developer tool is causing this and I opened a thread on that category, thanks to @rp0428's warnings and advises, I realized that something else is happening. so reopened this thread in that category, I also need an admin to delete other one:
https://forums.oracle.com/forums/thread.jspa?threadID=2420515&tstart=0
thanks.
so my problem is:
I have table partitioned by range (no subpartition) on a DATE column per months. It has almost 100 partitions. I run a query on that table based on partition column:
select *
from hareket_table
where islem_tar between to_date('01/05/2012', 'dd/mm/yyyy') and to_date('14/07/2012', 'dd/mm/yyyy') -- ISLEM_TAR is my partition column.
so, when I run this query from sql developer, query works parallel. I didnt just get execution plan via sql developer interface, first I used "EXPLAIN PLAN FOR" statement (which I always do, I dont use developer tools interfaces generally) then used developer interface (just to be sure) but I didnt satisfied and then I run the query and and get real execution plan via:
select * from table(dbms_xplan.display_cursor(sql_id => '7cm8cz0k1y0zc', cursor_child_info =>0, format=>'OUTLINE'));
and the same execution plan again with PARALLELISM. so INDEXES and TABLE has no parallelism (DEGREE column in DBA_INDEXES and DBA_TABLES is set to 1).
as I know, if I'm wrong please correct me, there is no reason to oracle run this query as parallel (I also did not give any hint). so I worried and run the same steps in "plsql developer" and query runs noparallel (inteface, explain plan for, dbms_xplan.display_cursor). sqlplus autotrace was the same( just autotrace, didnt try others dbms_xplan etc.) Based on that, I decided sql developer is causing to this (*edit: but I was wrong TOAD did same thing*).
so I focused on sql developer and I disabled parallel query using:
alter session disable parallel query;
then run the statement again and there were no Parallelism (expectedly).
so looked for execution plans:
I run query twice. one with normal, one with session disabled parallel query. and look for executed execution plan for both. (child 0 and 1)
-- WHEN PARALLEL QUERY IS ENABLE, SESSION DEFAULT
-- JUST CONNECTED TO DATABASE
select * from table(dbms_xplan.display_cursor('7cm8cz0k1y0zc', 0, 'OUTLINE'));
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2025 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 7910K| 1267M| 2025 (2)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 7910K| 1267M| 2025 (2)| 00:00:01 | 90 | 92 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| HAREKET_TABLE | 7910K| 1267M| 2025 (2)| 00:00:01 | 90 | 92 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('optimizer_index_cost_adj' 30)
OPT_PARAM('optimizer_index_caching' 50)
OPT_PARAM('optimizer_dynamic_sampling' 6)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "HAREKET_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
filter(("ISLEM_TAR">=TO_DATE(' 2012-05-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ISLEM_TAR"<=TO_DATE('
2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
/**********************************************************************************************************/
--WHEN DISABLED PARALLEL QUERY
--AFTER CONNECTED, EXECUTED "ALTER SESSION DISABLE PARALLEL QUERY"
select * from table(dbms_xplan.display_cursor('7cm8cz0k1y0zc', 1, 'OUTLINE'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 36504 (100)| | | |
| 1 | PARTITION RANGE ITERATOR| | 7910K| 1267M| 36504 (2)| 00:00:04 | 90 | 92 |
|* 2 | TABLE ACCESS FULL | HAREKET_TABLE | 7910K| 1267M| 36504 (2)| 00:00:04 | 90 | 92 |
----------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('optimizer_index_cost_adj' 30)
OPT_PARAM('optimizer_index_caching' 50)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "HAREKET_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ISLEM_TAR">=TO_DATE(' 2012-05-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
as you can see, when I've just connected to database (no any statements run)
OPT_PARAM('optimizer_dynamic_sampling' 6) is in my stats.
when I disable parallel query on the session, this is not in stats...
value of optimizer_dynamic_sampling is 2 in DB. so why is that query runs parallel ? I don't want that.
thanks for answers