Trace Analyzer

Trace analyzer is used to interpret raw trace files. It is similar to tkprof but its output is more readable than tkprof. It generates a report with details: time summary, call summary (parse, execute, fetch), identification of top SQL, row source plan, explain plan, CBO statistics, wait events, values of bind variables, I/O summary per schema object, latches, hot blocks, etc.

I like it for its ability of giving values for bind variables used in any SQL statement.

For a given raw SQL Trace generated by EVENT 10046 it provides:

1. Trace identification including actual start and completion time, host name, instance, size, RDBMS version, etc.
2. SQL statements count, user and internal, total and unique.
3. Time summary: elapsed, cpu, non-idle wait events, idle wait events, for user (recursive and non-recursive) and for internal.
4. Call summary for user and internal, with elapsed, cpu, logical reads, physical reads and similar performance details.
5. Summary of wait events, classified by non-idle and idle for user and for internal (if EVENT 10046 level 8 or 12 was used generating trace).
6. Top 20 SQL statements in terms of importance for SQL tuning analysis.
7. List of all unique SQL statements with one-line performance summary per statement.
8. Gaps of no trace activity found on file.
9. List of transactions found (commits and rollbacks).
10. Oracle errors if any.
11. I/O core waits including schema objects affected (tables, indexes, partitions), when traced with level 8 or 12.
12. Top 5 hot blocks, indicating the schema objects (level 8 or 12).
13. Latch wait summary, by name and specific parameters (level 8 or 12).
14. Non-default initialization parameters.

For every SQL statement included in the trace, it includes:

1. Cursor header with SQL statement text, hash value, length, line on trace, depth, user, etc.
2. Oracle errors if any.
3. Call summary (parse, execute and fetch totals).
4. Non-idle and idle wait events (if traced with level 8 or 12).
5. I/O and latch waits summary (if level 8 or 12).
6. First execution and top 10 for particular SQL statement.
7. List of bind variables values for first and top 10 executions if trace was generated using EVENT 10046 level 4 or 12.
8. Cumulative row source plan for all executions of SQL statement.
9. Detailed explain plan if Trace Analyzer is executed on same instance where trace was generated, and if SQL statement made the Top 20 list.
10. Table, index and partition details including row count, CBO statistics and indexed columns if the SQL statement generated an explain plan.

To download the installation script and usage visit http://www.metalink.oracle.com.

Renaming table when MV log is associated

Recently, I faced problem in renaming a table. The table had a materialized view log associated with it and I wanted to move the older version of the table and place a new version of the same instead.

But I was getting following error while renaming the old table:

ORA-26563: renaming this table is not allowed

One of the reasons of getting this error is, the table is used in a materialized view and a materialized view log is associated with the table to track the data changes.

Following are the steps need to be taken while renaming such tables:

1. Begin table organization to rename old table to bkup table
EXEC DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION(USER,'OLD_TAB');

2. Rename old table to bkup table

RENAME OLD_TAB TO NEW_BKUP;

3. Rename new table to original table name

RENAME NEW_TAB TO OLD_TAB;

4. End table organization after renaming new table to original table name

EXEC DBMS_MVIEW.END_TABLE_REORGANIZATION (USER,'OLD_TAB');

Automatic SQL Tuning

As we are aware, manual SQL tuning process is very complex and time consuming, Oracle 10g introduces automatic SQL tuning to ease out the tuning process. To use this feature, we need to use SQL tuning advisor.

Here, I will be giving a brief introduction of Automatic SQL Tuning and the simplest way of using SQL Tuning Advisor.

The query optimizer can run in two modes:

1. Normal mode: In this mode, the optimizer generates the access plan using already available statistics in a very short time and it may not get time to look at all the statistics available.

2. Tuning mode: In this mode, the optimizer generates recommendations after doing extra analysis for the query. This process can be time and resource consuming, so should be used for long ops queries only.

Automatic SQL tuning advisor performs following analysis:

· Statistics Analysis: The optimizer recommends the gathering of statistics wherever it has become stale.

· SQL Profiling: The optimizer generates a SQL profile with additional statistics specific to the query which can be saved and used later. This analysis generates a recommendation to use the SQL profile. A SQL profile, once accepted, is stored in the data dictionary and used to generate access plan for the query when the optimizer is running in normal mode.

· Access Path Analysis: In this analysis, the advisor recommends new indexes which may enhance the performance of the query.

· SQL Structure Analysis: The advisor suggests some changes in the SQL query itself to improve the performance. The developer should analyze the recommendations to check whether those are fit for the query. e.g. The advisor may suggest to replace UNION by UNION ALL. If the query will never give duplicate records, then UNION ALL will be faster.

SQL tuning advisor can work on various inputs such as ADDM, high load SQL statements, cursor cache, SQL tuning sets or manually specified statement. Here only cursor cache and manually specified statement methods are covered.

Using SQL Tuning Advisor

For query in cursor cache:

1. Create a SQL tuning task

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dn7gnkw06r2yu',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dn7gnkw06r2yu_tuning_task',
description => 'Tuning task for statement dn7gnkw06r2yu.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Execute a SQL tuning task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'dn7gnkw06r2yu_tuning_task' );
END;
/

The status of the task can be checke by following query

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'dn7gnkw06r2yu_tuning_task';

The execution progress of the SQL Tuning Advisor can be checked in the V$ADVISOR_PROGRESS view.

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'dn7gnkw06r2yu_tuning_task';

3. Display the results of a SQL tuning task

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('dn7gnkw06r2yu_tuning_task') FROM DUAL;

4. Implement recommendations as appropriate

For manually supplied query:

1. Create a SQL tuning task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/

2. Execute a SQL tuning task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'my_sql_tuning_task' );
END;
/

The status of the task can be checke by following query

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

The execution progress of the SQL Tuning Advisor can be checked in the V$ADVISOR_PROGRESS view.

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';

3. Display the results of a SQL tuning task

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM DUAL;

4. Implement recommendations as appropriate

COUNT(*) Vs. COUNT(1)

The debat COUNT(*) vs. COUNT(1) is on for years now. So I thought of doing experiment on the same and here are the result:

I have done the test for indexed table as well as non-indexed table. The table has 6 millions of rows in both the tables and the rowwidth is approx 4000 bytes.

1. COUNT(1) is internally converted to COUNT(*).

SQL> EXPLAIN PLAN FOR
2 select max(REG_COUNT) from f_reg
3 group by MG_BKEY
4 having COUNT(1) > 1;

Explained.

SQL> SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SQL> SQL> Plan hash value: 272420531

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 50738 (5)| 00:10:09 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 50738 (5)| 00:10:09 |
| 3 | TABLE ACCESS FULL| F_REG | 6632K| 37M| 49776 (4)| 00:09:58 |
-----------------------------------------------------------------------------

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

1 - filter(COUNT(*)>1)

15 rows selected.

The DBMS_XPLAN gives you the plan and predicates used by oracle to execute a query. You can see above that the filter "COUNT(1) > 1" is converted to "COUNT(*) > 1".

2. There is no difference in COUNT(*) and COUNT(1).

Case a: Test on indexed table

set autotrace traceonly
select count(1) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


select count(*) from f_reg;

Execution Plan
----------------------------------------------------------
Plan hash value: 622071604

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4429 (3)| 00:00:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| F_REG_PK | 6632K| 4429 (3)| 00:00:54 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19808 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Case b: Test on non-indexed table

set autotrace traceonly
select count(1) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
330 recursive calls
0 db block gets
221666 consistent gets
176232 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed



select count(*) from f_reg_1_bkup;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458129084

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48989 (1)| 00:09:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| F_REG_1_BKUP | 6632K| 48989 (1)| 00:09:48 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
221585 consistent gets
175908 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

You can see, there is no difference at all in statistics or timings in case of indexed table, whereas in case of non-indexed table there is a slight difference in consistenet gets which is less in case of COUNT(*) and no difference in elapsed time.

P.S. The tests were carried out in Oracle 10g.

Parameterized View

I always used to think why oracle didn't provide parameterized views similar to parameterized cursors, till I found workaround for the same.

Following is an example which shows use of parameterized view:

CREATE OR REPLACE VIEW test_vw AS
SELECT empno,ename,sal
FROM emp
WHERE empno = to_number(userenv('client_info')) ;

In Stored Procedure, will have to call following procedure before executing the View query, as in following example:

exec dbms_application_info.set_client_info(7934);

select * from test_vw ;

And the output is:

EMPNO ENAME SAL
---------- ---------- ----------
7934 MILLER 13040

Check out this