Personal Face Test (Oracle database development)

1, transaction slot update delete contention transaction slot each block has a data slot 2, 1 million partition 100 million partition difference ... 


100 million The amount of data can be considered sub-division table partition Reasonable query performance no difference 3, 8 million data index query several data performance promotion very big query a lot of data whole table better than index to return to table 4, three normal form the first normal form column can not be divided into the second normal form requires the primary key third normal form of foreign key constraints, reduce redundancy 5, stored procedure trigger function syntax/difference on the Internet to see the details of the 6, the constraints of what kinds of five (forgot) PRIMARY KEY constraint FOREIGN KEY constraint unique constraint check constraint non-null constraint 7, AWR event wait event lock db file scattered read file scatter read
Taking this event is usually related to a full table scan or a fast total index scan. DB file sequential read file order read whole code, especially table join: most common wait event buffer busy waiting buffer busy increase db_cache_size, speed checkpoint, adjust code latch free: When latch loss rate above 0
.5%, this issue needs to be adjusted. The Enqueue queue is a lock that protects some shared resources and prevents concurrent DML operations 8, Database optimization method 9, SGA PGA system Global area system total memory including share pool buffer cache Enjoy SQL District Store user SQL command data dictory cache data dictionary Caching storage database running dynamic information DB buffer cache cached data file read-out block large pool Java pool person
 
 
El Global area separate memory allocated for each user 10, audit use shutdown, etc.-audit--table-based Audit CREATE TABLE Test_audit (ID number, name VARCHAR2 (20));


Audit all on Logminer.test_audit;
INSERT into Test_audit values (1, ' Dong ');


Commit
Delete from Test_audit;


Commit Select Os_username, USERNAME,
       Userhost, TERMINAL, TIMESTAMP, OWNER, Obj_name, Action_name, SessionID,


Os_process, sql_text from Dba_audit_trail b where b.owner= ' Logminer ';




Noaudit all on Logminer.test_audit; -Audit AUDIT DELETE any TABLE; --Audit Delete table Operations AUDIT Delete any table whenever not successful; --Audit deletion failure only AUDIT delete any TABLE whenever successful; --Audit deletion success only AUDIT Delete,update,insert on user.table by test;


--Audit test user's Delete,update,insert operation on table user.table--revocation audit noaudit all on t_test; 11, the Implementation plan analysis steps * * Indent from right to left from top down///////////////////////////////////////////////////// District view Merge into merging CBO can change table join order with hint the access predicate affects the data Azimuth path table or index filter filter only set Autotrace on/12, materialized view creation usage scene--materialized test Figure/* Normal view is a virtual table materialized view is a real-world table create materialized view log create materialized view materialized view type on demand need to be refreshed refresh on commit transaction submit refresh materialized view the same as normal table dictionary information Refresh method manual Refresh Auto Brush NEW */Create materialized view Mv_name Refresh Force on demand start with sysdate
Next To_date (Concat (To_char (sysdate+1, ' yyyy-mm-dd '), ' 22:00:00 '), ' Yyyy-mm-dd hh24:mi:ss ') as Select D.deptno,count (*
CNT from EMP e,dept D where E.deptno=d.deptno group by D.deptno;
13. The RAC RMAN RAC is only installed on this machine with a heap of problems. Rman also used backup recovery on the local machine, there may also be some parameter settings, timed script 14, List meta ancestor dictionary to die say understand Python even the foundation is not clear list-[1,2,3] square brackets orderly modifiable Yuan Zu-(1,2,3) parentheses ordered not modified dictionary-{' NA
Me ': ' Dong, ' age ': 21 '} curly braces Key-value set-{1,2,3,4,5} unordered not repeat 15, several states of the database select status from V$instance; Shutdown Nomount startup Nomount started mount ALTER DATABASE Mount/startup Mount mounted open alter Databa Se open/startup Open--closes four shutdown normal shutdown transaction--waits for all transactions to be submitted shutdown immediate--uncommitted transactions are rolled back shutdown Abort--Force shutdown exception shutdown 16, control file loss How to handle/* Advance backup control file 1, build can be rebuilt * *