Oracle Database 12c DBA Civil Service Manual (8th Edition)-Chapter 5 Development and implementation of applications (continued)

5.2. Resource management

5.2.1. Realize database resource management

Resource plans, resource consumer groups, and resource plan directives need to be created.

A pending area must be created before using the Explorer command.

Enable ADMINISTRATOR_RESOURCE_MAANAGER system permissions for a session

Assign users to resource consumer groups

Create a resource plan directive to allocate related resources

1 switch consumer group

2 use SQL profile

5.2.2. Resize database objects

Using ASSM ignores the PCTUSED,FREELISTS and GROUPS parameters

1 reason: minimize space management requirements, reduce waste, improve reuse of deleted space

2 the golden planning of spatial computation

Keep the calculation simple, universal and consistent

3 basic planning of spatial calculation

Oracle allocates only the whole block, the block of the assigned group, and possibly larger or smaller areas

4 effect of disk size on performance

Read table data through ROWID or full scan

In the former, the disk area is not a factor affecting performance.

5 estimate the space requirements of the table

CREATE_TABLE_COST estimation using DBMS_SPACE packages

6 estimate the space requirements of the index

CREATE_INDEX_COST estimation using DBMS_SPACE packages

7 estimate the appropriate PCTFREE value

The PCTFREE value represents the percentage of free space used in each data block

For indexes with little change in key values and tables with few rows: 2

For tables that change frequently: 10-30

A linked row is a whole row that cannot be put into an empty block or has more than 255 columns.

Row migration is due to the movement of rows due to insufficient PCTREE space

8 reverse index

Used for IO contention or concurrent contention in queries that do not perform range scans and focus on indexes

9 resize the bitmap index

The bitmap index with a low base is between 2% and 10% of the B-tree index.

10 resize the index organization table

No ROWID stored in the order of primary keys

11 resize the table containing the large object LOB

When the total LOB data and locator value exceed 4000B, the data is stored separately from the main table.

12 resize the partition

5.2.3 using global temporary tables

CREATE GLOBEL TEMPORARY TABLE creates GTT

ON COMMIT DELETE ROWS can be specified based on transaction

ON COMMIT PRESERVE ROWS can be specified based on session

5.3 support for tables based on abstract data types

User-defined data types have related constructor methods

5.3.1 using object View

5.3.2 Security of abstract data types 5.3.3 create indexes on abstract data type properties

5.4 pause and suspend the database

Alter system quiesce restricted

Alter system unquiesce

Alter system suspend

Alter system resume

5.5 support for stacking × × hair

5.5.1 iterative column definition

Alter table tab1 set unnused columm col3

Alter table tab1 drop unused columns

5.5.2 Force cursor sharing

Bind variables are used in the program to reuse SQL commands that parse corpses in the shared area

5.6 Management package development

5.6.1 generate a chart

5.6.2 Space requirements

5.6.3 adjust the target

5.6.4 Security requirements

5.6.5 data requirements

5.6.6 version requirements

5.6.7 execution Plan

5.6.8 acceptance testing process