Home » RDBMS Server » Performance Tuning » IM Column Store ( indexes,lob data) (Oracle 12.1.0.2)
|
|
|
|
|
|
|
|
|
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 #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 #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 #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 #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 1853 times)
[Updated on: Mon, 20 February 2017 07:30] Report message to a moderator
|
|
|
|
|
Re: IM Column Store ( indexes,lob data) [message #660996 is a reply to message #660498] |
Fri, 03 March 2017 11:32   |
John Watson
Messages: 8965 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: Thu Feb 20 20:56:59 CST 2025
|