The problem of V$SQLAREA is that it is a group by of V$SQL. Because it
collects the VERSION_COUNT of sql's in the SQL area. So sometimes it =
could
hurt performance on account of that group by and consequently of the
execution plan.
Regards.
> -----Mensaje original-----
> De: Tim Onions [SMTP:tim.onions_at_speechmachines.com]
> Enviado el: mi=E9rcoles 17 de enero de 2001 9:46
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: Auditing, how to capture sql statement issued
>=20
> I've just finished Jonanthan Lewis's book (Practical Oracle8i) and if =
I
> understood him correctly V$SQLAREA is not a very efficient V$ table =
to use
> so I'm not sure looping on this table is a very good idea.
>=20
> Just something to consider....
>=20
> -----Original Message-----
> Sent: 16 January 2001 17:39
> To: Multiple recipients of list ORACLE-L
>=20
>=20
> You could infact look at writing a query to select from v$SQLAREA and =
loop
> infinitely (via whatever:), to collect SQL statements with certain
> thresholds etc, then insert these statements in full in to a =
"Repository"
> table.
>=20
> SQL optimistation tools do this all the time..
>=20
>=20
>=20
> -----Original Message-----
> Brian
> Sent: Tuesday, January 16, 2001 04:26
> To: Multiple recipients of list ORACLE-L
>=20
>=20
> Based on his subject, I think Mr. Cale was wanting to monitor other =
users'
> queries. For instance, looking for queries without where clauses that =
pull
> credit card numbers to find out if someone is doing something they
> shouldn't.
>=20
> There is no effective way to monitor queries from inside the DB. =
There
> are
> no "on select" triggers, system auditing only tracks that a query was
> issued
> against the table without any info on the rows returned or query =
text,
> and
> no log is generated on queries, so logminer is no help.
>=20
> Brian Norrell
> Senior Software Engineer
> QuadraMed
> 972-831-6600
>=20
>=20
> -----Original Message-----
> Sent: Tuesday, January 16, 2001 8:31 AM
> To: Multiple recipients of list ORACLE-L
>=20
>=20
>=20
>=20
> Cale
> I think you mean to reuse the sql statement sometime again.
> if this is the case then u can save the query in the sql*plus =
editor
> in
> some file location
> or u can just use SAVE <A> after your query in the sql*plus .
> The next time u want to use the query u can just use ' @ A ' .
>=20
> e.g select * from tab;
>=20
>=20
> TNAME TABTYPE CLUSTERID
> ------------------------------ ------- ---------
> AB TABLE
> CD TABLE
> DISTINCT_TEST TABLE
> EMP TABLE
> LXAPPLICATION TABLE
> LXAUCTION TABLE
> LXAUCTIONHISTORY TABLE
> LXAUCTIONSTATUS TABLE
> LXBID TABLE
>=20
>=20
> SQL> save a replace
> Wrote file a
>=20
> SQL> @ a
>=20
> TNAME TABTYPE CLUSTERID
> ------------------------------ ------- ---------
> AB TABLE
> CD TABLE
> DISTINCT_TEST TABLE
> EMP TABLE
> LXAPPLICATION TABLE
> LXAUCTION TABLE
> LXAUCTIONHISTORY TABLE
> LXAUCTIONSTATUS TABLE
> LXBID TABLE
>=20
>=20
> you can also use the sql*plus editor by just typing 'ed' as it will =
load
> the last executed sql query in
> the editor where you save your query.
>=20
> Rgds,
> =3D=3D=3D=3D=3D=3D
> Sachin Puri
> Oracle Development and Administration
> Solutions NY
> Tel : +91-11-6220102 x 22
> Direct : +919810373243
>=20
>=20
>=20
> -----Original Message-----
> Sent: Tuesday, January 16, 2001 6:31 PM
> To: Multiple recipients of list ORACLE-L
>=20
>=20
> Hi DBAs,
>=20
> Is there a way to capture the actual sql statement issued while in
> sql*plus,worksheet,etc?
>=20
> Thanks,
> Rick Cale, Science Applications International Corp.
> Phone:865-481-2198, fax:865-481-8555
> e-mail: caler_at_saic.com
>=20
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Cale, Rick T (Richard)
> INET: RICHARD.T.CALE_at_saic.com
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>=20
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sachin Puri
> INET: sachin_at_siworldwide.com
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Norrell, Brian
> INET: BNorrell_at_QuadraMed.com
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>=20
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: Tim Onions
> INET: tim.onions_at_speechmachines.com
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Wed Jan 17 2001 - 03:37:53 CST
