[JBoss JIRA] Commented: (TEIID-1726) Requesting ability to add/use Oracle hints in EDS
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1726?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1726:
---------------------------------------
based on your comments the refined set of options are:
- targeted use of native query features, see TEIID-669. Now that designer has flexible extension metadata, its quite easy for us to add handling for source procedures/views where the native sql is defined in designer. However any hints in the sql would always be applied and not contextually. Exposing source procedures allowing for dynamic sql (like what is done for MDX) could also be done, but that breaks all abstraction and would not integrate well.
- add support for the multi-hint comment container (alternative option #2 above). Unless some other syntax was used to separate the hints, a parsing change would be needed to support nested comments (see below). And without additional considerations, this would be non-positional in the source sql, so it really only applies to Oracle style hints. SQL Server, db2, mysql, Teiid, etc. make use of positional/syntax related hints. Referencing columns/tables as they appear in the user query would also add difficulty since we would have to introduce some escape syntax then parse/update the hint once the source query is planned - e.g. SELECT /*+ sh my-oracle:/*+LEADING(@BAL@) ALL_ROWS*/ */ ...
- implement TEIID handling for LEADING/ALL_ROWS (although the specifics of how tables/views are referenced would take more thought) as they are relevant for EDS queries (although our latency/processing tradeoff in union processing is fairly effective), and add pushdown propagation for the hints. As you point out this of course lacks generality.
- instead of using Java to customize translators, make use of something akin to DB2 optimization profiles, which has some tie in to alternative option #2/#3. The translator could be configured with a file that describes hint application. The most simplistic rules could just match the entire query or be regular expression matching the source query, e.g.:
<rule match="SELECT (.*) FROM BAL (.*) UNION SELECT (.*)" replace="SELECT /*+ALL_ROWS+/ $1 FROM BAL $2 UNION SELECT $3"/>
The tie to #2/#3 would be to take advantage of additional metadata / and or allow for the user to drive the hint application:
<rule user_hint="loop" match="INNER JOIN" replace="INNER LOOP JOIN"/>
which could handle simplistic application of SQL Server loop join hints (assuming that the translator was configured to expect ANSI FROM clauses), e.g.:
SELECT /*+ sh my-ssql:/+*loop*/ */ ...
The benefit here is that this doesn't require breaking any abstraction and can be done on an as needed basis without modifying vdbs or the client application, but it could be brittle and there are still a lot of details left out here, such as rule precedence, rule file format, etc.
> Requesting ability to add/use Oracle hints in EDS
> -------------------------------------------------
>
> Key: TEIID-1726
> URL: https://issues.jboss.org/browse/TEIID-1726
> Project: Teiid
> Issue Type: Feature Request
> Components: Server
> Affects Versions: 7.4.1
> Reporter: Debbie Steigner
>
> Requesting the ability to use Oracle hints inside virtual transformations and enduser queries in EDS and Teiid Designer
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month
[JBoss JIRA] Resolved: (TEIID-1728) Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1728?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1728.
-----------------------------------
Assignee: Steven Hawkins
Fix Version/s: 7.4.1
7.6
Resolution: Done
The issue was with the ExpirationAwareCache. The first thing that I noticed, which could be a KI for 7.4 and earlier is that if the user were to take out the default eviction config from the xml - then no evictions would be processed. This is because only the cache root is used to determine if the eviction thread is run. On a related note for 7.4 and older is that TTLs will not be respected if they are less than the eviction wake up interval.
For 7.4.1 and later (including 7.1.1.CP3) the issue is that region activation of a default active region removes the region eviction configuration from the eviction thread. This affects both mat views (until 7.6) and cached results. The code was altered to ensure that the eviction configuration is associated regardless.
Finally a fail-safe expiration check was added to the get method to ensure that expired items aren't returned - regardless of what the default eviction settings are for the cache root.
> Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-1728
> URL: https://issues.jboss.org/browse/TEIID-1728
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 7.4.1
> Environment: SOA-P 5.2 ER3, Production configuration
> Reporter: Paul Nittel
> Assignee: Steven Hawkins
> Fix For: 7.4.1, 7.6
>
>
> With the following transformation SQL, regardless of the time allowed to elapse, the cache does not expire/invalidate.
> I'm running the production profile. Here's the transformation SQL text:
> /*+ cache(ttl:30000) */
> SELECT
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE, CONCAT2(PartsSourceB.SUPPLIER.SUPPLIER_CITY, CONCAT2(', ', PartsSourceB.SUPPLIER.SUPPLIER_STATE)) AS City_State
> FROM
> PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
> WHERE
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID
> It's not expiring/invalidating the cache unless I CALL SYSADMIN.refreshMatView(viewname=>'PartsVirtual.SupplierInfo', invalidate=>true)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month
[JBoss JIRA] Commented: (TEIID-1728) Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1728?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1728:
---------------------------------------
>From an email with Paul, what's seen in the logs is "Loaded materialized view table #MAT_PARTSVIRTUAL.SUPPLIERINFO from cached contents from another clustered node.". However the only entry that can be present is the current value.
This seems to be an issue with the ExpirationAwareCache not working. If the TTL/expiration value were respected on the cache entry, then it should no longer be returned.
> Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-1728
> URL: https://issues.jboss.org/browse/TEIID-1728
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 7.4.1
> Environment: SOA-P 5.2 ER3, Production configuration
> Reporter: Paul Nittel
>
> With the following transformation SQL, regardless of the time allowed to elapse, the cache does not expire/invalidate.
> I'm running the production profile. Here's the transformation SQL text:
> /*+ cache(ttl:30000) */
> SELECT
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE, CONCAT2(PartsSourceB.SUPPLIER.SUPPLIER_CITY, CONCAT2(', ', PartsSourceB.SUPPLIER.SUPPLIER_STATE)) AS City_State
> FROM
> PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
> WHERE
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID
> It's not expiring/invalidating the cache unless I CALL SYSADMIN.refreshMatView(viewname=>'PartsVirtual.SupplierInfo', invalidate=>true)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month
[JBoss JIRA] Commented: (TEIID-1728) Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1728?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1728:
---------------------------------------
This was addressed in 7.6 by TEIID-1673
> Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-1728
> URL: https://issues.jboss.org/browse/TEIID-1728
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 7.4.1
> Environment: SOA-P 5.2 ER3, Production configuration
> Reporter: Paul Nittel
>
> With the following transformation SQL, regardless of the time allowed to elapse, the cache does not expire/invalidate.
> I'm running the production profile. Here's the transformation SQL text:
> /*+ cache(ttl:30000) */
> SELECT
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE, CONCAT2(PartsSourceB.SUPPLIER.SUPPLIER_CITY, CONCAT2(', ', PartsSourceB.SUPPLIER.SUPPLIER_STATE)) AS City_State
> FROM
> PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
> WHERE
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID
> It's not expiring/invalidating the cache unless I CALL SYSADMIN.refreshMatView(viewname=>'PartsVirtual.SupplierInfo', invalidate=>true)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month
[JBoss JIRA] Resolved: (TEIID-1729) EDS XML as source - results returned on 1st query only, subsequent query returns 0 rows - Streaming logic problem
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1729?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1729.
-----------------------------------
Resolution: Done
optimizing xmltable was modifying the pathmap, which even without streaming was causing document projection to not be used. With streaming no results were returned however.
Fixed by using a new pathmap to perform the optimization analysis.
This should be considered for a 7.5 patch.
> EDS XML as source - results returned on 1st query only, subsequent query returns 0 rows - Streaming logic problem
> ------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-1729
> URL: https://issues.jboss.org/browse/TEIID-1729
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1, 7.5
> Reporter: Warren Gibson
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 7.4.1, 7.6
>
>
> Submitted the following queries to SOAP-5.2 ER3 server:
> SELECT * FROM vParts
> SELECT * FROM vParts where Part_Name = 'windshield'
> SELECT * FROM vParts
> Results were returned on the first query but 0 rows returned on the other queries. this can be
> duplicated.
> The log file, PartsData.xml file and VDB are attached.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month
[JBoss JIRA] Commented: (TEIID-1726) Requesting ability to add/use Oracle hints in EDS
by Alan Fitton (JIRA)
[ https://issues.jboss.org/browse/TEIID-1726?page=com.atlassian.jira.plugin... ]
Alan Fitton commented on TEIID-1726:
------------------------------------
Hello Steve,
Thanks for looking into this issue and explaining the options. I did include some detail in the support ticket this issue was created based on. I'll send you the ticket number privately and also some SQL (which I can't share publicly) which I hope will demonstrate what we're trying to do.
The specific part of the query where the hint applies is below (this is just one example, we have others which is why a simple way to push through any hint would be useful).
UNION ALL
SELECT /*+ LEADING(bal) ALL_ROWS */
In the example above, I believe it would be necessary for the column names to be translated to the aliases used by EDS (I understand this probably complicates things slightly, sorry!).
As I mentioned in the ticket, we're aware of option 1 but this isn't an approach we would like to take. Mainly because we're supposed to be offering a service to the whole organisation, who shouldn't need knowledge of what optimisations are needed, but also (in our immediate project) because it's inconvenient to have to push driver specific features through our Spring JDBC abstraction. We would ideally like to apply Oracle hints on EDS views.
Option 2 sounds like it may have potential use for us. We're not doing updates through EDS, and it sounds like this would work on views. An example or where and how to embed the hint using this method would be appreciated, as I would have expected the translator or engine to strip this out.
Option 3 is something we would like to avoid, but could be an acceptable solution until there's a better one. If we did go down this route in the meantime, we would probably require our changes to be reviewed and know our usage is supported.
Alternative option #1: This sounds like it could be a very useful feature to us, although not the ideal fix for wanting to push hints through. We generally don't want to bypass the EDS query planner completely as it works well in most cases, but this sounds like it would offer a good fallback for us. There have been a few instances where we couldn't get EDS to perform the source query quite as we would like, and a native query would be a desirable solution to that.
Alternative option #2: This sounds ideal.
Alternative option #3: Unfortunately I don't think we want to go down this avenue every time we find a new hint we want to use, there's potentially many other hints that we will want to use when use of EDS increases in the organisation. We would like a generic way to do this.
If you have any more questions please let me know.
> Requesting ability to add/use Oracle hints in EDS
> -------------------------------------------------
>
> Key: TEIID-1726
> URL: https://issues.jboss.org/browse/TEIID-1726
> Project: Teiid
> Issue Type: Feature Request
> Components: Server
> Affects Versions: 7.4.1
> Reporter: Debbie Steigner
>
> Requesting the ability to use Oracle hints inside virtual transformations and enduser queries in EDS and Teiid Designer
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 2 months
[JBoss JIRA] Moved: (TEIID-1728) Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-1728?page=com.atlassian.jira.plugin... ]
Van Halbert moved SOA-3306 to TEIID-1728:
-----------------------------------------
Project: Teiid (was: JBoss Enterprise SOA Platform)
Key: TEIID-1728 (was: SOA-3306)
Affects Version/s: 7.4.1
(was: 5.2.0.ER3)
Component/s: Server
(was: EDS)
Security: (was: Public)
> Materialized View cache TTL (in a transformation) does not expire/invalidate the cache automatically
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-1728
> URL: https://issues.jboss.org/browse/TEIID-1728
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 7.4.1
> Environment: SOA-P 5.2 ER3, Production configuration
> Reporter: Paul Nittel
>
> With the following transformation SQL, regardless of the time allowed to elapse, the cache does not expire/invalidate.
> I'm running the production profile. Here's the transformation SQL text:
> /*+ cache(ttl:30000) */
> SELECT
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID, PartsSourceA.SUPPLIER_PARTS.PART_ID, PartsSourceA.SUPPLIER_PARTS.QUANTITY, PartsSourceA.SUPPLIER_PARTS.SHIPPER_ID, PartsSourceB.SUPPLIER.SUPPLIER_NAME, PartsSourceB.SUPPLIER.SUPPLIER_STATUS, PartsSourceB.SUPPLIER.SUPPLIER_CITY, PartsSourceB.SUPPLIER.SUPPLIER_STATE, CONCAT2(PartsSourceB.SUPPLIER.SUPPLIER_CITY, CONCAT2(', ', PartsSourceB.SUPPLIER.SUPPLIER_STATE)) AS City_State
> FROM
> PartsSourceA.SUPPLIER_PARTS, PartsSourceB.SUPPLIER
> WHERE
> PartsSourceA.SUPPLIER_PARTS.SUPPLIER_ID = PartsSourceB.SUPPLIER.SUPPLIER_ID
> It's not expiring/invalidating the cache unless I CALL SYSADMIN.refreshMatView(viewname=>'PartsVirtual.SupplierInfo', invalidate=>true)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 2 months
[JBoss JIRA] Commented: (TEIID-1726) Requesting ability to add/use Oracle hints in EDS
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-1726?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1726:
---------------------------------------
There is a lot more detail that is needed for this issue. For review the current approaches to source hints are:
1. use the execution payload to associate an Oracle hint with the source query. Requires JDBC client interaction and is only useful in situations where the hint is applicable to all Oracle source queries from that user query. This would typically be single source usage. From a general perspective the drawbacks are - requires JDBC logic, it's oracle specific, you have no control over the hint positioning, you have no control over which source queries receive the hint.
2. Modify table name in source values to include hints. The drawbacks are - hint position will be in the from clause, a valid source query may not be created in some update situations, and the hint is always applied regardless of the situation.
3. Customize the translator to look for known situations where hints are applicable. The drawback is that custom code is required.
Alternatives or possible enhancements to the above include:
1. Implement the native query feature. This would allow the user to embed the appropriate SQL at the physical model or possibly to issue a direct invocation through a procedure. The drawbacks here are that native query physical tables would still always apply the hint and procedures inhibit encapsulation.
2. Add a hint container with the ability to specify translator names and/or possibly nodeids/table aliases (form the source plans), e.g.
/*+ sh oracle:... 10:... */ SELECT ...
We would then ensure that relevant translators would append the hints. Source validation would be needed to ensure that SQL is not being injected. Drawbacks here include that node ids can change whenever the source plan is different, and relying just on translator names is not sufficient to distinguish multiple queries sent to the same source.
3. Have each hint application scenario addressed as an enhancement utilizing extension metadata. If the user can describe the hint and the situation when it should be applied, then we could add metadata/translator logic to automatically handle the case - or at least apply the hint when certain extension metadata is present and the source query has a known form.
Feedback will help determine what path(s) should be taken.
> Requesting ability to add/use Oracle hints in EDS
> -------------------------------------------------
>
> Key: TEIID-1726
> URL: https://issues.jboss.org/browse/TEIID-1726
> Project: Teiid
> Issue Type: Feature Request
> Components: Server
> Affects Versions: 7.4.1
> Reporter: Debbie Steigner
>
> Requesting the ability to use Oracle hints inside virtual transformations and enduser queries in EDS and Teiid Designer
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 2 months