Setting and Configuring the Workload Generator

mThis is Oracle by Example Module: Setting and Configuring the Workload Generator
ref: http://www.oracle.com/technology/obe/obe9ir2/obe-mng/wkldgen/wkldgen.htm

Loading the Data
-------------------
1. Download wkldgen.zip and unzip it into your working directory (e:\training-OracleDBA\oraexe\wkldgen).

2. Edit the following bat file:
OE_large_cre_popul.bat
OE_large_demo.bat
OE_large_drop.bat

Make sure that the working directory is set to your physicaly working directory path.
(YS: will directly execute sql not bat )

3. Open a DOS Command Prompt, go to your working directory, and
execute the OE_large_cre_popul.bat file.
This .bat file executes the SQL code to create two new tables in the OE schema: ORDERS_L and ORDER_ITEMS_L

(done successfully)


Setting Up the Workload Generator
--------------------------------

1. Open SQL*Plus and run the following script to create the Workload Generator PL/SQL package and package body:

@build_workload_generator

(done successfully)

2/3/4 Right-click on your desktop, select New..., then select Shortcut to create a shortcut to the Workload Generator application.

5. Right-click on My Computer from your desktop and select Properties. Click the Environment tab. Add a user variable called ORACLE_SID and set it to OEMREP (this is the database I intend to test, also is the OMS repository database, on my notebook), then click Set and then OK.

Testing the Workload Generator
--------------------------------
Now that you have set up the Workload Generator, you should test it to make sure that it is running appropriately. To do so, perform the following steps:

1. Open the Workload Generator application from your desktop. Click OK to accept the defaults.
2. Click the Options tab.
3. In the Order Entry box, enter 1 for Number of Users, and oe_work for the Script. In the Shipping box, enter 1 for the Number of Users, and sh_work for the Script and click Save Options. Then select the Statistics tab.
4. Click Start to begin running the procedure to generate a workload. You will see that some workload is being generated.
5. To stop the Workload Generator, click the Reset button and then click Yes.
(YS: only the default option (1 OE user 1 SH usr) is OK)

Using EXPLAIN PLAN, SQL Trace and TKPROF

SQL Trace facility and TKPROF
- access the efficiency of the SQL statements
- use these tools with EXPLAIN PLAN rather using EXPLAIN PLAN alone

TKPROF is be albe to
- format the contents of the trace file and place the output into a readable output file
- determine the execuaton plans of SQL statements
- create a SQL script that stores the statistics in the database

STEPS:

1. set initialization parameters
TIMED_STATISTICS
MAX_DUMP_FILE_SIZE
USER_DUMP_DEST

2. Enabling the SQL trace facility
- DBMS_SESSTION.SET_SQL_TRACE
- ALTER SESSION SET SQL_TRACE = TRUE;

3. Formatting Trace Files with TKPROF

TKPROF filename1 filename2 [ SORT = { option (option [option]...)} ]
[ PRINT = integer ] [ AGGREGATE = { YES NO } ]
[ INSERT = filename3 ]
[ SYS = { YES NO } ]
[ [ TABLE = schema.table ] EXPLAIN = user/password ]
[ RECORD = filename ]


-------------------
Using EXPLAIN PLAN
--------------------

- The EXPLAIN PLAN statement displays exercution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT and DELETE statements.
- before issuing an EXPLAIN PLAN statement, you must have a table to hold its output
- use UTLXPLAN.SQL to create the PLAN_TABLE
SQL> @e:\oracle\ora92\rdbms\admin\utlxplan.sql

- Displaying PLAN TABLE output: (most recent plan table )
UTLXPLS.SQL - Shows plan table output for serial processing
UTLXPLP.SQL - Shows plan table output with parallel execution columns

Interview Question - SQL/SQL plus

[SN060619-1202]
1. How can variable be passed to a SQL routine?
ans: by use of & symbol(ampersands)
- e.g. "select * from dba_tables where owner=&owner_name"
- double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable

2. How to include a carriage return /linefeed in your output from a SQL script?
ans: using CHR(10) e.g. select 'hello' chr(10) 'world!' from user_tables;k

3. How can you call a PL/SQL procedure from SQL?
Level: Intermediate
Expected answer: By use of the EXECUTE (short form EXEC) command.
4. How do you execute a host operating system command from within SQL?
Level: Low
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
5. You want to use SQL to build SQL, what is this called and give an example
Level: Intermediate to high
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?username? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?? the values selected from the database.

6. What SQLPlus command is used to format output from a select?
Level: low
Expected answer: This is best done with the COLUMN command.

7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Level: Intermediate
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.

8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high
Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.

9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

ANS: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:

select rowid from emp e
where e.rowid > (select min(x.rowid)
                 from emp x
                 where x.emp_no = e.emp_no);

10. What is a Cartesian product?
ANS: result of an unrestricted join of two or more tables. number of rows = #row of tab1 x #row of tab2 ...

11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?,
Ans: push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.

12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Ans: Ascending

13. What is tkprof and how is it used?
Ans:
- The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements
- First setting timed_statistics to true in the initialization file; then turning on tracing for either the entire database via sql_trace parameter or for the session using ALTER SESSION command.
- Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can aslo be used to generate explain planb output.

14. What is explain plan and how is it used?
Ans: The explain plan is a tool to tune SQL statements
- to use it you must have an explain_table generated
- created using utlplan.sql script
- run the explain commmand giving as its argument the SQL statement to be explained
- can be run using tkprof.

Interview Questions - Oracle Backup and Recovery

The following quesitons are from: http://www.geekinterview.com/Interview-Questions/Oracle/Backup-and-Recovery


1. What is the differenec between recovery and restoring of the database?

ans:
- Restoring a database is the replacement of lost or damaged file with a backup.
- You can retore files either with operating system commands as UNIX cp or the RMAN RESTORE COMMAND.
- Recovering a database is the appliaciton of redo data or incremental backups to database files in order to reconstuct lost changes.
- The three types of recovery are instance recovery, crash recovery and media recovery.
- Oracle performs the first two types of recovery automatically using online redo records; only media recovery requres you to restore a backup and issue commands. Only Recovery Manager can recover datafiles by applying incremental backups.

2. What are the different tools are available for hot backup? Is it perferable to take it manually all the time or it depends on the size of the database?
ans:
- hot backup(online backup): A backup of one or more datafiles taken while a database is open and the datafiles are online
- user-managed backup while the database is open: first must put the tablespace in backup mode by issing an ALTER TABLESPACE BEGIN BACKUP command
- RMAN backup: do not need to put the tablespace in backup mode
- OEM
- (cited): if the databse size in TB the RMAN backup will take more than 10 hours to complete
- special backup techiniques which are given by vendors like TIVOLI and Netbackup:
Business content Volume Sync which copies a snapshot of the primiary data to another palce and backup the database from one SAN to another within 15 min for 2TB of database and is the preferable method for big company


3. What is the difference between media recovery and crash recovery

Ans: Media recovery is the applicaiton of redo or incremental backups to a restored backup datafile or individual data block to bring it to a specified time.
- Datafile media recovery always begins at the lowest SCN recorded in the datafile headers.
- when perform media recovery, you an recover:
* The whole database
* A tablespace
* A datafile
* A set of blocks whithin a datafile
- Typically, you perform media recovery after a media failure damages some or all of the database files
crash recovery is the automatic application of oneline redo records to a database after either a single-instance database crashes or all instances of an Oracle Real Application Cluster configuration crash. Crash recovery only requires redo from the online logs: archive redo logs are not required


4. What are the steps involved in Database startup?
Ans: steps are:
Nomount stage (start an instance)
- read initialization parameter file
- SGA is allocated
- background process are started
- alert log file is opened

Mount stage
- control files is read
- name & location of the datafiles, logfiles are detected

Open stage
- opens the online datafiles and online redo log files.
- note: If a tablesace was offline when the database was previously shut down, the tablespace and its corrensponding datafiles will still be offline when you reopen the database.

5. What are steps involved in database shutdown?
ans:
(1) Close the database
- Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively.
- Next, Oracle closes all online datafiles and online redo log files.
(2) Unmount the database
- After the database is closed, Oracle unmounts the database to disassociate it from the instance. - At this point, the instance remains in the memory of your computer.
- After a database is unmounted, Oracle closes the control files of the database.
(3) Shutdown the instance
- SGA is removed from memory and the background processes are terminated.

Interveiw Questions - PL/SQL

[SN060619-1202]
1. Describe the difference between a proceudcure , function and anonymous pl/sql bock
Ans:
- A procedure is a subprogram that performs a specific action.
- A function is a subprogram that computes a value.
- Funciton adn procedures are structured alike, except that funcjitons have a RETURN clause
- PL/SQL is a block-structured language. Procedure, funciton and annymous PL/SQL bock are basic units of PL/SQL. They are logical blocks.
- a PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part.

2. What is a mutating table error and how can you get around it?
ans: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The ususal fix involes either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
ans: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages(if any) has Oracle provided for use by developers?
ans: DBMS_series of packages. e.g DBMS_SQL, DBMS_PIPE, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE

YS: how about for use by DBA?

5. Describe the use of PL/SQL tables
YS:
- not sure what PL/SQL tables refer to. Assume it refers to the nested tables
- Nested tables is one of the three collection types offered by PL/SQL. Other two types are index-by tables and Varrays.
- Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.

8. What are SQLCODE and SQLERRM and why they are important for PL/SQL developers?
ans:
- SQLCODE returns the value of error number for the last error encountered.
- SQLERRM returns the actual error message for the last error encountered.
- used in exception handling to report, or store in an error log table, the error that occurred in the code. There are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block if a cursor is open?
ans: use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Ans:
(1) Use the DBMS_OUTPUT package. it can be used to show intermediate results from loops and the status of variables as the procedure is executed
(2) use SHOW ERROR command: this only shows errors
(3) UTLL_FILE can also be used

11. What are the types of triggers?
ans:

- A database trigger is a stored subprogram associated with a database table, view, or event.
- One of the many uses for database triggers is to audit data modifications
- Triggers can be:
* DML triggers on tables
* INSTEAD OF triggers on view
* system triggers on DATABASE or SCHEMA

Note: The provided ansewer is: there are 12 types of triggers that conisist of combinations of (BEFORE, AFTER) (ROW, TABLE) (INSERT, UPDATE, DELETE)