Oracle optimizer
Sponsored Links
This presentation is the property of its rightful owner.
1 / 47

Oracle Optimizer PowerPoint PPT Presentation


  • 196 Views
  • Uploaded on
  • Presentation posted in: General

Oracle Optimizer. Types of Optimizers. There are different modes for the optimizer RULE: Rule-based optimizer (RBO) Deprecated; not updated since 1994 CHOOSE: Default option. Chooses cost-based optimizer (CBO) if statistics available, rule-based optimizer otherwise.

Download Presentation

Oracle Optimizer

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle Optimizer


Types of Optimizers

  • There are different modes for the optimizer

  • RULE: Rule-based optimizer (RBO)

    • Deprecated; not updated since 1994

  • CHOOSE: Default option. Chooses cost-based optimizer (CBO) if statistics available, rule-based optimizer otherwise.

ALTER SESSION SET optimizer_mode = {choose|rule|first_rows(_n)|all_rows}


Types of Optimizers

  • ALL_ROWS (CBO): Execute the query so that all of the rows are returned as quickly as possible

    • Merge Join has priority over Block Nested Loop Join

  • FIRST_ROWS(n) (CBO): execute the query so that all of the first n rows are returned as quickly as possible

    • Block Nested Loop Join has priority over Merge Join


Analyzing the Data

analyze table | index

<table_name> | <index_name>

compute statistics |

estimate statistics [sample <integer>

rows | percent] |

delete statistics;

analyze table Sailors estimate statistics sample 25 percent;


Viewing the Execution Plan

  • You need a PLAN_TABLE table. So, the first time that you want to see execution plans, run the command:

  • Set autotrace on to see all plans

    • Display the execution path for each query, after being executed

@$ORACLE_HOME/rdbms/admin/utlxplan.sql


Operations that Access Tables

  • TABLE ACCESS FULL: sequential table scan

    • Oracle optimizes by reading multiple blocks

    • Used whenever there is no where clause on a query

      select * from Sailors

  • TABLE ACCESS BY ROWID: access rows by their RowID values.

    • How do you get the rowid? From an index!

      select * from Sailors where sid > 10


Types of Indexes

  • Unique: each row of the indexed table contains a unique value for the indexed column

  • Nonunique: the row’s indexed values can repeat


Operations that Use Indexes

  • INDEX UNIQUE SCAN: Access of an index that is defined to be unique

  • INDEX RANGE SCAN: Access of an index that is not unique or access of a unique index for a range of values


When are Indexes Used/Not Used?

  • If you set an indexed column equal to a value, e.g., sname = 'Jim'

  • If you specify a range of values for an indexed column, e.g., sname like 'J%'

    • sname like '%m': will not use an index

    • UPPER(sname) like 'J%' : will not use an index

    • sname is null: will not use an index, since null values are not stored in the index

    • sname is not null: will not use an index, since every value in the index would have to be accessed


When are Indexes Used? (cont)

  • 2*age = 20: Index on age will not be used. Index on 2*age will be used.

  • sname != 'Jim': Index will not be used.

  • MIN and MAX functions: Index will be used

  • Equality of a column in a leading column of a multicolumn index. For example, suppose we have a multicolumn index on (sid, bid, day)

    • sid = 12: Can use the index

    • bid = 101: Cannot use the index


Optimizer Hints

  • You can give the optimizer hints about how to perform query evaluation

  • Hints are written in /*+ */ right after the select

  • Note: These are only hints. The Oracle optimizer can choose to ignore your hints


Hints

  • FULL hint: tell the optimizer to perform a TABLE ACCESS FULL operation on the specified table

  • ROWID hint: tell the optimizer to perform a TABLE ACCESS BY ROWID operation on the specified table

  • INDEX hint: tells the optimizer to use an index-based scan on the specified table


Examples

Select /*+ FULL (sailors) */ sid

From sailors

Where sname=‘Joe’;

Select /*+ INDEX (sailors) */ sid

From sailors

Where sname=‘Joe’;

Select /*+ INDEX (sailors s_ind) */ sid

From sailors S, reserves R

Where S.sid=R.sid AND sname=‘Joe’;


Combining Output From Multiple Index Scans

  • Suppose we have 2 indexes: sname, rating

  • select * from sailorswhere sname = 'Jim' and rating = 10

    ----------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SAILORS‘2 1 AND-EQUAL3 2 INDEX (RANGE SCAN) OF 'SNAME_IND' (NON-UNIQUE)4 2 INDEX (RANGE SCAN) OF 'RAT' (NON-UNIQUE)


Operations that Manipulate Data Sets

  • Up until now, all operations returned the rows as they were found

  • There are operations that must find all rows before returning a single row

  • Try to avoid these operations for online users!

    • SORT ORDER BY: query with order by

      select sname, age

      from Sailors

      order by age;


Operations that Manipulate Data Sets

  • SORT UNIQUE: sorting records while eliminating duplicates (e.g., query with distinct; query with minus, intersect or union)

  • SORT AGGREGATE, SORT GROUP BY: queries with aggregate or grouping functions (like MIN, MAX)


Is the table always accessed?

What if there is no index?


Operations that Manipulate Data Sets

  • Consider the query:

    • select sname from sailors

      union

      select bname from boats;


Operations that Manipulate Data Sets

  • Consider the query:

    • select sname from sailors

      minus

      select bname from boats;

How do you think that Oracle implements intersect?

union all?


Operations that Manipulate Data Sets

  • Select MIN(age), COUNT(*)

    from Sailors

    GROUP BY rating

    ------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 SORT (GROUP BY)2 1 TABLE ACCESS (FULL) OF 'SAILORS'


Distinct

  • What should Oracle do when processing the query (assuming that sid is the primary key):

    • select distinct sid

      from Sailors

  • Try it!!


Join Methods

  • Select * from Sailors, Reserves

    where Sailors.sid = Reserves.sid

  • Oracle can use an index on Sailors.sid or on Reserves.sid (note that both will not be used)

  • Join Methods: MERGE JOIN, NESTED LOOPS, HASH JOIN


Nested Loops Joins

  • Block nested loop join

    NESTED LOOPSTABLE ACCESS FULL OF our_outer_tableTABLE ACCESS FULL OF our_inner_table

  • Index nested loop join

    NESTED LOOPS

    TABLE ACCESS FULL OF our_outer_table

    TABLE ACCESS BY ROWID OF our_inner_tableINDEX RANGE SCAN OF inner_table_index


Nested Loops Joins

  • Select *from reserves r, sailors swhere r.sid=s.sid;

    -------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF 'RESERVES‘3 1 TABLE ACCESS (BY INDEX ROWID) OF 'SAILORS‘4 3 INDEX (UNIQUE SCAN) OF 'SYS_C00628777' (UNIQUE)


When Are Nested Loops Joins Used?

  • If tables are of unequal size

  • If results should be returned online


Hash Join Plan

HASH JOINTABLE ACCESS FULL OF table_ATABLE ACCESS FULL OF table_B


When Are Hash Joins Used?

  • If tables are small

  • If results should be returned online


Sort-Merge Join Plan

MERGE JOINSORT JOINTABLE ACCESS FULL OF table_ASORT JOINTABLE ACCESS FULL OF table_B


When Are Sort/Merge Joins Used?

  • Performs badly when tables are of unequal size. Why?


Hint Examples for Joins

inner table

Select /*+ USE_NL (sailors) */ sid

From sailors S, reserves R

Where S.sid=R.sid AND sname=‘Joe’;

Select /*+ USE_MERGE (sailors, reserves) */

sid

From sailors S, reserves R

Where S.sid=R.sid AND sname=‘Joe’;

Select /*+ USE_HASH */ sid

From sailors S, reserves R

Where S.sid=R.sid AND sname=‘Joe’;


XML (Extensible Markup Language) andthe Semi-Structured Data Model


Motivation

  • We have seen that relational databases are very convenient to query. However:

    • There is a LOT of data not in relational databases!!

  • Perhaps the most widely accessed database is the web, and it certainly isn’t a relational database.


Querying the Web

  • The web can be queried using a search engine, however, we can’t ask questions like:

    • What is the lowest price for which a Jaguar is sold on the web?

  • Problems:

    • There are no facilities for asking complex questions, such as aggregation of data


Understanding the Web

  • In order to query the web, we must be able to understand it.

  • 2 Computer Science Approaches:

    • Artificial Intelligence Approach

    • Database Approach


Database Approach

“The web is unstructured and we will structure it”

  • Sometimes problems that are very difficult can be solved easily by enforcing a standard

  • Encourage the use of XML as a standard for data exchange on the web


Example XML Document

<addresses>

<person friend="yes">

<name> Jeff Cohen</name>

<tel> 04-828-1345 </tel>

<tel> 054-470-778 </tel>

<email> jeffc@cs.technion.ac.il </email>

</person>

<person friend="no">

<name> Irma Levy</name>

<tel> 03-426-1142 </tel>

<email>irmal@yourmail.com</email>

</person>

</addresses>

Opening Tag

Element

Attribute

Closing Tag


Very Unstructured XML

<?xml version=“1.0”?>

<DamageReport>

The insured’s <Vehicle Make = “Toyota”> Corolla </Vehicle> broke through the guard rail and plummeted into the ravine. The cause was determined to be <Cause>faulty brakes </Cause>. Amazingly there were no casualties.

</DamageReport>


XML Vs. HTML

  • XML and HTML are brothers. They are both special cases of SGML.

  • HTML has specific tag and attribute names. These are associated with a specific meaning

  • XML can have any tag and attribute name. These are not associated with any meaning

  • HTML is used to specify visual style

  • XML is used to specify meaning


Characteristics of Semistructured Data

(From http://www2.cs.uh.edu/~ceick/6340/intro-xml.ppt)

  • Missing or additional attributes

  • Multi-valued attributes

  • Different types in different objects

  • Heterogeneous collections

Self-describing, irregular data, no a priori structure


Relational vs Semi-Structured


Data Exchange

  • Problem: Many data sources, each of a different type (different vendor), with a different schema.

    • How can the data be combined and used together?

    • How can different companies collaborate on their data?

    • What format should be used to exchange the data?


Separating Content from Style

  • Web sites develop over time

  • Important to separate style from data in order to allow changes to the site structure and appearance

  • Using XML, we can store data alone

  • CSS separates style from data only in a limited way

  • Using XSL, this data can be translated into HTML

  • The data can be translated differently as the site develops


XSL

XSL

XSL

WML

(hand-held

devices)

HTML

(web browser

TEXT

(Excel)

Write Once Use Everywhere

XML Data


Using XML

  • Quering and Searching XML: There are query languages and search engines that query XML and return XML. Examples: Xpath, Xquery /SQL4X, Equix, XSEarch

  • Displaying XML:An XML document can have an associated style-sheet which specifies how the document should be translated to HTML. Examples: CSS, XSL


  • Login