How high sysem load can it be from uptime command

Here it is , after a command to stop diagmond (HP-UX) and many stm process come up, DISASTER comes ...


> uptime
10:59am up 7 days, 21:45, 4 users, load average: 162.51, 64.13, 24.67
> uptime
^C
> uptime
11:00am up 7 days, 21:46, 5 users, load average: 170.60, 77.04, 30.64


--------------------------------------------------------------------------------
Cpu Util S SU U |100% 41% 100%
Disk Util | 0% 1% 9%
Mem Util S SU UF F | 32% 32% 32%
Swap Util U UR R | 17% 17% 17%
--------------------------------------------------------------------------------
PROCESS LIST Users= 4
User CPU % Thrd Disk Memory Block
Process Name PID Name ( 400% max) Cnt IOrate RSS/VSS On
--------------------------------------------------------------------------------
stm 1552 root 9.2 1 0.0 1.6mb 1.8mb PRI
stm 27516 root 9.0 1 0.0 1.6mb 1.8mb SLEEP
stm 28757 root 6.5 1 0.0 1.6mb 1.8mb PRI
stm 10511 root 5.9 1 0.0 1.6mb 1.8mb PRI
stm 28247 root 3.6 1 0.0 1.6mb 1.9mb PRI
stm 4899 root 3.4 1 0.0 1.6mb 1.8mb SLEEP
stm 12180 root 3.4 1 0.0 1.6mb 1.8mb SLEEP
stm 12471 root 3.1 1 0.0 1.6mb 1.8mb PRI
stm 26386 root 3.1 1 0.0 1.6mb 1.8mb SLEEP
stm 1811 root 2.5 1 0.0 1.6mb 1.9mb PRI
stm 26596 root 2.5 1 0.0 1.6mb 1.8mb PRI
\

Play with rowid_info

PROCEDURE ROWID_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT



SQL> select object_id , object_name from dba_objects where owner='PERFSTAT' and object_name like 'TMP%';

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
283365
TMP_LOG_HISTORY



SQL> select rowid from perfstat.TMP_LOG_HISTORY;

ROWID
------------------
AADNUcABYAAAXSKAAA


SQL> set serveroutput on
SQL> l
1 declare
2 my_rowid varchar2(200);
3 ts_type_in VARCHAR2(200);
4 rid_type number;
5 obj_num number;
6 file_num number;
7 block_num number;
8 row_num number;
9 begin
10 my_rowid:='AADNUcABYAAAXSKAAA';
11 ts_type_in:='SMALLFILE';
12 --dbms_rowid.rowid_info(my_rowid,rid_type, obj_num, file_num, block_num, row_num);
13 dbms_rowid.rowid_info(my_rowid,rid_type, obj_num, file_num, block_num, row_num,ts_type_in);
14 dbms_output.put_line ('object id is :'||to_char(obj_num));
15* end;
SQL> /
object id is :840988

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select object_id,object_name from dba_objects where data_object_id=840988 ;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
283365
TMP_LOG_HISTORY




-- note taht data_object_id is not the same as object id

Play with hao_show_space and table reorganization

--get hao_show_space from http://space.itpub.net/15415488/viewspace-609531

SYS@XE> insert into t1 select * from dba_objects;

14582 rows created.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................1
FS3 Bytes ..............................8192
--------------------------------------------------
FS4 Blocks..............................17
FS4 Bytes ..............................139264
--------------------------------------------------
FULL BLOCKS.............................288
FULL_BYTES .............................2359296
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> delete from t1 where owner='SYS';

6648 rows deleted.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................15
FS2 Bytes ..............................122880
--------------------------------------------------
FS3 Blocks..............................61
FS3 Bytes ..............................499712
--------------------------------------------------
FS4 Blocks..............................62
FS4 Bytes ..............................507904
--------------------------------------------------
FULL BLOCKS.............................168
FULL_BYTES .............................1376256
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> select bytes from dba_segments where segment_name='T1' and owner='SYS';

BYTES
----------
3145728

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
268

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
208

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................208
FULL_BYTES .............................1703936
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.


SYS@XE> set autotrace on
SYS@XE> select count(*) from t1;

COUNT(*)
----------
15418


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 14146 | 62 (2)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> delete from t1;

15418 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 775918519

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | DELETE | T1 | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
45 recursive calls
17171 db block gets
291 consistent gets
0 physical reads
5533796 redo size
939 bytes sent via SQL*Net to client
929 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15418 rows processed

SYS@XE> commit;

Commit complete.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
212 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................208
FS4 Bytes ..............................1703936
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................257
Last Used Block.........................3

PL/SQL procedure successfully completed.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Using PRAGMA EXCEPTION_INIT learned today

Here is more http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI

Execute to Parse

I’m confused about the execute-to-parse ratio in Statspack/automatic workload repository reports. I have observed this ratio as 12.02 percent. My team is suggesting a change to the CURSOR_SHARING parameter value—to SIMILAR—to improve this ratio. Is that a correct approach? Can you please explain what this ratio actually means?

The last part of that question should throw up a bunch of red warning flags. You are looking at a ratio: the execute-to-parse ratio. You feel it is “bad” and would like to fix it, so you suggest changing a parameter to fix it. But then you ask for the ratio to be explained, meaning you’re not really sure what it is, what it means, and if the value you have is bad. Not only that, but the suggestion to change CURSOR_SHARING cannot and will not change the execute-to-parse ratio.

OK, first of all, let’s start with an explanation of what the execute-to-parse ratio is. It is a measure of how many times, on average, your SQL statements are executed rather than parsed. The way this ratio is computed, it will be a number near 100 percent when the application executes a given SQL statement many times over but has parsed it only once. (An application must parse a SQL statement at least once to execute it but needs to parse the SQL statement only once to execute it over and over again.) This ratio will be near 0 percent if the application parses a statement every time it executes it (parse count = execute count). This ratio will go negative if the application parses more often than it executes (and that would definitely be a sign that something is seriously wrong in the application). The formula (from Statspack) is simply

'Execute to Parse %:’, round(100*
(1-:prse/:exe),2)

As you can see, if :prse (parse count) is about the same as :exe (execute count), the execute-to-parse ratio will be 0 percent. If :exe is much larger than :prse, :prse/:exe will be near zero and the execute-to-parse ratio itself will be near 100 percent. If :prse is greater than :exe, the execute-to-parse ratio will go negative (indicating “this is bad”).

So that is, technically speaking, what the ratio is. A number near 100 percent would be great but might not be attainable. A negative number should definitely be avoided—it would indicate that the application actually parses a SQL statement but never executes it. The application developers would need to be educated, because a parse is an expensive operation and their goal is to reduce the number of parse calls—not create extra, unnecessary ones!

So what about the observed percentage, 12 percent? There is room for improvement there, but it cannot come from CURSOR_SHARING. Changing that parameter from its default (and preferred) setting of EXACT to SIMILAR might change the type of parsing happening (the parameter change can convert a hard parse into a soft parse), but it will never reduce the number of parse calls made by the application. After the parameter change, the application will still be calling “prepare statement” to parse SQL—under the covers, it might be a soft parse, but it will still be a parse.

In short, your colleagues’ suggestion would do nothing to alter the execute-to-parse ratio and could, in fact, cause major issues in your already-running system. Don’t even consider changing the CURSOR_SHARING parameter in order to try to fix your execute-to-parse ratio.

The only way to modify the execute-to-parse ratio would be to alter the two variables used in the formula. You can change either (1) the number of times you parse or (2) the number of times you execute.

I vote for the first option. Your application should look for ways to reduce the number of times it parses SQL, and how to do this varies from environment to environment. Using JDBC, for example, you can enable JDBC statement caching (a quick search for “JDBC statement cache” will turn up plenty of information). Using .NET, you can also enable statement caching (a quick search for “Oracle .NET statement cache” will turn up lots of information as well).

My preferred way to improve your execute-to-parse ratio, however, is to move all the SQL out of the client application and into stored procedures. PL/SQL is a statement caching machine—it has, from its very beginning, kept a cache of cursors open for us. When you say “close this cursor” in PL/SQL, PL/SQL tells you, “OK, it’s closed,” but it really isn’t. The PL/SQL engine smartly keeps the cursor open, knowing that you are just going to call that stored procedure and execute that SQL again.

Suppose you have a subroutine in your client application that is called 10,000 times a day. Further, suppose that it executes five SQL statements and those statements are parsed and executed every single time that routine runs. You will be doing 50,000 parse calls and 50,000 executes.

If you were to move those five SQL statements into a stored procedure and execute just one PL/SQL call in the client application—even assuming that you didn’t cache that statement in the client—you would now have

• 10,000 parse calls for the PL/SQL block
• 5 parse calls for the 5 SQL statements
• 10,000 execute calls for the PL/SQL block
• 50,000 execute calls for the SQL in the PL/SQL code

So now you will have 10,005 parse calls and 60,000 executes (plus many fewer round-trips between client and server). The parse calls (be they hard or soft parses) are extremely expensive. Having this one application instance cut down the number of parse calls to 20 percent of what it used to be will have an impact on performance and scalability—a profound impact.

In short, the only way to really affect the execute-to-parse ratio is to change the number of execute or parse calls—and this is something the application developer has to do. No magic parameter can be set.

Upgrade adboe flashplugin

today open QQ farm as usually, however it requires me to use latest version of flashplugin.
No choice download it from adobe.com

hmc@hmc-desktop:~/Desktop/downloads$ ls -lrt inst*
-rw------- 1 hmc hmc 4022730 2010-02-06 10:02 install_flash_player_10_linux.deb
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -i --force-architecture install_flash_player_10_linux.deb
[sudo] password for hmc:
dpkg: warning: overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
dpkg: regarding install_flash_player_10_linux.deb containing adobe-flashplugin:
adobe-flashplugin conflicts with flashplugin-installer
flashplugin-installer (version 10.0.45.2ubuntu0.9.10.1) is present and installed.
dpkg: error processing install_flash_player_10_linux.deb (--install):
conflicting packages - not installing adobe-flashplugin
Errors were encountered while processing:
install_flash_player_10_linux.deb
hmc@hmc-desktop:~/Desktop/downloads$ man dpkg
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -r flashplugin-installer
dpkg: dependency problems prevent removal of flashplugin-installer:
flashplugin-nonfree depends on flashplugin-installer.
dpkg: error processing flashplugin-installer (--remove):
dependency problems - not removing
Errors were encountered while processing:
flashplugin-installer
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -r flashplugin-nonfree
(Reading database ... 372473 files and directories currently installed.)
Removing flashplugin-nonfree ...
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -r flashplugin-installer
(Reading database ... 372470 files and directories currently installed.)
Removing flashplugin-installer ...
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -i --force-architecture install_flash_player_10_linux.deb
dpkg: warning: overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
(Reading database ... 372464 files and directories currently installed.)
Unpacking adobe-flashplugin (from install_flash_player_10_linux.deb) ...
Setting up adobe-flashplugin (10.0.45.2-1) ...
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/iceape/plugins/flashplugin-alternative.so (iceape-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/iceweasel/plugins/flashplugin-alternative.so (iceweasel-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/mozilla/plugins/flashplugin-alternative.so (mozilla-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/firefox/plugins/flashplugin-alternative.so (firefox-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/xulrunner/plugins/flashplugin-alternative.so (xulrunner-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/midbrowser/plugins/flashplugin-alternative.so (midbrowser-flashplugin) in auto mode.
update-alternatives: using /usr/lib/adobe-flashplugin/libflashplayer.so to provide /usr/lib/xulrunner-addons/plugins/flashplugin-alternative.so (xulrunner-addons-flashplugin) in auto mode.



hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -s adobe-flashplugin
Package: adobe-flashplugin
Status: install ok installed
Priority: optional
Section: partner/web
Installed-Size: 10132
Maintainer: DL-Flash Player Ubuntu
Architecture: i386
Version: 10.0.45.2-1
Replaces: flashplugin (<< 6)
Provides: flashplugin-nonfree
Depends: debconf | debconf-2.0, fontconfig, libatk1.0-0 (>= 1.20.0), libc6 (>= 2.4), libcairo2 (>= 1.6.0), libfontconfig1 (>= 2.4.0), libfreetype6 (>= 2.3.5), libgcc1 (>= 1:4.1.1-21), libglib2.0-0 (>= 2.12.0), libgtk2.0-0 (>= 2.12.0), libpango1.0-0 (>= 1.20.5), libstdc++6 (>= 4.1.1-21), libx11-6, libxext6, libxt6, wget
Suggests: firefox, konqueror-nsplugins, libnspr4-0d, libnss3-1d, msttcorefonts, ttf-bitstream-vera | ttf-dejavu, ttf-xfree86-nonfree, x-ttcidfont-conf, xfs (>= 1:1.0.1-5)
Conflicts: flashplayer-mozilla, flashplugin (<< 6), flashplugin-installer, xfs (<< 1:1.0.1-5)
Description: Adobe Flash Player plugin version 10
This package will download the Flash Player from Adobe. It is a
Netscape/Mozilla type plugin. Any browser based on Netscape or Mozilla can use
the Flash plugin. This package officially supports the following browsers:
.
Firefox 2.x, Firefox 3.x, SeaMonkey 1.11
Npp-Applications: ec8030f7-c20a-464f-9b0e-13a3a9e97384, 92650c4d-4b8e-4d2a-b7eb-24ecf4f6b63a, aa5ca914-c309-495d-91cf-3141bbb04115
Npp-Description: Adobe Flash Plugin (http://www.adobe.com)
Npp-File: libflashplayer.so
Npp-Mimetype: application/x-shockwave-flash
Npp-Name: Adobe Flash Plugin
hmc@hmc-desktop:~/Desktop/downloads$ sudo dpkg -l adobe-flashplugin
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Description
+++-=====================================================-=====================================================-==========================================================================================================================
ii adobe-flashplugin 10.0.45.2-1 Adobe Flash Player plugin version 10
hmc@hmc-desktop:~/Desktop/downloads$ uname -a
Linux hmc-desktop 2.6.31-17-generic #54-Ubuntu SMP Thu Dec 10 17:01:44 UTC 2009 x86_64 GNU/Linux


:-)

What does VALIDATE STRUCTURE do?

SYS@ADMP> analyze table dbapay.CASH_REGISTER_CONTROL validate structure;

Table analyzed.



VALIDATE STRUCTURE

Specify VALIDATE STRUCTURE to validate the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.


For a table, Oracle Database verifies the integrity of each of the data blocks and rows. For an index-organized table, the database also generates compression statistics (optimal prefix compression count) for the primary key index on the table.
*

For a cluster, Oracle Database automatically validates the structure of the cluster tables.
*

For a partitioned table, Oracle Database also verifies that each row belongs to the correct partition. If a row does not collate correctly, then its rowid is inserted into the INVALID_ROWS table.
*

For a temporary table, Oracle Database validates the structure of the table and its indexes during the current session.
*

For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.

Oracle Database also computes compression statistics (optimal prefix compression count) for all normal indexes.

Oracle Database stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM.