1 / 47

Oracle Optimizer

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.

garrison-meadows
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Optimizer

  2. 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}

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

  4. 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;

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

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

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

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

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

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

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

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

  13. 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’;

  14. 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)

  15. 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;

  16. 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)

  17. Is the table always accessed? What if there is no index?

  18. Operations that Manipulate Data Sets • Consider the query: • select sname from sailors union select bname from boats;

  19. 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?

  20. 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'

  21. Distinct • What should Oracle do when processing the query (assuming that sid is the primary key): • select distinct sid from Sailors • Try it!!

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

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

  24. 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)

  25. When Are Nested Loops Joins Used? • If tables are of unequal size • If results should be returned online

  26. Hash Join Plan HASH JOINTABLE ACCESS FULL OF table_ATABLE ACCESS FULL OF table_B

  27. When Are Hash Joins Used? • If tables are small • If results should be returned online

  28. Sort-Merge Join Plan MERGE JOINSORT JOINTABLE ACCESS FULL OF table_ASORT JOINTABLE ACCESS FULL OF table_B

  29. When Are Sort/Merge Joins Used? • Performs badly when tables are of unequal size. Why?

  30. 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’;

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

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

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

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

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

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

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

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

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

  40. Relational vs Semi-Structured

  41. 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?

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

  43. XSL XSL XSL WML (hand-held devices) HTML (web browser TEXT (Excel) Write Once Use Everywhere XML Data

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

More Related