Application schema checklist
Perform physical configuration:
Does each application have its own schema?
Does each schema have its own set of table and index tablespaces?
Are tables and their corresponding indexes in separate tablespaces?
Check on performance issues:
If you are implementing referential integrity, are all core foreign keys indexed?
Are there tables without indexes?
Are there tables with too many indexes?
Are there tables with similar indexes?
Are the schema objects regularly analyzed?
Are there any invalid objects? (functions, procedures,triggers and packages; others can be ignored)
Are there any external tables?
Are there any partition tables?
Are there any triggers?
Are correct datatypes used? size and type
Are there any object statistics locked?
Are there another tablespace for index?
Are there any views, indexs, procedures, triggers etc naming convention.
Should not use a strings to store dates or times
Should not use strings to store numbers
Should not use VARCHAR(4000) to store all strings
Should not use CHAR(2000) to store all strings
Check on security issues:
Are all object grants performed through roles? (While doing this is not strictly necessary, it does make administration much easier.)
If your applications allow for it, are all updating capabilities granted through nondefault roles?
Check on miscellaneous issues:
Are naming conventions in place for all database objects? (While using consistent naming conventions is not strictly necessary, it does make administration much easier.)
Server
=======
Is Archivelog mode enabled?
What is the archive log format?(must have %R)
Is Automatic Database statistics collection(in 10g) enabled?
select * from dba_scheduler_jobs where job_name like 'GATHER_STATS%';
What is the Optimizer mode of the database?
Is there any DBMS_SCHEDULER(OR DBMS_JOBS) running?
What is the backup methods used to backup database?
Control files backup
Backup retention time
Is Flashback database feature enabled?
The database is running with shared server or dedicated server mode?
Is SQL_TRACE feature disabled?(must be disabled)
What are the user privileges？
Are control files and redo log files multiplexed？
What is the default temporary tablespace of the users？（prior to 10g, default is SYSTEM)
What is the value of compatible parameter?
Check the wait events
**An expert guide to Oracle
Check scheduler jobs details
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;
Post a Comment