5 Replies Latest reply: Mar 19, 2013 6:00 AM by DavidGreenfield RSS

    Cache DML formula results

    Dileep
      Hi

      I have a formula which is little expensive to compute and results would not change for every single query within a session. We currently cache results using $NATRIGGER and $VARCACHE properies but recently we noticed some odd behavior during query time when using these properties. Is there any other way to cache results of a particular formula within a session?

      Thanks
      Dileep.
        • 1. Re: Cache DML formula results
          DavidGreenfield
          Hi Dileep,

          You are already using the properties I would have recommended. (I assume you have a VARIABLE with the formula burned in as an $NATRIGGER, and that your calculated measure (in AWM) points to the variable.) What kind of 'odd behaviour' are you seeing?

          David
          • 2. Re: Cache DML formula results
            Dileep
            Thanks David.

            This is how I defined it currently.

            DEFINE TEST_CACHE VARIABLE TEXT <SPARSE <D1 D2 D3 D4 D5 D6 D7 D8 D9 D10>> TEMPORARY
            PROPERTY '$NATRIGGER' 'TEST_DML_FROMULA'
            PROPERTY '$VARCACHE' 'SESSION'

            Then I used this variable in reporting layer

            DEFINE REPORT_FORMULA FORMULA LOCKDFN NUMBER WITH NULLTRACKING <D1 D2 D3 D4 D5 D6 D7 D8 D9 D10>
            EQ TEST_CACHE

            We recently saw an issue with a formula which is using $VARCHAHE/$NATRIGGER. During cube refresh(RW session) if a user queries (RO session) we get following error only first time if I run same query second time in the same session it works fine.

            ORA-24374: define not done before fetch or execute and fetch

            I see No Data Found error when I use OLAP DML to query at the same time.

            After cube refresh we could not replicate the issue.

            Edited by: Dileep on Mar 18, 2013 11:26 AM
            • 3. Re: Cache DML formula results
              DavidGreenfield
              Hi Dileep,

              Sounds like a bug to me. Can you create a reproducible test case and send it to me?

              David
              • 4. Re: Cache DML formula results
                Dileep
                Hi David,

                I tried to reproduce the problem without much success but I will keep try.

                so I assume there were not any known issues with $VARCHAHE/$NATRIGGER and should be okay to use.

                Does it matter if I define variable as TEMP instead of permanent variable.

                DEFINE TEST_CACHE VARIABLE TEXT <SPARSE <D1 D2 D3 D4 D5 D6 D7 D8 D9 D10>> TEMPORARY
                PROPERTY '$NATRIGGER' 'TEST_DML_FROMULA'
                PROPERTY '$VARCACHE' 'SESSION'
                • 5. Re: Cache DML formula results
                  DavidGreenfield
                  Hi Dileep,

                  I know of no problems in this area, and the bug system doesn't show anything for the error you saw. So I think you should continue doing what you have been doing. If it starts to happen again, then try setting this in the session before the commands that cause the error:
                  alter session set events='24374 trace name errorstack';
                  Once the ORA-24374 error happens, there should be a trace file containing the call stack for the bad code. Then just open an SR and let us know. Of course if you get a reproducible case, that is even better...

                  David