Friday, November 23, 2012

SQL Profiling/Optimization in Oracle


To tune your SQL you need to profile/measure your sql performance (time, how many reads) and inspect the
execution plan generated by optimizer (e.g. to detect full table scan that can be avoided by creating index). Start with measuring the baseline and then try to modify your table/sql (e.g. creating index, give optimizer hints) and measure again.

There are several ways to measure your sql performace in Oracle:

1. The simplest way: click "auto trace" or "explain plan" in the SQL worksheet in  JDeveloper/SQLDeveloper:


To use the autotrace you need to have permission to the dictionary view V$mystat


2. In SQLplus you can use explain plan:

EXPLAIN PLAN FOR select e.employee_id,e.last_name,j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;

Explained.

SQL>  SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |    98 |  4508 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN               |            |    98 |  4508 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS    |    19 |   475 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN        | JOB_ID_PK |    19 |   |     1   (0)| 00:00:01 
|*  4 |   SORT JOIN               |            |    98 |  2058 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL      | EMPLOYEES |    98 |  2058 |     3   (0)| 00:00:01 |


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("J"."JOB_ID"="E"."JOB_ID")
       filter("J"."JOB_ID"="E"."JOB_ID")
   5 - filter("SALARY"<4000>

19 rows selected.

3. In SQLplus you can use autotrace so Oracle will display the explain plan or statistics everytime you perform a query.


To display the plan: SET AUTOTRACE TRACEONLY EXPLAIN or SET AUTOTRACE ON EXPLAIN;
To display the statistics: SET AUTOTRACE TRACEONLY STATISTICS or SET AUTOTRACE ON  STATISTICS;
Or display both: SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN;


Interesting statistics information are included:

  • recursive calls: how much works to construct query plan
  • consistent gets / logical reads: number of logical read operation when Oracle constructing query plan and execute it)
  • physical reads: number of disk read operations when Oracle can't find the data in the cache.
e.g.

Statistics
----------------------------------------------------------
       1097  recursive calls
        0  db block gets
      282  consistent gets
       24  physical reads
        0  redo size
       2434  bytes sent via SQL*Net to client
      464  bytes received via SQL*Net from client
        6  SQL*Net roundtrips to/from client
       13  sorts (memory)
        0  sorts (disk)


If you run the same query again, the performance is better due to caching:
Statistics
----------------------------------------------------------
       10  recursive calls
        0  db block gets
       17  consistent gets
        0  physical reads
        0  redo size
       2434  bytes sent via SQL*Net to client
      464  bytes received via SQL*Net from client
        6  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
       64  rows processed

To get accurate measure over your query you might need to flush the cache (execution plan & data) before you perform the sql again:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;


As an experiment we can see how big the infuence of indexing to the query performance. For example try to hide an index from the optimizer:
ALTER INDEX hr.job_id_pk INVISIBLE
or using hint:
SELECT /*+ NO_INDEX (j job_id_pk) */  e.employee_id, e.last_name, j.job_title FROM employees e, jobs j WHERE salary < 4000 and j.job_id = e.job_id;

The performance will be worse due to table full scan:

Execution Plan
----------------------------------------------------------
Plan hash value: 1300016118

--------------------------------------------------------------------------------
| Id  | Operation    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    98 |  4508 |     7     (15)| 00:00:01 |
|*  1 |  HASH JOIN         |         |    98 |  4508 |     7     (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JOBS      |    19 |   475 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES |    98 |  2058 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("J"."JOB_ID"="E"."JOB_ID")
       filter("J"."JOB_ID"="E"."JOB_ID")
   5 - filter("SALARY"<4000>

19 rows selected.

Statistics
----------------------------------------------------------
       1240  recursive calls
        2  db block gets
      317  consistent gets
       38  physical reads
        0  redo size
       2434  bytes sent via SQL*Net to client
      464  bytes received via SQL*Net from client
        6  SQL*Net roundtrips to/from client
       12  sorts (memory)
        0  sorts (disk)
       64  rows processed


4. You can use DBMS_SYSTEM.set_sql_trace_in_session to tell Oracle to generate *.trc trace files and then use tkprof utility to summary the result. Personally I find this method unhandy except when I need the trace files.

Tips:

1. When using autotrace, Oracle might complain that you don't have PLUSTRACE role privelege.
Solution:
connect sys as sysdba, run @$ORACLE_HOME/sqlplus/admin/plustrce.sql to perform several grants. Then Grant PLUSTRACE to the user you used. Connect again as a normal user (e.g. hr) create PLAN_TABLE in the schema you use by using  @$ORACLE_HOME/rdbms/admin/utlxplan

2. When your query produces big result and you're interested only to the profiling statistics, use TRACEONLY so that Oracle will not display the query result.
e.g.
SET AUTOTRACE TRACEONLY STATISTICS
SET AUTOTRACE TRACEONLY EXPLAIN

3. You can set the statistics level e.g. ALTER SESSION SET statistics_level=ALL;

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)




References:
Beginning Oracle Database 11g Administration by Iggy Fernandez



Oracle Database 11g Release 2 Performance Tuning Tips & Techniques by Niemiec

Database: Constraint check vs Trigger check


Usage examples

  • consistency check (e.g. if the employee address in HRM_apps table is changed the address in the Mailing_apps table should also be changed). It's important to maintain consistency if you have redundancy due to denormalization (in sake of read performance.)
  • to avoid business error (e.g. compare the execution price with the historical average price if the stock execution price makes sense)
  • recompute the summary table which is used to increase online performance
  • security check

Why business check in the database instead of application

In most cases I believe that the business logic should be in the application business layer instead of in the database layer, but there are reasons why people often implement business logics in the database instead of the application:
  • reusable and consistency across different applications if the schema is used by multiple applications
  • hidden from application (external) programmers (in sake of secrecy and complexity) e.g. stock_evaluation_formula
  • performance reason (computing the formula in the database will reduce the network traffic between application and databases, the formula then just send the result to the application, e.g. the datamining/statistics summary computation)

Tips

  • It's important that the business logic in the database (e.g. stored procedure, user functions, constraints, triggers) are documented for application developers.
  • Use constraint check & triggers sparingly since both of them can lead you to maintenance nightmare.
  • Avoid to build your own check if you can use the standard feature e.g. primary key and unique constraints for entity integrity, foreign key constraint for referential integrity.
  • Try to simplify the check formula for performance & maintanability.

Constraint check vs Trigger check

  • use constraint check to constraint the domain i.e. about your data
  • trigger is about how to handle data (e.g. cascading changes, rollback if the business constraint is violated e.g. the merchandised is out of order)

Constraint check example (Oracle):

ALTER TABLE employees ADD CONSTRAINT constraint_salarycheck CHECK (salary<1000>

Test: INSERT INTO employees (employee_id,naam,salary) VALUES (hrseq.nextval,'mr boss',10000);--not permitted


Trigger check example (Oracle):


CREATE OR REPLACE TRIGGER tr_salaryupdatecheck BEFORE UPDATE OF salary ON employees FOR EACH ROW
 BEGIN
  IF :new.salary < :old.salary THEN
   RAISE_APPLICATION_ERROR(-20003,'salary lower');
  END IF;
 END;

Test: UPDATE employees SET salary=100 WHERE name = 'mr boss';

Disable/enable:
ALTER TRIGGER tr_salaryupdatecheck DISABLE;
ALTER TRIGGER tr_salaryupdatecheck ENABLE;

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)


References:
Oracle Database 11g & MySQL 5.6 Developer Handbook

PL/SQL tips: use %TYPE



Use tablename.columnname%TYPE in your plsql, so when the table column type is changed you don't have to change the types in plsql.


Example (Oracle PL/SQL):

DECLARE

  CURSOR cursorsalary IS SELECT employee_name,salary FROM employees WHERE salary>100;
  name employees.employee_name%TYPE;
  sal employees.salary%TYPE;

BEGIN

 OPEN cursorsalary;

 FOR arow IN cursorsalary LOOP
  FETCH cursorsalary INTO name,sal;
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(name)||' has salary:'|| TO_CHAR(sal));
 END LOOP;

 CLOSE cursorsalary;

END

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)


References:
Oracle PL/SQL Programming

Sunday, November 4, 2012

Challenges with Indonesian & Chinese names from a software developer point a view


A lot of Indonesians have only 1 name (e.g. Soekarno, Suharto), while in western system a name commonly consist of (at least) first name & surname. Many administration software don't take this into account, the software/database don't let either the first name nor the surname to be empty. So this situation creates extra complexity for software developers.

Another problem is about the nobility title (e.g. Raden Soekarno), the title Raden is  comparable with Sir in English, so Raden should be in the title field on the GUI form / database field. Many people don't recognize this, so Raden will end up as the first name and Soekarno as the surname. Strangely further, people will infer that the first name Raden as his calling name, and people start to call him "Hi Raden" (this actually happened to a friend of mine here). I guess this complexity happens also to other non-western nobility title. (I know an African who has quite a long name due to multiple nobility titles).

Another well-known problem is over the order in Chinese names. The surname precede the first name, so for example the surname of Li Peng is Li instead of Peng. Many administrative operator and automation software make mistake by inferring that Peng is the surname.

Despite that the application/database nowadays supports also unicode encoding, in practice we still need to romanize the Chinese names (and other non latin character names such as the Russian Cyric). The probleem is that there is no standard for the Romanization. For example the Chinese family name of my mother can be translated as Guo or Kuo or even in Indonesia as Kwik or Kwiek while their semantic are the same. So the solution is the semantic web maybe?