0 Comments
I have read the interesting article from Riyaj today. He was explainig how the clusterware is tarted.
Here I will sum some information about what I have learned from the Tanel Poder's video about the ORA-4031 error.
There is an undocumented parameter in oracle, which can set the background process to higher priority (from the time shared process to round robin process(real time)).
Here are the processes running in the RR mode: [SODS1 oraspre@excdbadm01:~]> ps -eouser,pid,ppid,opri,comm,args,class|grep SODS|grep RR oraspre 125569 1 58 oracle ora_vktm_SODS1 RR oraspre 125673 1 58 oracle ora_lms0_SODS1 RR oraspre 125687 1 58 oracle ora_lms1_SODS1 RR All other are running in the TS mode. Hidden parameter for changing the parameter priority: INDX I_HEX NAME VALUE DESCRIPTION ---------- ----- -------------------------------------------------- ------------------------------ ---------------------------------------------------------------------- 31 1F _wait_yield_hp_mode yield Wait Yield - High Priority Mode 197 C5 _high_priority_processes LMS*|VKTM High Priority Process Name Mask There is one hint you can use if you want to seee the number of executions in the execution plan of the query.
It's the GATHER_PLAN_STATISTICS hint. Query: select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id; Execution plan: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID bn9y09ykff9m2, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_NL(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id Plan hash value: 1647061426 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 135 (100)| 132 |00:00:00.01 | 12 | | | | | 1 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 12 | | | | | 2 | NESTED LOOPS | | 1 | 132 | 567K| 135 (0)| 132 |00:00:00.01 | 9 | | | | | 3 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 5 | 1025K| 1025K| | |* 4 | INDEX RANGE SCAN | T | 132 | 1 | | 0 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| | | 5 | TABLE ACCESS BY INDEX ROWID| T | 132 | 1 | 2202 | 1 (0)| 132 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."USER_ID"="B"."USER_ID") - dynamic sampling used for this statement (level=2) Here you can see, how the nested loop works. For each row in the table a it has to run the index range scan on the table b. So it has to start the index range scan 132 times. On the other side, the hash join will buffer the output of the first table and tahan comapre it to the outup of the second table. So all the operations will be executed only once. Query: select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id; Execution plan: PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID g9jrn9h5mzu73, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS LEADING(a) USE_HASH(b) FULL(a) INDEX(b) */ * from t a,t b where a.user_id=b.user_id Plan hash value: 3043401287 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 9 (100)| 132 |00:00:00.01 | 12 | | | | |* 1 | HASH JOIN | | 1 | 132 | 567K| 9 (0)| 132 |00:00:00.01 | 12 | 1780K| 1780K| 1262K (0)| | 2 | TABLE ACCESS STORAGE FULL | T | 1 | 132 | 283K| 3 (0)| 132 |00:00:00.01 | 4 | 1025K| 1025K| | | 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 132 | 283K| 6 (0)| 132 |00:00:00.01 | 8 | | | | | 4 | INDEX FULL SCAN | T | 1 | 132 | | 1 (0)| 132 |00:00:00.01 | 2 | 1025K| 1025K| | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."USER_ID"="B"."USER_ID") - dynamic sampling used for this statement (level=2) |
NiscoHere, I will write some things I have learned and dont want to forget them :) Archives
September 2015
Categories |