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 Go to next message
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 #660476 is a reply to message #660475] Sat, 18 February 2017 03:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You cannot use index access to the in-memory column store, only full table scans. Out of line LOBs cannot be cached in it.
Re: IM Column Store ( indexes,lob data) [message #660477 is a reply to message #660476] Sat, 18 February 2017 03:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sorry, I forgot to do the moderator bit:

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Re: IM Column Store ( indexes,lob data) [message #660478 is a reply to message #660475] Sat, 18 February 2017 03:54 Go to previous messageGo to next message
chad_2000
Messages: 23
Registered: February 2017
Junior Member
Thanks, and thanks for response.
is there any way that caches indexes after starting instance?

Re: IM Column Store ( indexes,lob data) [message #660479 is a reply to message #660478] Sat, 18 February 2017 03:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
chad_2000 wrote on Sat, 18 February 2017 09:54
Thanks, 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 #660480 is a reply to message #660479] Sat, 18 February 2017 03:56 Go to previous messageGo to next message
chad_2000
Messages: 23
Registered: February 2017
Junior Member
Not in IM scheme,
Others ways in default buffer cache?

[Updated on: Sat, 18 February 2017 03:59]

Report message to a moderator

Re: IM Column Store ( indexes,lob data) [message #660481 is a reply to message #660480] Sat, 18 February 2017 04:02 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #660483 is a reply to message #660481] Sat, 18 February 2017 04:18 Go to previous messageGo to next message
chad_2000
Messages: 23
Registered: February 2017
Junior Member
We don't use IM column store now.
Our queries return large number of rows( index range scan),The problem is that the queries take very long time at first execution (because of I/O) , so we need a way that decrease execution time for queries which are executed for first time
Re: IM Column Store ( indexes,lob data) [message #660484 is a reply to message #660483] Sat, 18 February 2017 04:27 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #660489 is a reply to message #660487] Sat, 18 February 2017 13:04 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
I think, it should be possible in 12. Is it 12c?
Re: IM Column Store ( indexes,lob data) [message #660490 is a reply to message #660489] Sat, 18 February 2017 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

IM column store has been introduced in 12.1.0.2, so the answer is yes (in addition, it is in the title of the topic).

[Updated on: Sat, 18 February 2017 13:28]

Report message to a moderator

Re: IM Column Store ( indexes,lob data) [message #660491 is a reply to message #660490] Sat, 18 February 2017 15:23 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You 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.
Re: IM Column Store ( indexes,lob data) [message #660493 is a reply to message #660475] Sun, 19 February 2017 00:42 Go to previous messageGo to next message
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 #660494 is a reply to message #660493] Sun, 19 February 2017 01:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I changed query based on index_ffs hint,union all and use_concat, no improvement achieved.
Please can you show what you did, and what happened?
Re: IM Column Store ( indexes,lob data) [message #660495 is a reply to message #660491] Sun, 19 February 2017 01:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
LNossov wrote on Sat, 18 February 2017 21:23
You 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #660500 is a reply to message #660498] Sun, 19 February 2017 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note the hint is USE_CONCAT not USE_CANCAT, so it is not use here.

Re: IM Column Store ( indexes,lob data) [message #660501 is a reply to message #660500] Sun, 19 February 2017 05:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 1319 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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #660605 is a reply to message #660536] Wed, 22 February 2017 03:15 Go to previous messageGo to next message
chad_2000
Messages: 23
Registered: February 2017
Junior Member
Thank you so much, Your solutions was very beneficial on execution of queries, If we have any further improvement I will report here, Again thank you for your attention.

Regards.
Re: IM Column Store ( indexes,lob data) [message #660996 is a reply to message #660498] Fri, 03 March 2017 11:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
LNossov wrote on Sun, 19 February 2017 10:16
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.
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?

Re: IM Column Store ( indexes,lob data) [message #660999 is a reply to message #660996] Sat, 04 March 2017 02:33 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello John,

I didn't find the article where I read about parallelism for index scans in 12c. I asked my colleagues, but nobody have head about this feature. With my tests I only was able to reproduce parallel table lookups via rowid (similar as you did it). I observed this feature also at the customer's databases.

So it seems that I misunderstood something Smile.

Best regards
Leonid
Previous Topic: Oracle 12c -Query CPU Cost is very high
Next Topic: Query became very slow
Goto Forum:
  


Current Time: Thu Mar 28 11:31:55 CDT 2024