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

Oracle Optimizer PowerPoint PPT Presentation


  • 173 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

Oracle Optimizer


Types of optimizers

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 optimizers1

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

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

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

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

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

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

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

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

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

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

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

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

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 sets1

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

Is the table always accessed?

What if there is no index?


Operations that manipulate data sets2

Operations that Manipulate Data Sets

  • Consider the query:

    • select sname from sailors

      union

      select bname from boats;


Operations that manipulate data sets3

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?


Oracle optimizer

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

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

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

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 joins1

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

When Are Nested Loops Joins Used?

  • If tables are of unequal size

  • If results should be returned online


Hash join plan

Hash Join Plan

HASH JOINTABLE ACCESS FULL OF table_ATABLE ACCESS FULL OF table_B


When are hash joins used

When Are Hash Joins Used?

  • If tables are small

  • If results should be returned online


Sort merge join plan

Sort-Merge Join Plan

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


When are sort merge joins used

When Are Sort/Merge Joins Used?

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


Hint examples for joins

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 and the semi structured data model

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


Motivation

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

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

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

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

Example XML Document

<addresses>

<person friend="yes">

<name> Jeff Cohen</name>

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

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

<email> [email protected] </email>

</person>

<person friend="no">

<name> Irma Levy</name>

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

<email>[email protected]</email>

</person>

</addresses>

Opening Tag

Element

Attribute

Closing Tag


Very unstructured xml

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 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

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

Relational vs Semi-Structured


Data exchange

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

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


Write once use everywhere

XSL

XSL

XSL

WML

(hand-held

devices)

HTML

(web browser

TEXT

(Excel)

Write Once Use Everywhere

XML Data


Using xml

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