DBA interview questions — Alibaba

These questions come to the Alibaba DBA interview, how many can you answer? Maybe you can answer that you are one step further from the DBA. Please also try it below. An answer to the question will be attached to the back of the question. It may not be completely correct and is for reference only.

Round 1: SQL tuning class

1: List several table connection methods

Answer: merge join, hash join, nested loop

2: Without using third-party tools, how to view the execution plan of sql?

Answer: sqlplus, set autotrace …, utlxplan.sql create plan_table table

3: How to use CBO, the difference between CBO and RULE?

Answer: You can use cbo by setting optimizer_mode=choose/all_rows/first_row in the initialization parameters. rbo will choose an inappropriate index, cbo needs statistical information.

4: How to locate important (more resource-consuming) SQL?

Answer: According to the logical read /disk_read in v$sqlarea. As well as looking for sessions with excessive CPU usage, find out the current SQL statement of the current session, or: monitor the operation of Oracle on the WIN platform

5: How to track the SQL of a session?

Answer: Find out the corresponding’sid, serial’ first, and then call system_system.set_sql_trace_in_session(sid, serial, true); Reference: Trace a session

6: What is the most concerned about SQL adjustment?

Answer: logical read. IO amount

7: Talk about your understanding of the index (index structure, impact on dml, impact on query, why improve query performance)

Answer: The default index is b-tree. Impact on insert: split, we must ensure the balance of the tree. Effect on delete: When deleting a row, mark and change the node to delete. Impact on update: If the index field in the table is updated, the key value in the index must be updated accordingly. The query contains the key value of the index field and the physical address of the row.

8: Will using the index query improve the query performance? why?

Answer: No. If the number of rows returned is large, the performance of using a full table scan is better.

9: Array: What is the binding variable? What are the advantages and disadvantages of binding variables?

Answer: In layman’s terms, a bind variable is a placeholder for a variable. Using bind variables can reduce parsing of statements that differ only in their value.

10: How to stabilize (fix) the execution plan?

Answer: Use stored outline.

11: How to adjust the memory related to sorting in 8i and Arrayi respectively, what is the role of temporary table space?

Answer: 8i: Use sort_area_size, hash_area_size, and assign the same value to each session, regardless of whether it is used or not. Arrayi: Use pga_aggregate for unified management. The role of the temporary table space: the part that cannot be completed in sort_area_size is completed in the temporary table space. The temporary table space is used in rebuilding indexes and creating temporary tables. There are also temporary table spaces that hash join cannot complete.

12: There is a table T (a, b, c, d), if you want to sort according to the field c and take the 21–30 records to display, please give sql

select a,b,c,d from (select a,b,c,d from T order by c) where rownum< li>

minus

select a,b,c,d from (select a,b,c,d from T order by c) where rownum <li>

or:

select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;

The second round: database basic concepts

1: What does pctused and pctfree mean?

Answer: indicates when the data block is moved into and out of the freelist. pctused: If the usage rate of the data block is less than the value of pctused, the data block is added to the fresslist again. ctfree: If the usage of the data block is higher than the value of pctfree, the data block is removed from the freelist.

2: Briefly describe the relationship between table / segment / extent / block

Answer: A table is at least one segment. If you partition a table, each partition is a segment. A table can be regarded as a logical concept, and a segment can be regarded as a physical realization of this logical concept; a segment consists of one or more The extents are composed of segments that cannot span table spaces but can span data files; extents are composed of multiple consecutive blocks and cannot span data files; blocks are composed of 1-multiple os blocks and are the smallest storage unit of oracle i/o.

3: Describe the relationship between tablespace and datafile

Answer: tablespace is a logical concept, and datafile is a physical concept. A tablespace can be composed of multiple datafiles, and a datafile cannot span multiple tablespaces.

4: What are the characteristics of ASSM for locally managed tablespace and dictionary managed tablespace?

A: One uses freelist management and one uses bitmap management.

5: What is the role of the rollback segment?

Answer: Save the front image of the data to ensure the consistency of the time point of data reading. The multi-version feature of the data in Oracle is realized by rolling back the segment. Because of this, the Oracle database achieves the performance advantage of non-competitive reading and writing!

6: What is the role of the log?

Answer: Record operations on the database for easy recovery.

7: What are the main parts of SGA, what is the main role?

Answer: db_cache (cache data block), shared_pool (cache sql, execution plan, data dictionary information, etc.), large_pool (MTS mode, parallel, rman, etc. to be used), java pool (java program such as SQLJ stored procedure to use To).

8: What are the main processes of the Oracle system and what is their role?

Answer: smon (merge space, instance recovery), pmon (cleanup failed process), archive process (responsible for archiving log files during log switch), lgmr (log writer process, responsible for writing logs), ckpt (checkpoint process , Trigger checkpoint), dbwr (database writer, responsible for writing data to the guide datafile).

The third round: backup and recovery

1: How to classify backups?

Answer: logical backup (exp) and physical backup. Or cold backup and hot backup.

2: What does archiving mean?

Answer: Put the log file in another place.

3: If a table is dropped at 2004–08–04 10:30:00, how to restore it if there is perfect archiving and backup?

Answer: copy backup, recover database until time 2004–08–04 10:30:00 alter database open resetlogs;

4: What is rman and what are its characteristics?

Answer: rman is called the recovery manager. Many features. It can be backed up online, until the target database is in the mount state. 1) Hot backup. 2) Script can be stored. 3) Incremental backup. 4) Automatically manage backup sets

5: Characteristics of standby

Answer: Use transfer redo logs to achieve synchronization. Multiple protection levels can be set. Arrayi supports logical standbyDB.

6: For a system that requires a relatively short recovery time (database 50G, daily archive 5G), how do you design a backup strategy

Answer: A full backup every day.

Fourth round: System management

1: For a system with system performance, state your diagnosis and processing ideas

Answer: Make a statspack and adjust accordingly according to top 5, system load, top sql, etc.

2: List several methods to diagnose IO, CPU, performance status

Answer: hp-unix:iostat -x 1 5; top/vmstat/glance

3: What do you know about statspack?

A: It’s just a performance diagnostic tool. Its essence is to sample two system data at two time points. (Dynamic performance view), and then generate a report based on two snapshots.

4: If the system now needs to create an index on a large table, you will consider those factors and how to do it to minimize the impact on the application

Answer: 1) Increase the sort_area_size (8i)/pga_aggregate_target (Arrayi) value. 2) If the table has partitions (partitions are usually used for large tables), build indexes one by one, if it is a local index. 3) Built when the system is idle.

5: What do you know about raid10 and raid5?

Answer: raid10 is a mirror first and then stripe. It is suitable for database systems with high write speed requirements, especially online redolog files. raid5 is suitable for most database systems and data warehouse systems. The read performance is better than the write performance.

Thanks for the reading the post.