Category Archives: Emiel van Bockel
Times Ten best command ever
After a week of learning how to fill a Times Ten database, you will find out that it all is rubbish and you want to start over.
Use the command:
ttdestroy <DATA_STORE>
And now create the database you wanted to have in the first place 
Bitmap indexes in Times Ten
Normally Oracle database can merge two different indexes to optimize a query when they are both bitmapped. In Times Ten it is not possible to let TT use two indexes to resolve a double where clause.
F.e.
Table T1(PK, FK1, FK2, sales)
You have created an index IDX1 on FK1, and an additional index IDX1 on FK2. The next query will only use the most discriminant index.Select sum(sales) from T1 where FK1 = x AND FK2 = y;
Let’s us say FK1 is the most discriminant. It will use only the index IDX1. So you it is not advised to use Bitmap indexes in TimesTen. Oracle advise is to use either RANGE or HASH.
For better performance, you should create an index IDX3 on FK1 and FK2 together, to make this query have the best performance.
Times Ten loading into memory
By default, Times Ten (TT) DATA STORE will be loaded into memory from DISK only during the first connection and unloaded after the connection is terminated.
In order to eliminate the loading times during the first access, it is recommended to set the RAMPOLICY to MANUAL, this setting ensure the Times Ten DATA STORE is loaded into memory and stays there unless manually unloaded.
The command to use is to “Set the RAMPOLICY” to MANUAL
ttadmin -rampolicy=manual <Times Ten DATA STORE Name>
TO load the database manually into memory from disk:
ttadmin -ramload <Times Ten DATA STORE Name>
TO unload the database manually into memory from disk:
ttadmin -ramunload <Times Ten DATA STORE Name>
Key Column already exists
When you are creating indexes in TimesTen that contains the same column as an earlier created index, you can get the following type of warnings:
Warning 2233:
Key columns of new index IDX2 are a prefix of the columns of existing index IDXPK consider dropping index IDX2
(Primary Key Index)
Warning 2234:
Key columns of existing index IDX1 are a prefix of the columns of new index IDX2 consider dropping index IDX1
(Index 1)
If you understand how to create indexes in TT, ignore this warning and go on
Exalytics Architecture Design
Before you are thinking of implementing Exalytics you need to take care of the architecture that suits your situation best.
The default Exalytics architecture is:
You have the following software components:
- Operating System Oracle Linux
- Fusion Middleware for Weblogic
- Oracle BI Foundation
- TimesTen
- (and Essbase but I skip this one for now)
Besides the software components you also need to have a Repository (RCU) installed in a Oracle 11g Database. That could ofcourse also be on an Exadata machine.
But before you start building a BI Environment you additional need to have a Development and Test environment. Either you buy 2 other Exalytics machines or you install multiple domains (independent DEV, TST and PRD) on the Exalytics Machine.
The multi domain architecture would look like:
You will need to install FMW, BI Foundation, TimesTen three times and three RCU’s in different Databases (schema’s).
From resource and risk perspective it is not advisable to put a development domain on a production environment. You would like to seperate the development environment from the Exalytics environment. The test environment must be installed on the Exalytics if you don’t have an additiona Exalytics. Else you are not able to test the high performance with the in-memory TimesTen. Or as Oracle calls it, you are not able to test the engineered systems performance.
So from development perspective you will create a non exalytics installation.
The dev/tst/prd architecture would look like:
You install FMW, BI Foundation and TimesTen on a seperate non Exalytics machine. You still need to have 3 RCU’s in different Oracle 11G Databases.
You only have to install FMW, BI Foundation and TimesTen twice on the Exalytics Machine. This also saves space on the Exalytics machine. For the ultimate performance you can bring up the test domain only when it’s needed to test. Ofcourse if it suits your specific business situation. Else you need to have also test running continuously.
There are some four con´s you should be aware of.
First of all there is a license issue here.
If you have a CPU based license for Exalytics. You need to buy new licenses for the Non Exalytics environment. That could be a named users license because normally you don’t need a lot of development licenses.
Then there is a versioning issue:
The current release strategy from Oracle is that the NON exalytics version is different from the Exalytics version. That is because the Engineered System (Exalytics) works different from a non engineered system. The BP1 is only a BI Foundation bundle patch. You can read in the release notes that this not for Exalytics. There will be a new Patch Set released that combines patches for Oracle Linux, Weblogic, BI Foundation and TimesTen at once. It is not released yet. I am very curious how this will be in the future for organizations that have a seperate non Exalytics development environment.
The last two con’s are functional differences between non-Exalytics and Exalytics.
Functionality that is specific for Exalytics is the Summary Advisor:
The summary advisor won’t work on a non-Exalytics machine. But that is not a huge problem. The summary advisor uses the RCU to gather usage statistics. I don´t think you will analyse your development RCU to gather usage statistics. You can use either the test or production environment to gather the statistics from the production RCU by using the summary advisor on the Exalytics. The results you can use to import into the development environment. I haven´t tested it yet, but I think this will be a fair route.
The second functional difference is the analytic functions for TimesTen:
These are specific functions for an in-memory TimesTen database. The BI Server will generate TimesTen in-memory optized statements. Normally performance is tested on a test environment and not on development. So I think this is not a problem too. But ofcourse you need to take it in consideration. I know it’s preferable that query’s are executed the same on dev as on test as on production. The future will tell if it’s gonna be a problem or not. For now I can accept this con’s of this architecture.
I think a lot of companies who are thinking of buying an Exalytics first need to take their wanted architecture in consideration. Depending on the requirements you either buy multiple Exalytics or install multiple domains on the exalytics with a seperate non Exalytics environment.
I hope this blog helps you deciding for the right architecture. Next week I will explain how to create multiple domains on the Exalytics.
Emiel
