 |
|
The ANALYZE Command
Oracle Tips by Burleson Consulting
|
The ANALYZE command can also be used to
generate chained row information into the Remote DBA_TABLES view. Actual
chained-row rowids can be listed in a separate table if desired. The
general format of this command follows:
ANALYZE
TABLE_or_CLUSTER [schema.]table_or_cluster
LIST CHAINED ROWS INTO [schema.]table;
Under Oracle8, Oracle8i, and Oracle9i, there
is a script called utlchain.sql that will build the chained row table
for you. An example of a script to perform a chain analysis of a table
for an owner is shown in Source 10.10. Source 10.11 shows a second
script, which, with the script in Source 10.10 altered as annotated,
will analyze all tables for a specified owner for chains. Listing
10.10 shows the results of this automated chain analysis for an
owner’s tables.
SOURCE 10.10
Interactive SQL script to determine chained rows in a table.
rem
****************************************************************
rem
rem NAME: CHAINING.sql
rem
rem FUNCTION: Report number of CHAINED rows within a named table
rem
rem NOTES: Requires Remote DBA priviledges.
rem Target table must have
column that is the leading portion
rem of an index and is
defined as not null.
rem Uses the V$SESSTAT
table. USERNAME is the current user.
rem A problem if > 1 session
active with that USERID.
rem V$SESSTAT may change
between releases and
rem platforms. Make
sure that 'table fetch continued row' is
rem a valid statistic.
rem This routine can be run
by AUTO_CHN.sql by remarking the
rem two accepts and
un-remarking the two defines.
rem
rem INPUTS: obj_own = the owner of the table.
rem obj_nam =
the name of the table.
rem
rem
*******************************************************************
ACCEPT obj_own PROMPT 'Enter the table owner''s name: '
ACCEPT obj_nam PROMPT 'Enter the name of the table: '
rem DEFINE obj_own = &1 ? Remove comment to use with
auto_chain
rem DEFINE obj_nam = &2 ? Remove comment to use with
auto_chain
SET TERMOUT OFF feedback OFF VERIFY OFF ECHO OFF HEADING OFF
SET EMBEDDED ON
COLUMN statistic# NEW_VALUE stat_no NOPRINT
SELECT
statistic#
FROM
v$statname
WHERE
n.name = 'table fetch continued row'
/
rem Find out who we are in terms of sid
COLUMN sid NEW_VALUE user_sid
SELECT
distinct sid
FROM
v$session
WHERE
audsid = USERENV('SESSIONID')
/
rem Find the last col of the table and a not null indexed column
COLUMN column_name NEW_VALUE last_col
COLUMN name NEW_VALUE indexed_column
COLUMN value NEW_VALUE before_count
SELECT
column_name
FROM
Remote DBA_tab_columns
WHERE
table_name = upper('&&obj_nam')
and owner = upper('&&obj_own')
ORDER BY
column_id
/
SELECT
c.name
FROM
sys.col$ c,
sys.obj$ idx,
sys.obj$
base,
sys.icol$ ic
WHERE
base.obj# = c.obj#
and ic.bo#
= base.obj#
and ic.col# =
c.col#
and base.owner# = (SELECT user# FROM sys.user$
WHERE name = UPPER('&&obj_own'))
and ic.obj#
= idx.obj#
and base.name = UPPER('&&obj_nam')
and ic.pos# =
1
and c.null$ >
0
/
SELECT value
FROM v$sesstat
WHERE v$sesstat.sid = &user_sid
AND v$sesstat.statistic# = &stat_no
/
rem Select every row from the target table
SELECT &last_col xx
FROM &obj_own..&obj_nam
WHERE &indexed_column <= (SELECT MAX(&indexed_column)
FROM &obj_own..&obj_nam)
/
COLUMN value NEW_VALUE after_count
SELECT value
FROM v$sesstat
WHERE v$sesstat.sid = &user_sid
AND v$sesstat.statistic# = &stat_no
/
SET TERMOUT ON
SELECT
'Table '||UPPER('&obj_own')||'.'||UPPER('&obj_nam')||' contains '||
(TO_NUMBER(&after_count) - TO_NUMBER(&before_count))||
' chained row'||
DECODE(to_NUMBER(&after_count) - TO_NUMBER(&before_count),1,'.','s.')
FROM dual
WHERE RTRIM('&indexed_column')
IS NOT NULL
/
rem If we
don't have an indexed column this won't work so say so
SELECT 'Table '||
UPPER('&obj_own')||'.'||UPPER('&obj_nam')||
' has no indexed, not null columns.'
FROM dual
WHERE RTRIM('&indexed_column') IS NULL
/
SET TERMOUT
ON feedback 15 VERIFY ON PAGESIZE 20 LINESIZE 80 SPACE 1
SET HEADING ON
UNDEF obj_nam
UNDEF obj_own
UNDEF before_count
UNDEF after_count
UNDEF indexed_column
UNDEF last_col
UNDEF stat_no
UNDEF user_sid
CLEAR COLUMNS
CLEAR COMPUTES
SOURCE 10.11 The AUTO_CHN.SQL
script to automate chaining determination.
rem **********************************************************
rem
rem NAME: AUTO_CHN.sql
rem
rem FUNCTION: Run CHAINING.sql for all of a users tables.
rem
rem NOTES:Requires mod to CHAINING.sql. See CHAINING.sql header
rem
rem INPUTS:
rem tabown = Name of owner.
rem
rem ***********************************************************
rem
ACCEPT tabown PROMPT 'Enter table owner: '
rem
SET TERMOUT OFF feedback OFF VERIFY OFF ECHO OFF HEADING OFF PAGES 999
SET EMBEDDED ON
COLUMN name NEW_VALUE db NOPRINT
SELECT name FROM v$database;
SPOOL rep_out\auto_chn.gql
rem
SELECT 'start chaining &tabown '||table_name
FROM Remote DBA_tables
WHERE owner = UPPER('&tabown')
/
SPOOL OFF
SPOOL rep_out\&db\chaining
START rep_out\auto_chn.gql
SPOOL OFF
UNDEF tabown
SET TERMOUT ON feedback 15 VERIFY ON PAGESIZE 20 LINESIZE 80 SPACE 1
SET EMBEDDED OFF
HO del rep_out\auto_chn.gql
PAUSE Press enter to continue
LISTING 10.10 Example of output when
AUTO_CHN.SQL is run against a user’s tables.
Table
SYSTEM.CGS_REFLINE contains 0 chained rows.
Table SYSTEM.CGS_WKSTATION contains 0 chained rows.
Table SYSTEM.CGS_WSATTRIBUTES contains 0 chained rows.
Table SYSTEM.CGS_WSCOLORS contains 0 chained rows.
Table SYSTEM.CGS_WSFONTS contains 0 chained rows.
Table SYSTEM.CGS_WSLNSTYLES contains 0 chained rows.
Table SYSTEM.CGS_WSPATTERNS contains 0 chained rows.
Table SYSTEM.Remote DBA_TEMP has no indexed, not null columns.
Table SYSTEM.DEF$_CALL contains 0 chained rows.
Table SYSTEM.DEF$_CALLDEST contains 0 chained rows.
Table SYSTEM.DEF$_DEFAULTDEST contains 0 chained rows.
Also provided in the Remote DBA_TABLES view is a column showing
chained rows for a specific table. If you don’t particularly care what
rows are chained, just whether you have chaining, a simple query
against this view will tell you, if you have analyzed the table.
Monitoring Grants on a Table
The Remote DBA also needs to monitor grants on
tables. It is good to know who is granting which privileges to whom.
The script to determine this is shown in Source 10.12. Listing 10.11
shows the listing generated from Source 10.12.
SOURCE 10.12 SQL script to show object-level
grants.
rem*******************************************************************
rem NAME: db_tgnts.sql
rem
rem FUNCTION: Produce report of table or procedure grants
showing
rem GRANTOR, GRANTEE or ROLE and specific GRANTS.
rem
rem INPUTS: Owner name
rem
********************************************************************
rem
COLUMN grantee FORMAT
A18 HEADING "Grantee|or Role"
COLUMN owner
FORMAT A18 HEADING "Owner"
COLUMN table_name FORMAT A30
HEADING "Table|or Proc"
COLUMN grantor FORMAT
A18 HEADING "Grantor"
COLUMN privilege FORMAT A10
HEADING "Privilege"
COLUMN grantable FORMAT A19
HEADING "Grant|Option?"
rem
BREAK ON owner SKIP 4 ON table_name SKIP 1 ON grantee ON grantor ON
REPORT
rem
SET LINESIZE 130 PAGES 56 VERIFY OFF feedback OFF
START title132 "TABLE GRANTS BY OWNER AND TABLE"
DEFINE OUTPUT = rep_out/&&db/db_tgnts
SPOOL &output
REM
SELECT
owner,
table_name,
grantee,
grantor,
privilege,
grantable
FROM
Remote DBA_tab_privs
WHERE
owner NOT IN ('SYS','SYSTEM')
ORDER BY
owner,
table_name,
grantor,
grantee;
REM
SPOOL OFF
PAUSE Press enter to continue
LISTING 10.11 Example of report from grant
script.
Date:
05/22/96
Page:1
Time: 01:49 PM TABLE GRANTS
BY OWNER AND TABLE
SYSTEM
ORDSPTD6 database
Table
Grantee
Grant
Owner or Proc or Role
Grantor Privilege
Option?
-------- --------- ------------- --------- -------------------- ------
DSPTRemote DBA ACCCAR DSPT_DEV
DSPTRemote DBA DELETE
NO
INSERT
NO
SELECT
NO
UPDATE
NO
ALTER
NO
DSPT_USER DSPTRemote DBA DELETE
NO
UPDATE
NO
SELECT
NO
INSERT
NO
ACT
DSPT_DEV DSPTRemote DBA
DELETE
NO
SELECT
NO
UPDATE
NO
ALTER
NO
INSERT
NO
DSPT_USER DSPTRemote DBA DELETE
NO
UPDATE
NO
SELECT
NO
INSERT
NO
ADD_REC DSPT_USER
DSPTRemote DBA EXECUTE
NO
Using the above report makes it is easy to
monitor the grants on specific objects. A close look at the generation
script shows that this report may be as selective as the individual
object level, or as general as the entire database. Using this script,
the Remote DBA can find out the level of protection for any and all database
objects.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
