超难解决的问题:Oracle性能的优化

最新推荐文章于 2024-04-17 20:10:03 发布

幸福的小木鱼

最新推荐文章于 2024-04-17 20:10:03 发布

阅读量835

点赞数

分类专栏：数据库学习(Oracle,SqlServer,MySql) 文章标签： oracle database 数据库 character delete function

本文链接：https://blog.youkuaiyun.com/lin_lujian/article/details/6904019

版权

数据库学习(Oracle,SqlServer,MySql) 专栏收录该内容

23 篇文章

订阅专栏

关键还是建库的合理性，
分别建：
SYSTEM、INDEX、USERS、ROLLBACK、TEMP、
SYNONYM、BACKUP
TABLESPACE

经验：
1、合理使用索引
2、调整SGA
3、扩大会回滚段
4、db_block_size，把它设为至少8k,16k更好，这样你的数据库的操作效率会大大提高。
5、尽量用索引查询
6、内存开大点（按G算）
7、机器性能主流配置
8、换8i
9、采用硬盘阵列
10、增加TABLE的INCREA PCT

以上是对付1亿条以上数据的记录的，你只需参考几项。

某外企dba面试题

1. compare and contrast truncate and delete for a table;

2. a table is classified as a parent table and you want to drop and re-create it. how would you do this without affecting the chiledren tables?

3. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

4.Explain the difference between $oracle_home and $oracle_base.

5. Explain the difference between a FUNCTION,PROCEDURE AND PACKAGE.

6. Your database currently has one control file.You, after carefull consideration,decide that adding two more control file will provide better protection against a single point of failure at the control file level. To accomplish this ,you edit the initSID.ora file to point to the new location.

control_files='/mydatabase/mydb1/control_01.ctl',
'/mydatabase/mydb1/control_02.ctl',
'/mydatabase/mydb1/control_03.ctl',

You shut down your database normal and copy the control files to the new location.

CP /mydatabase/mydb1/control_01.ctl /mydatabase2/mydb1/control_02.ctl
CP /mydatabase/mydb1/control_01.ctl /mydatabase3/mydb1/control_03.ctl

when you restart you database using the altered pfile,you receive the error
ORA-00205:error in identifying control file

you look in the alert log, and determine that you specified the incorrect path for the control files, which steps are required to resolve the problem and restart the database?

7. You isssue the command
alter tablespace temp add datafile '/mydatabase/mydb1/temp02.dbf' 100m;
and you receive the orror
ora-03217:invalid option for alter of temporary tablespace
what is the problem with the statement?

8. what kind of index does the following syntax create?
create index address_state_idx
on address
pctfree 10
tablespace mydb1idx
nologging;

9.when is the sga created in an oracle instance> when is the pag created in an oracle instance?

10. where would you look for your database's national character set? what two new unicode character encoding sets did oracle introduce in 9i?

1. （1）truncate 是DDL操作，且删除的数据信息不计入redo log，效率高； delete DML操作，删除的信息写入redo log，效率低
（2）truncate 降低了HWM；delete不降低HWM

2. disable外键约束，重建完成以后enable外键约束

3. 归档和不归档模式的比较：
归档模式下数据库是可以恢复到任意一个时间点的，而不归档的模式下数据库不能恢复到任意一个时间点，由于归档需要写归档日志，所以归档模式下数据库性能比不归档模式下稍差一点。

4. ORACLE_HOME是Oracle数据库的安装目录，ORACLE_BASE则是Oracle产品的安装目录

5. package 是把一些互相之间有联系，或者业务上存在相近或者实现同一个目的的function, procedure组合在一起，便于迁移吧。function是需要返回一个值的，procedure是用来操作数据的，不需要返回值的，如果需要返回值的话，可以通过out参数来返回。

6. 关闭数据库，修改initSID.ora文件，然后重新启动数据库

7. alter tablespace temp add datafile '/mydatabase/mydb1/temp02.dbf' 100m; 不应该给temp表空间增加datafile，而是tempfile

8. 普通的B树索引，但是不写入日志

9. nomount的时候就已经分配了sga. 啥时候分配的pga不记得了，赶紧查查去

10. 数据库服务器端的字符集 select * from nls_database_parameters
9i新引入的unicode字符集还真的确实不知道