Do you mind attaching packaged performance recording workbook?
Dmitry Chirkov I'm a little late to the party, but I may have a scenario with the same root cause as the OP's. My scenario involves the creation of an extract. Also, my redundant queries are not exactly the same — the first one includes a calculated field that the second one does not.
I can't post my performance recording here due to sensitive info in the queries, but I have opened case # 02513731 with a performance recording. I would love to get some insight as to what would be causing the query to execute twice. Thanks very much.
Jamieson Christian, could you also send logs for this operation, please? I'd like to look at the execution context. (Sending Tableau Desktop Log Files | Tableau Software)
Workbook as well, if possible. (TWB only, no need for data)
2 of 2 people found this helpful
I see the issue.
Tableau has a retry logic in case it's unable to compute some of the calculations remotely. If "full query" fails we'll query for individual components so we could compute results locally.
In your case there's a calculation that Oracle is not happy with (DATE("2016-01-15") which becomes TRUNC(CAST(N'2016-01-15' AS DATE)))
That fails with ORA-01861: literal does not match format string. Since it's a sub-query from complex Custom SQL - it takes 400s+ for query to fail. Right after we run the query once again but without this calculation which runs fine (for another 400s+).
To get rid of extra query you have to eliminate the error - you have to fix date string to match format your Oracle server expects.
Run this query (you can use Custom SQL in Tableau) to see the format:
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'
Server I have, for example, needs DD-MON-RR which is 15-JAN-16 so my calculation would look like DATE("15-JAN-16").
Try this out and let me know how it goes.
Dmitry Chirkov, that was it! I replaced the DATE() function with a date literal — which, for some reason, I didn't even realize I could do until now — and then Tableau subsequently presented it to Oracle as TO_DATE('2016-01-15','YYYY-MM-DD'), something that Oracle could work with regardless of server native date format.
Thanks very much! Not only did I learn more about how to avoid the myriad date handling pitfalls, I learned a little something about reading the logs, too. I don't know if this very specific scenario helps with the OP's situation any, but I really appreciate your helping me understand what Tableau is doing behind the scenes that might cause it to run a query more than once.