Picasso Database Query Optimizer Visualizer

©Indian Institute of Science, Bangalore, India

Oracle

(The following setup procedure is known to work with Oracle 9i, 10g, 11g on Windows.)

1. Install Oracle: Run setup.exe and follow the wizard.
   
   
2. Open the SQL Plus command prompt window through one of the following ways:

1. Start → Programs → Oracle → OraHome → Application Development → SQL Plus.  Login as system/manager (or any other valid user/password).

2. Type sqlplus system/manager (or any other valid user name and password) in the command prompt.
   
   

3. Create the schema.  If the schema to be created is in a file, run the following command in the SQL prompt:

@ <full path>\<filename>

4. Load the data into the database tables. If you are loading data from a file: 

1. Create a .ctl file for each table with the following contents (in the same location as the data files):

load data
INFILE '<filename>'
INTO TABLE <tablename>
FIELDS TERMINATED BY '<delimiter>'
<table_format>

For example, if the data is pipe-separated, the customer.ctl file will have these contents:

load data
INFILE 'customer.tbl'
INTO TABLE CUSTOMER
FIELDS TERMINATED BY '|'
(C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT)

2. After creating this file, open a command prompt, navigate to the location of the above files and run the command:

sqlldr <username>/<password> control = <filename>                  

For example, for username ‘system’ loading table CUSTOMER with files located in C:\, navigate to C:\ and run the command:

sqlldr system/manager control = customer.ctl

5. Create the integrity constraints (primary key, foreign key and indexes) on the data. If you are using a file for this, run the following command in the SQL prompt:

@ <full path>\<filename>

6. Create the explain plan tables. These are the tables in which query execution plans are stored by the optimizer. To create these tables, use this command in the SQL prompt:

@ <path of oracle home>\rdbms\admin\utlxplan.sql

For more information about Oracle plan tables, see
http://www.adp-gmbh.ch/ora/explainplan.html

7. Create statistical summaries for all table columns that may be used as Picasso predicates in the query templates, using the following command in the SQL prompt:

EXEC dbms_stats.gather_table_stats ('<schema_name>’, ‘<table_name>’, method_opt => 'FOR COLUMNS <column_names>');

For example, to create statistics on the N_NAME and N_COMMENT columns of the NATION table:
EXEC dbms_stats.gather_table_stats ('system', ‘NATION’, method_opt => 'FOR COLUMNS N_NAME, N_COMMENT');

To improve the scope for optimization, you could optionally create statistics for all columns of each table. This can be done using the command:

EXEC dbms_stats.gather_schema_stats ('<schema_name>’, method_opt => 'FOR ALL COLUMNS', cascade => TRUE);

Note: The diagrams on the Picasso website have been generated with statistical summaries created on all table columns.

8. You can optionally set the following parameters to fine-tune the optimization environment (the diagrams on the Picasso website were generated using the default values for all these parameters):
   
   

1. query_rewrite_enabled

2. optimizer_dynamic_sampling

3. optimizer_mode

4. optimizer_features_enabled
   
   

For a more exhaustive list of optimization parameters, see
http://www.dba-oracle.com/art_ault_optimization_parameters.htm

Notes:

1. Creating statistical summaries is computationally intensive and may take significant time to complete. If there is an error "unable to extend temp segment in tablespace <tablespace_name>", enable the expansion of the datafile in that tablespace by checking the ‘Automatically extend datafile when full’ check box in the datafile properties. Then execute the above commands again.
   
   

2. For more information on gathering Oracle statistics, see:  http://www.psoug.org/reference/dbms_stats.html

Documentation Home