[Oracle-l] "control file sequential read" on RAC

On 5/14/07, Shivaswamy Raghunath wrote:
Hello.

We have a pl/sql script which we run regularly in the database to monitor
tablesapce usage and to generate email notification on our regular (non-RAC)
database. This script when run on RAC database takes way too long to
complete.(30+ minutes) whicle it completes in under a couple of minutes on
the regular database.

On 5/15/07, Norman Dunbar wrote:
I've been told by Oracle trainers that joining a V$ view to an physical
table or other dictionary tyope objkect is fraught with danger because:

* the V$ stuff is memory based and not protected by read consistency
* the V$ stuff changes frequently

When you join V$ to others, each time the V$ changes, Oracle starts the
query again. It only finishes when it manages to join all of V$ with
whatever without a change occurring part way through.

On 5/15/07, Jared Still wrote:
Though I have in the past run into difficulty with v$ views in joins, I've
never before heard this bit.

Being something of a skeptic, I would probably need to be convinced
with some evidence. :)

The 'ordered' hint has worked well for me in the past when joining V$
views.

For instance:

1 select /*+ ordered */
2 --b.kaddr,
3 c.sid,
4 c.username,
...
155 from
156 v$lock b
157,v$session c
158,sys.user$ u
159,sys.obj$ o
160,( select * from sys.dba_waiters) lock_blocker
161,( select * from sys.dba_waiters) lock_waiter
162 where
163 b.sid = c.sid
164 and u.user# = c.user#
165 and o.obj#(+) = b.id1
166 and lock_blocker.waiting_session(+) = c.sid
167 and lock_waiter.holding_session(+) = c.sid
168 and c.username != 'SYS'
169 --order by kaddr, lockwait
170* order by b.sid, object

Without the ordered hint this query can be really slow.
Been awhile since I tested it though. I run it regularly on 9i
and 10g, but the ordered hint was added for 8i.

Alex G. mentioned the use of subquery factoring (WITH clause).

That makes queries easier to read and offers the ability to materialize
the subquery, which might be a good thing.

I recently ran into a problem with it however. When using subquery
factoring with a somewhat complex bit of SQL, the optimizer would
not come up with a favorable execution plan. The MATERIALIZE and
INLINE hints returned the same plans.

The query would only work in a reasonable time when re-written
without subquery factoring.

It would be interesting to see if SF would help your query.


--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On 5/15/07, Norman Dunbar wrote:
I've been told by Oracle trainers that joining a V$ view to an physical
table or other dictionary tyope objkect is fraught with danger because

On 5/15/07, Alex Gorbachev wrote:

One way would be to use WITH clause and make Oracle instantiating
temporary tables that are then joined.
--
Best regards,
Alex Gorbachev

http://www.oracloid.com

cause.

Meanwhile, if you have more insight do let me know.

On 5/15/07, Alexander Fatkulin wrote:

Have you collected stats on the fixed objects?

dbms_stats.gather_fixed_objects_stats
dba_data_files (Now in 10.2 to give the >ONLINE_STATUS Of the data
file) is causing the issue. But I have not yet gone in to the root

--
Alexander Fatkulin

SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)

On 5/14/07, Shivaswamy Raghunath wrote:

Hello.

We have a pl/sql script which we run regularly in the database to monitor
tablesapce usage and to generate email notification on our regular (non-RAC)
database. This script when run on RAC database takes way too long to
complete.(30+ minutes) whicle it completes in under a couple of minutes on
the regular database.

I have identified the SQl and the associated event it is waiting on. Here
is the extract from the Level 12 trace;

********************************************************************************

SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 394 0.16 0.13 0 4
0 0
Fetch 394 525.73 1725.44 0 1592569
317564 394
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 789 525.89 1725.58 0 1592573
317564 394

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
library cache lock 2 0.00
0.00
control file sequential read 3984128 0.01
1377.06
row cache lock 403 0.00
0.04
gc current block 3-way 1 0.00
0.00
gc current block 2-way 3 0.00
0.00

********************************************************************************


While I can try to dig in more in to the corresponding contril file on the
ASM disk, I tend to believe this is because of some unexpected behavior on
the part of Oracle.

BTW, we are on (Linux) ASM. Generally speaking I have not observed any
major issues so far on IO related issues. DB is on 10.2.0.2

Can any of you throw some light on this?

Thanks,
Shiva

On 5/15/07, Shivaswamy Raghunath wrote:

Forgot to ask you, do you have the bug number?

On 5/18/07, Karla, Srini (HAS-SAT) wrote:
All,

Any one has apps financials and projects setup documents. I have
installed Apps 10.5.10.2 from media, every thing is working fine. Now I
want implement the AR, AP GL, OP modules, Please send me step-by-step
implementation documents for these modules.