-
1. Re: Issue with new transaction-detecting logic in Teiid 8.1.0 Final
shawkins Aug 29, 2012 8:48 AM (in response to manoj.m.agarwal)Manoj,
So there are two lines of thought here.
1. to determine if we are starting a transaction unnecessarily, what does your plan look like and what is your transaction isolation level? In general https://issues.jboss.org/browse/TEIID-2091 should reduce when transactions were started.
2. What does your full connection scenario look like? Are you setting the autoCommitTxn prior to obtaining a connection?
Steve
-
2. Re: Issue with new transaction-detecting logic in Teiid 8.1.0 Final
manoj.m.agarwal Aug 29, 2012 10:54 AM (in response to shawkins)Hi Steven,
Please find the relevent information for your questions-
1. to determine if we are starting a transaction unnecessarily, what does your plan look like and what is your transaction isolation level?
============================================================================
USER COMMAND:
SELECT * FROM chorus_metadata.chorus_role
----------------------------------------------------------------------------
OPTIMIZE:
SELECT * FROM chorus_metadata.chorus_role
----------------------------------------------------------------------------
GENERATE CANONICAL:
SELECT * FROM chorus_metadata.chorus_role
CANONICAL PLAN:
Project(groups=[chorus_metadata.chorus_role], props={PROJECT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
Source(groups=[chorus_metadata.chorus_role])
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[chorus_metadata.chorus_role], props={PROJECT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
Access(groups=[chorus_metadata.chorus_role])
Source(groups=[chorus_metadata.chorus_role])
============================================================================
EXECUTING RaiseAccess
AFTER:
Access(groups=[chorus_metadata.chorus_role], props={MODEL_ID=Schema name=chorus_metadata, nameInSource=null, uuid=tid:8d825ab1a54d-0df5a5a8-00000000})
Project(groups=[chorus_metadata.chorus_role], props={PROJECT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
Source(groups=[chorus_metadata.chorus_role])
============================================================================
EXECUTING AssignOutputElements
AFTER:
Access(groups=[chorus_metadata.chorus_role], props={MODEL_ID=Schema name=chorus_metadata, nameInSource=null, uuid=tid:8d825ab1a54d-0df5a5a8-00000000, OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
Project(groups=[chorus_metadata.chorus_role], props={PROJECT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
Source(groups=[chorus_metadata.chorus_role], props={OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME]})
============================================================================
EXECUTING CalculateCost
AFTER:
Access(groups=[chorus_metadata.chorus_role], props={MODEL_ID=Schema name=chorus_metadata, nameInSource=null, uuid=tid:8d825ab1a54d-0df5a5a8-00000000, OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], EST_CARDINALITY=-1.0})
Project(groups=[chorus_metadata.chorus_role], props={PROJECT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], EST_CARDINALITY=-1.0})
Source(groups=[chorus_metadata.chorus_role], props={OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], EST_COL_STATS={chorus_metadata.chorus_role.INSTALL_STATUS=[-1.0, -1.0], chorus_metadata.chorus_role.USER_ACCESS=[-1.0, -1.0], chorus_metadata.chorus_role.ROLE_NAME=[-1.0, -1.0], chorus_metadata.chorus_role.LICENSE_STATUS=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Access(groups=[chorus_metadata.chorus_role])
Project(groups=[chorus_metadata.chorus_role])
Source(groups=[chorus_metadata.chorus_role])
============================================================================
EXECUTING CollapseSource
AFTER:
Access(groups=[chorus_metadata.chorus_role], props={MODEL_ID=Schema name=chorus_metadata, nameInSource=null, uuid=tid:8d825ab1a54d-0df5a5a8-00000000, OUTPUT_COLS=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=SELECT chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME FROM chorus_metadata.chorus_role})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
AccessNode(0) output=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME] SELECT g_0.INSTALL_STATUS, g_0.USER_ACCESS, g_0.ROLE_NAME, g_0.LICENSE_STATUS, g_0.SCHEMA_NAME FROM chorus_metadata.chorus_role AS g_0
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[chorus_metadata.chorus_role.INSTALL_STATUS, chorus_metadata.chorus_role.USER_ACCESS, chorus_metadata.chorus_role.ROLE_NAME, chorus_metadata.chorus_role.LICENSE_STATUS, chorus_metadata.chorus_role.SCHEMA_NAME] SELECT g_0.INSTALL_STATUS, g_0.USER_ACCESS, g_0.ROLE_NAME, g_0.LICENSE_STATUS, g_0.SCHEMA_NAME FROM chorus_metadata.chorus_role AS g_0
============================================================================
2. What does your full connection scenario look like? Are you setting the autoCommitTxn prior to obtaining a connection?
Yes, i am setting the autoCommitTxn before getting connection and for doing so i tried it with two different methods(one at a time)-
TeiidDataSource.setAutoCommitTxn("OFF");
TeiidDataSource.setTransactionAutoWrap("OFF");
Which method i should use and what the difference between these two ? i see setTransactionAutoWrap() as deprecated in the API, so is setAutoCommitTxn() replacement of setTransactionAutoWrap() ?
Thanks !
Manoj -
3. Re: Issue with new transaction-detecting logic in Teiid 8.1.0 Final
shawkins Aug 29, 2012 11:31 AM (in response to manoj.m.agarwal)1. The plan that you're showing does not require a transaction. It this being run in a recursive scenario?
2. Yes, autoCommitTxn is the proper method to use. Setting it to off prior to obtaining your connection should be sufficient - unless you are running in a recursive scenario and need to affect the underlying connection.
-
4. Re: Issue with new transaction-detecting logic in Teiid 8.1.0 Final
rokhmanov Aug 30, 2012 9:46 AM (in response to shawkins)Steven, I think we know what is going on, this might be something related to our application logic. No need to worry about it for now, thanks.