Home » RDBMS Server » Performance Tuning » IM Column Store ( indexes,lob data) (Oracle 12.1.0.2)
IM Column Store ( indexes,lob data) [message #660475] |
Sat, 18 February 2017 02:40 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
Hi,
If we use IM Column store feature for a table:
1- does it cache indexes related to table ? (after instance startup when use critical option)
2- does it cache lob data?(after instance startup when use critical option)
Thanks in advance.
|
|
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660479 is a reply to message #660478] |
Sat, 18 February 2017 03:54 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
chad_2000 wrote on Sat, 18 February 2017 09:54Thanks, and thanks for response.
is there any way that caches indexes after starting instance?
Quote:You cannot use index access to the in-memory column store, only full table scans.
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660481 is a reply to message #660480] |
Sat, 18 February 2017 04:02 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The in-memory column store caches columns. The clue is in the name, you know. You can drop all your indexes, or you can create indexes on every combination of every column: the in-memory column store will function the same way. Table scans only. Index searches (other than fast full scan) use single block indirect IO, through buffer cache.
This is basic stuff, was there something in the docs that didn't make sense? Or in your tests?
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660484 is a reply to message #660483] |
Sat, 18 February 2017 04:27 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Ah, right, thank you for explaining.
In my experience, the most common "mistake" made by Oracle's cost based optimizer is excessive use of indexes (typically, index driven nested loop joins when scan driven hash joins would be astronomically faster) and that may be what you are seeing. There are also a number of common "mistakes" made by developers: use of SQL constructs that are known to be inefficient.
Using the IM column store will speed up scan access paths, but you aren't getting scans at all. I would start by tuning the SQL. Some guidelines here, http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660486 is a reply to message #660475] |
Sat, 18 February 2017 05:37 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
For example a query is:
SELECT /*+ PARALLEL (2) */ p_id FROM mt_products WHERE 1=1
AND (dest_id IN (select * from table(:in_dest_ids)) AND ((1=1) OR :in_region_ids IS NULL))
AND ((1=1) OR :order_id IS NULL))
And execuation plan is:
------------------------------------------------------------------------------------------------------------------------------------- --
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- --
| 0 | SELECT STATEMENT | | 103M| 1278M| 141K (1)| 00:00:06 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 103M| 1278M| 141K (1)| 00:00:06 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 103M| 1278M| 141K (1)| 00:00:06 | Q1,00 | PCWC | |
|* 4 | INDEX RANGE SCAN | IX_MT_PRODUCTS_DEST_PID | 103M| 1278M| 141K (1)| 00:00:06 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------- --
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((("DEST_ID"=1 OR "DEST_ID"=3 OR "DEST_ID"=8 OR "DEST_ID"=9 OR
"DEST_ID"=12 OR "DEST_ID"=22 OR "DEST_ID"=41 OR "DEST_ID"=61))
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 2 because of hint
--moderator update: corrected [quote] tags to [code]
[Updated on: Sat, 18 February 2017 06:17] by Moderator Report message to a moderator
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660487 is a reply to message #660486] |
Sat, 18 February 2017 07:08 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
That is an unusual plan. It should not be possible to parallelize an index range scan unless the index is partitioned, but that plan does not show any partition iteration. Can you give a bit more info about the table and the index? Also, any particular reason for using degree of 2? And what does the CBO do without the hint?
There are several possibilities for tuning this. I would first try hinting an index fast full scan. That might make your use of parallelism much more efficient and allow for higher parallel degree.
You could re-write the query to use UNION ALL rather the IN list. Or perhaps you could hint USE_CONCAT.
I would also try setting parallel_degree_policy=auto (or adaptive) to enable in-memory parallel scans and auto DOP, rather than hinting a degree. Be sure to calibrate IO and gather system stats first.
Then with auto policy enabled, you can enable the db_big_table_cache_percent_target, which (unlike the in-memory option) doesn't cost anything.
That's just a few suggestions, they will be many more, try them independently and in combination. Eventually one should come up with a solution.
|
|
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660493 is a reply to message #660475] |
Sun, 19 February 2017 00:42 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
Quote:
That is an unusual plan. It should not be possible to parallelize an index range scan unless the index is partitioned, but that plan does not show any partition iteration. Can you give a bit more info about the table and the index? Also, any particular reason for using degree of 2? And what does the CBO do without the hint?
There are several possibilities for tuning this. I would first try hinting an index fast full scan. That might make your use of parallelism much more efficient and allow for higher parallel degree.
You could re-write the query to use UNION ALL rather the IN list. Or perhaps you could hint USE_CONCAT.
I would also try setting parallel_degree_policy=auto (or adaptive) to enable in-memory parallel scans and auto DOP, rather than hinting a degree. Be sure to calibrate IO and gather system stats first.
Then with auto policy enabled, you can enable the db_big_table_cache_percent_target, which (unlike the in-memory option) doesn't cost anything.
That's just a few suggestions, they will be many more, try them independently and in combination. Eventually one should come up with a solution.
Thank you so much.
Table definition:
CREATE TABLE "MT_PRODUCTS"
( "P_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ,
"DEST_ID" NUMBER,
"REGION_ID" NUMBER,
"ORDER_ID" NUMBER,
"GROUP_ID" NUMBER,
"DATE_ID" NUMBER,
"P_NAME" VARCHAR2(50 CHAR),
"P_CODE" VARCHAR2(75 CHAR),
"P_DESC" VARCHAR2(255 CHAR)
)
One of indexes is:
CREATE INDEX "IX_MT_PRODUCTS_DEST_PID" ON "MT_PRODUCTS" ("DEST_ID", "P_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DATA"
PARALLEL ;
Tabls is partitioned on REGION_ID column ( by list ) and sub partition (
by range) on DATE_ID.
yes, Index is not partitioned, also we have created other indexes based on different where clause filters.
I changed query based on index_ffs hint,union all and use_concat, no improvement achieved.
Quote:Also, any particular reason for using degree of 2? And what does the CBO do without the hint?
yes, you are right, we have not seen any improvement using this hint.
Quote:You are right of course. I didn't read the title of the topic, I'm sorry. I meant, it should be possible to parallelism index range scan in 12c also without partitioning.
thanks,could you explain how can do that please?
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660495 is a reply to message #660491] |
Sun, 19 February 2017 01:50 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
LNossov wrote on Sat, 18 February 2017 21:23You are right of course. I didn't read the title of the topic, I'm sorry. I meant, it should be possible to parallelize index range scan in 12c also without partitioning. I can't get a parallel range scan against a non-partitioned index, and the 12.1.0.2 docs sayQuote:A SELECT statement can be executed in parallel only if the following conditions are satisfied:
The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
At least one table specified in the query requires one of the following:
A full table scan
An index range scan spanning multiple partitions
A parallel table function so there must be something more going on. I cannot see how parallel query can ever work with a range scan: the nature of the single block reads navigating from block to block would seem to make it impossible.
I would welcome being corrected on this.
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660496 is a reply to message #660475] |
Sun, 19 February 2017 03:02 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
1- Create GLOBAL PARTITION BY HASH on IX_MT_PRODUCTS_DEST_PID
2- Change query using union all
SELECT p_id FROM mt_products where dest_id=1 union all
SELECT p_id FROM mt_products where dest_id=3 union all
SELECT p_id FROM mt_products where dest_id=8 union all
SELECT p_id FROM mt_products where dest_id=9 union all
SELECT p_id FROM mt_products where dest_id=10 union all
SELECT p_id FROM mt_products where dest_id=12 union all
SELECT p_id FROM mt_products where dest_id=22 union all
SELECT p_id FROM mt_products where dest_id=41
Execution plan:
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 188M| 1793M| 181K (1)| 00:00:08 | | | | | |
| 1 | UNION-ALL | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 10 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 1 | 10 | 2 (0)| 00:00:01 | 24 | 24 | Q1,00 | PCWC | |
|* 5 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 1 | 10 | 2 (0)| 00:00:01 | 24 | 24 | Q1,00 | PCWP | |
| 6 | PX COORDINATOR | | | | | | | | | | |
| 7 | PX SEND QC (RANDOM) | :TQ20000 | 3753K| 35M| 6707 (1)| 00:00:01 | | | Q2,00 | P->S | QC (RAND) |
| 8 | PX BLOCK ITERATOR | | 3753K| 35M| 6707 (1)| 00:00:01 | 4 | 4 | Q2,00 | PCWC | |
|* 9 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 3753K| 35M| 6707 (1)| 00:00:01 | 4 | 4 | Q2,00 | PCWP | |
| 10 | PARTITION HASH SINGLE | | 4097K| 39M| 10854 (1)| 00:00:01 | 15 | 15 | | | |
|* 11 | INDEX RANGE SCAN | IX_MT_PRODUCTS_DEST_PID | 4097K| 39M| 10854 (1)| 00:00:01 | 15 | 15 | | | |
| 12 | PX COORDINATOR | | | | | | | | | | |
| 13 | PX SEND QC (RANDOM) | :TQ30000 | 240K| 2347K| 214 (1)| 00:00:01 | | | Q3,00 | P->S | QC (RAND) |
| 14 | PX BLOCK ITERATOR | | 240K| 2347K| 214 (1)| 00:00:01 | 14 | 14 | Q3,00 | PCWC | |
|* 15 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 240K| 2347K| 214 (1)| 00:00:01 | 14 | 14 | Q3,00 | PCWP | |
| 16 | PX COORDINATOR | | | | | | | | | | |
| 17 | PX SEND QC (RANDOM) | :TQ40000 | 79222 | 773K| 62 (0)| 00:00:01 | | | Q4,00 | P->S | QC (RAND) |
| 18 | PX BLOCK ITERATOR | | 79222 | 773K| 62 (0)| 00:00:01 | 26 | 26 | Q4,00 | PCWC | |
|* 19 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 79222 | 773K| 62 (0)| 00:00:01 | 26 | 26 | Q4,00 | PCWP | |
| 20 | PX COORDINATOR | | | | | | | | | | |
| 21 | PX SEND QC (RANDOM) | :TQ50000 | 1 | 10 | 2 (0)| 00:00:01 | | | Q5,00 | P->S | QC (RAND) |
| 22 | PX BLOCK ITERATOR | | 1 | 10 | 2 (0)| 00:00:01 | 6 | 6 | Q5,00 | PCWC | |
|* 23 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 1 | 10 | 2 (0)| 00:00:01 | 6 | 6 | Q5,00 | PCWP | |
| 24 | PX COORDINATOR | | | | | | | | | | |
| 25 | PX SEND QC (RANDOM) | :TQ60000 | 89M| 858M| 65228 (1)| 00:00:03 | | | Q6,00 | P->S | QC (RAND) |
| 26 | PX BLOCK ITERATOR | | 89M| 858M| 65228 (1)| 00:00:03 | 22 | 22 | Q6,00 | PCWC | |
|* 27 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 89M| 858M| 65228 (1)| 00:00:03 | 22 | 22 | Q6,00 | PCWP | |
| 28 | PX COORDINATOR | | | | | | | | | | |
| 29 | PX SEND QC (RANDOM) | :TQ70000 | 89M| 857M| 98198 (1)| 00:00:04 | | | Q7,00 | P->S | QC (RAND) |
| 30 | PX BLOCK ITERATOR | | 89M| 857M| 98198 (1)| 00:00:04 | 15 | 15 | Q7,00 | PCWC | |
|* 31 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 89M| 857M| 98198 (1)| 00:00:04 | 15 | 15 | Q7,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("dest_id"=1)
9 - filter("dest_id"=3)
11 - access("dest_id"=8)
15 - filter("dest_id"=9)
19 - filter("dest_id"=10)
23 - filter("dest_id"=12)
27 - filter("dest_id"=22)
31 - filter("dest_id"=41)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
4- Change query using use_cancat
SELECT /*+use_cancat */ p_id FROM mt_products where dest_id=1 or dest_id=3 or dest_id=8 or dest_id=10 or dest_id=12 or dest_id=22 or dest_id=41
Execution plan:
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 187M| 1790M| 171K (2)| 00:00:07 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 187M| 1790M| 171K (2)| 00:00:07 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 187M| 1790M| 171K (2)| 00:00:07 |KEY(I) |KEY(I) | Q1,00 | PCWC | |
|* 4 | INDEX FAST FULL SCAN| IX_MT_PRODUCTS_DEST_PID | 187M| 1790M| 171K (2)| 00:00:07 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DEST_ID"=1 OR "DEST_ID"=3 OR "DEST_ID"=8 OR "DEST_ID"=10 OR "DEST_ID"=12 OR "DEST_ID"=22 OR "DEST_ID"=41)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660497 is a reply to message #660496] |
Sun, 19 February 2017 03:44 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
So you have changed the index to a global hash partitioned index? That is a big change, and you can see that both your UNION ALL query and the original query (the one with the mis-spelled use_concat hint, which will not have had any effect) are now using properly parallelized index fast full scans. You could try for a higher degree of parallelism now.
I would also try hinting an index range scan with parallelism and the partitioned index: that should work very well.
What was the result of hinting a fast full scan with the original non-partitioned index?
You have missed providing the most important bit of information of all: how long does each version of the query take?
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660498 is a reply to message #660495] |
Sun, 19 February 2017 04:16 |
|
LNossov
Messages: 318 Registered: July 2011 Location: Germany
|
Senior Member |
|
|
John,
I'm pretty sure that I have read about this feature some months ago. It was very surprisingly for me too. Unfortunately I haven't check this myself (postponed for later). After your comment I tried to find again a source of this information but haven't found it. So the question is open. If I find any confirmation to this feature, I let you know about this.
[Updated on: Sun, 19 February 2017 04:23] Report message to a moderator
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660501 is a reply to message #660500] |
Sun, 19 February 2017 05:50 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
Quote:Note the hint is USE_CONCAT not USE_CANCAT, so it is not use here.
Thanks
Quote:
You have missed providing the most important bit of information of all: how long does each version of the query take?
# When Index is not partitioned
(Fetched record count = 180 Million)
Orginal Query : 45 Second
Orginal Query With Index_ffs Hint:62 Second
Union All: 57 Second
use_concat: 55 Second
# When Index is partitioned
(Fetched record count = 180 Million)
Orginal Query : 45 Second
Union All(with parallel hint): 48 Second
use_concat(with parallel hint): 45 Second
Quote:I would also try hinting an index range scan with parallelism and the partitioned index: that should work very well.
This impacts on Union All scheme and time reduced from 48 to 46.
[Updated on: Sun, 19 February 2017 06:06] Report message to a moderator
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660512 is a reply to message #660501] |
Mon, 20 February 2017 02:55 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Did you get the expected (or desired) execution plans? Those timing differences are probably not statistically significant.
Have you experimented with higher degrees of parallelism? When you use range scan against a partitioned index, or index fast fast full scan against either partitioned or non-partitioned index, parallel query with a higher degree may become a lot more effective.
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660534 is a reply to message #660475] |
Mon, 20 February 2017 07:29 |
|
chad_2000
Messages: 23 Registered: February 2017
|
Junior Member |
|
|
Quote:Did you get the expected (or desired) execution plans? Those timing differences are probably not statistically significant.
Have you experimented with higher degrees of parallelism? When you use range scan against a partitioned index, or index fast fast full scan against either partitioned or non-partitioned index, parallel query with a higher degree may become a lot more effective.
I tested different scheme with higher degrees, but no improvement achieved, for example one query is as follows:
SELECT /*+ USE_CONCAT INDEX_RS_ASC(mt_products,IX_MT_PRODUCTS_DEST_PID) PARALLEL(16) */ p_id
FROM mt_products WHERE 1=1 AND (des_id IN (1,3,8,9,10,12,22,41,61)) ;
Execution plan is:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184M| 1760M| 30557 (1)| 00:00:02 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 184M| 1760M| 30557 (1)| 00:00:02 | | | Q1,00 | P->S | QC (RAND) |
| 3 | INLIST ITERATOR | | | | | | | | Q1,00 | PCWC | |
| 4 | PX PARTITION HASH ITERATOR| | 184M| 1760M| 30557 (1)| 00:00:02 |KEY(I) |KEY(I) | Q1,00 | PCWC | |
|* 5 | INDEX RANGE SCAN | IX_MT_PRODUCTS_DEST_PID | 184M| 1760M| 30557 (1)| 00:00:02 |KEY(I) |KEY(I) | Q1,00 | PCWP | |
A case that can be noted is that parallel degree is downgraded from 16 to 8 with a hint about library cache lock.(As can be seen in the attached image file), however it did not happen for the original query.
-
Attachment: img.png
(Size: 33.11KB, Downloaded 1823 times)
[Updated on: Mon, 20 February 2017 07:30] Report message to a moderator
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660536 is a reply to message #660534] |
Mon, 20 February 2017 07:45 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The fact that your parallel query was downgraded suggests that you do not have enough PX servers available. How many do you have? Are they all busy? What is CPU usage like? how about the IO capacvity?
You have seen several execution plans, with different parallelism, and they are all about the same run time. If you are hitting some resource limits, it may be that there is nothing you can do to improve this without change to data design or hardware.
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660996 is a reply to message #660498] |
Fri, 03 March 2017 11:32 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
LNossov wrote on Sun, 19 February 2017 10:16John,
I'm pretty sure that I have read about this feature some months ago. It was very surprisingly for me too. Unfortunately I haven't check this myself (postponed for later). After your comment I tried to find again a source of this information but haven't found it. So the question is open. If I find any confirmation to this feature, I let you know about this.
Leonid, do you remember that we were questioning whether a scan of a non-partitioned index could be parallelized? I think I may have come across what you were thinking of. In 12.x, Oracle can't parallelize the search of the index, but it can parallelize the rowid lookups. See here, where the serial to parallel conversion occurs:127.0.0.1:1521/orclpdb> alter session set optimizer_features_enable='11.2.0.4';
Session altered.
127.0.0.1:1521/orclpdb> select /*+ parallel(2) */ * from employees where department_id=20;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 138 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 138 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=20)
Note
-----
- Degree of Parallelism is 1 because of hint
127.0.0.1:1521/orclpdb> alter session set optimizer_features_enable='12.1.0.1';
Session altered.
127.0.0.1:1521/orclpdb> select /*+ parallel(2) */ * from employees where department_id=20;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2676127558
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 138 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2 | 138 | 2 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 2 | 138 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 2 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 2 | | 1 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 8 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | | 1 (0)| 00:00:01 | Q1,00 | SCWP | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("DEPARTMENT_ID"=20)
Note
-----
- Degree of Parallelism is 2 because of hint
127.0.0.1:1521/orclpdb> Clever, isn't it?
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 08:20:44 CDT 2024
|