EVENT: 10060 dump predicates in optimizer (kko)

Event:10060
Text:   dump predicates in optimizer (kko)
-------------------------------------------------------------------------------
Explanation:
        This is a special EVENT code.
        It should *NOT* be used unless explicitly requested by ST support.
        This event can be useful in conjunction with an explain plan
        to confirm which operations occur where in the execution plan.
        It dumps the predicates into a special table.
Usage:
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE
        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
    
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
            );            
        - To enable tracing:
            ALTER session set events '10060 trace name context forever';
        - Run the statement to be traced
        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75
        - select * from KKOIPT_TABLE;
        - Best used in conjunction with <Event:10053> trace and an
          execution plan.
KKOIPT_TABLE.C2 output information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates
Example:
~~~~~~~
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;
Output: (from kkoipt_table)
         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"

EVENT:10218 dump uba of applied undo

@23-JAN-01, rpowell.uk, Distribution justification: "Should not be used by
@ customers unless told to by support so making noncustomer-viewable." - dbither.us
Event: 10218
Text:  dump uba of applied undo
-------------------------------------------------------------------------------
Note:
   Events should NEVER be set by customers unless advised to do so by
   Oracle Support Services.  See <Note:75713.1> for more details.
Description:
   This event simply shows the undo block addresses for the undo
   applied during recovery.  This can be useful to see the progress of
   the rolling out of transactions, and to help identify where a rollback
   has gotten stuck.
Example output:
         uba: 80000ca.0d.12
         uba: 80000ca.0d.11
         uba: 80000ca.0d.10
         uba: 80000ca.0d.0f
         uba: 80000ca.0d.0e
         uba: 80000ca.0d.0d
         uba: 80000ca.0d.0c
         uba: 80000ca.0d.0b

EVENT:10226 trace CR applications of undo for data

Error:  ORA 10226
Text:   trace CR applications of undo for data operations
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code. It can be used
to signal Oracle to perform SQL_TRACE actions.
This event causes application of UNDO for producing CR copies of
blocks. It can produce a lot of output (in the form of REDO
application dump to rewind the block)

EVENT:10061 disable SMON from cleaning temp segment

Error:  ORA 10061
Text:   disable SMON from cleaning temp segment
-------------------------------------------------------------------------------
Explanation:
        This is NOT an error but is a special EVENT code.
        It should *NOT* be used unless explicitly requested by RD support.
        It allows you to prevent SMON from cleaning up temporary segments.
        If set in:
         7.0.X:
            This will prevent SMON performing cleanup of TEMP segments
            and from coalsecing extents. The cleanup action was performed
            every 3 minutes in 7.0.X releases.
        7.1.X:
            The event behaviour is modified in that TEMP segment cleanup
            is skipped on TIMEOUTS but will be performed if SMON is
            posted. The cleanup only occurs every 12 hours or when posted
            in 7.1.X releases.
            You can POST SMON by performing a failed CREATE TABLE command.
            A new event 10269 is for coalescing extents.
Usage:  
        event="10061 trace name context forever, level 10"
Articles:
@Archived Managing SMON Activity on multiple OPS instances   <Note:45856.1>

EVENT:10212 check cluster integrity

Error:  ORA 10212
Text:   check cluster integrity
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
Event 10212 enables cluster block integrity checking.
The causes a number of checks on the contents of a block.
If the block is found to be corrupt in some way it is marked as
SOFTWARE CORRUPT.
NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT checked
when accessed for read.
Take care with this as if there are blocks in the database which
are being accessed happily but have a minor corruption this will
mark the block as corrupt.
Once a block in an cluster is marked SOFTWARE CORRUPT it is best to
rebuild the entire cluster.
Usage:  event="10212 trace name context forever, level 10"
Articles:
Data and Index block equivalents  <Oerr:10210> <Oerr:10211>

EVENT:10228 trace application of redo by kcocbk

Error:  ORA 10228
Text:   trace application of redo by kcocbk
-------------------------------------------------------------------------------
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.
Event 10228 is potentially useful in recovery situations since it
traces the application of redo. Therefore, if cache recovery
appears to hang or corrupt blocks then this can help track the dba
and operations upon it.
This event is available in Oracle 6 and 7.
The code just calls the same routines used to dump a redo log using
'alter system dump logfile' so has the same output.
The benefit is that it'll stop when it hits the corruption.
Articles:
Interpreting DUMP LOGFILE Output <Note:29726.1>

EVENT:10210 check data block integrity

Event:10210                  See  for similar events / parameters
~~~~~~~~~~~
Version/Use:
7.0 - 8.1.7  Check data block integrity after each modification
9.0+         This event is no longer valid. Use <Parameter:DB_BLOCK_CHECKING>
instead.
7.0 - 8.1.7 "Check data block integrity after each modification"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTE: Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. Read  before setting any event.
Summary Syntax:
This event should be set at instance level:
EVENT="10210 trace name context forever, level 10"
Levels:
This event is either ON (level 1 or higher) or OFF (not set)
Note that in 8.1.6 onwards you should use the
instead of this event.
Description/Steps:
Event 10210 enables data block integrity checking.
This causes the data layer to perform a number of checks on the
contents of a data block. If the block is found to be corrupt
in some way it is marked as SOFTWARE CORRUPT.
NB: Blocks are ONLY checked/corrupted when CHANGED. They are NOT
checked when accessed for read.
Take care with this, as if there are blocks in the database which
are being accessed happily but have a minor corruption such as
an incorrect free space count, this event will mark the block as
corrupt. Once marked SOFTWARE CORRUPT the data in the block
is not accessible to Oracle.
See  for more detail of various block checking features.
Example Output / Interpreting Output:
This event will cause an ORA-600 error to be signalled if a corruption
is detected after a data block has been modified. The exact content
of the trace file depends on the ORA-600 and the action at the time
of the corruption being noticed. Refer to the relevant ORA-600 article
for the error which is signalled.
Related:
Database block checking features
Index and Cluster block equivalents
See event 10231 which allows SOFTWARE CORRUPT blocks
to be skipped on full table scans

Known Oracle Internal Stack Call Meaning

 ksedmp             # KSE: dump the process state
ksfdmp             # Call relevant dump routine
kgeasi             # Raise an error on an ASSERTION failure (IGNORE)
ktcrab             KTC: Kernel Transaction Control Real ABort - Abort a
transaction.
ktcsod             KTC: Transaction Control: STATE OBJECT PROCEDURE VECTOR
DEFINITION
kssdch_stage       
kssdch             KSS: delete children of state obj.
ksures             
ktmres             ktmres - KTM Resource cleanup routine.
ktmmon             KTM: TX Monitor: background timeout action
ksbrdp             KSB: run a detached (background) process
opirip             # Oracle Program Interface Run Independent Process
(IGNORE)
opidrv             # opidrv - ORACLE Program Interface DRiVer (IGNORE)
sou2o              # Main Oracle executable entry point
main               # Standard executable entry point

Function Based Indexes and Global Temporary Tables

A nonunique index can be used to enforce a primary key or unique constraint.

In Oracle8i indexes can be rebuilt without locking the table.

The DROP COLUMN option of the ALTER TABLE command is restartable.

The MOVE option of the ALTER TABLE command retains the constraints of the table.

Data rows in the global temporary table are always deleted when A user session is terminated

1.    As user Scott, create a table with three columns.  Create an index on all three columns in the order they appear in the table.  Then add a primary key constraint using the first two columns with the second column of the table appearing first.  Verify that only one index, the index being used to enforce the constraint, has been defined for the table.

Hint: if the columns in the table were labeled A, B, and C, the index would be on (A, B, C) while the constraint would be on columns (B, A).

Solution:

 

connect scott/tiger

 

 

CREATE TABLE acct

( acct_no       NUMBER(10),

customer_id           NUMBER(10),

acct_comment VARCHAR2(200),

CONSTRAINT pk_cid_aid  PRIMARY KEY(customer_id, acct_no) DISABLE

)

/

CREATE INDEX I_ANO_CNO_ACOMM

ON acct(acct_no, customer_id, acct_comment)

ONLINE

/

ALTER TABLE acct

ENABLE CONSTRAINT pk_cid_aid

/

 

select index_name, table_name from user_indexes

where table_name = ‘ACCT’

/

2. As user Scott create a table containing three columns.  Remove the third column using one of the new methods introduced in Oracle8i.  Verify that the column is no longer part of the table.

Solution:

connect scott/tiger

 

 

CREATE TABLE acct_col

( acct_col_no         NUMBER(10),

customer_id           NUMBER(10),

acct_col_comment    VARCHAR2(200)

)

/

ALTER TABLE acct_col

SET UNUSED COLUMN acct_col_comment

/

 

desc acct_col

 

SELECT * FROM user_unused_col_tabs

/

ALTER TABLE acct_col

DROP UNUSED COLUMNS

/

SELECT * FROM user_unused_col_tabs

/

 

3. As user SYS create a global temporary table containing three columns.  The inserted rows should remain available until explicitly deleted or the session ends.  Make the table available to anyone who wishes to use it.  The users should not have to know the table owner in order to make use of it.

Solution:

connect / as sysdba

 

CREATE GLOBAL TEMPORARY TABLE emp_temp_X

(eno NUMBER,

ename VARCHAR2(20),

sal NUMBER)

ON COMMIT PRESERVE ROWS;

 

connect / AS SYSDBA

 

CREATE PUBLIC SYNONYM emp_temp for emp_temp_x

/

GRANT ALL ON emp_temp TO PUBLIC

/

col object_name format a20

SELECT owner, object_name, object_type FROM dba_objects

WHERE object_name LIKE ‘%EMP_TEMP%’

/

connect scott/tiger

 

desc emp_temp

 

select * from emp_temp

/

 

 

Materialized Views and Dimensions

Materialized Views and Refresh Types

This practice will familiarize you with the various features and privileges to ensure successful creation of a materialized view from a base table.

1) Grant the necessary privileges for user Scott to create materialized views and allow query rewrite on the materialized views owned by schema Scott.

As user SYSTEM, execute the following command:> grant CREATE MATERIALIZED VIEW, QUERY REWRITE to scott;

2) As user Scott, create a materialized view name STAFF_MV_SIMPLE from the Employees table. You want the materialized view to only store data  for the job of a STAFF, and you want a complete refresh.  You need to first create the EMPLOYEES table by importing employees.dmp.

As user Scott, execute the following command:> CREATE MATERIALIZED VIEW staff_mv_simple
REFRESH COMPLETE
AS SELECT * FROM EMPLOYEES  WHERE JOB = ‘STAFF’;

3) Create a materialized view name STAFF_MV_REFRESH, still only storing data  for the job of a STAFF,  but you want a refresh feature that will only apply the changes made to the base table since the last time you refresh the materialized view.  You will be creating a materialized view with a fast refresh.

As user Scott, execute the following command: > CREATE MATERIALIZED VIEW staff_mv_refresh
REFRESH FAST
AS SELECT * FROM EMPLOYEES  WHERE JOB = ‘STAFF’;

4) Create a materialized view name STAFF_MV_QR, still only storing data  for the job of a STAFF, using 2 parallel processes, allowing query rewrite, and you
want a complete refresh.

As user Scott, execute the following command: > CREATE MATERIALIZED VIEW staff_mv_qr
PARALLEL (DEGREE 2)
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’;

Query Rewrites

This practice will familiarize you with the various features of creating a materialized view with query rewrite capabilty.

1) Alter your session to allow query rewrite.

As user Scott, execute the following command: > alter session set QUERY_REWRITE_ENABLED = true;

2) Use EXPLAIN PLAN to verify that rewrite has taken place. Confirm you have a Plan_Table.  If you do not, please create it by running the utlxplan.sql file.
It should be located in the subdirectory where you installed Oracle.

For example, if Oracle 8.1.6 is install on c:oracle, then the file will be in the c:oracleora81rdbmsadmin

Create the Plan_table for schema Scott if it does not exist already.

As user Scott, execute the following command: > @c:oracleora81rdbmsadminutlxplan.sql

Confirm the plan_table exists.

As user Scott, execute the following command: > describe plan_table

3) Confirm materialized view STAFF_MV_QR will be use in a query rewrite request.

As user Scott, execute the following command: >delete from plan_table;

This is to ensure there are no row the the plan_table before populaing it with the explain plan results.

>explain plan for
> SELECT * FROM EMPLOYEES WHERE JOB = ‘STAFF’

>col Operation format a30
col Options   format a20
col Object    format a20

>select lpad(‘ ‘, 2*LEVEL) || OPERATION ||
decode( ID, 0, ‘ Cost = ‘||POSITION) “Operation”,
OPTIONS “Options”, OBJECT_NAME “Object”
from PLAN_TABLE
connect by prior ID = PARENT_ID  start with ID = 0
order by ID
/

Dimensions

This practice will familiarize you with the various features of creating a dimension, storing the hierachy definition in the database, and being familiar with the the data dictionary views that can be used to gather information regarding dimensions.

1) Confirm user Scott has the privilege to create a dimension.  If not,  grant that privilege to Scott.

As user System, execute the following command: > select grantee, privilege
from dba_sys_privs
where grantee = ‘SCOTT’; 

If you don’t see user Scott has the CREATE DIMENSION privilege, grant it to user Scott.

> grant create dimension to scott;

2) As user Scott, create a dimension name mv_time_dim from the time table with a hierarchy name scott_calendar.  Frist create the time table by exporting from
the file time.dmp.

As user Scott execute the following command: >CREATE DIMENSION mv_time_dim
LEVEL sdate IS time.sdate
LEVEL month IS time.month
LEVEL qtr   IS time.quarter
LEVEL yr    IS time.year
HIERARCHY scott_calendar
(sdate CHILD OF month CHILD OF qtr CHILD OF  yr)
ATTRIBUTE month DETERMINES month_name;

3) Determine the levels of the dimersion you have created.  To see that information, query the user_dim_levels view.
As user Scott execute the following command:

>select dimension_name, level_name, detailobj_name
from user_dim_levels;

Summary Management

1) After you have set up Oracle Trace Manager to monitor the utilization of your materialized views. you can determine if you should keep the materialized views you have created by querying the mview$_recommendations view.
As user Scott execute the following command:

>SELECT recommended_action, mview_name, group_by_columns, measures_list
FROM mview$_recommendations;

沪ICP备14014813号

沪公网安备 31010802001379号