Disclaimer All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use.
This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove.
Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only.
This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only.
If you see any issues with Content and copy write issues, I am happy to remove if you notify me.
Contact Geek DBA Team, via geeksinsights@gmail.com
|
Typically we would have the following patterns when we use like operator.
- SEARCH-STRING%
- %SEARCH-STRING
- %SEARCH-STRING%
- SEARCH%STRING
1. a)The SEARCH-STRING% will perform INDEX RANGE SCAN data in least possible time.
b)Also uses INDEX FULL SCAN when you have only indexed column list in the queries.
c) If multiple predicates used, this may loose the index usage and goes to full table scan
2. When using %SEARCH-STRING it’s access the FULL table
3. When using %SEARCH-STRING% it’s access the FULL table.
4. The SEARCH%STRING will perform INDEX RANGE SCAN and generate an initial result set, containing the values that match first string i.e. SEARCH%. Next it will scan through the values to get second string i.e. %STRING
Let’s have a closer look
I have a table called EMPLOYEES and having a composite index on Last_name and First_name.
Case 1 (a):- Index range scan is in use, I am accessing all columns
SELECT * FROM employees WHERE LAST_NAME like 'K%';
Case 1 (b):- Index full scan is in use. Accessing only indexed columns in select.
SELECT FIRST_NAME,LAST_NAME FROM employees WHERE LAST_NAME like 'K%' OR LAST_NAME like 'L%';
Case 1(c ):- Added a predicate L% which leads to a full table scan.
SELECT * FROM employees WHERE LAST_NAME LIKE 'K%' OR LAST_NAME LIKE 'L%';
Case 2 & 3:- Used lower case filter predicate with %k% or k%
SELECT * FROM employees WHERE LAST_NAME LIKE '%k';
 If you access only index columns, index full scan like case 2(a) above will be possible.
Case 4:- Used String%string
SELECT * FROM employees WHERE LAST_NAME LIKE 'Smith%Joe';
Optimizer first check the first part of the string i.e Smith and then matches with second part of the string i.e joe with index range scan.
Thanks
-Geek DBA
The question is tricky,
I have asked accessed not modified/changed. If so modified/changed dba_tab_modifications can give you a clue.
For both of this questions, the below query will help.
Table is in use or not? This question may arise from App team that they have released a new module which may not require certain tables , but before dropping they want to confirm that no query using those tables,
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner = '&USERNAME' and p.operation like 'TABLE%' and p.sql_id = s.sql_id and p.object_name='&OBJNAME' group by p.object_owner,p.object_name,p.operation,p.options order by 1,2,3
Similarly Index is in use or not? This question may arise from App team or from DBA team, does certain index has an value or an overhead to the storage, so if any sql execution plans does not use that index it means that index does not need at all.
For a given object i.e index
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner = '&USERNAME' and p.operation like '%INDEX' and p.sql_id = s.sql_id and p.object_name='&objname' group by p.object_owner, p.object_name,p.operation,p.options order by 1,2,3;
For a given owner i.e schema level,
So if you found index with count zero, that means that index is not used at all any time in execution plan.
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner = '&USERNAME' and p.operation like '%INDEX' and p.sql_id = s.sql_id group by p.object_owner, p.object_name,p.operation,p.options order by 1,2,3;
For just to find only index that has count zero means never used, add condition having count(1) <=0
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner = '&USERNAME' and p.operation like '%INDEX' and p.sql_id = s.sql_id group by p.object_owner, p.object_name,p.operation,p.options having Idx_usg_cnt <=0 order by 1,2,3;
Another script to use track of object usage. This will show you the date & time as well
select to_char(sn.begin_interval_time,'yy-mm-dd hh24') Begin_Int_Time, p.search_columns Search_Col, count(*) Invocation_Cnt from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and lower(object_name) like /*lower('%&idxname%')*/ lower ('Idx_Name') group by begin_interval_time,search_columns
-Thanks
Geek DBA
Why you statistics are not collecting via automatic stats gather job, though your job showing succeeded?
Well the answer is, coz of Duration of schedule window, yes
As you aware , the jobs now scheduled in windows Read here
- The weekday windows start at 10PM and run for 8 hours.
- The weekend windows start at 6am and run for 20 hours.
Consider that, on monday the job kicked off and its analyzing a big table say about 200gb and has many partitions and took 6 hours, (normally table stats run one by one, not concurrently unlike 11g feature if you opt so)
So once its completed its duration, the job silently finishes off and does not report any error, resulting the other tables statistics to be stale and indeed this big table too.
To make worsen things, on tuesday, the job again started and we aware the weekday window is of 8 hours and again this big table started first to analyze (that’s the priority oracle chooses, verify the gather_stats_job_prog) and didn’t completed though in 8 hours, again resulted other tables statistics to be stale and this table too.
Its get go on for whole week and then weekend window came up, remember we have 20 hours for weekend window, and our job proceeded to collect big table and also the other tables whole weekend and , bingo we got stats.
But what about other days i.e weekdays, without statistics you well aware how your optimizer behaves, to fix this either lock the big table and exclude from the job and manually create a separate job for the same.
Foot Note:- You can leverage the concurrent stats gathering feature in 11g to mitigate this. But caveat is more resource usage.
-Thanks
Geek DBA
With previous post you see the duration of stats job, but how long they can run?
Well the answer for that would be depends on the scheduler windows.
To understand scheduler windows, according to documentation:-
A maintenance window is a contiguous time interval during which automated maintenance tasks are run. Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP. A Scheduler window can be a simple repeating interval (such as "between midnight and 6 a.m., every Saturday"), or a more complex interval (such as "between midnight and 6 a.m., on the last workday of every month, excluding company holidays").
When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window. Each job is assigned a job name that is generated at runtime. All automated maintenance task job names begin with ORA$AT. For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26. When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.
Each window is assigned to the resource plan DEFAULT_MAINTENANCE_PLAN which is enabled automatically when the maintenance windows are opened. The DEFAULT_MAINTENANCE_PLAN resource plan has a number of consumer groups assigned to it and various associated tasks including:
- ORA$AUTOTASK_SQL_GROUP - Automatic SQL Tuning tasks are assigned to this consumer group.
- ORA$AUTOTASK_SPACE_GROUP - Segment Advisor tasks are assigned to this group.
- ORA$AUTOTASK_STATS_GROUP - Automatic statistics gathering is assigned to this group.
Oracle provides a view that makes it easy to see each of the maintenance windows. This is the DBA_AUTOTASK_SCHEDULE view. The DBA_AUTOTASK_SCHEDULE view contains a 31 day record of all the autotask windows to be executed. This example shows the various autotask schedules:
SQL> SELECT * FROM dba_autotask_schedule ORDER BY start_time;
WINDOW_NAME START_TIME DURATION
---------------- ----------------------------------- -------------
SUNDAY_WINDOW 30-DEC-07 06.00.00.300000 AM -07:00 +000 20:00:00
MONDAY_WINDOW 31-DEC-07 10.00.00.300000 PM -07:00 +000 04:00:00
TUESDAY_WINDOW 01-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
WEDNESDAY_WINDOW 02-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
THURSDAY_WINDOW 03-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
FRIDAY_WINDOW 04-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
SATURDAY_WINDOW 05-JAN-08 06.00.00.300000 AM -07:00 +000 20:00:00
SUNDAY_WINDOW 06-JAN-08 06.00.00.300000 AM -07:00 +000 20:00:00
MONDAY_WINDOW 07-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
TUESDAY_WINDOW 08-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
WEDNESDAY_WINDOW 09-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
Note:- in the output of the query that the weekday windows start at 10PM, and run for 4 hours. The weekend windows start at 6am and run for 20 hours.
Let’s see what window my stats job has been assigned
SQL> select job_name, enabled, state, program_name, schedule_name from dba_scheduler_jobs where job_name like 'GATHER_STATS_JOB';
This query produces a result similar to the following:
So the schedule name is MAINTENANCE_WINDOW_GROUP, and this maintenance window will be active on weekdays and weekends runs as above timings and closed in that duration.
Here is the small script
column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
select
job_name, status, actual_start_date, run_duration
from
dba_scheduler_job_run_details
where
job_name = 'GATHER_STATS_JOB'
order by
actual_start_date
;
JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
-------------------- ------------ -------------------------------- --------------
GATHER_STATS_JOB SUCCEEDED 21-JAN-10 22.00.03.629836 +00:00 +000 02:26:12
GATHER_STATS_JOB SUCCEEDED 22-JAN-10 22.00.05.236441 +00:00 +000 02:37:48
GATHER_STATS_JOB SUCCEEDED 23-JAN-10 06.00.11.181440 +00:00 +000 01:23:25
GATHER_STATS_JOB SUCCEEDED 25-JAN-10 22.00.02.629410 +00:00 +000 05:06:38
GATHER_STATS_JOB SUCCEEDED 26-JAN-10 22.00.01.182149 +00:00 +000 00:11:09
GATHER_STATS_JOB SUCCEEDED 27-JAN-10 22.00.04.178122 +00:00 +000 00:09:54
GATHER_STATS_JOB SUCCEEDED 28-JAN-10 22.00.01.500210 +00:00 +000 03:15:44
From the dba_opt_stat_operations,
set lines 100
col operation form a40 wrap head 'operation(on)'
col target form a1
spool show_auto_stat_runs.lst
select operation||decode(target,null,null,'-'||target) operation
,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time
,to_char( end_time,'YYMMDD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc
/
clear colu
spool off
operation(on) START_TIME END_TIME
----------------------------------- -------------------- --------------------
gather_database_stats(auto) 090518 06:09:43.3429 090520 00:00:52.7399
gather_database_stats(auto) 090517 22:00:01.0653 090518 06:01:02.1637
gather_database_stats(auto) 090516 22:00:00.5150 090517 06:00:39.3076
gather_database_stats(auto) 090515 22:00:00.4157 090516 06:01:10.6544
gather_database_stats(auto) 090514 22:00:01.6624 090515 06:01:10.3767
gather_database_stats(auto) 090513 22:00:01.0706 090514 06:01:07.7145
gather_database_stats(auto) 090511 06:01:44.2381 090513 00:01:23.3091
gather_database_stats(auto) 090510 22:00:00.3218 090511 06:00:53.7735
gather_database_stats(auto) 090509 22:00:02.4334 090510 06:01:11.2443
gather_database_stats(auto) 090508 22:00:03.2080 090509 06:01:22.6858
gather_database_stats(auto) 090507 22:00:01.4859 090508 06:01:19.1966
gather_database_stats(auto) 090506 22:00:02.1927 090507 06:03:55.9747
gather_database_stats(auto) 090504 06:00:47.3064 090506 00:00:29.0017
gather_database_stats(auto) 090503 22:00:01.0015 090504 06:00:45.7018
gather_database_stats(auto) 090502 22:00:00.9199 090503 06:00:47.9868
gather_database_stats(auto) 090501 22:00:02.3382 090502 06:01:24.6070
gather_database_stats(auto) 090530 22:00:01.4914 090501 06:01:31.4369
gather_database_stats(auto) 090529 22:00:01.1076 090530 06:01:14.7856
gather_database_stats(auto) 090527 06:01:01.0286 090529 00:01:16.2678
gather_database_stats(auto) 090526 22:00:00.3628 090527 06:00:52.0203
Today I learnt something new, on sql profiles.
Thanks to Kerry Osborne’s blog that let me understand on this stuff
Before going forward have a look at the below plans.
SQL> select * from table(dbms_xplan.display_awr('2pg20pzpc6yb7'));
PLAN_TABLE_OUTPUT-
-----------------------------------------------------------------------------------------------------
SQL_ID 2pg20pzpc6yb7
--------------------
SQL Statement removed intentionally, and it is irrelevant to subject
Plan hash value: 526584108
-----------------------------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
|-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 287 | 10 (0)| 00:00:01 | | |
| 3 | PARTITION LIST SINGLE | | 3 | 456 | 7 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID |XXXOBJECT_HDR | 3 | 456 | 7 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN |XXXOBJECT_HDR_2 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
| 6 | PARTITION LIST SINGLE | | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | INDEX RANGE SCAN |XXXOBJECT_CONTENT_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID |XXXOBJECT_CONTENT | 1 | 135 | 1 (0)| 00:00:01 | 1 | 1 |
|-----------------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_0138294c151e0002" used for this statement
SQL_ID 2pg20pzpc6yb7
--------------------
SQL Statement removed intentionally, and it is irrelevant to subject
Plan hash value:1359745422
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
|----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 4 | 1212 | 6 (0)| 00:00:01 | | |
| 3 | PARTITION LIST SINGLE | | 1 | 168 | 4 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID |XXXOBJECT_HDR | 1 | 168 | 4 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN |XXXOBJECT_HDR_2 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
| 6 | PARTITION LIST SINGLE | | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | INDEX RANGE SCAN |XXXOBJECT_CONTENT_2 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID |XXXOBJECT_CONTENT | 4 | 540 | 2 (0)| 00:00:01 | 1 | 1 |
|----------------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL profile "SYS_SQLPROF_0138294c151e0002" used for this statement
Tables names also edited for safe guard the originality
Okay, what you have observed? (Let me tell you both statements are same using bind variables)
1. Both statements are same but having different plan hash values, means different plans, Absolutely right, the plan hash value 526584108 says its using index XXXOBJECT_CONTENT_PK where in the later plan hash value says its using XXXOBJECT_CONTENT_2 , perfectly right.
But sigh, check the note section, it says SQL profile “SYS_SQLPROF_0138294c151e0002” has been used.
There you are, that’s the catch,
??? if a SQL profile has been used is not that same plan with same name (object) should be used?
So what happened here?
First observation:-
This profile is not manually created, the name shows SYS_SQLPROF* , means created by Automatic SQL Tuning advisory
Second observation:-
Search google :(, really I have no second thought in searching this, fortunately came across kerry osborne’s 2009 post come to rescue to understand this behavior.
Final things:-
1) First, verify what are the hints that appended to this profile
Using Kerry’s script:-
sql_profile_hints.sql
OPT_ESTIMATE(@"SEL$1", TABLE, "C"@"SEL$1", SCALE_ROWS=0.0536172171)
OPT_ESTIMATE(@"SEL$2", JOIN, "C"@"SEL$1", SCALE_ROWS=4)
OPT_ESTIMATE(@"SEL$3", INDEX_RANGE_SCAN, "H"@"SEL$3", SCALE_ROWS=4)
OPTIMIZER_FEATURES_ENABLE(default)
Manipulated hints , Just for your understanding pasted here.
2) Second, the profile Used indirect hints, like OPT_ESTIMATE,
Note:- When sql profiles created automatically, these indirect hints will be placed and they really do not see the namespace (object names) instead go with aliases (here in my case C) and estimated that INDEX_PK would be cheaper to gain the data (in reality there may be many reasons, stats blah blah etc)
3) Third, to use direct hints like INDEX or something else, you have to create the sql profile manually on top of this SYS_SQLPROF**
Create SQL Profile manually.
create_sql_profile.sql
create_sql_profile_awr.sql
As per Kerry Osborne:-
Outlines don’t appear to use the OPT_ESTIMATE hint. So I believe it is still a valid approach to accept a SQL Profile as offered by the SQL Tuning Advisor and then create an Outline on top of it. It seems to work pretty well most of the time. (be sure and check the hints and the plan that gets produced) ** but not in our case
Manually created SQL Profiles also don’t appear to use the OPT_ESTIMATE hint. So I believe it is also a valid approach to accept a SQL Profile as offered by the SQL Tuning Advisor and then create a SQL Profile on top of it. Note that you’ll have to use a different category, then drop the original, then enable the new SQL Profile. Which means this approach is a bit more work than just creating an Outline in the DEFAULT category. ** Worked in our case (I will update the output)
So when it appeared that SQL Profile has been picked but you have different plans shown, you might have hitting that indirect hints are in place as that’s common design for SQL profiles (most possibly by automatic tuning advisor), we have to still create a manual sql profile which appeared to fix the plan irrespective of estimations that optimizer choose to do.
Hope this helps
-Thanks
Geek DBA
Hello Readers,
Thanks to Kirill for allowing me to share his blog posts on 12c database. Nice write up where you found the preliminary information on 12c pluggable databases which is coming soon.
Architecture Overview:-
http://www.dadbm.com/2013/01/oracle-12c-pluggable-database-fundamental-architectural-change/
Features Insight:-
http://www.dadbm.com/2013/01/oracle-12c-pluggable-database-feature-insights/
Some hands on:-
http://www.dadbm.com/2013/02/oracle-12c-pluggable-database-pdb-feature-hands-on-experience/
Plug & Unplug the databases in 12c:-
http://www.dadbm.com/2013/02/oracle-12c-pluggable-database-plug-unplug-and-clone/
BTW, I missed to participate in the beta program 🙁
Trying to find the conceptual documents
-Thanks
Geek DBA
During a query’s cost estimation the optimizer picks up many values or parameters to evaluate cost or invoke hints etc to produce an execution plan. v$sess_optimizer_env is a good view to know about any anamolies of a given statement. This can be useful when you comparing UAT and production environment where the query results are not same
For for a specific session
set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id=b.id
join v$session c on a.sid=c.sid and c.sid=191
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP')
order by a.sid,a.name;
SID USERNAME NAME VALUE
---------- ---------- ---------------------------------------- ----------------------------------------
22 SCOTT workarea_size_policy manual
267 ADAM optimizer_mode first_rows
For a specific sql_id
select
child_number, name, value
from v$sql_optimizer_env
where
sql_id = 'g4pkmrqrgxg3b'
order by
child_number,
name
;
CHILD_NUMBER NAME VALUE
------------ ---------------------------------------- -------------------------
0 _db_file_optimizer_read_count 16
active_instance_count 1
bitmap_merge_area_size 1048576
cpu_count 2
cursor_sharing exact
hash_area_size 131072
optimizer_dynamic_sampling 2
optimizer_features_enable 10.2.0.3
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_mode first_rows_1
optimizer_secure_view_merging true
parallel_ddl_mode enabled
parallel_dml_mode disabled
parallel_execution_enabled true
parallel_query_mode enabled
parallel_threads_per_cpu 2
pga_aggregate_target 204800 KB
query_rewrite_enabled true
query_rewrite_integrity enforced
skip_unusable_indexes true
sort_area_retained_size 0
sort_area_size 65536
sqlstat_enabled true
star_transformation_enabled false
statistics_level typical
workarea_size_policy auto
1 _db_file_optimizer_read_count 16
_hash_join_enabled false
active_instance_count 1
bitmap_merge_area_size 1048576
cpu_count 2
cursor_sharing exact
hash_area_size 131072
optimizer_dynamic_sampling 2
optimizer_features_enable 10.2.0.3
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_mode first_rows_1
optimizer_secure_view_merging true
parallel_ddl_mode enabled
parallel_dml_mode disabled
parallel_execution_enabled true
parallel_query_mode enabled
parallel_threads_per_cpu 2
pga_aggregate_target 204800 KB
query_rewrite_enabled true
query_rewrite_integrity enforced
skip_unusable_indexes true
sort_area_retained_size 0
sort_area_size 65536
sqlstat_enabled true
star_transformation_enabled false
statistics_level typical
workarea_size_policy auto
-Thanks
Geek DBA
As per documentation its 30 seconds
The parameter is (from 11gr2) is parallel_min_time_threshold which invokes the automatic degree of parallelism when the query execution goes beyond 30 seconds.
Nevertheless, whether you have object parallelism (table/index degree) set or not, if parallel_degree_policy is set to Limited or Auto, Oracle uses parallelism for any queries depends on the resources available if not it will wait until the resources available. So when it needed it further check that execution time goes beyond 30 seconds then DOP will be invoked.
This is 11g behavior, not sure if something exists prior to 11g.
The 11gR2 optimizer will first determine if the query could indeed benefit from parallel operations and what would be an appropriate DOP value. Oracle 11gR2 will next ascertain if the query’s estimated execution time is likely to run longer than the acceptable value (in seconds) for PARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources – for example, all possible parallel execution threads, or all CPUs in a clustered environment – at the cost of other non-parallelizable operations.
BTW, the documentation say’s 30 secs where in the metalink note 1264548.1 says 10 secs
-Thanks
Geek DBA
Came across this good table while scrolling the documentation,
When you need to do a parallel DML and that involves partitions, give a serious thought about the DML locks that can acquire. Below table gives you a handy info for the same.
Source:- here
| Type of Statement |
Coordinator Process Acquires: |
Each Parallel Execution Server Acquires: |
| Parallel UPDATE or DELETE into partitioned table; WHERE clause pruned to a subset of partitions or subpartitions |
1 table lock SX
1 partition lock X for each pruned partition or subpartition |
1 table lock SX
1 partition lock NULL for each pruned partition or subpartition owned by the query server process
1 partition-wait lock S for each pruned partition or subpartition owned by the query server process |
| Parallel row-migrating UPDATE into partitioned table; WHERE clause pruned to a subset of partitions or subpartitions |
1 table lock SX
1 partition X lock for each pruned partition or subpartition
1 partition lock SX for all other partitions or subpartitions |
1 table lock SX
1 partition lock NULL for each pruned partition or subpartition owned by the query server process
1 partition-wait lock S for each pruned partition owned by the query server process
1 partition lock SX for all other partitions or subpartitions |
| Parallel UPDATE, MERGE, DELETE, or INSERT into partitioned table |
1 table lock SX
Partition locks X for all partitions or subpartitions |
1 table lock SX
1 partition lock NULL for each partition or subpartition
1 partition-wait lock S for each partition or subpartition |
| Parallel INSERT into partitioned table; destination table with partition or subpartition clause |
1 table lock SX
1 partition lock X for each specified partition or subpartition |
1 table lock SX
1 partition lock NULL for each specified partition or subpartition
1 partition-wait lock S for each specified partition or subpartition |
| Parallel INSERT into nonpartitioned table |
1 table lock X |
None |
For example:-
Consider a table with 600 partitions running with a DOP of 100. Assume all partitions are involved in a parallel UPDATE or DELETE statement with no row-migrations.
The coordinator acquires:
- 1 table lock SX
- 600 partition locks X
Total server processes acquire:
- 100 table locks SX
- 600 partition locks NULL
- 600 partition-wait locks S
-Hope this helps
Geek DBA
|
Oracle 23c New Features for DBA's
If you access only index columns, index full scan like case 2(a) above will be possible.
Follow Me!!!