123
 123

Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2018-03-26 Mon

11:18 Oracle 12.2 新特性:BigSCN 支持 8 字节存储解决SCN越界问题 (19403 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

前情回顾:

更新通报:Oracle修正了关于DB Link补丁的公告

解决方案:Oracle的DB Link问题及升级路线详述

预警揭秘:11.2.0.4前版必须在2019年4月升级

Oracle Database 12.2 中,为了更彻底的解决SCN问题,Oracle 通过引入 BigSCN 的新特性,最终改变了 SCN 的算法。

ScnChangedCover.jpg

BigSCN 新特性最根本的改变是:将原来 SCN 的存储位数从 6 字节扩展为 8 字节。对比起来,我们将原来的SCN算法称为 SmallSCN,现在的就是 BigSCN。

在 Oracle 12.2 的执行文件中,可以看到其中的一点提示:

[oracle12c@enmotech bin]$ strings oracle | grep big_scn

_big_scn_test_mode

Raising initial SCN from 0x%016llx to 0x0002000000000000 due to _big_scn_test_mode = 4

Raising initial SCN from 0x%016llx to 0x0000ffffffff1fff due to _big_scn_test_mode = %d

通过隐含参数列表,可以获得 big scn 的一个隐含参数,从这个注释中可以看出新特性被命名为 BigSCN, 缺省值是 2 ,在产品环境中这个参数不可以修改,是以测试目的设置的 :

NAME: _big_scn_test_mode

VALUE: 2

DESCRIB: testing mode for BigSCN

通过以上两类输出,可以看到,当 _big_scn_test_mode 被设置为 4 的时候,SCN 会增进为 0x0002000000000000 ,由这些我们可以看出 SCN 终于突破了 6 Bytes 的设置,进入到了 8 Bytes 时代。


插播活动信息

2018 ACOUG中国行之·上海站 4月13日上海相见,从Oracle 18c到MySQL 8.0 ,5 大技术主题,欢迎来约,报名详情参考:

ACOUG China Tour 2018 - 4月13日启航上海站

当SCN mode 设置为 4 的时候,SCN 会直接跃迁到 7 Bytes,超越了 6 Bytes 的界限。

那么这个SCN 是多少?

SQL> select to_number('2000000000000','xxxxxxxxxxxxx') scn

from dual;

SCN

------------------------

562,949,953,421,312

而 6 Bytes 的 SCN极限值是281 trillion :

SQL> select power(2,48) scn from dual;

SCN

------------------------

281,474,976,710,656

将这两组数据放到一个表格会显得一目了然:

SCN 位数和设置 SCN值
6 Bytes最大可用

281,474,976,710,656

_big_scn_test_mode=4

562,949,953,421,312

BigSCN 最大可用
9,223,372,036,854,775,808

_big_scn_test_mode=4 的起点是 49 位,比较 原来的 48 位增进一位,这个起点就直接超越了过去的最大限制:

SQL> select power(2,49) scn from dual;

SCN

------------------------

562,949,953,421,312

BigSCN 最大可用值上升到一个天量数字,可以看到关于SCN问题,我们越来越不需要去担心了:

SQL> select power(2,63) scn from dual;

SCN

--------------------------------

9,223,372,036,854,775,808

虽然理论值做出了改变,SCN的地址空间也获得了增加,但是在实践中,这些新特性的获得是渐进式,在 12.2 之后,这些特性才会逐渐的释放出来。

在以下我的测试环境中,尝试将SCN推进到了极高的位置:

SQL> select current_scn scn from v$database;

SCN

--------------------------------

4,519,057,215,000,399

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dumpvar sga kcsgscn_

kcslf kcsgscn_ [0600113B8, 0600113E8) = 00050F5D 00100E0F

将这个数字放到前面的表格中,大家可以看到SCN在实践中可以获得的海量值空间:

SCN 位数和设置 SCN值
6 位SmallSCN 最大

281,474,976,710,656

_big_scn_test_mode=4

562,949,953,421,312

测试环境 SCN 推进量 4,519,057,215,000,399

8 位BigSCN 最大

9,223,372,036,854,775,808

为了防止SCN的过度增加,Oracle 增加了内部函数去分析headroom,并通过 600 号错误的 kcm_low_scn_headroom_alert_1 抛出异常:

2018-03-23T18:12:01.849206+08:00

Errors in file /enmo12c/enmo12c/trace/enmo12c_ora_5259.trc (incident=174424) (PDBNAME=CDB$ROOT):

ORA-00600: internal error code, arguments: [2252], [4520092301887888], [4519517455400960], [], [], [], [], [], [], [], [], []

Incident details in: /enmo12c/enmo12c/incident/incdir_174424/enmo12c_ora_5259_i174424.trc

2018-03-23T18:12:01.858629+08:00

Errors in file /enmo12c/enmo12c/trace/enmo12c_ckpt_5220.trc (incident=174304) (PDBNAME=CDB$ROOT):

ORA-00600: internal error code, arguments: [kcm_low_scn_headroom_alert_1], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /enmo12c/enmo12c/incident/incdir_174304/enmo12c_ckpt_5220_i174304.trc

这个启用了 BigSCN 的 12.2 数据库,当通过DB Link连接 11.2.0.4 的数据库时:

SQL> create database link enmo

connect to eygle identified by eygle using 'enmo';

Database link created.

SQL> select * from dual@enmo;

select * from dual@enmo

*

ERROR at line 1:

ORA-24442: SCN exceeds the capability of the target OCI database or client

这是一个新的错误号:

ORA-24442: SCN exceeds the capability of the target OCI database or client

Cause: An attempt was made to transfer a system change number (SCN) to an Oracle database or client that is older than Release 12.2 and the SCN exceeds the maximum value that such a system can handle.

Action: If needed, update the target database or client to Release 12.2 or higher.

有了BigSCN的新特性,在12.2版本之后,Oracle 关于SCN的种种问题,可能再也不容易被遇到了。

在官方文档上有一些描述与 8 Bytes的 BigSCN相关:

Data Pump Export and Data Pump Import support the new big SCN size of 8 bytes. See the Export FLASHBACK_SCN and the Import FLASHBACK_SCN parameters.

As of Oracle Database 12c release 2 (12.2), the SCN value can be a big SCN (8 bytes). You can also specify a big SCN when you create a dump file for an earlier version that does not support big SCNs because actual SCN values are not moved. See the following restrictions for more information about using big SCNs.

  • You cannot specify a big SCN for a network export or network import from a version that does not support big SCNs.

附录

在kcm内核文件中,可以看到和BigSCN相关的一些函数调用:

bigscn: %c

-?comment:PROF: NO_PROFILE krsu_fal_rcv_hndl_bigscn()

-?comment:PROF: NO_PROFILE krsu_fal_send_hndl_bigscn()

-?comment:PROF: NO_PROFILE krsu_rfs_rcv_hndl_bigscn()

-?comment:PROF: NO_PROFILE krsu_rfs_send_hndl_bigscn()

-?comment:PROF: NO_PROFILE krsu_rfx_rcv_hndl_bigscn()

-?comment:PROF: NO_PROFILE krsu_rfx_send_hndl_bigscn()

krsu_fal_rcv_hndl_bigscn

krsu_fal_send_hndl_bigscn

krsu_rfs_rcv_hndl_bigscn

krsu_rfs_send_hndl_bigscn

krsu_rfx_rcv_hndl_bigscn

krsu_rfx_send_hndl_bigscn

-?comment:PROF: USED kcm_low_scn_headroom_alert()

kcm_low_scn_headroom_alert

kcm_low_scn_headroom_alert_1

相关阅读:

更新通报:Oracle修正了关于DB Link补丁的公告

解决方案:Oracle的DB Link问题及升级路线详述

预警揭秘:11.2.0.4前版必须在2019年4月升级

SCN、ORA-19706错误和内部参数

深入剖析:Oracle SCN 机制详解

安全警报:2018一月号安全补丁修复安全漏洞

安全警告:WebLogic WSAT组件漏洞挖矿程序攻击

如果在此问题上需要进一步的协助,请联系云和恩墨的技术团队,更详细的解决方案将提供给我们的服务客户。也欢迎加入我们的微信群进一步的讨论该问题。

云和恩墨

数据驱动,成就未来。整合业界顶尖的技术与合作伙伴资源,围绕数据及相关领域,提供解决方案和专业服务。
IT基础架构
分布式存储 | zData一体机 | 容灾方案
数据架构
Oracle DB2 MySQL NoSQL
专项服务:架构/安全/优化/升级/迁移
运维服务:运维服务 代维服务
人才培养:个人认证 企业内训
软件产品:SQL审核、自动化运维、数据恢复
应用架构
应用和中间件:数据建模 | SQL审核和优化 | 中间件服务

相关文章|Related Articles

01:31 “How did you learn so much stuff about Oracle?” (9356 Bytes) » Cary Millsap
In LinkedIn, a new connection asked me a very nice question. He asked, “I know this might sound stupid, but how did you learn so much stuff about Oracle. :)”

Good one. I like the presumption that I know a lot of stuff about Oracle. I suppose that I do, at least about some some aspects of it, although I often feel like I don’t know enough. It occurred to me that answering publicly might also be helpful to anyone trying to figure out how to prepare for a career. Here’s my answer.

I took a job with the young consulting division of Oracle Corporation in September 1989, about two weeks after the very first time I had heard the word “Oracle” used as the name of a company. My background had been mathematics and computer science in school. I had two post-graduate degrees: a Master of Science Computer Science with a focus on language design and compilers, and a Master of Business Administration with a focus in finance.

My first “career job” was as a software engineer, which I started before the MBA. I designed languages and wrote compilers to implement those languages. Yes, people actually pay good money for that, and it’s possibly still the most fun I’ve ever had at work. I wrote software in C, lex, and yacc, and I taught my colleagues how to do it, too. In particular, I spent a lot of time teaching my colleagues how to make their C code faster and more portable (so it would run on more computers than just the one on which you wrote it).

Even though I loved my job, I didn’t see a lot of future in it. At least not in Colorado Springs in the late 1980s. So I took a year off to get the MBA at SMU in Dallas. I went for the MBA because I thought I needed to learn more about money and business. It was the most difficult academic year of my life, because I was not particularly connected to or even interested in most of the subject matter. I hated a lot of my classes, which made it difficult to do as well as I had been accustomed. But I kept grinding away, and finished my degree in the year it was supposed to take. Of course I learned many, many things that year that have been vital to my career.

A couple of weeks after I got my MBA, I went to work for Oracle in Dallas, with a salary that was 168% of what it had been as a compiler designer. My job was to visit Oracle customers and help them with their problems.

It took a while for me to get into a good rhythm at Oracle. My boss was sending me to these local customers that were having problems with the Oracle Financial Applications (the “Finapps,” as we usually called them, which would many years later become the E-Business Suite) on version 6.0.26 of the ORACLE database (it was all caps back then). At first, I couldn’t help them near as much as I had wanted to. It was frustrating.

That actually became my rhythm: week after week, I visited these people who were having horrific problems with ORACLE and the Finapps. The database in 1990, although it had some pretty big bugs, was still pretty good. It was the applications that caused most of the problems I saw. There were a lot of problems, both with the software and with how it was sold. My job was to fix the problems. Some of those problems were technical. Many were not.

A lot of the problems were performance; problems of the software running “too slowly.” I found those problems particularly interesting. For those, I had some experience and tools at my disposal. I knew a good bit about operating systems and compilers and profilers and linkers and debuggers and all that, and so learning about Oracle indexes and rollback segments (two good examples, continual sources of customer frustration) wasn’t that scary of a step for me.

I hadn’t learned anything about Oracle or relational databases in school, I learned about how the database worked at Oracle by reading the documentation, beginning with the excellent Oracle® Database Concepts. Oracle sped me along a bit with a couple of the standard DBA courses.

My real learning came from being in the field. The problems my customers had were immediately interesting by virtue of being important. The resources available to me for solving such problems back in the early 1990s were really just books, email, and the telephone. The Internet didn’t exist yet. (Can you imagine?) The Oracle books available back then, for the most part, were absolutely horrible. Just garbage. Just about the only thing they were good for was creating problems that you could bill lots of consulting hours to fix. The only thing that was left was email and the telephone.

The problem with email and telephones, however, is that there has to be someone on the other end. Fortunately, I had that. The people on the other end of my email and phone calls were my saviors and heroes. In my early Oracle years, those saviors and heroes included people like Darryl Presley, Laurel Jamtgaard, Tom Kemp, Charlene Feldkamp, David Ensor, Willis Ranney, Lyn Pratt, Lawrence To, Roderick Mañalac, Greg Doherty, Juan Loaiza, Bill Bridge, Brom Mahbod, Alex Ho, Jonathan Klein, Graham Wood, Mark Farnham (who didn’t even work for Oracle, but who could cheerfully introduce me to anyone I needed), Anjo Kolk, and Mogens Nørgaard. I could never repay these people, and many more, for what they did for me. ...In some cases, at all hours of the night.

So, how did I learn so much stuff about Oracle? It started by immersing myself into a universe where every working day I had to solve somebody’s real Oracle problems. Uncomfortable, but effective. I survived because I was persistent and because I had a great company behind me, filled with spectacularly intelligent people who loved helping each other. Could I have done that on my own, today, with the advent of the Internet and lots and lots of great and reliable books out there to draw upon? I doubt it. I sincerely do. But maybe if I were young again...

I tell my children, there’s only one place where money comes from: other people. Money comes only from other people. So many things in life are that way.

I’m a natural introvert. I naturally withdraw from group interactions whenever I don’t feel like I’m helping other people. Thankfully, my work and my family draw me out into the world. If you put me into a situation where I need to solve a technical problem that I can’t solve by myself, then I’ll seek help from the wonderful friends I’ve made.

I can never pay it back, but I can try to pay it forward.

(Oddly, as I’m writing this, I realize that I don’t take the same healthy approach to solving business problems. Perhaps it’s because I naturally assume that my friends would have fun helping solve a technical problem, but that solving a business problem would not be fun and therefore I would be imposing upon them if I were to ask for help solving one. I need to work on that.)

So, to my new LinkedIn friend, here’s my advice. Here’s what worked for me:
  • Educate yourself. Read, study, experiment. Educate yourself especially well in the fundamentals. So many people don’t. Being fantastic at the fundamentals is a competitive advantage, no matter what you do. If it’s Oracle you’re interested in learning about, that’s software, so learn about software: about operating systems, and C, and linkers, and profilers, and debuggers, .... Read the Oracle Database Concepts guide and all the other free Oracle documentation. Read every book there is by Tom Kyte and Christian Antognini and Jonathan Lewis and Tanel Põder and Kerry Osborne and Karen Morton and James Morle all the other great authors out there today. And read their blogs.
  • Find a way to hook yourself into a network of people that are willing and able to help you. You can do that online these days. You can earn your way into a community by doing things like asking thoughtful questions, treating people respectfully (even the ones who don’t treat you respectfully), and finding ways to teach others what you’ve learned. Write. Write what you know, for other people to use and improve. And for God’s sake, if you don’t know something, don’t act like you do. That just makes everyone think you’re an asshole, which isn’t helpful.
  • Immerse yourself into some real problems. Read Scuttle Your Ships Before Advancing if you don’t understand why. You can solve real problems online these days, too (e.g., StackExchange and even Oracle.com), although I think that it’s better to work on real live problems at real live customer sites. Stick with it. Fix things. Help people.
Help people.

That’s my advice.
01:31 work with a custom schema with Spark loading CSV file (33571 Bytes) » Developer 木匠
Here's how you can work with a custom schema, a complete demo:
Unix shell code,
$>
echo "
Slingo, iOS
Slingo, Android
" > game.csv
Scala code:
import org.apache.spark.sql.types._

val customSchema = StructType(Array(
StructField("game_id", StringType, true),
StructField("os_id", StringType, true)
))

val csv_df = spark.read.format("csv").schema(customSchema).load("game.csv")
csv_df
.show

csv_df
.orderBy(asc("game_id"), desc("os_id")).show
csv_df
.createOrReplaceTempView("game_view")
val sort_df = sql("select * from game_view order by game_id, os_id desc")
sort_df
.show

Reference,
01:31 spark 2.2.1 installation guide on Mac (1606 Bytes) » Developer 木匠
// blog install spark 2.2.1 on Mac

Spark is incompatible with Java 9.
Install Java 8 instead.

# to uninstall Java 9
brew cask uninstall java

Here is the complete and simple installation steps for spark on Mac :

brew tap caskroom/versions
brew cask search java
brew cask install java8

# brew install scala # You might need these step,
brew install apache-spark


# Start spark shell,

./bin/spark-shell --master local[2]

./bin/spark-shell --master local[1]
01:31 flex CSV loader (6213 Bytes) » Developer 木匠
Purpose: Load any structure CSV file in Meteor to MongoDB, through package harrison:papa-parse.

meteor add harrison:papa-parse
meteor add themeteorchef:bert
meteor add reactive-var
meteor add fortawesome:fontawesome

$> meteor create csv

Then copy below 2 files into ./csv/

  • csv.html

<head>
  <title>cvs</title>
</head>

<body>
  <h1>Welcome to CSV loader!</h1>

  {{> upload}}
</body>

<template name="upload">
  <h4 class="page-header">Upload a CSV, unfixed structure.</h4>

  {{#unless uploading}}
    <input type="file" name="upload_CSV">
  {{else}}
    <p><i class="fa fa-spin fa-refresh"></i> Uploading files...</p>
  {{/unless}}
</template>


  • csv.js

Order = new Mongo.Collection('order');
if (Meteor.isClient) {
  Template.upload.onCreated( () => {
    Template.instance().uploading = new ReactiveVar( false );
  });

  Template.upload.helpers({
    uploading() {
      return Template.instance().uploading.get();
    }
  });

  Template.upload.events({
    'change [name="upload_CSV"]' (event, template ) {
      Papa.parse(event.target.files[0], {
        header: true,
        complete(results, file) {
          Meteor.call('parseUpload', results.data, (error, response) => {
            if (error) {
              Bert.alert(error.reason, 'warning');
            } else {
              template.uploading.set(false);
              Bert.alert('Upload complete!', 'success', 'growl-top-right' );
            }
          });
        }
      });
    }
  });

}
if (Meteor.isServer) {
  Meteor.startup(function () {
    // code to run on server at startup
  });

  Meteor.methods({
    parseUpload(data){
      //check(data, Array);

      for ( let i = 0; i < data.length; i++) {
        let item = data[i],
            exists = Order.findOne({orderId: item.order_no});
        if ( (item.order_no === undefined) || !exists ) {
          Order.insert(item);
        } else {
          console.warn( 'Rejected. This item already exists.');
          console.log(exists.Age + "," + item.order_no);
        }
      }
    }
  });
}
01:31 code review (8027 Bytes) » Developer 木匠
The biggest rule is that the point of code review is to find problems in code before it gets committed - what you're looking for is correctness.
The most common mistake in code review - the mistake that everyone makes when they're new to it - is judging code by whether it's what the reviewer would have written.
Remember that there are many ways to solve a problem.

Advice


  • What you're looking for is correctness.

Programming decisions are a matter of opinion. Reviewers and developers should seek to understand each other’s perspective but shouldn’t get into a philosophical debate.
  • Be humble.

Don’t be a prick.  :-)
“I didn’t see where these variables were initialized”.
“What do you think about Standard DRY and if it’s applies here?”, 
“I don’t understand why this is a global variable “
  • Make sure you have coding standards in place.

Coding standards are shared set of guidelines in an organization with buy-in from everyone. If you don’t have coding standards, then don’t let the discussion turn into a pissing contest over coding styles (opening braces ‘{‘ on the same line or the next!) If you run into a situation like that, take the discussion offline to your coding standards forum.
One of the goals of code reviews is to create ‘maintainable’ software.
  • Learn to communicate well.

You must be able to clearly express your ideas and reasons.
  • Authors should annotate source code before the review.

Authors should annotate code, what is the problem and goaldesign and solution implementation, before the review occurs, because annotations guide the reviewer through the changes, showing which files to look at first and defending the reason behind each code modification. Annotations should be directed at other reviewers to ease the process and provide more depth in context. As an added benefit, the author will often find additional errors before the peer review even begins. More bugs found prior to peer review will yield in lower defect density because fewer bugs exist overall.
.
.
Effective code reviews require a healthy culture that values quality and excellence. Code reviews will not give you the desired results if the team doesn’t believe in delivering high-quality products. You need a positive culture in which people are engaged – one that thrives on constructive criticism and allows the best ideas to win.
Peer code review can be efficient and effective while fostering open communication and knowledge-share between teammates.

Reference:


01:31 Words I Don’t Use, Part 5: “Wait” (10127 Bytes) » Cary Millsap
The fifth “word I do not use” is the Oracle technical term wait.

The Oracle Wait Interface

In 1991, Oracle Corporation released some of the most important software instrumentation of all time: the “wait” statistics that were implemented in Oracle 7.0. Here’s part of the story, in Juan Loaiza’s words, as told in Nørgaard et. al (2004), Oracle Insights: Tales of the Oak Table.
This stuff was developed because we were running a benchmark that we could not get to perform. We had spent several weeks trying to figure out what was happening with no success. The symptoms were clear—the system was mostly idle—we just couldn’t figure out why.

We looked at the statistics and ratios and kept coming up with theories, the trouble was that none of them were right. So we wasted weeks tuning and fixing things that were not the problem. Finally we ran out of ideas and were forced to go back and instrument the code to figure out what the problem was.

Once the waits were instrumented the problem was diagnosed in minutes. We were having “free buffer” waits because the DBWR was not writing blocks fast enough. It’s amazing how hard that was to figure out with statistics, and how easy it was to figure out once the waits were instrumented.

...In retrospect a lot of the names could be greatly improved. The wait interface was added after the freeze date as a “stealth” project so it did not get as well thought through as it should have. Like I said, we were just trying to solve a problem in the course of a benchmark. The trouble is that so many people use this stuff now that if you change the names it will break all sorts of thing tools, so we have to leave them alone.
Before Juan’s team added this code, the Oracle kernel would show you only how much time its user calls (like parse, exec, and fetch) were taking. The new instrumentation, which included a set of new fixed views like v$session_wait and new WAIT lines in our trace files, showed how much time Oracle’s system calls (like reads, writes, and semops) were taking.

The Working-Waiting Model

The wait interface begat a whole new mental model about Oracle performance, based on the principle of working versus waiting:
Response Time = Service Time + Wait Time
In this formula, Oracle defines service time as the duration of the CPU used by your Oracle session (the duration Oracle spent working), and wait time as the sum of the durations of your Oracle wait events (the duration that Oracle spent waiting). Of course, response time in this formula means the duration spent inside the Oracle Database kernel.

Why I Don’t Say Wait, Part 1

There are two reasons I don’t use the word wait. The first is simply that it’s ambiguous.

The Oracle formula is okay for talking about database time, but the scope of my attention is almost never just Oracle’s response time—I’m interested in the business’s response time. And when you think about the whole stack (which, of course you do; see holistic), there are events we could call wait events all the way up and down:
  • The customer waits for an answer from a user.
  • The user waits for a screen from the browser.
  • The browser waits for an HTML page from the application server.
  • The application server waits for a database call from the Oracle kernel.
  • The Oracle kernel waits for a system call from the operating system.
  • The operating system’s I/O request waits to clear the device’s queue before receiving service.
  • ...
If I say waits, the users in the room will think I’m talking about application response time, the Oracle people will think I’m talking about Oracle system calls, and the hardware people will think I’m talking about device queueing delays. Even when I’m not.

Why I Don’t Say Wait, Part 2

There is a deeper problem with wait than just ambiguity, though. The word wait invites a mental model that actually obscures your thinking about performance.

Here’s the problem: waiting sounds like something you’d want to avoid, and working sounds like something you’d want more of. Your program is waiting?! Unacceptable. You want it to be working. The connotations of the words working and waiting are unavoidable. It sounds like, if a program is waiting a lot, then you need to fix it; but if it’s working a lot, then it is probably okay. Right?

Actually, no.

The connotations “work is virtuous” and “waits are abhorrent” are false connotations in Oracle. One is not inherently better or worse than the other. Working and waiting are not accurate value judgments about Oracle software. On the contrary, they’re not even meaningful; they’re just arbitrary labels. We could just as well have been taught to say that an Oracle program is “working on disk I/O” and “waiting to finish its CPU instructions.”

The terms working and waiting really just refer to different subroutine call types:

“Oracle is workingmeans“your Oracle kernel process is executing a user call”
“Oracle is waitingmeans“your Oracle kernel process is executing a system call”

The working-waiting model implies a distinction that does not exist, because these two call types have equal footing. One is no worse than the other, except by virtue of how much time it consumes. It doesn’t matter whether a program is working or waiting; it only matters how long it takes.

Working-Waiting Is a Flawed Analogy

The working-waiting paradigm is a flawed analogy. I’ll illustrate. Imagine two programs that consume 100 seconds apiece when you run them:

Program AProgram B
DurationCall typeDurationCall type
98system calls (waiting)98user calls (working)
2user calls (working)2system calls (waiting)
100Total100Total

To improve program A, you should seek to eliminate unnecessary system calls, because that’s where most of A’s time has gone. To improve B, you should seek to eliminate unnecessary user calls, because that’s where most of B’s time has gone. That’s it. Your diagnostic priority shouldn’t be based on your calls’ names; it should be based solely on your calls’ contributions to total duration. Specifically, conclusions like, “Program B is okay because it doesn’t spend much time waiting,” are false.

A Better Model

I find that discarding the working-waiting model helps people optimize better. Here’s how you can do it. First, understand the substitute phrasing: working means executing a user call; and waiting means executing a system call. Second, understand that the excellent ideas people use to optimize other software are excellent ideas for optimizing Oracle, too:
  1. Any program’s duration is a function of all of its subroutine call durations (both user calls and system calls), and
  2. A program is running as fast as possible only when (1) its unnecessary calls have been eliminated, and (2) its necessary calls are running at hardware speed.
Oracle’s wait interface is vital because it helps us measure an Oracle program’s complete execution duration—not just Oracle’s user calls, but its system calls as well. But I avoid saying wait to help people steer clear of the incorrect bias introduced by the working-waiting analogy.
01:31 Words I Don’t Use, Part 4: “Expert” (2240 Bytes) » Cary Millsap
The fourth “word I do not use” is expert.

When I was a young boy, my dad would sometimes drive me to school. It was 17 miles of country roads and two-lane highways, so it gave us time to talk.

At least once a year, and always on the first day of school, he would tell me, “Son, there are two answers to every test question. There’s the correct answer, and there’s the answer that the teacher expects. ...They’re not always the same.”

He would continue, “And I expect you to know them both.”

He wanted me to make perfect grades, but he expected me to understand my responsibility to know the difference between authority and truth. My dad thus taught me from a young age to be skeptical of experts.

The word expert always warns me of a potentially dangerous type of thinking. The word is used to confer authority upon the person it describes. But it’s ideas that are right or wrong; not people. You should evaluate an idea on its own merit, not on the merits of the person who conveys it. For every expert, there is an equal and opposite expert; but for every fact, there is not necessarily an equal and opposite fact.

A big problem with expert is corruption—when self-congratulators hijack the label to confer authority upon themselves. But of course, misusing the word erodes the word. After too much abuse within a community, expert makes sense only with finger quotes. It becomes a word that critical thinkers use only ironically, to describe people they want to avoid.
01:31 Words I Don’t Use, Part 3: “Best Practice” (1515 Bytes) » Cary Millsap
The third “word I do not use” is best practice.

The “best practice” serves a vital need in any industry. It is the answer to, “Please don’t make me learn about this; just tell me what to do.” The “best practice” is a fine idea in spirit, but here’s the thing: many practices labeled “best” don’t deserve the adjective. They’re often containers for bad advice.

The most common problem with “best practices” is that they’re not parameterized like they should be. A good practice usually depends on something: if this is true, then do that; otherwise, do this other thing. But most “best practices” don’t come with conditions of execution—they often contain no if statements at all. They come disguised as recipes that can save you time, but they often encourage you to skip past thinking about things that you really ought to be thinking about.

Most of my objections to “best practices” go away when the practices being prescribed are actually good. But the ones I see are often not, like the old SQL “avoid full-table scans” advice. Enforcing practices like this yields applications that don’t run as well as they should and developers that don’t learn the things they should. Practices like “Measure the efficiency of your SQL at every phase of the software life cycle,” are actually “best”-worthy, but alas, they’re less popular because they sound like real work.
01:31 Words I Don’t Use, Part 2: “Holistic” (728 Bytes) » Cary Millsap
The second “word I do not use” is holistic.

When people use the word “holistic” in my industry (Oracle), it means that they’re paying attention to not just an individual subcomponent of a system, but to a whole system, including (I hope) even the people it serves.

But trying to differentiate technology services by saying “we take a holistic view of your system” is about like differentiating myself by saying I’ll wear clothes to work. Saying “holistic” would make it look like I’ve only just recently become aware that optimizing a system’s individual subsystems is not a reliable way to optimize the system itself. This should not be a distinctive revelation.
01:31 When is Video Better? » Cary Millsap
01:31 Welcome » Guy Harrison's main page
01:31 The String Puzzle » Cary Millsap
01:31 Load CSV file in Meteor » Developer 木匠
01:31 INSERT ALL and sequence NextVal » Developer 木匠
01:31 I Wish I Sold More » Cary Millsap
01:31 I Can Help You Trace It » Cary Millsap
01:31 Gwen Shapira on SSD » Cary Millsap
01:31 Golang range and close channel » Developer 木匠
01:31 Golang connect to Oracle database » Developer 木匠
01:31 Golang Oracle Performance Extensions » Developer 木匠
01:31 Golang Exercise: rot13Reader » Developer 木匠
01:31 Golang Exercise: Stringers » Developer 木匠
01:31 Golang Exercise: Slices » Developer 木匠
01:31 Golang Exercise: Images » Developer 木匠
01:31 Golang Exercise: HTTP Handlers » Developer 木匠
01:31 Golang Exercise: Errors » Developer 木匠
01:31 Golang Exercise: Equivalent Binary Trees » Developer 木匠
01:31 From Python chapter 2 code solution » Developer 木匠
01:31 Fail Fast » Cary Millsap
01:31 运维的经济分析 » 知道分子
01:31 没有药方的病历 » 知道分子
01:31 大法官说了算 » 知道分子
01:31 国画 » 知道分子
01:31 Incast » 知道分子
01:30 Will the Optimizer development team be at Oracle Open World? » Inside the Oracle Optimizer - Removing the black magic
01:30 Will the Optimizer Team be at Oracle Open World 2009? » Inside the Oracle Optimizer - Removing the black magic
01:30 Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g » Inside the Oracle Optimizer - Removing the black magic
01:30 Why are there more cursors in 11g for my query containing bind variables? » Inside the Oracle Optimizer - Removing the black magic
01:30 Why are some of the tables in my query missing from the plan? » Inside the Oracle Optimizer - Removing the black magic
01:30 What's Changed between my New Query Plan and the Old One? » Inside the Oracle Optimizer - Removing the black magic
01:30 What should I do with old hints in my workload? » Inside the Oracle Optimizer - Removing the black magic
01:30 We have moved! » Inside the Oracle Optimizer - Removing the black magic
01:30 Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer » Inside the Oracle Optimizer - Removing the black magic
01:30 Upgrading from 9i to 11g and the implicit migration from RBO » Inside the Oracle Optimizer - Removing the black magic
01:30 Update on Adaptive Cursor Sharing » Inside the Oracle Optimizer - Removing the black magic
01:30 Understanding DBMS_STATS.SET_*_PREFS procedures » Inside the Oracle Optimizer - Removing the black magic
01:30 SQL Plan Management (Part 4 of 4): User Interfaces and Other Features » Inside the Oracle Optimizer - Removing the black magic
01:30 SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines » Inside the Oracle Optimizer - Removing the black magic
01:30 SQL Plan Management (Part 2 of 4): SPM Aware Optimizer » Inside the Oracle Optimizer - Removing the black magic
01:30 Plan regressions got you down? SQL Plan Management to the rescue! » Inside the Oracle Optimizer - Removing the black magic
01:30 Outerjoins in Oracle » Inside the Oracle Optimizer - Removing the black magic
01:30 Oracle keeps closing my TAR because I cannot provide a testcase, can you help? » Inside the Oracle Optimizer - Removing the black magic
01:30 Oracle Open World follow up » Inside the Oracle Optimizer - Removing the black magic
01:30 Oracle Open World 2009 Summary » Inside the Oracle Optimizer - Removing the black magic
01:30 Open World Recap and New White papers » Inside the Oracle Optimizer - Removing the black magic
01:30 Maintaining statistics on large partitioned tables » Inside the Oracle Optimizer - Removing the black magic
01:30 Improvement of AUTO sampling statistics gathering feature in Oracle 11g » Inside the Oracle Optimizer - Removing the black magic
01:30 Explain adaptive cursor sharing behavior with cursor_sharing = similar and force. » Inside the Oracle Optimizer - Removing the black magic
01:30 Displaying and reading the execution plans for a SQL statement » Inside the Oracle Optimizer - Removing the black magic
01:30 比 F4 还红的四人组合 » Hey!! Sky!
01:30 在 vi 中替换字符串 » Hey!! Sky!
01:30 口吃、口语、口音 » Hey!! Sky!
01:30 又过生日 » Hey!! Sky!
01:30 exp query 参数的格式 » Hey!! Sky!
01:30 Where Amazing Happens » Hey!! Sky!
01:30 UNIX 的时间和时区 » Hey!! Sky!
01:30 SQL*PLUS 格式化输出 » Hey!! Sky!
01:30 My readings released » Hey!! Sky!
01:30 Getting a Traffic Ticket » Hey!! Sky!
01:30 Flickr 也挂了 » Hey!! Sky!
01:30 11gr2 IGNORE_ROW_ON_DUPKEY_INDEX hint bug » Brotherxiao's Home
01:30 带车进美国 import car from Canada to USA » 木匠 Creative and Flexible
01:30 为啥 Amazon 的 DBA 比较累 worn out » 木匠 Creative and Flexible
01:30 一些扯淡的职业中介 debunk job agent » 木匠 Creative and Flexible
01:30 一个 Development Manager 的自我修养 » 木匠 Creative and Flexible
01:30 people skills on manager 搞好领导关系 » 木匠 Creative and Flexible
01:30 Where to go and what to invest 人员流向和价值投资 » 木匠 Creative and Flexible
01:30 To be a full stack developer » 木匠 Creative and Flexible
01:30 Replace car battery 给汽车换电池 Ford Edge » 木匠 Creative and Flexible
01:30 Looking for renting a room in Seattle/Bellevue » 木匠 Creative and Flexible
01:30 IT role trending 软件行业的职位收入趋势 » 木匠 Creative and Flexible
01:30 Better than t-mobile » 木匠 Creative and Flexible
01:30 2017 个人总结 sum up » 木匠 Creative and Flexible
01:30 You can't trust everything you see... » The Tom Kyte Blog
01:30 When is a foreign key not a foreign key... » The Tom Kyte Blog
01:30 What I learned new about Total Recall... » The Tom Kyte Blog
01:30 Upcoming Events... » The Tom Kyte Blog
01:30 UKOUG 2012... » The Tom Kyte Blog
01:30 The keys to Oracle… » The Tom Kyte Blog
01:30 Raw Devices... » The Tom Kyte Blog
01:30 Oracle OpenWorld 2013, where I'll be... » The Tom Kyte Blog
01:30 Latin America OTN Tour.... » The Tom Kyte Blog
01:30 In Israel... » The Tom Kyte Blog
01:30 Doctors... » The Tom Kyte Blog
01:30 All day optimizer event.... » The Tom Kyte Blog
01:30 12c - Whitelists... » The Tom Kyte Blog
01:30 12c - Utl_Call_Stack... » The Tom Kyte Blog
01:30 12c - SQL Text Expansion » The Tom Kyte Blog
01:30 12c - SQL Plus new things.... » The Tom Kyte Blog
01:30 12c - Multiple same column indexes... » The Tom Kyte Blog
01:30 12c - Invisible Columns... » The Tom Kyte Blog
01:30 12c - Implicit Result Sets... » The Tom Kyte Blog

2018-03-24 Sat