在Oracle10g之后，提供了DBMS_ADVANCED_REWRITE包，具有强大的查询重写功能，可以让我们在数据库层面实现很多微妙的调整。假设我们有一个应用，但是现在无法直接修改应用程序的编码，但是又想能够让应用程序的某些SQL产生我们想要的变化，那么就可以使用DBMS_ADVANCED_REWRITE包。

drop table t;
create table t as select object_id,object_name from dba_objects;
drop table t1;
create table t1 as select object_id,object_name from dba_objects where 1=0;

SQL> select count(*) from t;

  COUNT(*)
----------
     16636

SQL> select count(*) from t1;

  COUNT(*)
----------
         0

现在我们有表T和T1，表结构相同，但是表T中有1.6万记录，而表T1中没有记录，如果说我们的应用中有一个SQL多次地查询表T的总记录数，占用了大量的CPU和逻辑读，而这样的count记录数又是完全没有用处的，但是我们无法修改应用程序去掉这个SQL，那么我们就可以通过DBMS_ADVANCED_REWRITE包来讲查询表T的SQL转变为查询表T1，这样就大大减少了这条SQL的逻辑读。

首先DBMS_ADVANCED_REWRITE包的执行权限必须显式赋给需要的用户。

CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO kamus;

CONN kamus/password

BEGIN
  SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
     name             => 't_rewrite',
     source_stmt      => 'SELECT count(*) FROM t',
     destination_stmt => 'SELECT count(*) FROM t1',
     validate         => FALSE,
     rewrite_mode     => 'TEXT_MATCH');
END;
/

然后需要设置会话层面的QUERY_REWRITE_INTEGRITY参数，该参数默认值为ENFORCED，表示只有重写后的SQL输出结果跟原结果完全一样时，查询才会被真正重写，在这里需要修改为TRUSTED。

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
SQL> set autot on
SQL> select count(*) from t;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
Plan hash value: 238181912

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  VIEW               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |      |     1 |       |            |          |
|   3 |    TABLE ACCESS FULL| T1   |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;
SQL> select count(*) from t;

  COUNT(*)
----------
     16636

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 16639 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         69  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

可以看到在重写之后，执行计划中显示直接去查询表T1，而consistent gets也从查询表T需要的69减少为3。

可以从[USER|ALL|DBA]_REWRITE_EQUIVALENCES视图中获得查询重写的信息。

SQL> select * from user_rewrite_equivalences;

OWNER  NAME       SOURCE_STMT               DESTINATION_STMT          REWRITE_MO
------ ---------- ------------------------- ------------------------- ----------
KAMUS  T_REWRITE  SELECT count(*) FROM t    SELECT count(*) FROM t1   TEXT_MATCH

DBMS_ADVANCED_REWRITE包也有限制，不可以重写牵涉到SYS用户对象的SQL。

drop table t1;
create table t1 as select * from all_tables where 1=0;

SQL> BEGIN
  2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
  3       name             => 't_rewrite',
  4       source_stmt      => 'SELECT count(*) FROM all_tables',
  5       destination_stmt => 'SELECT count(*) FROM t1',
  6       validate         => FALSE,
  7       rewrite_mode     => 'TEXT_MATCH');
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-30354: Query rewrite not allowed on SYS relations
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

在测试中，如果destination_stmt中包含SYS用户对象，是可以成功创建查询重写的，但是在执行SQL的时候却会报ORA-03113错误，无法正常执行。

SQL> BEGIN
  2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
  3       name             => 't_rewrite',
  4       source_stmt      => 'SELECT count(*) FROM t1',
  5       destination_stmt => 'SELECT count(*) FROM all_tables',
  6       validate         => FALSE,
  7       rewrite_mode     => 'TEXT_MATCH');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from user_rewrite_equivalences;

OWNER  NAME       SOURCE_STMT               DESTINATION_STMT          REWRITE_MO
------ ---------- ------------------------- ------------------------- ----------
KAMUS  T_REWRITE  SELECT count(*) FROM t1   SELECT count(*) FROM all_ TEXT_MATCH
                                            tables

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered.

SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8360
Session ID: 135 Serial number: 22

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:

• Denormalization
  
• Eventual Consistency
  
• Schema-Free
  
• Horizontal Scale
  

NoSQL tries to scale (more) simply, it is starting to go mainstream – NY Times, BBC, SourceForge, Digg, Sony, ShopWiki, Meebo, and more. But it’s not *entirely* mainstream, it’s still hard to sell due to compliance and other reasons.

NoSQL has gotten very popular, lots of blog posts about them, but they reach this hype peak and obviously it can’t do everything.

“NoSQL is a (growing) collection of tools, not a new way of life.”

What is NoSQL? Can be several things:

• Key-Value Stores
  
• Document Databases
  
• Column-oriented data stores
  
• Graph Databases
  

Key-Value Stores

Speaker’s favorite is Redis because it’s similar to memcached.

• key-value store + datatypes (list, sets, scored sets, soon hashes will be there)
  
• cache-like functions (like expiration)
  
• (Mostly) in-memory
  

Another interesting key-value store is Riak

• Combination of key-value store and document database
  
• heavy into HTTP REST
  
• You can create links between documents, and do “link walking” that you don’t normally get out of a key-value store
  
• built-in Map Reduce
  

Map Reduce:

• Massively parallel way to process large datasets
  
• First you scour data and “map” a new set of dataM
  
• Then you “reduce” the data down to a salient result — for example, map reduce function to make a tag cloud: map function makes an array with a tag name and a count of 1 for each instance of that tag, and the reduce tag goes through that array and counts them…
  
• http://en.wikipedia.org/wiki/MapReduce
  

Other key-value stores:

• Tokyo Cabinet
  
• Dynomite
  
• memcachedDB
  
• Voldemort
  

Document Databases

• MongoDB – Document store that speaks BSON (Binary JSON, which is compact). This is the speaker’s favorite because it has a rich query syntax that makes it close to SQL. Can’t do joins, but can embed objects in other objects, so it’s a tradeoff


• Also has GridFS that can store large files efficiently, can scale to petabytes of data
  
• does have MapReduce but it’s deliberate and you run it every so often.
  
• CouchDB
  
• Pure JSON Document Store – can query directly with nearly pure javascript (there are auth issues) but it’s an interesting paradigm to be able to run your app almost entirely through javascript.
  
• HTTP REST interface
  
• MapReduce only to see items in CouchDB. Incremental MapReduce, every time you add or modify a document, it dynamically changes the functions you’ve written. You can do really powerful queries as easy as you can do simple queries. However, some things are really complex, ie, pagination is almost impossible to do.
  
• Intelligent Replication – CouchDB is designed to work with offline integration. Could be used instead of SQLite as the HTML5 data store, but you need CouchDB running locally to be doing offline stuff w/CouchDB
  

Column-oriented store

• Cassandra – Built by Facebook, also used by Twitter
  
• BigTable
  
• Hypertable
  
• HBase
  

Graph Databases

When should I use this stuff?

Don’t look for a single tool that does every job. Use more than one if it’s appropriate, weigh the tradeoffs (ie, don’t have 7 different data stores either!)

NoSQL solves real scalability and data design issues. But financial transactions HAVE to be atomic, so don’t use NoSQL for those.

A good presentation is http://www.slideshare.net/bscofield/the-state-of-nosql.

Using SQL and NoSQL together

You can blend by hand, but the easy way is DataMapper:
Generic, relational ORM (adapters for many SQL dbs and many NoSQL stores)
Implements Identity Map
Module-based inclusion (instead of extending from a class, you just include into a class).

You can set up multiple data targets (default is MySQL, example sets up MongoDB too).
DataMapper is:

• Ultimate Polyglot ORM
  
• simple r’ships btween persistence engines are easy
  
• jack of all, master none
  
• Sometimes perpetuates false assumptions –
  
• If you’re in Ruby, your legacy stuff is in ActiveRecord, so you’re going to have to rewrite your code anyway.
  

Speaker’s idea to be less generic and better use of features of each data store – Gloo – “Gloo glues together different ORMs by providing relationship proxies.” this software is ALPHA ALPHA ALPHA.

The goal is to be able to define relationships on the terms of any ORM from any class, ORM or not
Right now – partially working activeRecord relationships
Is he doing it wrong? Is it a crazy/stupid idea? Maybe.

Example:

He wrote the example in about 3 hours, so integration of multiple data stores can be done quickly and work.

Most of this stuff is not PHP specific, and Python or Ruby or Java or .NET developers can use the tools in this talk.

The session on joind.in, with user comments/feedback, is at http://joind.in/talk/view/1320.

Slides are at http://talks.php.net/show/confoo10

“My name is Rasmus, I’ve been around for a long time. I’ve been doing this web stuff since 1992/1993.”

“Generally performance is not a PHP problem.” Webservers not config’d, no expire headers on images, no favicon.

Tools: Firefox/Firebug extension called YSlow (developed by yahoo) gives you a grade on your site.

Google has developed the Firefox/Firebug pagespeed tool.

Today Rasmus will pick on wordpress. He checks out the code, then uses Siege to do a baseline benchmark — see the slide for the results.

Before you do anything else install an opcode cache like APC. Wordpress really likes this type of caching, see this slide for the results. Set the timezone, to make sure conversions aren’t being done all the time.

Make sure you are cpu-bound, NOT I/O bound. Otherwise, speed up the I/O.

Then strace your webserver processs. There are common config issues that you can spot in your strace code. grep for ENOENT which shows you “No such file or directory” errors.

AllowOverride None to turn off .htaccess for every directory, just read settings once from your config file….(unless you’re an ISP).

Make sure DirectoryIndex is set appropriately, watch your include_path. All this low-hanging fruit has examples on the common config issues slide.

Install pecl/inclued and generate a graph – here is the graph image (I have linked it because you really want to zoom in to the graph…)

In strace output check the open() calls. Conditional includes, function calls that include files, etc. need runtime context before knowing what to open. In the example, every request checks to see if we have the config file, once we have config’d we can get rid of that stuff. Get rid of all the conditionals and hard-code “include wp-config.php”. Examples are on the slide.

His tips to change:
Conditional config include in wp-load.php (as just mentioned)
Conditional did-header check in wp-blog-header.php
Don’t call require_wp_db() from wp-settings.php
Remove conditional require logic from wp_start_object_cache

Then check strace again, now all Rasmus sees is theming and translations, which he decided to keep, because that’s the good benefit of Wordpress – Performance is all about costs vs. flexibility. You don’t want to get rid of all of your flexibility, but you want to be fast.

Set error_reporting(-1) in wp-settings.php to catch all warnings — warnings slow you down, so get rid of all errors. PHP error handling is very slow, so getting rid of errors will make you faster.

The slide of warnings that wordpress throws.

Look at all C-level calls made, using callgrind, which sits under valgrind, a CPU emulator used for debugging. See the image of what callgrind shows.

Now dive into the PHP executor, by installing XDebug.

Check xhprof – Facebook open sourced this about a year ago, it’s a PECL extension. The output is pretty cool, try it on your own site, Rasmus does show you how to use it. It shows you functions sorted by the most expensive to the least expensive.

For example, use $_SERVER[REQUEST_TIME] instead of time(). Use pconnect() if MySQL can handle the amount of webserver connections that will be persistent, etc.

After you have changed a lot of the stuff above, benchmark again with siege to see how much faster you are. In this case there is not much gained so far.

So keep going….the blogroll is very slow — Rasmus gets rid of it by commenting out in the sidebar.php file. I’d like to see something to make it “semi-dynamic” — that is, make it a static file that can be re-generated, since you might want the blogroll but links are not changed every second…..

At this point we’re out of low-hanging fruit.

HipHop is a PHP to C++ converter & compiler, including a threaded, event-driven server that replaces apache. Rasmus’ slide says “Wordpress is well-suited for HipHop because it doesn’t have a lot of dynamic runtime code. This is using the standard Wordpress-svn checkout with a few tweaks.”

Then, of course, benchmark again.

The first time you compile Wordpress with HipHop, you give it a list of files to add to the binary, it will complain about php code that generate file names, so you do have to fix that kind of stuff. There’s a huge mess of errors the first time you run it (”pages and pages”), and Rasmus had to patch HipHop (and Wordpress) but the changes in HipHop have been put back into HipHop, so you should be good for the most part.

Check out the errors, lots of them show logical errors like $foo.”bar” instead of $foo.=”bar” and $foo=”bar” instead of $foo==”bar” in an if statement. Which of course is nice for your own code, to find those logical errors.

(Wordpress takes in a $user_ID argument and immediately initializes a global $user_ID variable, which overwrites the argument passed in, so you can change the name of the argument passed in….)

You can also get rid of some code, things that check for existence of the same thing more than once. So it will take a bit of tweaking, but it’s worth it.

There are limitations to HipHop, for example:

• It doesn’t support any of the new PHP 5.3 language features
  
• Private properties don’t really exist under HipHop. They are treated as if they are protected instead.
  
• You can’t unset variables. unset will clear the variable, but it will still be in the symbol table.
  
• eval and create_function are limited
  
• Variable variables $$var are not supported
  
• Dynamic defines won’t work: define($name,$value)
  
• get_loaded_extensions(), get_extension_funcs(), phpinfo(), debug_backtrace() don’t work
  
• Conditional and dynamically created include filenames don’t work as you might expect
  
• Default unix-domain socket filename isn’t set for MySQL so connecting to localhost doesn’t work

and HipHop does not support all extensions — see the list Rasmus has of extensions HipHop supports.

Then Rasmus showed an example using Twit (which he wrote) including the benchmarks. He shows that you can see what’s going on, like 5 MySQL calls on the home page and what happens when you don’t have a favicon.ico (in yellow).

In summary, “performance is all about architecture”, “know your costs”.

Be careful, because some tools (like valgrind and xdebug) you don’t want to put it on production systems, you could capture production traffic and replay it on a dev/testing box, but “you just have to minimize the differences and do your best”.

2010年3月11日，为了提高人民群众的安全防范意识，减少生命财产损失，公安部和辽宁省公安厅主办的国内第一个全方位、多角度宣传公共安全防范知识的专业门户网站——“365安全防范网”在北京正式开通。

而这一公共安全宣传网站的首次亮相，便与亚洲最大零售商圈淘宝网以及国内最大的第三方支付平台支付宝携手宣布，三方联合打造的“网络交易安全信息平台”正式上线。该平台旨在为公众提供权威的网络交易安全防范信息，提高公众安全防范意识，打击各类网络欺诈行为，从而净化整体的网络环境。

公安部宣传局副局长刘世斌在会上表示，本次三方合作共建创新了安全教育的新形式，感谢淘宝网、支付宝的真诚合作，今后将加强与淘宝网、支付宝的合作，不断拓宽公共安全防范宣传的领域，共同创造一个更加美好、更加安全的社会环境。

据悉，该平台内容将主要由淘宝网以及支付宝提供给365安防网，经审核后在该信息平台上予以发布。此外，三方还制定了相关安全防范信息的互通、共享的联动机制，定期交流、交换相关经验，共同推进网络安全教育。目前首批网络交易安全防范信息已经发布，随后将陆续更新。

淘宝网副总裁路鹏在会上表示，经过多年总结积累的网络交易安全经验与策略已经成为淘宝网强大竞争力的一部分，在打击网络交易欺诈中发挥了重要作用。这部分安全经验能够通过365安防网对全社会开放、共享，从而为产业的良性发展、网民的交易安全发挥更大的价值，是淘宝网作为企业公民应尽的社会责任之一。

支付宝总裁邵晓锋则表示，当前针对电子支付用户的网络安全威胁日趋严重，由于安全意识不高以及使用方式不当使得相关案件时有发生。各界均希望有一个权威的安全教育网站加强安全教育。他表示，参与365安防网及网络交易安全防范信息平台的建设，是支付宝为用户、行业、全社会承担更多责任的表现，这一计划对整个互联网经济的发展起到保障与促进作用。

与会的公安部经济犯罪侦查局副局长韩浩和公安部网络安全保卫局副巡视员邓宏敏也对淘宝网、支付宝多年来的做法给予了高度评价。

互联网业内人士则认为，落后的用户安全教育已经成为网络整体安全的短板，有识之士应积极转变思路，加强安全教育工作。该人士对于该平台的开设表示欢迎，“这是行业领导者首次积极参与公安部的公共安全防范宣传工作，在推动安全教育、打击网络非法行为、净化网络环境有重要的推动作用。”

No related posts.

今天有个兄弟要删除不想用的temporary tablespace,,我大概整理了下面几点意见..

create temporary tablespace tempyyy tempfile 'path_to_tempfile_yyy' size xxx;
alter user zzz temporary tablespace tempyyy;
alter database default temporary tablespace tempyyy;

select username,temporary_tablespace from dba_users where temporary_tablespace = 'tempxxx';

select username,session_num,tablespace from v$sort_usage where tablespace = 'tempxxx';

alter database tempfile 'tempfilexxx' drop;

4. 再删除这个临时表空间.

drop tablespace tempxxx;

No related posts.