Scenario 3 Data Management

Scenario 3 Data Management

Data management

Performance optimization

OLTP OLAP

Materialized view: A snapshot of a table

Transport table Space: Data Migration for heterogeneous platforms

Star Conversions: Fact tables

OLTP: Online transaction processing

1. Transaction-> DML

2. Small amount of data

3. Short processing time--SQL share, binding variable

4. Large concurrency

Olap,dss

1. The results are put in the table (materialized view) with the result of the data summary, the main query (mass).

2. Long processing time

3. Almost no concurrency

4. Do parallel processing

SGA + PGA

Memory_target: Auto-on-demand SGA and PGA

Eg:

Set Autotrace on

Set Linesize 120

Set time on

Set timing on

Set PageSize 20

Set Sqlprompt

DB Link: Connecting the front-end database and the back-end Database

Front Database (query)

Background database (statistics)

Read-write separation for materialized views

Eg:

Grant create materialized view to Scott

Set auto Trace off

Create materialized view EMP_MV1 as select sum (SAL) Sum_al, avg (sal) avg_sal, Min (sal) Min_al, Dept no from EMP GROUP by D Eptno;

Desc user_segments

Col Segment_name for A20

Select Segment_name, Segment_type, Tablespace_name, extents, bytes/1024 from user_segments where segment_name= ' EMP_MV1 ' ;

Eg:

Set Autotrace on

Select ...; (View resource consumption) execution plans execution plan

Storing complex query results in materialized views

PCTFREE: The general table Reserves 10% of the space to do DML operations such as update

Eg:show parameter rewrite

Drop materialized View Emp_mv1

Create materialized view emp_mv1 enable query rewrite as select sum (SAL) Sum_al, avg (sal) avg_sal, Min (sal) Min_al, Dept N O from the EMP Group by DEPTNO;

Li New email: [Email protected]

Manually build library scripts to modify from official document copies

Eg:desc Dbms_mview

Set Autotrace off;

SELECT * from EMP;

Update emp Set deptno=40 where empno = ...;

Commit

SELECT * from EMP;

SELECT * from Emp_mv1;

exec dbms_mview.refresh (' emp_mv1 ');

SELECT * from Emp_mv1;

Materialized view logging changes in base table data for quick refreshes

Eg:

SELECT * from tab;

Create INDEX Emp_mv1_ind on EMP_MV1 (deptno) tablespace indx;

Analyze index emp_mv1_ind compute ...;

PCT Refresh

Table Space Movement

Data migration:

1. EXPDP, IMPDP (exp, IMP) (Heterogeneous platform)

2. Tablespace Transport (heterogeneous platform) EXPDP metadata, Copu datafile

3. DG (Data Guard): Disaster tolerant data cloning (homogeneous platform)

4. ogg (Oracle Golden Gate): Paid software

5. Storage image

Table Space Self-contained detections:

Execute Dbms_tts.transport_set_check (' users ');

Eg:

CREATE TABLE EMP1 as SELECT * from EMP;

Create INDEX Emp1_empno on EMP1 (empno) tablespace indx;

ALTER TABLE EMP1 ADD constraint PK_EMP1 primary key (EMPNO);

ALTER TABLE EMP1 move tablespace indx;

Execute Dbms_tts.transport_set_check (' users ');

will use EXPDP

External Tables External Table

Rebuild OEM:

Emca-config Dbcontrol Db-repos-recreate

Unlock sh user

Fact table

Dimension table

Select Constraint_name, table_name from user_constraints;

Desc user_indexes

Select Index_name, table_name from user_indexes;

Star query: Connect queries between fact tables and dimension tables

Bitmap index

Show Parameter Star

Set Star_tranformation_enabled to True

Eg:

Emctl Status Dbconsole

Emctl Start Dbconsole

Merge Join Cartesian

Analyze the table t2:

Analyze table T2 compute statistatics;

Star-shaped conversions

Parallel queries

Setting the degree of parallelism (DOP degree of parallelism)

Eg:

Alter session force parallel query;

Alter session set parallel_degree_policy=limited; (Set all SQL execution parallelism)

Eg:

Show parameter Parallel

Parallel_min_servers: Specifies that several parallel processes are enabled at least

Set Autotrace on

R

Eg:

Alter session enable parallel DML;

Set Autotrace on

Insert/*+ Parallel (4) */into EMP1 select/*+ Parallel (4) */from EMP;

Eg:alter table Sh.sales parallel 8; (Sets the degree of parallelism of the table sales to 8)

Parallel_min_time_threshold: Specifies the execution time, the parallel query is automatically turned on, DOP

Scenario 3 Data Management