Welcome to Nick Aiva 's blog!: Database

On the bright side, this is one of the easiest review for me to write, since there are so many readers of the several editions of the book, that I shall mainly copy and paste some lines of reviews I agree to. Thus, I quote: " This book is an encyclopedic overview of all aspects of Oracle performance. Mr. Harrison takes a layered approach, starting at the top with application and data model design where the focus is minimizing the demand for database resources. He then moves down into database code internals, where the focus is maximizing concurrency through reduction of lock, latch, and mutex contention. The next step is to optimize memory usage to minimize the need for physical IO. Finally, he moves to the bottom layer, where the focus is on optimizing physical IO at the disk layer.

Each of these layers is worthy of its own book, so to combine all these topics in a single book is an ambitious goal. Indeed, experienced readers will often want a bit more detail, or wonder why their favorite optimization was not mentioned. However, Mr. Harrison strikes a very good balance between depth and coverage. He also provides a very useful bibliography, including the Oracle documentation, books, and Internet sites and blogs.

For each chapter, Mr. Harrison provides extraordinarily clear, concise, and helpful introductions and summaries. He also uses boxed borders to highlights particularly important points within the text. One can learn a great deal by simply reading these summaries and boxed items, and these can also be used to help the reader find relevant sections, which is especially valuable in a book of this length.

This book is not the last word on SQL tuning, optimizer internals, Oracle troubleshooting, the SGA, nor latch contention. However, its coherent approach, useful summaries and highlights, and efficient organization, make it a valuable and essential guide to anyone wishing to expand their Oracle performance skill set.

The target audience includes both application developers and DBAs. It covers 10g, 11g, 11gR2, with context from 8i and 9i ." The latter is true for the newest edition of course! Furthermore, the full source code is available, as well as an errata page on the author's site.

On the dark side, there are extensive sections of the text promoting the products Mr. Harrison and his company create, which although humorously introduced ("Full disclosure..."), are a waste of time to read, for open source funs or anyone who is not interested in buying the software.

All in all, the book is highly recommended and I am looking forward to reading each new edition. Congratulations to the author. Well done, indeed!

Recently, a french project manager working in a 5 year long European commission project in Brussels, contacted me and complained that their fully working code (in testing environment), using Oracle Text 10g, failed to meet the customer expectations. In their RAC production environment the application was unbearably slow. The project deadline is early June this year. Not an unknown situation, isn't it?

One of the most usual issues when moving from the development, or testing environment to the production, is setting up properly or updating the database schema and tuning it. In this new post, a fast and handy way for web developers to create, or even drop, indexes, partition tables or materialized views will be demonstrated. No SQL plans or other hard core DBA stuff will be mentioned here, the SQL of the notorious TUHRA ADF model project will be used as an example. So, let's start!
Consider for instance the following trivial ANSI query for the employees search page based on the email, first and last name criteria entered by the user:

SELECT /* + INDEX_COMBINE( Employees Departments )*/

Employees.EMPLOYEE_ID,

Employees.EMAIL,

Employees.FIRST_NAME,

Employees.LAST_NAME,

Employees.PHONE_NUMBER,

Departments.DEPARTMENT_NAME,

Departments.DEPARTMENT_ID,

Jobs.JOB_TITLE,

Jobs.JOB_ID

FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)

join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)

ORDER BY LAST_NAME,FIRST_NAME

When one opens the relevant view object and observes the SQL query to explain, one sees the image presented below:

The highlighted code below, the where clause, is actually missing. You need to enable ADFLogger logging, run the app and search for the actual full query in the output log which is rather time consuming. Besides, when you open sqlplus and enter:

Explain plan for

SELECT * FROM (SELECT /* + INDEX_COMBINE( Employees Departments )*/
Employees.EMPLOYEE_ID,
Employees.EMAIL,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.PHONE_NUMBER,
Departments.DEPARTMENT_NAME,
Departments.DEPARTMENT_ID,
Jobs.JOB_TITLE,
Jobs.JOB_ID
FROM EMPLOYEES Employees join DEPARTMENTS Departments on (Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID)
join JOBS Jobs on (Employees.JOB_ID = Jobs.JOB_ID)) QRSLT

WHERE ( ( (UPPER(EMAIL) LIKE UPPER('%' || :QueryEmail || '%') )

AND (UPPER(FIRST_NAME) LIKE UPPER('%' || :QueryFirstName || '%') )

AND (UPPER(LAST_NAME) LIKE UPPER('%' || :QueryLastName || '%') ) ) )

ORDER BY LAST_NAME,FIRST_NAME

select *
from table(dbms_xplan.display)
/

you get a new plan, which is naturally different. Many people would rightfully argue that creating a function based index, concatenating all the three columns in that order, would solve the tuning problem. But would that really solve it?

To index or not to index? That is the question!

The easiest way to fly is via the automatic pilot. You test fully your app, ideally in isolation and now you can ask the SQL advisor, thanks to the enterprise manager, following the images below:

Click on Advisor central to proceed. Next click on sql access advisor in order to create a db job and schedule it to run immediately. Next click on current and recent sql activity, proceed to select indexes or materialized views or partition tables, reach the review page where show sql, reveals what is happening under the cover:

DECLARE taskname varchar2(30) := 'SQLACCESSTuhra3'; task_desc varchar2(256) := 'SQL Access Advisor'; task_or_template varchar2(30) := 'SQLACCESS_EMTASK'; task_id number := 0; wkld_name varchar2(30) := 'SQLACCESSTuhra3_wkld'; saved_rows number := 0; failed_rows number := 0; num_found number;BEGIN/* Create Task */dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);/* Reset Task */dbms_advisor.reset_task(taskname);/* Delete Previous Workload Task Link */select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;IF num_found > 0 THENdbms_advisor.delete_sqlwkld_ref(taskname,wkld_name);END IF;/* Delete Previous Workload */select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;IF num_found > 0 THENdbms_advisor.delete_sqlwkld(wkld_name);END IF;/* Create Workload */dbms_advisor.create_sqlwkld(wkld_name,null);/* Link Workload to Task */dbms_advisor.add_sqlwkld_ref(taskname,wkld_name);/* Set Workload Parameters */dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'ORDER_LIST','PRIORITY,OPTIMIZER_COST'); dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_SQLSTRING_LIST','"@!"'); dbms_advisor.set_sqlwkld_parameter(wkld_name,'JOURNALING','4'); dbms_advisor.set_sqlwkld_parameter(wkld_name,'DAYS_TO_EXPIRE','30'); dbms_advisor.import_sqlwkld_sqlcache(wkld_name,'REPLACE',2,saved_rows,failed_rows);/* Set Task Parameters */dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','INDEX,TABLE,PARTITION'); dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST'); dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000); dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED'); dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED); dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE'); dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL'); dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE'); dbms_advisor.set_task_parameter(taskname,'JOURNALING','4'); dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');/* Execute Task */dbms_advisor.execute_task(taskname);END;

You end up as shown:

where one can see the whole set of the sql statements issued. If you click on schedule implementation the recommended indexes are created, while clicking show sql gives you the script to run when you wish, on your own:

Rem SQL Access Advisor: Version 11.2.0.1.0 - Production Rem Rem Username: SYSTEMRem Task: SQLACCESSTuhra3 Rem Execution date: Rem /* RETAIN INDEX "HR"."IMAGE_USAGES_PK" *//* RETAIN INDEX "HR"."DEPT_ID_PK" *//* RETAIN INDEX "HR"."JOB_ID_PK" *//* RETAIN INDEX "HR"."IMAGE_USAGES_ASSOCIATED_IDX" *//* RETAIN INDEX "HR"."JOBS_TITLE_IDX" *//* RETAIN INDEX "HR"."EMP_DEPARTMENT_IX" *//* RETAIN INDEX "HR"."EMP_EMAIL_UK" *//* RETAIN INDEX "HR"."IMAGES_PK" *//* RETAIN INDEX "APEX_030200"."WWV_FLOW_PLATFORM_PREFS_FKIDX" *//* RETAIN INDEX "HR"."EMP_EMP_ID_PK" */CREATE INDEX "HR"."EMPLOYEES_NAME_IDX"ON "HR"."EMPLOYEES" ("LAST_NAME","FIRST_NAME")COMPUTE STATISTICS;

Thus, the aforementioned concatenated index is not being used, and is just decelerating inserts and taking up valuable disk space.We can safely drop it for now. This does not mean that we are done! Since the production db will have millions of rows, other statistics,etc, the db job results, i.e. about indexing, may frequently change. The morale of this story is that the earliest one starts to test against the full production data, the higher the possibility of project success. Performance tuning is not an afterthought, but a gradually ongoing process, which must ideally begin with the first application release. Nonetheless, not many developers follow this rule. Cynicals say that experience is just a set of past, bitter failures. Germans say: Durch Schaden wird mann klug. The man on the street tends to forget more easily, whatever has been unpleasant in the past. It seems that IT staff does not have that privilege!