Re: help tracking down root of high executions.

From: Ben <benalvey_at_yahoo.com>
Date: Mon, 7 Jan 2008 09:02:03 -0800 (PST)
Message-ID: <cb9ab60e-40ab-44cd-9919-69c13ed912a7@v46g2000hsv.googlegroups.com>


On Jan 7, 11:28am, Ben <benal..._at_yahoo.com> wrote:
> On Jan 7, 11:17am, 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



SELECT * FROM PRODDTA.F4801 WHERE ( WADOCO = :KEY1 ) Plan hash value:
2734625618
| 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

Original text of this message