Archive for the ‘Performance Tuning’ Category

Performance Monitoring

List of Common Oracle Metrics

Monitoring Methodologies

 

  • Reactive
  • Proactive

Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.

 

Database and Instance Metrics

Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress

“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.

Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.

SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "

 

Metric                                                  Description                                                                                                  
Event Class Metrics Metrics collected on the wait event class level. e.g.  DB_TIME_WAITING
Event Metrics Metrics collected on various wait events
File Metrics Long Duration Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME
Service Metrics Metrics collected at the service level. e.g. CPU_TIME_PER_CALL
Session Metrics Short Duration Metrics collected at the session level. e.g. BLOCKED_USERS
System Metrics Short Duration Metrics collected at the system level
Tablespae Metrics Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL

 

Viewing Metrics

The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through

  • Data Dictionary
  • Dynamic Performance Views
  • Optimizer Statistics

Data Dictionary Metrics

Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.

Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.

Compiling PL/SQL procedures and Packages

Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.

Rebuilding Indexes

 

Viewing In-Memory Metrics

All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:

  • V$SYSSTAT
  • V$SESSSTAT
  • V$SYSMETRIC
  • V$SYSMETRIC_HISTORY

Example of few system metrics maintained in V$SYSMETRIC view:

  • Buffer Cache Hit Ratio
  • CPU Usage Per Sec
  • Disk Sort Per Sec
  • Host CPU Utilization (%)
  • Library Cache Hit Ratio
  • SQL Service Response Time
  • Shared Pool Free (%)

Viewing Saved Metrics

After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like

  • DBA_HIST_SERVICE_NAME
  • DBA_HIST_SESSMETRIC_HISTORY

Optimizer Statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.

Metrics collected include:

  • Size of the table or index in database blocks
  • Number of rows
  • Average row size and chain count (tables only)
  • Height and number of deleted leaf rows (indexes only)

As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.

Advertisements

Join Types NL,Hash and Sort-Merge

Click the following link of Sachin for Join Types Nested Loops, Hash and Sort Merge

Join Types by Sachin

Previously, i copied most of the important points from his post for my own request but now deleting as the author does not like it.

Related articles by Zemanta

Reblog this post [with Zemanta]

Optimize Joins and Query Plan Changes

Joins and Query Plans

Details of the article can be viewed at ask Tom
Below are two queries posted by a user on askTom site.

</pre>1) SELECT distinct D.deptno, D.dname
    FROM dept D, emp E
        WHERE E.deptno = D.deptno
        ORDER BY D.deptno;

2) SELECT D.deptno, D.dname
    FROM dept D
        WHERE EXISTS
                 (
                 SELECT 1
                 FROM emp E
                 WHERE E.deptno = D.deptno
                 )
        ORDER BY D.deptno;

<span style="color: rgb(0, 0, 0); font-family: verdana;"><span>In general, you should phrase the queries in the manner  that says it best. If one set of joins were particularly efficient to use in all  cases, Oracle would not have implemented the rest of them! </span></span><br style="color: rgb(0, 0, 0); font-family: verdana;" /><br style="color: rgb(0, 0, 0); font-family: verdana;" /><span style="color: rgb(0, 0, 0); font-family: verdana;"><span>In general, you use a join when you need data from more  than one table in the ultimate SELECT list. Here you need data only from DEPT,  so it is unlikely that you would consider a join. You would instead consider  using either WHERE EXISTS or WHERE IN. (The cost-based optimizer [CBO] sees them  as more or less equivalent—you might, too.)</span></span><br style="color: rgb(0, 0, 0); font-family: verdana;" />
<span>I would write the query as either</span>

<pre>select deptno, dname
  from dept
 where exists
   ( select NULL
       from emp
      where emp.deptno
         = dept.deptno )
 order by deptno;

or

select deptno, dname<br />  from dept<br /> where deptno in <br />( select deptno <br />    from emp )<br /> order by deptno;

In both cases, the optimizer would employ either a semi join, which you cannot specify but the optimizer may perform (it stops joining after the first hit), or an index probe using NESTED LOOPS into EMP to test for row existence.

This example demonstrates, in general, what happens. When the optimizer detects that DEPT is small and EMP is large, it index-probes the large EMP table for each row in DEPT. On the other hand, if it deems DEPT to be large, the optimizer will do a pure semi join in bulk, with no indexes. I’m using a copy of the EMP and DEPT tables for the test:

SQL> create table emp as<br />  2  select * from scott.emp;<br />Table created.<br /><br />SQL> create table dept as<br />  2  select * from scott.dept;<br />Table created.<br /><br />SQL> create index emp_deptno_idx<br />  2  on emp(deptno);<br />Index created.

Then I set the first representative statistics. I use SET_TABLE_STATS to make EMP appear large, with 1,000,000 rows, and DEPT appear small, with only 100 rows. Additionally, I tell the optimizer about the index I envision having on EMP(DEPTNO). Because Oracle Database 10g Release 2 computes statistics on an index creation by default, I start by removing any existing statistics on this index and putting in place statistics that might be more representative:

 <br />SQL> begin<br />  2   dbms_stats.set_table_stats<br />  3   ( user,<br />  4    'EMP',<br />  5    numrows => 1000000,<br />  6    numblks => 100000 );<br />  7   dbms_stats.delete_index_stats<br />  8   ( user,<br />  9    'EMP_DEPTNO_IDX' );<br /> 10   dbms_stats.set_index_stats<br /> 11   ( user,<br /> 12    'EMP_DEPTNO_IDX',<br /> 13     numrows => 1000000,<br /> 14     numdist => 10000,<br /> 15     numlblks =>10000 );<br /> 16   dbms_stats.set_column_stats<br /> 17   ( user,<br /> 18    'EMP',<br /> 19    'DEPTNO',<br /> 20     DISTCNT => 10000 );<br /> 21   dbms_stats.set_table_stats<br /> 22   ( user,<br /> 23    'DEPT',<br /> 24     numrows=> 100,<br /> 25     numblks => 100 );<br /> 26  end;<br /> 27  /

When using the CBO with a large EMP and a small DEPT, Oracle Database might use a plan such as the one in Listing 1. The optimizer opts to read every row in DEPT and then performs an index range scan—the index probe into the EMP table—to see if the row exists in the other table. Because I have relatively few index probes to perform (about 100 is what the optimizer thinks) and because the EMP table is large, the optimizer uses this plan in both the WHERE IN and WHERE EXISTS cases.

Code Listing 1: CBO plan with large EMP and small DEPT

SQL> set autotrace traceonly explain<br />SQL> select deptno, dname<br />  2    from dept where deptno  in<br />  3    ( select deptno<br />  4        from emp )<br />  5   order by deptno;<br /><br />Execution Plan<br />-----------------------------------------------------------<br />Plan hash value: 3383088615<br /><br />-----------------------------------------------------------<br />|  Id |	Operation	| Name	        |   Rows | Bytes  |<br />-----------------------------------------------------------<br />|   0 |SELECT STATEMENT	|		|   100	 |  3500  |<br />|   1 |SORT ORDER BY	|		|   100	 |  3500  |<br />|   2 |NESTED LOOPS SEMI|		|   100	 |  3500  | <br />|   3 |TABLE ACCESS FULL| DEPT	        |   100	 |  2200  | <br />|*  4 |INDEX RANGE SCAN	| EMP_DEPTNO_IDX|  1000K |    12M |<br />-----------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />-----------------------------------------------------------<br /><br />   4 - access("DEPTNO"="DEPTNO")<br /><br />SQL> select deptno, dname<br />  2    from dept where exists<br />  3    ( select null<br />  4        from emp<br />  5       where emp.deptno =<br />  6            dept.deptno )<br />  7   order by deptno;<br /><br />Execution Plan<br />------------------------------------------------------------<br />Plan hash value: 3383088615<br /><br />------------------------------------------------------------<br />|  Id |	Operation	 | Name	         |   Rows | Bytes  |<br />------------------------------------------------------------<br />|   0 |	SELECT STATEMENT |	         |   100  |  3500  |<br />|   1 | SORT ORDER BY	 |		 |   100  |  3500  |<br />|   2 | NESTED LOOPS SEMI|		 |   100  |  3500  |<br />|   3 | TABLE ACCESS FULL| DEPT	         |   100  |  2200  |<br />|*  4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX|  1000K |    12M |<br />------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />------------------------------------------------------------<br /><br />   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Compare that with the RBO plan, shown in Listing 2. When using the RBO, the developer must ask, “What size will EMP and DEPT be?” and, depending on the answer, choose either WHERE IN or WHERE EXISTS to get an “optimal” plan. The RBO comes up with two different plans based solely on how the question was asked, not on how large the amount of data was. This is one of the major advantages of the CBO over the RBO: Developers don’t have to know how to write the best query in all cases—which is not to say that they don’t need to learn SQL! They just do not have to figure out minutiae such as where they should use WHERE IN versus WHERE EXISTS.

Code Listing 2: RBO plan

SQL> select /*+ RULE */<br />  2    deptno, dname<br />  3    from dept where deptno  in<br />  4    ( select deptno<br />  5        from emp )<br />  6   order by deptno;<br /><br />Execution Plan<br />-------------------------------------<br />Plan hash value: 1932208647<br /><br />-------------------------------------<br />|  Id |	Operation	 | Name	    |<br />-------------------------------------<br />|   0 | SELECT STATEMENT |	    |<br />|   1 | MERGE JOIN       |	    |<br />|   2 | SORT JOIN	 |	    |<br />|   3 | TABLE ACCESS FULL| DEPT     |<br />|*  4 | SORT JOIN	 |	    |<br />|   5 | VIEW		 | VW_NSO_1 |			<br />|   6 | SORT UNIQUE	 |          |<br />|   7 | TABLE ACCESS FULL| EMP	    |<br />-------------------------------------<br /><br />Predicate Information (identified by operation id):<br />-------------------------------------<br /><br />   4 - access("DEPTNO"="$nso_col_1")<br />       filter("DEPTNO"="$nso_col_1")<br /><br />Note<br />----------<br />   - rule based optimizer used (consider using cbo)<br /><br />SQL> select /*+ RULE */<br />  2    deptno, dname<br />  3    from dept where exists<br />  4    ( select null<br />  5        from emp<br />  6       where emp.deptno =<br />  7            dept.deptno )<br />  8   order by deptno;<br /><br />Execution Plan<br />----------------------------------------<br />Plan hash value: 4109416194<br /><br />----------------------------------------<br />| Id | Operation	| Name	       |<br />----------------------------------------<br />|  0 | SELECT STATEMENT	|	       |<br />|  1 | SORT ORDER BY	|	       |<br />|* 2 | FILTER		|	       |<br />|  3 | TABLE ACCESS FULL| DEPT 	       |<br />|* 4 | INDEX RANGE SCAN	| EMP_DEPTNO_ID|<br />----------------------------------------<br /><br />Predicate Information (identified by operation id):<br />----------------------------------------<br /><br />   2 - filter( EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE<br />                "EMP"."DEPTNO"=:B1))<br />   4 - access("EMP"."DEPTNO"=:B1)

Now, back to the CBO. As the amount of data in DEPT increases—and the optimizer decides that it will have to perform significantly more index probes into the EMP table—the query plans change. They go from using NESTED LOOPS and INDEX RANGE SCANS to more-efficient bulk operations. To see this, I tell the optimizer that DEPT is much larger now

SQL> begin<br />  2     dbms_stats.set_table_stats<br />  3     ( user,<br />  4      'DEPT',<br />  5       numrows=> 100000,<br />  6       numblks => 10000 );<br />  7  end;<br />  8  /

and then rerun the queries and review the resulting plans, as shown in Listing 3. As you can see, the optimizer, in both cases, now chooses to perform full scans and a nice big hash semi join—a join that stops after the first match—instead of using an index probe, because it would have to use the index 100,000 times.

Code Listing 3: CBO plan with larger DEPT

SQL> set autotrace traceonly explain<br />SQL> select deptno, dname<br />  2    from dept where deptno  in<br />  3    ( select deptno<br />  4        from emp )<br />  5   order by deptno;<br /><br />Execution Plan<br />-----------------------------------------------------------------<br />Plan hash value: 3127359958<br /><br />-----------------------------------------------------------------<br />| Id |	Operation	    | Name	     |	 Rows |  Bytes	|	<br />-----------------------------------------------------------------<br />|  0 |	SELECT STATEMENT    |		     |   100K |  3417K	|	<br />|  1 |  SORT ORDER BY	    |		     |   100K |  3417K	|<br />|* 2 |  HASH JOIN SEMI	    |		     |   100K |  3417K	|<br />|  3 |  TABLE ACCESS FULL   | DEPT	     |   100K |  2148K	|	<br />|  4 |  INDEX FAST FULL SCAN| EMP_DEPTNO_IDX |   1000K|    12M	|<br />-----------------------------------------------------------------<br />Predicate Information (identified by operation id):<br />-----------------------------------------------------------------<br /><br />   2 - access("DEPTNO"="DEPTNO")<br /><br />SQL> select deptno, dname<br />  2    from dept where exists<br />  3    ( select null<br />  4        from emp<br />  5       where emp.deptno =<br />  6            dept.deptno )<br />  7   order by deptno;<br /><br />Execution Plan<br />---------------------------------------------------------------<br />Plan hash value: 3127359958<br /><br />---------------------------------------------------------------<br />| Id |	Operation	    | Name	    |	Rows |   Bytes|	<br />---------------------------------------------------------------<br />|  0 |	SELECT STATEMENT    |		    |   100K |  3417K |	<br />|  1 |  SORT ORDER BY	    |		    |   100K |  3417K | <br />|* 2 |  HASH JOIN SEMI	    |		    |   100K |  3417K | <br />|  3 |  TABLE ACCESS FULL   | DEPT	    |   100K |  2148K |	<br />|  4 |  INDEX FAST FULL SCAN| EMP_DEPTNO_IDX|   1000K|    12M |	<br />---------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------------------<br /><br />   2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Another important thing to note about these examples is that the RBO never performed a semi join, as the CBO did. The RBO has a very limited set of access paths and join operations available—it simply doesn’t know how to do a semi join and won’t use that join operation. Instead it performs operations such as the SORT DISTINCT it did in Listing 2, which is much less efficient than the cost-based plan in this case. Another reason to abandon the RBO in favor of the CBO!

Why Does My Plan Change?
I have noticed that gathering statistics against tables where none of the data has changed can cause the query plans against those tables to change. For example, one day I gather statistics, run some queries, and save the query plans in a report. The next day, I gather statistics (but I have not modified any of the data in any table) and I discover that the plans are different. What could cause that? The new query plans were better, but I would like to understand how this could be.

This change is likely due to the default value of the METHOD_OPT parameter used with DBMS_STATS. In Oracle Database 10g, the METHOD_OPT parameter defaults to a SIZE AUTO. After you ran a query, the database remembered the predicates and updated a dictionary table, SYS.COL_USAGE$. Then, the next time you ran DBMS_STATS to gather statistics on these tables, DBMS_STATS queried that table to find out what columns should have histograms collected automatically, based on past query workload. It looked at your predicates and said, “Hmm, these columns are candidates for histograms based on the queries the end users have been running.”

You can see this easily with a relatively small example. I start out by creating a small table with some skewed data in a column named ID and gathering the default set of statistics on it. The data in the ID column is such that for the values 0 through 4, about 20 percent of the table will be retrieved, but for the value 99, only a single row will be returned. I’m using this exaggerated example just to see the plans change:

SQL> create table t<br />  2  as<br />  3  select mod(rownum,5) id, a.*<br />  4    from all_objects a;<br />Table created.<br />SQL> update t<br />  2     set id = 99<br />  3   where rownum = 1;<br />1 row updated.<br /><br />SQL> create index t_idx on t(id);<br />Index created.<br /><br />SQL> begin<br />  2   dbms_stats.gather_table_stats<br />  3   ( user, 'T' );<br />  4  end;<br />  5  /<br /><br />SQL> select column_name, count(*)<br />  2    from user_tab_histograms<br />  3   where table_name = 'T'<br />  4     and column_name = 'ID'<br />  5   group by column_name;<br /><br />COLUMN_NAME   COUNT(*)<br />-----------   ------------<br />ID                  2

So, right now, the ID column doesn’t have complete histograms, by default—just two buckets, as shown by the query against USER_TAB_HISTOGRAMS. To understand the skewed nature of the data, I need a histogram with more than just two buckets; the two entries in USER_TAB_HISTOGRAMS tell the optimizer only the high and low values right now.

The optimizer knows the high value (99), the low value (0), the number of distinct values (6 in this case), and the number of rows in my table T (50,119 when I tested). Given those facts, the optimizer will believe that WHERE ID = will return about 50,119/6, or 8,353 rows. Sure enough, when I query with ID=1 or ID=99, I observe the results in Listing 4.

Code Listing 4: CBO plans when ID=1 and ID=99

SQL> set autotrace traceonly explain<br />SQL> select *<br />  2    from t<br />  3   where id = 1;<br /><br />----------------------------------------------------------------------------<br />|  Id |	Operation	 | Name	|  Rows	|  Bytes|   Cost (%CPU)	|  Time	   |<br />----------------------------------------------------------------------------<br />|   0 |	SELECT STATEMENT |	|  8353	|   783K|   163	(2)	| 00:00:02 |<br />|*  1 | TABLE ACCESS FULL| T	|  8353	|   783K|   163	(2)	| 00:00:02 |<br />----------------------------------------------------------------------------<br /><br />SQL> select *<br />  2    from t<br />  3   where id = 99;<br /><br />Execution Plan<br />----------------------------------------------------------------------------<br />Plan hash value: 1601196873<br /><br />----------------------------------------------------------------------------<br />|  Id |	Operation	 | Name	|   Rows |  Bytes|  Cost (%CPU)	| Time	   |<br />----------------------------------------------------------------------------<br />|   0 |	SELECT STATEMENT |	|   8353 |   783K|   163 (2)	| 00:00:02 |<br />|*  1 | TABLE ACCESS FULL| T	|   8353 |   783K|   163 (2)	| 00:00:02 |<br />----------------------------------------------------------------------------

Now, I immediately gather statistics, using the same command as before:

SQL> begin<br />  2   dbms_stats.gather_table_stats<br />  3   ( user, 'T' );<br />  4  end;<br />  5  /<br />SQL> select column_name, count(*)<br />  2    from user_tab_histograms<br />  3   where table_name = 'T'<br />  4     and column_name = 'ID'<br />  5   group by column_name;<br /><br />COLUMN_NAME   COUNT(*)<br />------------  -----------<br />ID                  5

Note, however, that I have more than two buckets for my histograms. DBMS_STATS, using the AUTO setting, gathered more information here. If I were to query the SYS.COL_USAGE$ table right now, I would discover that a new row was added, indicating that I have some queries in my system that use equality predicates against this particular database column. That is the “magic” that caused DBMS_STATS to change how it gathered statistics and caused a dramatic change in my resulting query plans, as shown in Listing 5.

Code Listing 5: New CBO plans when ID=1 and ID=99

  2    from t<br />  3   where id = 1;<br /><br />Execution Plan<br />----------------------------------------------------------------------------<br />Plan hash value: 1601196873<br /><br />----------------------------------------------------------------------------<br />| Id |	Operation	 | Name	|  Rows	|  Bytes|  Cost (%CPU)	| Time	   |<br />----------------------------------------------------------------------------<br />|  0 |	SELECT STATEMENT |	|  10260|   961K|   164	(2)	| 00:00:02 |<br />|* 1 |  TABLE ACCESS FULL| T	|  10260|   961K|   164	(2)	| 00:00:02 |<br />----------------------------------------------------------------------------<br /><br />SQL> select *<br />  2    from t<br />  3   where id = 99;<br /><br />Execution Plan<br />----------------------------------------------------------------------------------------<br />Plan hash value: 470836197<br /><br />----------------------------------------------------------------------------------------<br />| Id |  Operation	           | Name    | Rows |  Bytes|  Cost (%CPU)  |  Time    |<br />----------------------------------------------------------------------------------------<br />|  0 |  SELECT STATEMENT	   |	     | 1    |    96	|   2	(0) | 00:00:01 |<br />|  1 |  TABLE ACCESS BY INDEX ROWID|	T    | 1    |    96	|   2	(0) | 00:00:01 |<br />|* 2 |  INDEX RANGE SCAN	   |	T_IDX| 1    |   	|   1	(0) | 00:00:01 |<br />----------------------------------------------------------------------------------------

Note how the cardinalities are very different—8,353 changed to either 10,260 or 1. The resulting change in estimated cardinality led to a change in the overall cost of the first query plan and resulted in an entirely different (but better) query plan in the second case.

This is a significant fact for a DBA to be aware of. As the queries submitted by your end users change over time, the AUTO feature of statistics gathering could change what statistics are gathered over time as well. Unless you understand this feature, enabled by default in Oracle Database 10g, it can look as if inexplicable magic is happening in your database.

Powered by ScribeFire.

Reblog this post [with Zemanta]

Basic SQL Performance Diagnosis

Article is based on Randolf’s Post on Basic SQL statement performance diagnosis-HOW TO

 

The following steps should be taken to gather detailed information about the SQL statement:

1. Gather information about parameters relevant to the optimizer
2. Gather the output of the SQL*Plus AUTOTRACE and TIMING feature when executing your statement
3. Get the EXPLAIN PLAN output of DBMS_XPLAN.DISPLAY including the "Predicate Information" section
4. Run an extended SQL trace of your statement and analyze the generated trace file using a trace file analyzer like TKPROF
5. If you’re on Oracle 10g or later, gather the output of DBMS_XPLAN.DISPLAY_CURSOR after executing your statement

 

Step 1

Save in File Diag1.sql

set echo on
set timing on trimspool on linesize 130 pagesize 999

alter session set timed_statistics = true;
spool diag1.log
show parameter user_dump_dest
show parameter optimizer
show parameter db_file_multi
show parameter db_block_size
column sname format a20
column pname format a20
column pval2 format a20
select  sname , pname , pval1 , pval2
from sys.aux_stats$;

explain plan for
— put your statement here
select   *  from  t_demo
where type = ‘VIEW’ order by id;

select * from table(dbms_xplan.display);

rollback;
set autotrace traceonly arraysize 100

alter session set tracefile_identifier = ‘mytrace1’;

alter session set events ‘10046 trace name context forever, level 8’;
— put your statement here
select  *
from t_demo where type = ‘VIEW’  order by id;
disconnect
spool off
exit

 

Make sure you see the optimizer settings (provided your user has access to V$PARAMETER), the EXPLAIN PLAN output and the AUTOTRACE statistics.

Please note that it’s crucial that the EXPLAIN PLAN output includes the "Predicate Information" section below the execution plan. If it’s missing and you get instead the message "PLAN_TABLE is old version" in the "Notes" section, you need to either re-create the PLAN_TABLE using the server-side script "$ORACLE_HOME/rdbms/admin/utlxplan.sql" or in 10g you can simply drop any PLAN_TABLE not owned by SYS since the SYS schema already provides a global temporary table PLAN_TABLE$ that is exposed via public synonym, so there is no need for a private PLAN_TABLE any longer.

Step 2

The next step that you need to perform is to analyze the trace file generated. The "USER_DUMP_DEST" setting in your script output tells you where your trace file has been generated on the server. It will have the "mytrace1" trace file identifier specified in its name, so it should be easy to identify, even if you have pass this request to a DBA.

TKPROF [input_trace_filename] trace_output.txt

You need to find the part in the trace file that corresponds to the statement that you’ve traced. It might contain other statements and cover even some statements recursively executed by Oracle.
Once you’ve located your statement in the trace file, the information below immediately following your statement is the crucial one:

 

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1501 0.53 1.36 800 149101 0 149938
——- —— ——– ———- ———- ———- ———- ———-
total 1503 0.53 1.36 800 149101 0 149938
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
——- —————————————————
149938 TABLE ACCESS BY INDEX ROWID T_DEMO (cr=149101 pr=800 pw=0 time=60042 us cost=0 size=60 card=1)
149938 INDEX RANGE SCAN IDX_DEMO (cr=1881 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 74895)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 1501 0.00 0.00
db file sequential read 800 0.05 0.80
SQL*Net message from client 1501 0.00 0.69

This covers the summary timing and blocks related information, the "Parse" related information (was it a hard parse or soft parse represented by the "Misses in library cache…" information), the "Row Source Operations" and the wait events. If one of these sections is missing, something went wrong.

 

Step 3

If you’re on 10g or later, you can run in a second step the "DBMS_XPLAN.DISPLAY_CURSOR" function to gather some additional information about the execution of your statement.

 

set echo on
set timing on trimspool on linesize 130 pagesize 999
set arraysize 100 termout off
spool off
— put your statement here
— use the GATHER_PLAN_STATISTICS hint
— if you’re not using STATISTICS_LEVEL = ALL
select /*+ gather_plan_statistics */
* from t_demo
where type = ‘VIEW’  order by id;
set termout on
spool diag2.log
select * from table(dbms_xplan.display_cursor(null, null, ‘ALLSTATS LAST’));
spool off
exit

 

If result of

select * from table(dbms_xplan.display_cursor(null, null, ‘ALLSTATS LAST’));

is Last statement has a PREV_SQL_ID of ZERO then execute the following query and get sql_id and replace above sql with the following

select sql_id,sql_text,child_number from v$sql
where sql_text like ‘SELECT  a16.mkt_oid mkt_oid,%’

select * from table(dbms_xplan.display_cursor(‘aut6x3q9gska4’, 0, ‘ALLSTATS’));

 

For any clarification visit Randolf’s blog

Creating SQL Tuning Task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := ‘SELECT a16.mkt_oid mkt_oid,’||
‘a16.mkt_nm mkt_nm,’||
‘ a16.mkt_cd mkt_cd,’||’a15.consolidated_brand_nm consolidated_brand_nm,’||
‘SUM (a11.xp_trx) wjxbfs3’ ||
‘FROM  prescrip_sales a11 JOIN month a12  ON (a11.mo_id = a12.mo_id)’||
‘JOIN zzmq00 pa13 ON (a12.mo_oid = pa13.mo_oid)  JOIN  d_product a14 ON (a11.src_prod_oid = a14.src_prod_oid) 
‘GROUP BY a16.mkt_oid,         a16.mkt_nm, a16.mkt_cd, a15.consolidated_brand_nm, a18.spec_group_oid, a18.spec_group_nm, pa13.mo_oid, pa13.mo_disp’;

 

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => ‘SYS’,
         scope       => ‘COMPREHENSIVE’,
         time_limit  => 60,
         task_name   => ‘bi_sql_tuning_test’,
         description => ‘Task to tune a query generated by  Microstrategy ‘);
END;

Executing a Tuning Task

After you have created a tuning task, you need to execute the task and start the tuning process.

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'bi_sql_tuning_test’ );
END;
 
Check status of the task.
 
SELECT status FROM DBA_ADVISOR_LOG WHERE task_name = 'bi_sql_tuning_test'
 

Displaying the Results of a Tuning Task

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'bi_sql_tuning_test')
  FROM DUAL;
 

Dropping a Tuning task

BEGIN 
DBMS_SQLTUNE.DROP_TUNING_TASK( task_name => 'bi_sql_tuning_test’ );
END;

SQL optimization using Oracle Hints

ORACLE HINT REFERENCE

 

 

 

PARALLEL

Scanning full table with parallel degree of 8. Parallel always assumes full table scan.

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name
  FROM employees hr_emp;
 

PARALLEL_INDEX

The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

 

 

INDEX

When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specified in the hint. In this case, the optimizer does not go for a Full Table Scan nor does it use any other index. In addition, it does not calculate the cost of the Index to be used.

If no INDEX hint is specified the optimizer determines the cost of the each index that could be used to access the table and uses the one with the lower cost.

If there are multiple indexes specified with the Hint then the optimizer has to determine the cost of each index to be used with the specified table. Once that is determined, it uses the Index with the lower cost to access the table. In this case, the optimizer does not do a FULL Table Scan. Also note that, the optimizer may choose to use multiple indexes and then merge the result sets to access the table. This method is used if the cost is low.

select /*+ INDEX(emp_city idx_job_code) */ empname, 
job_code from emp where job_code = 'T';

INDEX_COMBINE

The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;
 
FULL

You can use the FULL hint to bypass the use of the INDEX. For example if you have a table, which is indexed, and the value you are searching for has a large number of duplicates, then you can go in for a Full Table scan. If an index is used, in this case it will be inefficient. Using FULL hint will bypass the index(es).

select /*+ FULL(emp_status) */ empname, status from 
emp_status where status = 'P';
 
NO_INDEX

The NO_INDEX hint explicitly specifies which index cannot be used for the specified table.

select /*+ NO_INDEX(emp_status emp_status) */ empname,

status from emp_status where status = ‘P’;

 
  • If this hint specifies single or multiple available index(es), then the optimizer does not consider a scan on these indexes. Other indexes not specified are still considered.
  • If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

 

*+ ALL_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption)

 

Hints for Join Operations

NO_USE_NL

The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table. With this hint, only the hash join and the sort-merge joins will be considered for the specified tables.

          SELECT /*+ NO_USE_NL ( employees ) */

NO_USE_MERGE

This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.

          SELECT /*+ NO_USE_MERGE ( employees dept ) */

NO_USE_HASH

This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.

           SELECT /*+ NO_USE_HASH ( employees dept ) */ …

NO_INDEX_FFS

This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.

            SELECT  /*+ NO_INDEX_FFS ( tablespec indexspec ) */

CARDINALITY

This hint specifies the estimated cardinality returned by a query or portions of the query. Note that if no table is specified, the cardinality is the total number of rows returned by the entire query.

SELECT /*+ CARDINALITY ( [tablespec] card ) */

 

 

DATA WAREHOUSE HINTS

STAR_TRANSFORMATION

The STAR_TRANSFORMATION hint instructs the optimizer to use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. For example:

SELECT /*+ STAR_TRANSFORMATION */  *
  FROM sales s, times t, products p, channels c
  WHERE s.time_id = t.time_id
    AND s.prod_id = p.product_id
    AND s.channel_id = c.channel_id
    AND p.product_status = 'obsolete';

Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

 

UNDOCUMENTED HINTS

CPU_COSTING

This hint turns CPU costing on for the SQL statement. This is the default cost model for the optimizer. The optimizer estimates the number and type of IO operations and the number of CPU cycles the database will perform during execution of the given query. It uses system statistics to convert the number of CPU cycles and number of IO(s) to the estimated query execution time. The CPU_COST column of the PLAN_TABLE stores the CPU cost.

               SELECT /*+ CPU_COSTING (tablespec indexspec) */

NO_CPU_COSTING

This hint turns off CPU costing for the SQL statement. CBO then uses the IO cost model, which measures everything in single-block reads and ignores CPU cost.

               SELECT /*+ NO_CPU_COSTING */

Optimizing Import/Export

EXPORT:

  • Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer "2000000")
  • Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer "64000")
  • Use DIRECT=yes (direct mode export)
  • Stop unnecessary applications to free-up resources for your job.
  • If you run multiple export sessions, ensure they write to different physical disks.
  • DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:

  • Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
  • Place the file to be imported on a separate physical disk from the oracle data files
  • Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
  • Set the LOG_BUFFER to a big value and restart oracle.
  • Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
  • Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
  • Use COMMIT=N in the import parameter file if you can afford it
  • Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics.
  • Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements
  • Remember to run the indexfile previously created.

Improving Data Pump Export and Import:

Use Parallel option and export to as many files as the degree of parallelism.

expdp username/password directory=dump_dir filesize=1G dumpfile=full%U.dmp logfile=fulllog parallel=16

Increasing pga_aggregate_target to boost import.

Impdp Options

Analyze once after the load – Set analyze=n and analyze with dbms_stats after the load has completed.

Increase recordlength – Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).

Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase.  Larger tables may not be suitable for this option due to the required rollback/undo space.

Dedicate a single, large rollback segment – Many professionals create a single large rollback segment and take all others offline during the import.

Set indexes=n – Index creation can be postponed until after import completes, by specifyingindexes=n.  If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table.  Setting indexes=neliminates this maintenance overhead.   You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.

Defer CBO stats – Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.

Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.

Disable logging – You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

Further read Oracle data load