Background
In 2018, I was working with a customer on sizing out an environment for analytics. They had QlikView already as well as some manual and ad hoc reporting Excel (and, I cringe, some side Access databases) but were looking for a solution that offered scalability as they wanted to expand reporting to field management.
The customer desired enterprise analytics functionality (data modeling, dashboards, self-service reporting, single version of truth, etc) to report on their multiple data sources. For the data warehouse, we chose to go with 8 OCPUs (Oracle CPUs) of DBCS – High Performance. We chose 2 OCPUs of OAC.
The partner finished the RPD in OAC and there was ~16GB of data in the data warehouse with a handful of dashboards.
Early 2020
Knowing the customer wanted to expand OAC to more people in the field, we wanted to visit them to get some numbers and determine if right-sizing needed to be done on the environment. With the number of concurrent users increasing as well as adding self-service capabilities, we determined more production OAC OCPUs should be added.
In addition the customer wanted to start running the dashboards on their full data set – around 64GB – versus the limited 16GB. We pitched the idea of moving from DBCS to ADW for the scalability, auto-bursting, and auto indexing.
To test this, we created a 4 OCPU OAC instance and a 4 OCPU ADW instance. Yes…you read that correctly, we went with half the OCPUs in their current production environment of DBCS. We had the partner move the full dataset over to ADW so I could alter the RPD to connect to ADW instead of DBCS.
To move the RPD connections, I simply used a utility in the BI Admin Tool.
Replace Column or Table in Logical Table Sources…Execute
Make sure you choose to “Replace whole table” then select the DBCS table on the left and replace it with the ADW table on the right. Click Finish.
Once done, the physical tables used in your “Business Model and Mapping” layer will now point to ADW instead of DBCS.
No need to redo the mappings!
Publish to the Cloud then wait for the results!
…Except we were not noticing any gains. At all. The times were the same as DBCS.
So I decided to implement Usage Tracking in OAC to see where the hang-ups were happening. After implementation, the times to run the dashboards were now taking 3-4 times longer than before Usage Tracking! Utilizing the “Monitor SQL” feature in ADW, we could see that ADW wasn’t the issue.
The steps to Monitor SQL are here:
Go to your ADW instance and choose “Service Console”.
Choose the query you are investigating and click “Show Details”.
I can see that the database time is only a couple seconds so why are we having issues?
The Oracle team got together to figure out what the issue was – OAC? ADW? Other?
The first thing we (I) learned was that auto-indexing in ADW is (1) not turned on by default and (2) is available starting in version 19c. One of our hubsters got on the ball to set up a new 19c (vs 18c) ADW instance and turn on auto-indexing. Once that was done, the partner once again moved the 64GB of data over and we tested in OAC (after re-mapping the tables again in the RPD).
Still NO gain. What the heck?
The partner and Oracle team got together to walk through my steps of changing the database connection to see if there was something I missed. The one piece of the original Connection Pool setup that remained was a Connection Script – Execute on connect to force parallel queries. So, we removed it.
That was it! Once we removed the script, we were getting the ADW gains we expected immediately. I also went back and re-implemented Usage Tracking…no performance degradation.
Results
So what were our timed results?
Note:
Prod: 2 OCPUs OAC, 4 OCPUs DBCS – High Performance, 16GB data
Dev: 2 OCPUs OAC, 8 OCPUs DBCS – High Performance, 64GB data
Dev on ADW: 4 OCPUs OAC, 4 OCPUs ADW, 64GB data
Over DBCS, ADW in Dev performed, on average, 125% faster! And still with 4 times more data, ADW was only, on average, 10% slower on first runs. Once the queries get run more often, the auto-indexing will kick in to, likely, make it even faster than on ¼ of the data. In TOTAL time, ADW was only 48 seconds slower with 4x the data.
If this isn’t proof ADW is what it claims to be, then I don’t know what it is!
RealTriGeek 