《Pro Oracle SQL》 Chapter2--2.1 Oracle Architecture Basics

Chapter 2 SQL Execution     SQL的执行

Karen Morton   (page51)
    You likely learned the mechanics of writing basic SQL in a relatively short period of time. Over the course of a few weeks or few months, you became comfortable with the general statement structure and syntax, how to filter, how to join tables, and how to group and order data. But, how far beyond that initial level of proficiency have you traveled?  Writing complex SQL that executes efficiently is a skill that requires you to move beyond the basics.  Just because your SQL gets the job done doesn't mean it does the job well.
    你可能在相当短的时间内掌握写基本SQL的方法。经过几周或几月，你就对一般语句的结构和语法变的很自如，如何使用过滤器，如何连接表，以及如何分组和排序。但是，相比初级的熟练你走出了多远？写执行效率高的复杂SQL是一项技能，需要你在基本水平之上走的更远。就好比你的SQL能完成工作但不意味着它能把工作做好。
    In this chapter, I’m going to raise the hood and look at how SQL executes from the inside-out .  I’ll
discuss basic Oracle architecture and introduce the cost-based query optimizer.  You’ll learn how and
why the way you formulate your SQL statements affects the optimizer's ability to produce the most
efficient execution plan possible.  You may already know what to do, but understanding how SQL
execution works will help you help Oracle accomplish the results you need in less time and with fewer
resources required.   
    在本章，我将揭开"面罩"，从里向外 探询SQL如何执行。我将讨论基础的Oracle架构和介绍基于成本的查询优化器。你将学会如何和为什么你书写SQL 语句的方式将影响优化器，尽可能生成最有效执行计划的能力。你可能已经知道该做什么，但是理解SQL如何执行计划将有助于你帮助Oracle用更少的时间和资源完成你需要的结果。
Oracle Architecture Basics     Oralce 架构基础
    The SQL language is seemingly easy enough that you can learn to write simple SQL statements in fairly
short order.  But, just because you can write SQL statements that are functionally correct (i.e. produce
the proper result set), that doesn’t mean you’ve accomplished the task in the most effective and efficient
way.  
    SQL语言似乎非常容易，你可以在短期内写出简单的SQL语句。但是，仅仅因为你能写出功能正确的SQL(例如：生成适当的结果集)，并不意味着你用最有效和快捷的方式完成了任务。
    Moving beyond basic skills requires a deeper understanding of that skill.  For instance, when I
learned to drive, my father taught me the basics.  We walked around the car and discussed the parts of
the car that he thought were important to be aware of as the driver of the vehicle.  We talked about the
type of gas I should put in the car, the proper air pressure the tires should have, and the importance of
getting regular oil changes.  Being aware of these things would help make sure the vehicle would be in
good condition when I wanted to drive it.  
    超越菜鸟级别需要深入理解其中的技术。例如，当初我学开车，我的父亲教授了我基础驾驶的相关知识。我们围绕车转了一圈，然后讨论他认为驾驶汽车必须知道的车的各个部分。我们谈论关于我应该给汽车加什么样的汽油，轮胎的压力，以及多久换一次机油。知道这些有助于让我判断：当驾驶的时候，车是不是在良好状态。
    He then taught me the mechanics of driving.  I learned how to start the engine, shift gears, increase
and decrease my speed, use the brake, use turn signals, and so on.  But, what he didn’t teach me was
specifically how the engine worked, how to change the oil myself, or anything else other than what I
needed to do to allow me to safely drive the vehicle from place to place.  If I needed for my car to do
anything outside of what I learned, I’d have to take it to a professional mechanic.
    他接着教我驾驶操作。我学习怎么发动引擎，换挡，增速，减速，使用刹车，换信号灯，等等。但是，他没有专门教我引擎是如何工作的，我怎么换机油，以及关于 我要安全的从一处开另一处需要做什么。如果我要让我的车做任何所学以外的事，我只能去请教专门的（汽车）技工了。
    This isn’t a bad thing.  Not everyone needs to have the skills and knowledge of a professional
mechanic just to drive a car.  But the analogy applies to anyone who writes SQL.  You can learn the
basics and be able to get your applications from place to place.  But, without extending your knowledge,
I don’t believe you’ll ever be more than an everyday driver.  To really get the most out of SQL, you need
to understand how it does what it does.  That means you need to understand the basics of the underlying architecture on which the SQL you write will execute. 

    这不是件坏事。不是所有人需要仅仅为了开车，而去掌握专业技工所需要的知识和技能。但是这个类比适用于任何写SQL的人。你知道基本的SQL且能让你的应 用在很多地方跑的起来。但是，如果不扩展你的知识，我不觉得你会比普通的“司机”要强多少。要真正的掌握SQL，你需要理解它是怎么做的，做了什么。这意味着你需要理解所写的SQL运行的底层架构基础。
    Figure 2-1 depicts how most people view the database when they first learn to write SQL.  It is
simply a black box to which they direct SQL requests to and get data back from.  The “machinery” inside
the database is a mystery.   
    图2-1描绘了大多数人开始学习SQL时数据库的观念。它是一个简单的黑盒，用户发送SQL请求然后通过它获取数据。数据库内部的“机制”是一神秘事物。
   

    The  term  “Oracle database”  is  typically  used  to  refer  to both  the  files,  stored on disk, where  data resides and the memory structures used to manage those files.  In reality, the term “database” belongs to the  data  files and  the  term  “instance”  belongs  to  the memory  structures.   An  instance  consists of  the system global area  (SGA) and a set of background processes.   Each user connection  to  the database  is managed  via  a  client  process.    Client  processes  are  associated  with  server  processes  which  are  each allocated  their own private memory area called  the program, or process, global area  (PGA).    Figure 2-2 shows the Oracle Instance and Database diagram as found in the Oracle Concepts Guide.
    术语"Oracle 数据库"通常指磁盘中的文件，数据存储于文件中，和管理这些文件的内存结构。现实中，术语"数据库"属于数据文件而术语"实例"属于内存结构。一个实例由系统全局区(SGA)和一组后台进程所组成。每个连接到数据库的用户通过一客户进程管理着。客户进程与服务器进程相连接，每一连接分配他们各自的私有内存，(这个内存称之为)程序全局区(或进程全局区 PGA)。 图2-2展示了Oracle概念指南中包括的Oracle实例和数据库架构图。
    Don’t  get  overwhelmed  by  how  complex  this  looks.    The  Oracle  Concepts  Guide  goes  into  detail  about  each  of  the  elements  you  see  in  Figure  2-1.    I  think  it’s  a  great  idea  for  everyone who will  use Oracle to read the Oracle Concepts Guide. But for our purposes, I want to limit the discussion to a few key areas  that will help you understand how SQL operates.   Specifically,  I want  to  review  two  areas  of  the SGA,  the  shared  pool  (specifically,  the  library  cache within  the  shared  pool)  and  the  database  buffer cache.   Later  in  the  book,  I’ll  also  discuss  some  particulars about  the PGA, but  I’m going  to  keep  our review  limited to the SGA for now.   Note that these discussions will present a fairly broad picture.   As I said,  I don’t want  to overwhelm you, but  I do  think  this  is  critical  information  to get a grasp on before you go any further. 
    不要被所看到的复杂(结构)所晕倒。Oracle 概念指南你所见的图2-1的每个元素均有详细描述。我认为对每一个将使用Oracle的人先读一下《Oracle 概念指南》是一个好主意。但是对我们的目的而言，我想要限制讨论几个关键的将帮助我们理解SQL如何运作的区域。特别的，我想要关注SGA的两个区域，共享池(尤其是在共享池中的库缓存)和数据库缓冲区。在本书末尾，我将讨论一个关于PGA的特别之处，但是现在我只限于SGA。注意这些讨论将呈现一相当宽大的结构图。如我所述，我不想让你晕倒，但是我想掌握这些至关重要的信息是你进一步提升的前提。

 

评论

1 楼 mojunbin 2012-04-12  

这个不能不顶。