How do we Tune the Queries?
A) Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical a nalysis about the query using which appropriate actions can be taken.
21. What is Explain Plan? How do u use Explain Plan in TOAD?
A) It is a utility provided by toad that gives the statistics about the performance of the query. It gives information such as number of full table scans occurred, cost, and usage of indexes
What is a TK-PROF and its usage?
A) Tk-Prof is a utility that reads the trace files and generates more readable data that gives the statistics about the performance of the query on a line to line basis.
What is Optimization? How many types of Optimization are there?
A) Rule based Optimization and Cost Based Optimization.
What is the default optimization chosen by Oracle?
A) Cost based Optimization.
What is the difference between When no data Found and cursor attribute % DATA FOUND?
A) When no Data Found is a predefined internal exception in PLSQL. Where as % Data found is a cursor attribute that returns YES when zero rows are retrieved and returns NO when at least one row is retrieved.
What is the difference between the snapshot and synonym?
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.
A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.
25. What is the difference between data types char and varchar?
A) Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).