Re: help tracking down root of high executions.

On Jan 7, 11:28 am, Ben <benal..._at_yahoo.com> wrote:
> On Jan 7, 11:17 am, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > Ben wrote:
> > > 10.2.0.2 AIX 5.2 64bit.
>
> > > I've got a few sql statements that are being executed once every
> > > centisecond or so, according to an AWR report. I can see the type of
> > > program that is executing the statement but our ERP system is setup in
> > > a way that pretty much everyone gets the same database user name, so
> > > it's hard to track down where or what is executing the statements.
> > > With my knowledge of our ERP and our database in general I can't of
> > > anything that should be executing this rapidly.
>
> > > What would be a good way to attempt to find out exactly what is
> > > causing this?
>
> > What is the SQL statement?
>
> > If you don't know then try this:
>
> > SELECT * FROM TABLE(dbms_xplan.display_cursor('dpcugg8dz3y5k'));
>
> > Substituting your sql_id for the one above.
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Here are the results from the select you posted. If I select * from v
> $sql for that sql_id it returns the statement that I see in the awr
> report.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('g4pfx2dzf81xp'));
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­-----
> SQL_ID: g4pfx2dzf81xp, child number: 0 cannot be found- Hide quoted text -
>
> - Show quoted text -

I just pulled this from Morgan's Library and got the following results.

SELECT t.*
FROM gv$sql s,
TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%SELECT * FROM PRODDTA.F4801 WHERE ( WADOCO = :KEY1 )%' SQL_ID g4pfx2dzf81xp, child number
1

| Id  | Operation                   | Name     | Rows  | Bytes | Cost

|   0 | SELECT STATEMENT            |          |       |       |     3
|
|   1 |  TABLE ACCESS BY INDEX ROWID| F4801    |     1 |   673 |     3
|
|*  2 |   INDEX UNIQUE SCAN         | F4801_PK |     1 |       |     2
|
------------------------------------------------------------------------

Predicate Information (identified by operation id):

   2 -
access("WADOCO"=:KEY1)

Note

• cpu costing is off (consider enabling it)

along with 3 other statements that had an order by clause after the last close paren. Received on Mon Jan 07 2008 - 11:02:03 CST