Re: transactions

Steve,

If we did all COMMIT OR ROLLBACK, should v$resource_limit current_utilization of transactions = count(*) from v$transaction?

Joan

Steve Adams wrote:
>
> Hi Joan,
>
> Queries over db links are distributed transactions. You must COMMIT (or
> ROLLBACK) after them. The ORA-1460 error is also of concern and should be
> investigated further.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
> @
> @ Going to OpenWorld?
> @ Catch the Ixora performance tuning seminar too!
> @ See http://www.ixora.com.au/seminars/ for details.
>
> -----Original Message-----
> From: Joan Hsieh [mailto:jhsieh_at_infonet.tufts.edu]
> Sent: Wednesday, 20 September 2000 6:01
> To: Steve Adams
> Cc: Multiple recipients of list ORACLE-L
> Subject: Re: transactions
>
> Hi Steve,
>
> We are using db link across two databases. The query over link is just
> select only type of query. Update, insert and delete is not allowed via
> link. The whole structure which I implemented as follows;
>
> On HR db
> Created some complicated views (join 5 tables) like hr_views.
> On FM db
> CREATE SYNONYM HR_VIEWS FOR OWNER.HR_VIEWS_at_hr;
>
> All the queries on FM against HR views are select only and vise visa.
>
> Now I noticed our HR alert file have all those errors: FM alert file
> didn't show any errors.
>
> Mon Sep 18 22:00:12 2000
> Errors in file /u01/app/oracle/admin/HRPROD/bdump/reco_62848_hrprod.trc:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01460: unimplemented or unreasonable conversion requested
> ORA-02063: preceding 2 lines from FMPROD
> Mon Sep 18 22:00:12 2000
> Errors in file /u01/app/oracle/admin/HRPROD/bdump/reco_62848_hrprod.trc:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01460: unimplemented or unreasonable conversion requested
> ORA-02063: preceding 2 lines from FMPROD
> Mon Sep 18 22:00:12 2000
>
> reco.trc file shows the same errors. Is this explaining the # of
> transaction puzzles? I am not sure why comes all those errors. I have to
> investigate the definition of views.
>
> Mon Sep 18 22:00:12 2000
> *** SESSION ID:(7.1) 2000.09.18.22.00.12.000
> Mon Sep 18 22:00:12 2000
> DISTRIB TRAN HRPROD.WORLD.812f187a.3.54.24314
> is local tran 3.54.24314 (hex=03.36.5efa))
> delete pending collecting tran, scn=37452418 (hex=0.023b7a82)
> ======> HERE, WHY DELETE?
> Mon Sep 18 22:00:12 2000
> ERROR, tran=6.23.23935, session#=1, ose=0:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01460: unimplemented or unreasonable conversion requested
> ORA-02063: preceding 2 lines from FMPROD
> Mon Sep 18 22:00:12 2000
> ERROR, tran=6.23.23935, session#=1, ose=0:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01460: unimplemented or unreasonable conversion requested
> ORA-02063: preceding 2 lines from FMPROD
> Mon Sep 18 22:00:12 2000
>
> Thanks again,
>
> Joan
>
> Steve Adams wrote:
> >
> > Hi Joan,
> >
> > Are you failing to COMMIT after queries over a database link?
> >
> > @ Regards,
> > @ Steve Adams
> > @ http://www.ixora.com.au/
> > @ http://www.christianity.net.au/
> > @
> > @ Going to OpenWorld?
> > @ Catch the Ixora performance tuning seminar too!
> > @ See http://www.ixora.com.au/seminars/ for details.
> >
> > -----Original Message-----
> > From: Joan Hsieh [mailto:jhsieh_at_infonet.tufts.edu]
> > Sent: Wednesday, 20 September 2000 1:51
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: transactions
> >
> > Thanks, Mladen.
> >
> > I think that number is very useful. I can create a temp table download
> > to excel and make a nice graph from there. Do you know how to get
> > average respond time? Another question which puzzled me is
> > v$resouce_limit,
> > I can't figure out why current_utilization of transactions is not match
> > v$transaction? What kind of relationship of these two numbers?
> >
> > Thanks again,
> >
> > Joan
> > select * from v$resource_limit;
> >
> > RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
> > INITIAL_AL LIMIT_VALU
> > ------------------------------ ------------------- ---------------
> > ---------- ----------
> > processes 26
> > 32 200 200
> > sessions 27
> > 34 225 225
> > lm_procs 0
> > 0 0 0
> > lm_ress 0
> > 0 0 UNLIMITED
> > lm_locks 0
> > 0 0 UNLIMITED
> > enqueue_locks 49 53
> > 2931 2931
> > enqueue_resources 52
> > 93 520 520
> > dml_locks 0
> > 37 500 500
> > temporary_table_locks 0 0
> > UNLIMITED UNLIMITED
> > transactions 213
> > 214 247 247
> > sort_segment_locks 0 3
> > UNLIMITED UNLIMITED
> > max_rollback_segments 11
> > 11 31 31
> > distributed_transactions 3
> > 8 61 61
> > mts_max_servers 0
> > 0 20 20
> > parallel_max_servers 0
> > 0 6 6
> >
> > select count(*) from v$transaction;
> >
> > COUNT(*)
> > ----------
> > 1
> >
> > "Gogala, Mladen" wrote:
> > >
> > > Select name, value from v$sysstat where name='user commits';
> > > That will give you the number of user issued COMMIT statements (which
> > > is probably the number your boss wants). The other alternative is so
> > > called "scientific method" in which you invent the data, create a lovely
> > > and colorfull diagram and tell your boss that the diagram looks so well
> > > because your database is mauve and such databases use the least memory.
> > >
> > > -----Original Message-----
> > > Sent: Monday, September 18, 2000 2:56 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > Hi Listers,
> > >
> > > I am wondering how to find the high water mark of number transactions as
> > > daily basis. I tried v$resource_limit, but max_utilization of
> > > transactions always reached the max value which is 247. My boss like to
> > > generate monthly graph representation of # transactions, users and
> > > average respond time kind of report. I can produced all kind of
> > > performance related report except this one. License high water mark
> > > cannot reflect our # of users too. Since we use application server. All
> > > the uers just has 3 tier connections.
> > >
> > > Thanks in advance,
> > >
> > > Joan
> > > --
> > > Author: Joan Hsieh
> > > INET: jhsieh_at_infonet.tufts.edu
> > >
> > > 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).
> > > --
> > > Author: Gogala, Mladen
> > > INET: MGogala_at_oxhp.com
> > >
> > > 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).
> > --
> > Author: Joan Hsieh
> > INET: jhsieh_at_infonet.tufts.edu
> >
> > 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 Tue Sep 19 2000 - 15:36:49 CDT