lab128 is no display graph.

[oracle dba]

Hi, every one.

I received err messages from lab128
That is 'ORA-03127: no new operations allowed until the active
operation ends.
And then lab128 is no display graph.
Quite often occur this problem.
How do I solve this problem?


OS: Redhat Linux ES 5
DBMS:11.2.0.1
client:10.2.0.1


09/11 07:34:44 - ORA-03127: no new operations allowed until the active
operation ends.
SQL:
--lab128
select sql_id,plan_hash_value,parse_calls,disk_reads,direct_writes,
buffer_gets,rows_processed,serializable_aborts,fetches,executions,
end_of_fetch_count,loads,invalidations,px_servers_executions,
cpu_time,elapsed_time,application_wait_time,concurrency_wait_time,
cluster_wait_time,user_io_wait_time,plsql_exec_time,java_exec_time,
sorts,last_active_time
from v$sqlstats where last_active_time+.0000231 >= :1
09/11 07:34:44 - Query ID=939, Name='v$sqlstats', recoverable error
103: Query execution attempt failed.
09/11 07:34:46 - ORA-03127: no new operations allowed until the active
operation ends.
SQL:
--lab128
select sid,inst_id,decode(type,'TM',id1,null) obj_id,type,id1,id2,
lmode,lmode "Lock Held",request,request "Lock Request",
ctime,kaddr
from gv$lock where type!='MR'
09/11 07:34:46 - Query ID=903, Name='v$lock', recoverable error 103:
Query execution attempt failed.
09/11 07:34:47 - ORA-03127: no new operations allowed until the active
operation ends.
SQL:
--lab128
select sql_id,plan_hash_value,parse_calls,disk_reads,direct_writes,
buffer_gets,rows_processed,serializable_aborts,fetches,executions,
end_of_fetch_count,loads,invalidations,px_servers_executions,
cpu_time,elapsed_time,application_wait_time,concurrency_wait_time,
cluster_wait_time,user_io_wait_time,plsql_exec_time,java_exec_time,
sorts,last_active_time
from v$sqlstats where last_active_time+.0000231 >= :1
09/11 07:34:47 - Query ID=939, Name='v$sqlstats', recoverable error
103: Query execution attempt failed.

[Sergei - Lab128]

How persistent is this problem? Have you ever been able to run without
this problem? What if you manually execute similar query:

select sql_id,plan_hash_value,parse_calls,disk_reads,direct_writes,
buffer_gets,rows_processed,serializable_aborts,fetches,executions,
end_of_fetch_count,loads,invalidations,px_servers_executions,
cpu_time,elapsed_time,application_wait_time,concurrency_wait_time,
cluster_wait_time,user_io_wait_time,plsql_exec_time,java_exec_time,
sorts,last_active_time

from v$sqlstats where last_active_time>sysdate-1/24

Will you get same problem?

[Vitaly Savenkov]

Hi I got the same error
the problem is that SQL
select sid, inst_id, decode(type, 'TM', id1, null) obj_id, type, id1,
id2, lmode, lmode "Lock Held", request, request "Lock Request", ctime,

kaddr from
gv$lock
where type!='MR'

is running very slow because it uses Merge JOIN Cartesin between X
$KSQRTS X$KSUSE

each run takes 32 seconds and query if I run it from Oracle use the
same plan and run 32 seconds, so I believe this is not lab128 issue
but still it should not use this query I believe.

Is it possible for the time being while I'm looking for workaround,
easily, withoud hard code development disable such query from
lab128 ?

> >    fromgv$lockwhere type!='MR'

> > 09/11 07:34:46 - Query ID=903, Name='v$lock', recoverable error 103:
> > Query execution attempt failed.
> > 09/11 07:34:47 - ORA-03127: no new operations allowed until the active
> > operation ends.
> >   SQL:
> >    --lab128
> >    select sql_id,plan_hash_value,parse_calls,disk_reads,direct_writes,
> >    buffer_gets,rows_processed,serializable_aborts,fetches,executions,
> >    end_of_fetch_count,loads,invalidations,px_servers_executions,
> >    cpu_time,elapsed_time,application_wait_time,concurrency_wait_time,
> >    cluster_wait_time,user_io_wait_time,plsql_exec_time,java_exec_time,
> >    sorts,last_active_time
> >    from v$sqlstats where last_active_time+.0000231 >= :1
> > 09/11 07:34:47 - Query ID=939, Name='v$sqlstats', recoverable error

> > 103: Query execution attempt failed.- Hide quoted text -
>
> - Show quoted text -

[Sergei - Lab128]

Yes, you can disable this query:
Main menu | Measurement | Edit Query. Then find "v$lock" query, select
it. Then in "Time Between Measurements" box type "disable" and then
press "Save Changes".

Can you tell the Oracle version? What are these parameters:

select * from v$parameter
where name in (
'optimizer_features_enable'
,'optimizer_index_caching'
,'optimizer_index_cost_adj'
,'optimizer_dynamic_sampling'
);

Regards,
Sergei