Re: Uknown query

calberto2312_at_gmail.com wrote:

[Following up only to comp.databases.oracle.misc]

> In my statspack reports, I've been seeing some similar queries that
> are unknown for me. ... I want to find out who is running
> them. Here's one of the queries :
>
> select a.default_cpu_cost, a.default_io_cost
> from association$ a
> where a.obj# = :1
> and a.property = :2

Carlos,

You can peek in on what SQL statements have been executed systemwide, including by whom, by querying the system view SYS.GV_$SQLAREA. For your example:

    SELECT SA.first_load_time, SA.executions as nexec,

           AU.username, SA.sql_text
    FROM sys.gv_$sqlarea SA, sys.all_users AU     WHERE SA.parsing_user_id = AU.user_id       AND SA.sql_text LIKE '%select a.default_cpu_cost%'     ORDER BY SA.first_load_time DESC
    ;

should show when (first), by whom (at least for that first execution), and how many times a query has been run that contains text unique to your mystery query. In this case, the username column, and perhaps the time of first execution, will probably shed light on where the queries are "coming from."

Note that GV_$SQLAREA is an in-memory cache, and that queries can disappear from there for several reasons:

• they were kicked out because the cache is full, to make room for "newer" queries
• ALTER SYSTEM FLUSH SHARED_POOL has been run
• the server is restarted and perhaps others.

Thus if you don't see your mystery queries in GV_$SQLAREA it could be they were flushed out, in which case you can wait and try again.

Hope that helps,

        John Hinsdale Received on Sat Dec 30 2006 - 14:08:52 CST