Hi,

In which area of the memory structure do the V$ views reside? And how much memory do they comsume?

Thank you very much Tom

Sir,

As we know that V$Views are taking data from data dictionary, and it is normally resides in System tablespace,

if at that system tablespace datafile have problem, we have read the data from it

Like V$DATAFLE, V$CONTROLFILE

Thanks and Regards

Hayata

Hi,

Tom you said:
most all v$ views, based on x$ tables, are in memory data structures, not disk.

And I search Google and found the following statment that the x$ tables are not really tables, they are C language structures inside the SGA RAM heap.

Is that right about C language structures inside the SGA RAM heap?

Thanks

Sir,

It means when we add datafile or any modfication in controlfile, it is also written in Linked List / Array strucutre in file format.

Oracle can read this file, and placed its contents in SGA, although at that DB Structure not linked with Instance.

Regards,

Hi Tom,
After firing the following SQLs, what I saw is a bit wierd at least for me. The public synonym V$BH points to the view SYS.V_$BH. However, the underlying SQL for the view SYS.V_$BH in turn selects from the public synonym V$BH.



SQL> select OWNER, OBJECT_NAME, OBJECT_ID,  OBJECT_type from dba_objects where OBJECT_NAME in ('V_$BH','V$BH');

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS                            V_$BH                                1397 VIEW
PUBLIC                         V$BH                                 1398 SYNONYM

SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='V$BH';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC                         V$BH                           SYS                            V_$BH

SQL> set long 1000000
SQL> SELECT TEXT from dba_views where VIEW_NAME='V_$BH';

TEXT
--------------------------------------------------------------------------------
select "FILE#","BLOCK#","CLASS#","STATUS","XNC","FORCED_READS","FORCED_WRITES","
LOCK_ELEMENT_ADDR","LOCK_ELEMENT_NAME","LOCK_ELEMENT_CLASS","DIRTY","TEMP","PING
","STALE","DIRECT","NEW","OBJD","TS#","LOBID","CACHEHINT" from v$bh




I tried to make up a similar scenerio involving circular view definition.


SQL> sho user
USER is "SYS"
SQL> create table T1 (aa int);

Table created.

SQL> create view V1_ as select * from T1;

View created.

SQL> create public synonym V1 for V1_;

Synonym created.

SQL> insert into T1 select rownum from dual connect by level<=5;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from V1_;

        AA
----------
         1
         2
         3
         4
         5

SQL> create or replace view V1_ as select * from V1;
create or replace view V1_ as select * from V1
                       *
ERROR at line 1:
ORA-01731: circular view definition encountered



So my questions are -
1) How did oracle manage to create circular view definition?
2) I guess, v$BH fetches data from X$BH internally (I might be wrong). From where exactly to vaidate this if not from dba_views?


Regards,
Prajjwal

Something that I discovered just now.. to partially answer my second question.

SQL> set autot trace exp
SQL> select * from v$bh;

Execution Plan
----------------------------------------------------------
Plan hash value: 798134341

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   164 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN OUTER  |      |     1 |   164 |     1 (100)| 00:00:01 |
|*  2 |   FIXED TABLE FULL| X$BH |     1 |   134 |     0   (0)| 00:00:01 |
|   3 |   FIXED TABLE FULL| X$LE |   100 |  3000 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BH"."LE_ADDR"="LE"."LE_ADDR"(+))
   2 - filter("BH"."INST_ID"=USERENV('INSTANCE'))



select * from v$bh;
 ...is very close to...
select * from x$bh bh, x$le le where BH.LE_ADDR=LE.LE_ADDR(+) and BH.INST_ID=USERENV('INSTANCE');

Hi Tom,
I tried to grant privilege on v$database to a user. But it returned the error: ORA-02030: can only select from fixed tables/views.
After Googling I understood that v$database is a synonym of actual views v_$database. So I thought we can't grant privilege on synonyms.
When I experimented this myself by manually creating synonym for a table and then granting privilege on that synonym to a user, it succeeded.
How was it impossible to grant privilege on v$database and similar syninyms? Kindly guide me Tom.

Kindly,
Vimal

ops$tkyte%ORA11GR2> !oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause:  An attempt is being made to perform an operation other than
//         a retrieval from a fixed table/view.
// *Action:  You may only select rows from fixed tables/views.

Tom, I will ask the question precisely! Granting privilege on v_$ (views) is perfectly working. But the same is failing with v$ (synonyms). Can't we grant privileges on Synonyms? I tried it by creating my own synonyms and granting privilege, it is working fine. But why not the same with v$ (synonyms)?

Hi Tom

I would like to clear up how useful (if at all) is to probe the V$FILESTAT view to check specifically for its two values - as below:

The consultant who had been engaged executes the SQL statement below and says that we need to worry about Oracle I/O should these values go over 150 (i.e. 1.5 sec)

Tom - correct me if I am wrong - but these two particular attributes (MAXIORTM,MAXIOWTM) display the High-Water mark each. So in my opinion even if they show high values for two data-files (like below) - it is impossible to say if this long I/O persisted for a long time - or was a recurring issue - or was just a spike.

The database has been running 24x7 8 days in a row and in the first 4 days these values had only reached 132 - and on the file #32 (which still displays the same).

Unless the database is bounced - this V$FILESTAT view will keep on accumulating data - so I would very much question the usefullness of these two attributes specifically.

But I might be wrong - and the consultant might be correct in asserting that we have a problem.

BTW - this is the same database that had been deployed on one single logical volume that is also mapped to 12 other hosts - so IMHO - such spikes in I/O would be attributed to the fact that storage is SHARED rather than that there is any particular issue with one of the database files.

YOUR OPINION please...?

Kind regards
Andre

SQL> select file#,MAXIORTM,MAXIOWTM FROM v$filestat order by MAXIORTM DESC;

     FILE#   MAXIORTM   MAXIOWTM
---------- ---------- ----------
        18        348         48
        19        175         74
        16        132         74
        14        131         75
        11        126         57
        17        123         53
         2        120         75
        31        113         75
         1        108         58
        15        102         58
        21         99         74
        12         86         57
        20         81         74
        32         79         77
        13         77         57
        30         70         75
         5         41          5
         3         12          5
         7         10          4
         6          9          5
        10          6          4
        22          4          2

Tom

a short PS note to what I said earlier today

I have researched this further and found out about DBMS_SYSTEM.kcfrms packaged procedure

This indeed does reset these values (and more).

In this case HWM are NOT useless as they can be reset by some job which would probe the V$FILESTAT

Would you give any further recommendations as to how one should monitor this...?

Is there any specific HWM value which should cause concern?

Thank you

Kindly
Andre

Hi Tom

Thank you for your response to both of my posts - re MAXIOxxx attributes in the V$FILESTAT.

The consultant keeps executing the query every hour as before only to find these values suddenly had gone through the roof..!!

>> Please see below the output from V$FILESTAT
-- with the max(MAXIORTM) = 133.64 sec and max(MAXIOWTM) = 223.49 sec.

BTW: He received a response from the infra/SAN team:
>>>-q/ There was a DWDM link failure this afternoon (3 SAN circuits on a single fabric - second fabric unaffected). Based on the time of your email I suspect that the degraded response times may coincide with any path failover events. Incident occurred circa 16:00-16:30. Root cause is still under investigation
/q-<<<

+
Re your recommendation:
-----------------------
I would have resorted to histogram views also - but the v$event_histogram had NOT been made available on Oracle 9i - only introduced with 10g.

+

Your statement "... mess up other things with this reset ..." ==> I interpret to be that various other accrued attributes in other V$ views would also be reset to zeros thus possibly affecting STATSPACK report...? for a while until more data gets collected - or database is restarted?

So - correct me if I am wrong - this would be the only impact - right..?

>>> Well -
It has been going the full circle -
- - i.e. to the root cause being SAN's one single managed disk (striped over 96 DASD) => mapped to one logical volume => this in turn provisioned to 13 hosts and presented as file systems => with anyone of the other databases or other purpose machines going to town on I/O (as per my other posts on ASM deployed on a set of raw devices to replace the current shared SAN RAID-5 infrastructure) ...

I would love to be able - under these very high constraints (no access to any tools) to be able to make a case ONLY to be allowed bench-mark tests between:
(1) SAN "AS IS NOW" - and
(2) RAW devices that are OUTSIDE their "managed-disks" - accessible to ASM - (these raw devices would need to be housed on a SAN + accessible via some switch via disk controllers etc., as the logical volumes are now - but at least these would NOT be shared)

Say that you need to make a case like that - i.e. convince the management to at least allow you to try bench-mark tests - how would you go about it

Please keep in mind -
(a) old (EOL=2009) HP machine
(b) old Win-2000 / no tools
(c) old Oracle 9i

making a case so that at least someone may listen and give a "Go-Ahead" to get bench-mark tests underway.

Thank you

Kindly - Andre


FILE# MAXIORTM MAXIOWTM
---------- ---------- ----------
1 108 58
2 120 22349
3 12 5
5 41 5
6 9 5
7 10 4
10 6 4
11 126 57
12 86 57
13 77 57
14 131 75
15 102 58
16 132 74
17 123 22349
18 348 48
19 175 22349
20 13364 22349
21 99 22349
22 4 2
30 84 75
31 113 75
32 83 22349

Hi Tom,

Thank you for a very succinct suggested advice to the mgt re benchmarking of I/O on SAN etc...

Re the use of the dbms_system.kcfrms undocumented procedure - I have just spotted Jonathan Lewis article in which he is using it - although he also gives a warning later in the same note - to be careful doing it on systems with large number of users...

Here is the URL
http://www.jlcomp.demon.co.uk/log_file_writes.html

Thanks

Kindly
Andre

Hi Tom,

I am trying to reconcile the times being gathered on two SYS views: FILESTAT and V_$FILE_HISTOGRAM

On a 11g database (that is also sharing SAN) - and that had been deployed without ARCHIVELOG the team shuts it down every weekend to take an old fashion cold backup.

Let us forget NOARCHIVELOG for a moment.

The important thing is that all V$ views get reset on Saturday.

I have just checked these 2 views and I do not understand why the times gathered are so different...?

E.g.: MAXIORTM = 4871 ... MAXIOWTM = 2204

but ... max(SINGLEBLKRDTIM_MILLI) = 1024

48.71 sec on Read or 22.04 sec on Write vs 1 sec recorded in the histogram ..???

Furthermore:
The file# with recorded 1sec on histogram is for SYSAUX TBS
this is reflected with MAXIOWTM 22.04 sec - however MAXIORTM for the same file is 0.56 sec.

I tried to research this - but could not find any info other than very basic references in Oracle docs.

How can I trust these measurements...?

Are there some conditions that need to be met for these measurements to be reconciled or at least be closer..?

Thank you - as always

Andre