Some dba interview question generally faced by me.
1)How do you verify the No. of Databases running on a Host A: ps -ef|grep pmon
either check cat /etc/oratab/ file
2)How do you verify the name of the database
A:select name from v$database; Or Show parameter db_name;
3)How do you verify whether your database is running o rnot A: Here is how to check if your database is running:
check_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`; oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ] then
exit 0 fi
4)How do you verify when the database is created
5)How do you verify since when your database is running 6)How do you verify the name of your instance
A:Sql>ssshow instance;
7)How do you verify the mode of your database
A: SQL> SELECT LOG_MODE FROM SYS.V$DATABASE; LOG_MODE
---NOARCHIVELOG SQL> startup mount
SQL> alter database archivelog; SQL> alter database open;
There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified. V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar
information. V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database; LOG_MODE
---ARCHIVELOG
SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST 8)How do you enable automatic archiving
A: SQL> shutdown SQL> startup mount
SQL> alter database archivelog; SQL> alter database open;
9)How do you do manual archiving A: SQL>startup mount
SQL>alter database archivelog manual; SQL> archive log list
10)How do you set the archive file format
11)What is the physical structure of your database
12)How do you verify whether instance is using pfile or spfile A: 1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT
NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.
13)How do you start an instance with spfile
A: SQL> startup pfile=\\C0027\database\SPFILEZWEITEDB.ORA 14)How do you start an instance with pfile
15)How do you read the contents of spfilesss &
A: i could read the content of the spfile with , Vi editor 16)How do you change the contents of spfile
A: it's content can ,only be altered with
SQL>alter system set <parameter=value> scope=memory or spfile;
17)List out the Initialisation parameters used by your instance A:
Parameter Name Description
BACKGROUND_DUMP_DEST:Specifies the directory where the trace files generated by the background processes are to be written. This is also the location of the alert log for the database.
COMPATIBLE: Provides Oracle with the understanding of what features you intend the database to have. If there is a feature in 9i that was not available in 8i and this parameter is set to 8.1.7, the feature will fail to perform.
CONTROL_FILES :The location of the control files for the database.
DB_BLOCK_SIZE:The default block size for the database.
USER_DUMP_DEST:Specifies the directory where the trace files generated by user sessions are written.
CORE_DUMP_DEST :Specifies the location where core dump files generated by Oracle are written.
DB_NAME :The name of the database and also of the SID.
INSTANCE_NAME :The name of the instance and, with the exception of a RAC environment, also the database and the SID.
OPEN_CURSORS :The maximum number of cursors that you want to have opened in your instance at any given time.
18)Who is the owner of your oracle software
19)What is the version of your database A: SQL> select * from v$version;
20)What is the version of your sqlplus 21)Where is the SQLPLUs located A: $ORACLE_HOME/bin directory
22)Who is the owner of data dictionary
A: The SYS user owns the data dictionary. The SYS and SYSTEM users are created when the database is created.
23)Where is data dictionary located
24)What are the dynamic views available in Nomount stage 25)What are the dynamic views available in Mount stage
26)What are the data dictionary views available in NOmount Stage 27)What are the data dictionary view available in Mount STage
28)How do you change the database from a Mount stage to Nomount stage
29)How do you view the current log sequence No. A:sql> ARCHIVE LOG LIST;
Below it will display current sequence number
30)What is the difference between instance name and databasename A: Database: A collection of physical operating system files or disk. When using Oracle 10g Automatic Storage Management (ASM) or RAW partitions, the database may not appear as individual separate files in the operating system, but the definition remains the same.
* Instance: A set of Oracle background processes/threads and a shared memory area, which is memory that is shared across those threads/processes running on a single computer
31)Write down the steps to change the database mode to NoarchiveLog
A: SQL> select log_mode from v$database; LOG_MODE
---NOARCHIVELOG
SQL> startup nomount;
SQL> alter database noarchivelog; SQL> alter database open;
32)What are the contents of the alert log file
33)Where are the Background processes logging information written to 34)How do you specify the location of the Background processes
logging information
35)How do you specify the location of the User Processes logging information
Installing Oracle9i/10g at unix (RedHat) environment
2) What are the components of physical database structure of Oracle database
3) What is the difference between materialized view and snapshots A: Snapshot is a copy of a table on a remote system but materialized view is used to make a snapshot of a table available on a remote system.
4) What is difference between base table and data dictionary views? A: Base tables are made when the database is created and these tables are stored in data dictionary. these base tables contains the information related to the database. these tables are own by sys. the information in these tables is crypted and cannot be modified. So we use the views to access the information in these base tables. these views are called data dictionary views. these views are created when we run the script
@ ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL 5) Cloning and Standby Databases
A: Cloning is nothing but the copy of your database which can be open in read write mode. The standby database is also a copy of your
database which is in standby mode and which is made in sink with production database by applying the redo log generated at source database (prodcution database). This database can not be open in read write mode. This standby database can be mode in read write mode by activating the database which reset's it's redo log sequence.
6) What is SCN number in Oracle? Plz any one give me the explanation for SCN
A: The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database. Every time a user commits a transaction Oracle records a new SCN. You can obtain SCNs in a number of ways for example from the alert log. You can then use the SCN as an identifier for purposes of recovery.
7) Is VARCHAR2 size optimization worthwhile ?
8) How to manager Memory in Oracle Database? How to maximize nos. of user in Oracle Database?
9) Index tablespace for a Database
A: There is no such provision in oracle to have default index
tablespace. Workaround is- you can have a job which will scan for indexes in other tablespaces and rebuild into desired one.
10) What are the fixed memory structures inside the SGA?
A: Part of the SGA contains general information about the state of the database and the instance which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes such as locking information.
With the dynamic SGA infrastructure the size of the buffer cache the shared pool the large pool and the process-private memory can be changed without shutting down the instance.
Dynamic SGA allows Oracle to set at run time limits on how much virtual memory Oracle uses for the SGA. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components up to a maximum of SGA_MAX_SIZE.
11) what is directory naming in oracle9i ?
A: Oracle Net Services use a centralized directory server as one of the primary methods for storage of connect identifiers. Clients configured directory usage can use the connect identifiers in their connect string. The directory server resolves the connect identifier to a connect
descriptor that is passed back to the client.
oracle net services support oracle internet directory and microsoft active directory
12) What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG ? A: backup the entire database...b’coz, alter system archive log start otherwise the database halts if unable to rotate redo logs
13) What is the difference between Pctused and PctFree?
A: PCT USERD - The Percentage of Minimum space user for insertion of New Row data. The value determines when the block gets into the FREELISTS structure
PCTFREE - The Percentage of Space reserved for future updation of existing data
14) how to find which tablespace belongs to which datafile ? A: SQL> select tablespace_name,file_name from dba_data_files; 15) What is a synonym
A: A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
SQL> CREATE SYNONYM emp FOR SCOTT.EMP; SQL> DROP SYNONYM emp
16) What is a Schema ?
17) What is a deadlock ? Explain .
A: Two processes wating to update the rows of a table which are locked by the other process then deadlock arises.
18) What is a latch?
A: A latch is a serialization mechanism. In order to gain access to a shared data structure, you must "latch" that structure. that will prevent others from modifying it while you are looking at it or modifying it your self. It is a programming tool.
19) Latches vs Enqueues
A: Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more sophisticated mechanism which permits several concurrent processes to have varying degree of sharing of "known" resources. Any object which can be concurrently used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode or in share update mode
20) What is difference between Logical Standby Database and Physical Standby database?
A: Physical standby differs from logical standby:
Physical standby schema matches exactly the source database.
Archived redo logs and FTP'ed directly to the standby database which is always running in "recover" mode. Upon arrival, the archived redo logs are applied directly to the standby database.
Logical standby is different from physical standby:Logical standby database does not have to match the schema structure of the source database.
Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby database.
Logical standby tables can be open for SQL queries (read only), and all other standby tables can be open for updates.Logical standby
database can have additional materialized views and indexes added for faster performance.
21) Explain about Oracle Statistics parameter in export?
A: Export is one of taking backup. In export backup you can specify many parameters in par file or at command line. One of the parameter is statistics=y or n. If you specify y it is going to export statistics
generated in the database placed in dump file.22) what is difference between latch,locks and enqueue ?
23) Waht is the frequency of log Updated..?
A: 1.COMMIT or ROLLABCK 2.time out occurs (3 secs) 3 1/3 of log is full 4. 1 mb of redo 5. Checkpoint occurs
LGWR writes:
1) COMMIT/ ROLLBACK 2) 1 MB of large transaction 3) Before DBWR writes 4) 1/3 of REDO LOG is full 5) time out occurs
6) Check Point encounters whenever commit,checkpoint or redolog buffer is 1/3rd full
24) Which process writes data from data files to database buffer cache?
25) what is the difference between local managed tablespace & dictionary managed tablespace ?
A: The basic diff between a locally managed tablespace and a dictionary managed tablespace is that in the dictionary managed tablespace every time a extent is allocated or deallocated data
dictionary is updated which increases the load on data dictionary while in case of locally managed tablespace the space information is kept inside the datafile in the form of bitmaps every time a extent is allocated or deallocated only the bitmap is updated which removes burden from data dictionary.
26) What is clusters ?
A: A cluster is a data structure that improves retrieval performance. A cluster, like an index, does not affect the logical view of the table. A cluster is a way of storing related data values together on disk. Oracle reads data a block at a time, so storing related values together reduces the number of I/O operations needed to retrieve related values, since a single data block will contain only related rows.
A cluster is composed of one or more tables. The cluster includes a cluster index, which stores all the values for the corresponding cluster key. Each value in the cluster index points to a data block that contains only rows with the same value for the cluster key.
27) What is an extent
28) Database Auto extend question
A: This is an Interview Question By BMC Software.. " while installing the Oracle 9i ( 9.2) version, automatically system takes the space of
approximately 4 GB.. thats fine.... Now, if my database is growing up and it is reaching the 4GB of my database space...Now, i would like to
extend my Database space to 20 GB or 25 GB... what are the things i have to do " Pls give me the accurate Solutions or alternates for this Query.
29) How to know which query is taking long time?
30) where does the SCN resides (system change number)
A: SCN changes for every 3 minutes in 10g and for each and every action in 9i. It resides in control files and data files. CKPT (checkpoint) background process updates the SCN numbers from control files to the datafiles. SMON (system monitor) background process checks for the SCN numbers to be same in both datafiles and control files while starting the database. If same then only the database is consistent. Otherwise the database will not start.
31) What is RAC? What is Data Migration? What is Data Pumping? 32) Is it possible to drop more than one table using single sql statement? if yes then how.
A: No because we can drop only one table or table data by using drop. 33) One DDL SQL script that has kept at certain location should be run on multiple servers to keep database synchronize. This task has to do in oracle database and this should be done as a job from scheduler. How will you do it?
A: There are many ways to do that. Following is the one of the ways I would prefer as I do it usually.
You can achieve this by having a small Unix Scripting / Windows Shell Scripting / any other scripting (including PR*C).
This Unix/WSH/ script has to go through a loop for each database and get connect and execute the SQL script.
34) How to you move from Dedicated server Process to a Shared Server Process
A: Use DBCA toolYou will get the option to select shared server mode. 1. set SHARED_SERVERS=(more than 1) in init.ora
2. make changes in tnsnames.ora file to get the connection with DISPATHERS rather than dedicated servers
35) What are the attributes of the Virtual Indexes
A: It does not store any data value in it unlike normal index do.Queries will not get benefitted. This can be used only for analysis.
1. These are permanent and continue to exist unless we drop them. 2. Their creation will not affect existing and new sessions. Only
sessions marked for Virtual Index usage will become aware of their existence.
3. Such indexes will be used only when the hidden parameter _use_nosegment_indexes is set to true.
36) HOW 2 ENABLE PARTITIONING FEAUTURE IN ORACLE 8i
37) You have taken import of a table in a database. you have got the Integrity constraint violation error. How you are going to resolve it. A: use this DDL statement in create table script to avoid integrity constraint violation error DROP TABLE tabl_name CASCADE
CONSTRAINTS ; cascade constraints delete foreign keys associated with table and table frees with foreign keys.
38) Why in 10G, when you use real time apply feature in conjunction with Maximum Protection, you can achive zero data loss, but not zero database downtime??
A: When you say If the last standby database configured in this mode becomes unavailable processing stops on the primary database. does 'processing stops' mean committing a transaction stops on the primary database...if so there is data loss correct?
39) What is ORA-1555?
A: ORA-1555 error can occur in Oracle 10g also even with UNDO RETENTION GUARANTEE enabled.
ORA-1555 happens when Oracle server process could not find the block-image in UNDO tablespace for the read-consistency.
40) How can the problem be resolved if a SYSDBA, forgets his password for logging into enterprise manager?
A: I think there are 2 ways to do that.
1. Login as SYSTEM and change the SYS password by using ALTER USER.
2. Recreate the password file using orapwd and set
remote_password_file=exclusive and then restart the instance.
41) What is the correct sequence among FETCH, EXECUTE, And PARSE A: 1. Parse
2. Execute 3. Fetch
42) What is database link
A: A database link is a pointer in the local database that allows you to access on a remote database.
43) What is a Database instance ? Explain
44) What is an Index ? How it is implemented in Oracle Database ?
45) What is Parallel Server ?
46) What is a deadlock and Explain
47) What are the components of logical database structure of Oracle database
48) What is an Oracle index 49) What is a tablespace
50) When a database is started, Which file is accessed first? 51) How to handle data curreption for ASM type files?
52) Do a view contain data
A: No, View never contain the the data, Only defination of it stores in the data base, when ever you invoke them they show you the data based on their defination.Only Materlized view or SnaptShot contain the the data.
53) How many maximum number of columns can be part of Primary Key in a table in Oracle 9i and 10g?
A: The maximum number of columns that can be a part of Primary key in a table in Oracle 10g is 32.
54) I am getting error "No Communication channel" after changing the domain name? what is the solution?
A: Change the domain name in the sqlnet.ora file in NAMES.DEFAULT_DOMAIN parameter.
55) When a user comes to you and asks that a particular SQL query is taking more time. How will you solve this?
A: If you find the Sql Query (which make problem) then take a Sqltrace with explain plan it will show how the sql query will executed by oracle depending upon the report you will tune your database
for example: one table have 10000 record but you want to fetch only 5 rows but in that query oracle does the full table scan.
only for 5 rows full table is scan is not a good thing so create a index on the particular column by this way to tune the datatabse
56) How to find how many database reside in Oracle server in query? A: select count(*) from v$database;
Or open oratab
57) What process writes from data files to buffer cache? 58) Can you tell something about Oracle password Security? A: If user authentication is managed by the database security
administrators should develop a password security policy to maintain database access security. For example database users should be required to change their passwords at regular intervals and of course when their passwords are revealed to others. By forcing a user to modify passwords in such situations unauthorized database access can be reduced.
Set the ORA_ENCRYPT_LOGIN environment variable to TRUE on the client machine.
Set the DBLINK_ENCRYPT_LOGIN server initialization parameter to TRUE.
59) What is the function of redo log
A: redo log is a part of physical structure of oracle. its basic function is to record all the changesmade to daatabase information. wheneveer an abnormal shutdown take place preventing system to update the database changes can be obtained from redolog and hence the changes are not lost. 60) What is SYSTEM tablespace and when is it created 61) How to DROP an Oracle Database?
A: You can do it at the OS level or go to dbca and click on delete database
62) what is RAP?
63) Can you start a database without SPfile in oracle 9i? A:no
64) Where we use bitmap index ?
A: Bitmap indexes are most appropriate for columns having low distinct values
65) what are the diffrent file types that are supported by SQL*Loader?
A: 1. .txt 2. .dat 3. .csv 4. .mdb
66) how do sql statement processing oracle database? A: When a select statement is executed first of all the statements hash code is genrated then that hash code is matched in library cache if the hash code matched then statement is directly executed and if the hash code is not present then hard parsing is done and statement is
executed
67) How to Estimate the size of Tablespace???
68) How to query to know the structure of a single Database and from more than one database.
69) how to estimate size of database?
70) What is difference between spfile and init.ora file??? 72. Explain the relationship among database, tablespace and data file.What is schema
A: -- A Oracle Database consists of one or more tablespaces
--- Each Table space in an Oracle database consists of one or more files called datafiles.
--- A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
73. Name init.ora parameters which effects system performance.
A: These are the Parameters for init.ora DB_BLOCK_BUFFERS
SHARED_POOL_SIZE SORT_AREA_SIZE
DBWR_IO_SLAVES ROLLBACK_SEGMENTS SORT_AREA_RETAINED_SIZE
DB_BLOCK_LRU_EXTENDED_STATISTICS SHARED_POOL_RESERVE_SIZE
74. What is public database link
A: Database link is a schema object in one database to access objects in another database. When you create database link with Public clause it is available for access to all the users and if you omit this clause then database link is privat and available only to you.
75. What are the uses of rollback segment A: The uses of Roll Back Segment are :
1. Transaction Rollback 2. Transaction Recovery 3. Read Consistency
76. What is the use of control file
77. What is difference between SQLNET.ORA AND TNSNAMES.ORA AND LISTENER.ORA??
A: Oracle uses all three files (tnsnames.ora, sqlnet.ora, listener.ora) for network configuration.
78. What is the difference between .ora and net file or .ora and .net or tnsnames.ora sqlnet.ora listener.ora what ever the differnence makes between ora and net.
A: .ora files contain Oracle Engine papameters info .net files contain O.S engine parameter info
79. What are materialized views? when are they used? A: Use of Meterialized view:-
Expensive operations such as joins and aggregations do not need to be reexecuted.
If the query is astisfied with data in a Meterialized view, the server transforms the query to reference the view rather than the base tables.
81. What is the database holding Capacity of Oracle ?
A: database holding capacity of oracle 10 g is 8 trillion tera bytes. 82. How do you rename a database?
A: STEP 1: Backup the database.
STEP 2: Mount the database after a clean shutdown: SHUTDOWN IMMEDIATE
STARTUP MOUNT
STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
nid TARGET=sys/password@TSH1 DBNAME=TSH2
Assuming the validation is successful the utility prompts for
confirmation before performing the actions. Typical output may look something like:
C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2 DBNEWID: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. Connected to database TSH1 (DBID=1024166118)
Control Files in database: C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y Proceeding with operation
Changing database ID from 1024166118 to 1317278975 Changing database name from TSH1 to TSH2
Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name
Database name changed to TSH2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TSH2 changed to 1317278975.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option. Succesfully changed database name and ID.
DBNEWID - Completed succesfully. STEP 4: Shutdown the database: SHUTDOWN IMMEDIATE
STEP 5: Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.
STARTUP MOUNT
SHUTDOWN IMMEDIATE
STEP 6: Create a new password file:
orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10
STEP 7: Rename the SPFILE to match the new DBNAME.
STEP 8: If you are using Windows you must recreate the service so the correct name and parameter file are used:
oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora
If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
ORACLE_SID=TSH2; export ORACLE_SID
STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
lsnrctl reload
STEP 10: Open the database with RESETLOGS: STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS; STEP 11: Backup the database. 83. What are clusters
84. What is private database link
85. How can be determine the size of the database?
A: select sum(bytes)/1024/1024/1024 Size_in_GB from dba_data_files 86. Can you name few DBMS packages and their use?
A: DBMS_METADATA DBMS_STATS
DBMS_SUPPORT DBMS_SESSION
87. What is the view name where i can get the space in MB for tables or views?
88. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM?
89. How can you check which user has which Role. A: select * from dba_role_privs order by grantee;
90. How do you find wheather the instance was started with pfile or spfile
91. What are the Advantages of Using DBCA
A: You can use its wizards to guide you through a selection of options providing an easy means of creating and tailoring your database. It allows you to provide varying levels of detail. You can provide a minimum of input and allow Oracle to make decisions for you, eliminating the need to spend time deciding how best to set
parameters or structure the database. Optionally, it allows you to be very specific about parameter settings and file allocations.
92. State new features of Oracle 10g.
93. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
A: CBO (Cost Based Optimizer):Generates an execution plan for a SQL statement
optimizer_index_cost_adj parameter can be set to help CBO to decide an execution plan which effects the speed of SQL query.
we can also make necessary changes to the following parameters to effect CBO performance:
optimizer_search_limit & optimizer_max_permutations
A: The Primary function of the redo log is to record all changes made to data.
95. can we create index on long raw column? A: NO we can't create index on long raw column. 96. What does database do during mounting process?
A: During database mount process, Oracle would check for the
existence of controlfiles mentioned in init.ora file but it wont check the contents of the controlfile which is done during the opening of
database.
97. What is a database instance and Explain 98. What is Oracle table
99. What are the characteristics of data files
A: A data file can be associated with only one database. Once created a data file can't change size.One or more data files form a logical unit of database storage called a tablespace.
71) What are the different types of segments 72) What are the Advantages of Using DBCA 84) What are the types of database links
A: Private Database Link: You can create a private database link in a specific schema of a database. Only the owner of a private database link or PL/SQL subprograms in the schema can use a private database link to access data and database objects in the corresponding remote database.
Public Database Link : You can create a public database link for a database. All users and PL/SQL subprograms in the database can use a public database link to access data and database objects in the
corresponding remote database.
Global Database Link - When an Oracle network uses Oracle Names the names servers in the system automatically create and manage global database links for every Oracle database in the network. All users and PL/SQL subprograms in any database can use a global database link to access data and database objects in the corresponding remote
database.
85) When can hash cluster used
A: Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is
hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
86) What is cluster key
A: The related columns of the tables in a cluster is called the Cluster Key.
87) What is a private synonym 88) What is an Oracle view
89) What are Schema Objects
A: Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.
90) Can a tablespace hold objects from different schemes
A: It can be the only required option is that your tablespace have quota assigned to any user that want to store objects in it.
91) What is a segment 92) What is row chaining
A: In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs , the data for the row is stored in a chain of data block (one or more) reserved for that
segment.
93) What is an index and How it is implemented in Oracle database A: Indexes are used both to improve performence and to ensure uniquness of a column. Oracle automatically creates an index when a UNIQUE or PRIMARY key constarints clause is specified in a create table command.
94) What is a schema
95) What does a control file contains 96) How to define data block size
A: stansard block size which is set with parameter DB_BLOCK_SIZE cannot be changed after creating database. We can set non standard parameter size later with parameter DB_nk_BLOCK_SIZE and it can be changed.
98) What is index cluster 99) What are clusters
100) How are the index updates 101) What is a public synonym 102) What is an Oracle sequence
103) Can a view based on another view 104) What is the use of redo log information
A: The Information in a redo log file is used only to recover the
database from a system or media failure prevents database data from being written to a database's data files.
105) How do you pin an object.
106) Is it possible to configure primary server and stand by server on different OS?
A: NO. Standby database must be on same version of database and same version of Operating system.
107) Explain Oracle memory structure.
Oracle uses memory to store information such as the following:
Program code
Information about a connected session, even if it is not currently active
Information needed during program execution (for example, the current
state of a query from which rows are being fetched)
Information that is shared and communicated among Oracle processes
(for example, locking information)
Cached data that is also permanently stored on peripheral memory (for
example, data blocks and redo log entries)
The basic memory structures associated with Oracle include:
System Global Area (SGA), which is shared by all server and
background processes and holds the following:
o
Database buffer cache
oRedo log buffer
o
Shared pool
Program Global Areas (PGA), which is private to each server and
background process; there is one PGA for each process. The PGA holds
the following:
o
Stack areas
oData areas
Figure
7-1
illustrates the relationships among these memory structures.
Figure 7-1 Oracle Memory Structures108) What are memory structures in Oracle? 109) What is a datafile
110) What is data block
111) What are synonyms used for
A: Synonyms are used to : Mask the real name and owner of an object. 112) What is a cluster Key ?
113) What are the basic element of Base configuration of an oracle Database ?
A: It consists of
one or more data files. one or more control files.
two or more redo log files. The Database contains multiple users/schemas
one or more rollback segments one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito) PMON (Process MONitor) LGWR (LoG Write)
DBWR (Data Base Write) ARCH (ARCHiver)
CKPT (Check Point) RECO
Dispatcher
User Process with associated PGS
114) How Materialized Views Work with Object Types and Collections
11.Backup and Recovery Interview Questions
Some of the Common Backup and Recovery Interview Questions for Oracle Database Administrator. These questions are common for both Senior Oracle DBA or Junior DBA. I have compiled these questions
based upon the feedback I got from many candidates who have attended interviews in various MNC's
1. Which types of backups you can take in Oracle?
2. A database is running in NOARCHIVELOG mode then which type of backups you can take?
A: If your Databse is in No Archivelog Mode then you must take a Cold backup of your Database.
3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?
4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?
A:no
5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
6. You cannot shutdown the database for even some minutes, then in which mode you should run the database?
7. Where should you place Archive logfiles, in the same disk where DB is or another disk?
8. Can you take online backup of a Control file if yes, how? 9. What is a Logical Backup?
10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
11. Why do you take tablespaces in Backup mode? 12. What is the advantage of RMAN utility?
Advantage over tradition backup system:
1). copies only the filled blocks i.e. even if 1000 blocks is allocated to datafile but 500 are filled with data then RMAN will only create a backup for that 500 filled blocks.
2). incremental and accumulative backup. 3). catalog and no catalog option.
4). detection of corrupted blocks during backup;
5). can create and store the backup and recover scripts.
6). increase performance through automatic parallelization( allocating channels), less redo generation.
What is Channel?
Latest Answer : Channel is a link that RMAN requires to link to target database. This link is required when backup and recovery operations are performed and recorded. This channel can be allocated manually or can be preconfigured by using automatic channel ...
13. How RMAN improves backup time?
A: Add channel to improve the performance of rman but it create session on DB and I/O on disk will increase so configure channel at proper number.
14. Can you take Offline backups using RMAN?
Recall that an offline backup is a backup of the database while it is not running.
Hence, to perform our backup we will shutdown the database from RMAN and
then mount the database. We will perform the backup. Once the backup is
complete we will restart the database again. Here is an example of this process:
RMAN>shutdown immediateRMAN>startup mount RMAN>backup database;
RMAN>sql ’alter database open’;
Once this process is complete, you have completed your first backup
15. How do you see information about backups in RMAN? A: RMAN> List Backup;
A recovery catalog can be used to store metadata about multiple target databases. The tables and views constituting a recovery catalog are owned by a recovery catalog schema. Oracle recommends creating a recovery catalog schema in a separate dedicated database and not in the target database. A database containing a recovery catalog schema is called a recovery catalog database.
A: Recovery catalog is a repository of metadata that is available in the control file of the target database. Whenver we take backups using RMAN the copy of the backup is placed in the control file in the form of reusable records and as well as in the recovery catalog in the form of tables. So that while taking recovery also these table info is useful to apply the backup data
17. Should you place Recovery Catalog in the Same DB? a: Recovery catalog not in same target db
Can take backup without catalog
18. Can you use RMAN without Recovery catalog? 19. Can you take Image Backups using RMAN?
20. Can you use Backupsets created by RMAN with any other utility? 20.what is difference b/w hot backup & Rman backup?
To take both backups we should keep database in
archive log
mode.
RMAN will take the backup of database used block only
where as
hot backup will take physical existing database files
completely
21. Where RMAN keeps information of backups if you are using RMAN without Catalog?
A: RMAN keeps information of backups in the control file.
22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
for it in RMAN?
24. Which is more efficient Incremental Backups using RMAN or Incremental Export?
25. Can you start and shutdown DB using RMAN?
26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now? 29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?
31. You loss controlfile how do you recover from this? c
32. The current logfile gets damaged. What you can do now? 33. What is a Complete Recovery?
34. What is Cancel Based, Time based and Change Based Recovery? 35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in
ARCHIVELOG mode?
36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?
38. How do you recover from the loss of a controlfile if you have backup of controlfile?
39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?
41. Have you faced any emergency situation. Tell us how you resolved it?
42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the
parameters set to previous values.
some more oracle dba interview questions
1. explain the difference between a hot backup and a cold backup and the
benefits associated with each.
A:a hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. a cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. the benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. the benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. in addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
2. you have just had to restore from backup and do not have any control files.
how would you go about bringing up this database?
A:i would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
A:issue the create spfile from pfile command.
4. explain the difference between a data block, an extent and a segment.
A:a data block is the smallest unit of logical storage for a database object. as objects grow they take chunks of additional storage that are composed of contiguous data blocks. these groupings of contiguous data blocks are called extents. all the extents that an object takes when grouped together are considered the segment of the database object.
5. give two examples of how you might determine the structure of the table
dept.
A:use the describe command or use the dbms_metadata.get_ddl package.
6. where would you look for errors from the database engine? A:in the alert log.
7. compare and contrast truncate and delete for a table.A:both the truncate and delete command have the desired outcome of getting rid of allthe rows in a table. the difference between the two is that the truncate command is a ddl operation and just moves the high water mark and produces anow rollback. the delete command, on the other hand, is a dml operation, which will produce a rollback and thus take longer to complete.
8. give the reasoning behind using an index. A:faster access to data blocks in a table.
9. give the two types of tables involved in producing a star schema and the type
of data they hold.
A:fact tables and dimension tables. a fact table contains measurements while
dimension tables will contain data that will help describe the fact tables.
10. what type of index should you use on a fact table? A:a bitmap index.
11. give two examples of referential integrity constraints. A:a primary key and a foreign key.
12. 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 children tables?
A:disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
13. explain the difference between archivelog mode and noarchivelog mode and
the benefits and disadvantages to each.
A:archivelog mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. noarchivelog mode is basically the absence of archivelog mode and has the disadvantage of not being able to recover to any ball in time.
noarchivelog mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
14. what command would you use to create a backup control file? A:alter database backup control file to trace.
15. give the stages of instance startup to a usable state where normal users may access it.
A:startup nomount - instance startup startup mount - the database is mounted startup open - the database is opened
16. what column differentiates the v$ views to the gv$ views and how? A:the inst_id column which indicates the instance in a rac environment the information came from.
A:create a plan table with utlxplan.sql. use the explain plan set statement_id = 'tst1' into plan_table for a sql statement look at the explain plan with utlxplp.sql or utlxpls.sql
18. how would you go about increasing the buffer cache hit ratio? A:use the buffer cache advisory over a given workload and then query the
v$db_cache_advice table. if a change was necessary then i would use the alter system set db_cache_size command.
19. explain an ora-01555
A:you get this error when you get a snapshot too old within rollback. it can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
20. explain the difference between $oracle_home and $oracle_base. A:oracle_base is the root directory for oracle. oracle_home located beneath oracle_base is where the oracle products reside.
12.INDEXES-ORACLE
All About Indexes in Oracle What is an Index?
A:An index is used to increase read access performance. A book,
having an index, allows rapid access to a particular subject area within that book. Indexing a database table provides rapid location of specific rows within that table, where indexes are used to optimize the speed of access to rows. When indexes are not used or are not matched by SQL statements submitted to that database then a full table scan is
executed. A full table scan will read all the data in a table to find a specific row or set of rows, this is extremely inefficient when there are many rows in the table.
*It is often more efficient to full table scan small tables. The optimizer will often assess full table scan on small tables as being more efficient than reading both index and data space, particularly where a range scan rather than an exact match would be used against the index. An index of columns on a table contains a one-to-one ratio of rows
more on this later. An index is effectively a separate table to that of the data table. Tables and indexes are often referred to as data and index spaces. An index contains the indexed columns plus a ROWID value for each of those column combination rows. When an index is searched through the indexed columns rather than all the data in the row of a table is scanned. The index space ROWID is then used to access the table row directly in the data space. An index row is generally much smaller than a table row, thus more index rows are stored in the same physical space, a block. As a result less of the database is accessed when using indexes as opposed to tables to search for data. This is the reason why indexes enhance performance.
The Basic "How to" of Indexing
A:There are a number of important factors with respect to efficient and effective creation
and use of indexing.
The number of indexes per table.
The number of table columns to be indexed.
What datatypes are sensible for use in columns to be indexed?
Types of indexes from numerous forms of indexes available.
How does SQL behave with indexes?
What should be indexed?
What should not be indexed? Number of Indexes per Table
Whenever a table is inserted into, updated or deleted from, all indexes plus the table must be updated. Thus if one places ten indexes onto a single table then every change to that table requires an effective change to a single table and ten indexes. The result is that
performance will be substantially degraded since one insert requires eleven inserts to insert the new row into both data and index spaces. Be frugal with indexing and be conscious of the potential ill as well as the good effects produced by indexing. The general rule is that the more dynamic a table is the fewer indexes it should have.
A dynamic table is a table changes constantly, such as a transactions table. Catalog tables on the other hand store information such as
customer details; customers change a lot less often than invoices. Customer details are thus static in nature and over-indexing may be advantageous to performance.
Number of Columns to Index
Composite indexes are indexes made up of multiple columns. Minimize on the number of columns in a composite key. Create indexes with single columns. Composite indexes are often a requirement of traditional relational database table structures.
With the advent of object-oriented application programming languages such as Java, sequence identifiers tend to be used to identify every row in every table uniquely. The result is single column indexes for every table. The only exceptions are generally manyto-many join resolution entities.
It may sometimes be better to exclude some of the lower-level or less relevant columns from the index since at that level there may not be much data, if there are not many rows to index it can be more efficient to read a group of rows from the data space. For instance, a composite index comprised of five columns could be reduced to the first three columns based on a limited number of rows traversed as a result of ignoring the last two columns. Look at your data carefully when
constructing indexes. The more columns you add to a composite index the slower the search will be since there is a more complex
requirement for that search and the indexes get physically larger. The benefit of indexes is that an index occupies less physical space than the data. If the index gets so large that it is as large as the data then it will become less efficient to read both the index and data spaces
rather than just the data space.
Most database experts recommend a maximum of three columns for composite keys.
Datatypes of Index Columns
Integers make the most efficient indexes. Try to always create indexes on columns with fixed length values. Avoid using VARCHAR2 and any object data types. Use integers if possible or fixed length, short strings. Also try to avaoid indexing on dates and floatingpoint values. If using dates be sure to use the internal representation or just the date, not the date and the time. Use integer generating sequences wherever possible to create consistently sequential values.
Types of Indexes
There are different types of indexes available in different databases. These different indexes are applicable under specific circumstances, generally for specific search patterns, for instance exact matches or range matches.
The simplest form of indexing is no index at all, a heap structure. A heap structure is effectively a collection of data units, rows, which is completely unordered. The most commonly used indexed structure is a B tree (Binary Tree). A B tree index is best used for exact matches and range searches. Other methods of indexing exist.
1. Hashing algorithms produce a pre-calculated best guess on general row location and are best used for exact matches.
2. ISAM or Indexed Sequential Access Method indexes are not used in Oracle. 3. Bitmaps contain maps of zero's and 1's and can be highly efficient access methods for read-only data.
4. There are other types of indexing which involve clustering of data with indexes. In general every index type other than a B tree involves overflow. When an index is required to overflow it means that the index itself cannot be changed when rows are added, changed or removed. The result is inefficiency because a search to find overflowing data involves a search through originally indexed rows plus overflowing rows. Overflow index space is normally not ordered. A B tree index can be altered by changes to data. The only exception to a B tree index coping with data changes in Oracle is deletion of rows. When rows are deleted from a table, physical space previously used by the index for the deleted row is never reclaimed unless the index is rebuilt.
Rebuilding of B tree indexes is far less common than that for other types of indexes since non-B tree indexes simply overflow when row changes are applied to them. Oracle uses has the following types of indexing available.
B tree index. A B tree is a binary tree. General all-round index and common in OLTP systems. An Oracle B tree index has three layers, the first two are branch node layers and the third, the lowest, contains leaf nodes. The branch nodes contain pointers to the lower level branch or leaf node. Leaf nodes contain index column values plus a ROWID pointer to the table row. The branch and leaf nodes are optimally arranged in the tree such that each branch will contain an equal number of branch or leaf nodes.
Bitmap index. Bitmap containing binary representations for each row. A zero implies that a row does not have a specified value and a 1 denotes that row having that value. Bitmaps are very susceptible to overflow in OLTP systems and should only be used for read-only data such as in Data Warehouses.
Function-Based index. Contains the result of an expression pre-calculated on each row in a table.
Index Organized Tables. Clusters index and data spaces together physically for a single table and orders the merged physical space in the order of the index, usually the primary key. An index organized table is a table as well as an index, the two are merged.
Clusters. Partial merge of index and data spaces, ordered by an index, not necessarily the primary key. A cluster is similar to an index organized table except that it can be built on a join (more than a single table). Clusters can be ordered using binary tree structures or hashing algorithms. A cluster could also be viewed as a table as well as an index since clustering partially merges index and data spaces.
Bitmap Join index. Creates a single bitmap for one table in a join.
Domain index. Specific to certain application types using contextual or spatial data, amongst others.
Indexing Attributes
Various types of indexes can have specific attributes or behaviors applied to them. These behaviors are listed below, some are Oracle specific and some are not.
Ascending or Descending. Indexes can be order in either way.
Uniqueness. Indexes can be unique or non-unique. Primary keys must be unique since a primary key uniquely identifies a row in a table referentially. Other columns such as names sometimes have unique constraints or indexes, or both, added to them.
Composites. A composite index is an index made up of more than one column in a table.
Compression. Applies to Btree indexes where duplicated prefix values are removed. Compression speeds up data retrieval but can slow down table changes.
Reverse keys. Bytes for all columns in the index are reversed, retaining the order of the columns. Reverse keys can help performance in clustered server environments (Oracle8i Parallel Server / RAC
Oracle9i) by ensuring that changes to similar key values will be better physically spread. Reverse key indexing can apply to rows inserted into OLTP tables using sequence integer generators, where each number is very close to the previous number. When searching for and updating rows with sequence identifiers, where rows are searched for
Null values. Null values are generally not included in indexes.
Sorting (NOSORT). This option is Oracle specific and does not sort an index. This assumes that data space is physically ordered in the desired manner.
What SQL does with Indexes
A:In general a SQL statement will attempt to match the structure of itself to an index, the where clause ordering will attempt to match available indexes and use them if possible. If no index is matched then a full table scan will be executed. A table scan is extremely inefficient for anything but the smallest of tables. Obviously if a table is read sequentially, in physical order then an index is not required. A table does not always need an index.
What to Index
A:Use indexes where frequent queries are performed with where and order by clause matching the ordering of columns in those indexes. Use indexing generally on larger tables or multi-table, complex joins. Indexes are best created in the situations listed below.
Columns used in joins.
Columns used in where clauses.
Columns used in order by clauses.
In most relational databases the order-by clause is generally
executed on the subset retrieved by the where clause, not the entire data space. This is not always unfortunately the case for Oracle. Traditionally the order-by clause should never include the columns contained in the where cause. The only case where the order-by clause will include columns contained in the where clause is the case of the where clause not matching any index in the database or a requirement
for the order by clause to override the sort order of the where, typically in highly complex, multi-table joins.
The group-by clause can be enhanced by indexing when the range of values being
grouped is small in relation to the number of rows in the table selected. What not to Index
A:Indexes will degrade performance of inserts, updates and deletes, sometimes
substantially.
Tables with a small number of rows.
Static tables.
Columns with a wide range of values.
Tables changed frequently and with a low amount of data retrieval.
Columns not used in data access query select statements. Tuning Oracle SQL Code and Using
Indexes
What is SQL Tuning?
A:Tune SQL based on the nature of your application, OLTP or read-only Data Warehouse. OLTP applications have high volumes of concurrent transactions and are better served with exact match SQL where many transactions compete for small amounts of data. Read-only Data Warehouses require rapid access to large amounts of information at once and thus many records are accessed at once, either by many or a small number of sessions.
The EXPLAIN PLAN command can be used to compare different versions of SQL statements, and tune your application SQL code as required. When tuning OLTP applications utilize sharing of SQL code in PL/SQL procedures and do not use triggers unless absolutely necessary. Triggers can cause problems such as self-mutating transactions where a table can expect a lock on a row already locked by the same
termination commands such as COMMIT and ROLLBACK. In short, do not use triggers unless absolutely necessary.
The best approach to tuning of SQL statements is to seek out those statements consuming the greatest amount of resources (CPU, memory and I/O). The more often a SQL statement is executed the more finely it should be tuned. Additionally SQL statements executed many times more often than other SQL statements can cause issues withlocking. SQL code using bind variables will execute much faster than those not. Constant re-parsing of similar SQL code can over stress CPU time resources.
Tuning is not necessarily a never-ending process but can be iterative. It is always best to take small steps and then assess improvements. Small changes are always more manageable and more easier to implement. Use the Oracle performance views plus tools such as TKPROF, tracing, Oracle Enterprise Manager, Spotlight, automated scripts and other tuning tools or packages which aid in monitoring and Oracle performance tuning. Detection of bottlenecks and SQL
statements causing problem is as important asresolving those issues. In general tuning falls into three categories as listed below, in
order of importance and performance impact. 1. Data model tuning.
2. SQL statement tuning.
3. Physical hardware and Oracle database configuration.
Physical hardware and Oracle database configuration installation will, other than bottleneck resolution, generally only affect performance by between 10% and 20%. Most performance issues occur from poorly developed SQL code, with little attention to SQL tuning during development, probably causing around 80% of general system
performance problems. Poor data model design can cause even more serious performance problems than SQL code but it is rare because data models are usually built more carefully than SQL code. It is a common problem that SQL code tuning is often left to DBA personnel. DBA people are often trained as Unix Administrators, SQL tuning is conceptually a programming skill; programming skills of Unix
Administrators are generally very lowlevel,if present at all, and very different in skills requirements to that of SQL code.
Indexing
A:When building and restructuring of indexing never be afraid of removing unused indexes.The DBA should always be aware of where indexes are used and how.
Oracle9i can automatically monitor index usage using the ALTER INDEX index name [NO]MONITORING USAGE; command with
subsequent selection of the USED column from the V$OBJECT_USAGE column.
Taking an already constructed application makes alterations of any kind much more complex. Pay most attention to indexes most often utilized. Some small static tables may not require indexes at all. Small static lookup type tables can be cached but will probably be force table-scanned by the optimizer anyway; table-scans may be adversely affected by the addition of unused superfluous indexes. Sometimes table-scans are faster than anything else. Consider the use of
clustering, hashing, bitmaps and even index organized tables, only in Data Warehouses. Many installations use bitmaps in OLTP databases, this often a big mistake! If you have bitmap indexes in your OLTP database and are having performance problems, get rid of them! Oracle recommends the profligate use of function-based indexes, assuming of course there will not be too many of them. Do not allow too many programmers to create their indexes, especially not function-based indexes, because you could end-up with thousands of indexes. Application developers tend to be unaware of what other developers are doing and create indexes specific to a particular requirement where indexes may be used in only one place. Some DBA control and
approval process must be maintained on the creation of new indexes. Remember, every table change requires a simultaneous update to all indexes created based on that table.
SQL Statement Reorganisation
SQL statement reorganization encompasses factors as listed below, amongst others.
WHERE clause filtering and joining orders matching indexes.
Use of hints is not necessarily a good idea. The optimizer is probably smarter than you are.
Use bind variables to minimize on re-parsing. Buying lots of
expensive RAM and sizing your shared pool and database buffer cache to very large values may make performance worse. Firstly, buffer cache reads are not as fast as you might think. Secondly, a large SQL parsing shared pool, when not using bind variables in SQL code, will simply fill up and take longer for every subsequent SQL statement to search.
Oracle9i has adopted various SQL ANSI standards. The ANSI join syntax standard could cause SQL code performance problems. The most effective tuning approach to tuning Oracle SQL code is to remove rows from joins using where clause filtering prior to joining multiple tables, obviously the larger tables, requiring the fewest rows should be filtered first. ANSI join syntax applies joins prior to where clause
filtering; this could cause major performance problems. Nested subquery SQL statements can be effective under certain
circumstances. However, nesting of SQL statements increases the level of coding complexity and if sometimes looping cursors can be utilized in PL/SQL procedures, assuming the required SQL is not completely ad-hoc.
Avoid ad-hoc SQL if possible. Any functionality, not necessarily business logic, is always better provided at the application level. Business logic, in the form of referential integrity, is usually best catered for in Oracle using primary and foreign key constraints and explicitly created indexes. Nested subquery SQL statements can become over complicated and impossible for even the most brilliant coder to tune to peak efficiency. The reason for this complexity could lie in an over-Normalized underlying data model. In general use of subqueries is a very effective approach to SQL code performance tuning. However, the need to utilize intensive, multi-layered subquery SQL code is often a symptom of a poor data model due to
requirements for highly complex SQL statement joins. Some Oracle Tricks
Use [NOT] EXISTS Instead of [NOT] IN
In the example below the second SQL statement utilizes an index in the subquery because of the use of EXISTS in the second query as opposed to IN. IN will build a set first and
EXISTS will not. IN will not utilize indexes whereas EXISTS will. SELECT course_code, name FROM student
WHERE course_code NOT IN
(SELECT course_code FROM maths_dept); SELECT course_code, name FROM student WHERE NOT EXISTS
(SELECT course_code FROM maths_dept
WHERE maths_dept.course_code = student.course_code);
In the example below the nesting of the two queries could be reversed depending on which table has more rows. Also if the index is not used or not available, reversal of the subquery is required if tableB has significantly more rows than tableA.
DELETE FROM tableA WHERE NOT EXISTS
(SELECT columnB FROM tableB WHERE tableB.columnB = tableA.columnA);
Use of value lists with the IN clause could indicate a missing entity. Also that missing entity is probably static in nature and can potentially be cached, although caching causing increased data buffer size
requirements is not necessarily a sensible solution. SELECT country FROM countries WHERE continent IN ('africa','europe','north america');
Equijoins and Column Value Transformations
AND and = predicates are the most efficient. Avoid transforming of column values in any form, anywhere in a SQL statement, for instance as shown below.
SELECT * FROM <table name> WHERE TO_NUMBER(BOX_NUMBER) = 94066;
And the example below is really bad! Typically indexes should not be placed on descriptive fields such as names. A function-based index would be perfect in this case but would probably be unnecessary if the data model and the data values were better organized.
