Is V$SQLAREA limited in size?

[Bekele]

Dear tektips users,

I am using the V$SQLAREA vue in order to log statitistics regarding the usage of Business Object reports. The statistics are aimed at listing the frequency of usage of each of my BO reports. Since my BO reports use sql statements, V$SQLAREA logs the sql statements and stores the statistics i need to produce.
My problem is that V$SQLAREA is a vue and gets re-initialized after reaching a size limit. Before reaching that limit i need to copy the content into a separate table, in order to save the info.

Does anyone know how can i solve this problem?

Thanks

 

[SantaMufasa]

Bekele,

First, Welcome to Tek-Tips. I hope your time here is helpful and rewarding.

Your problem, as I see it, is your reliably predicting when Oracle reaches the point at which it will "age out" SQL code from the V$SQLAREA to make room for new SQL. Without some reliable predictive method, you risk "losing" the historical SQL that Oracle is aging out.

I anticipate that although Oracle now allows triggers on tables owned by user "SYS", since V$SQLAREA is a "virtual" table (created "on-the-fly" by Oracle and not based upon a "real"/permanent table), that you cannot be successful in creating a trigger on V$SQLAREA.

Unless someone else can come up with an alternate method, I'm thinking that a "very regular" querying of V$SQLAREA (into a "permanent" SQLAREA table of your own) to record SQL statements and their respective statistics that are not already in the table may be the way you must go. Obviously, such an exercise could prove to be VERY labor/resource intensive...but if it is a critical business need for you to perform such a survey, then, as they say, "Ya gotta do what ya gotta do."

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Bekele]

Mufasa,

Thank you for your reply and for clarifying my problem.

In the meantime, I have decided to regularly save the info from V$SQLAREA, which you've suggested by the way. I will try to do it twice a day first, and progressively increase the frequency if needed.

However, i am curious to know how V$SQLAREA gets "aged out" as you said. By analyzing this process maybe i can find a workaround. Do you have any info about that? or you think there's nothing else i can do?

 

[SantaMufasa]

Oracle's "aging" algorithm for V$SQLAREA is (probably) similar to the algorithm it uses to age buffers from its Database Buffer Cache: When it needs space, it overwrites the "least-recently used" block.

...you think there's nothing else i can do?

Historically, anytime I've said, "There is no other way," someone comes up with a fine "other way".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

The only alternative I can see to a periodic query would be if you could modify your reports to also log themselves every time they are used (e.g., insert a row into a table indicating their name, the current time, and maybe even who was running the report.

If these reports are run via web calls, you might also be able to put something like your Apache log to use as an external table. I have created a simple web-based click-stream database using just this approach.