科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网安全频道Oracle数据库11g:SQL计划管理(二)

Oracle数据库11g:SQL计划管理(二)

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

本文—该系列文章的第二篇—解释在升级一个现存的Oracle10gR2数据库到Oracle11g时如何使用SQL计划管理,与部署新应用程序代码一样,有效地限制SQL语句性能异常回退。

作者:Jim Czuprynski/黄永兵 译 来源:51CTO.com    2008年7月7日

关键字: ORACLE SQL 数据库安全

  • 评论
  • 分享微博
  • 分享邮件

在本页阅读全文(共3页)

  SPM情景#1:

  /*

  || Script: SPM_2_1.sql

  || Purpose: Generate several SQL statements that perform Data

  || Warehouse query loads on an Oracle 10gR2 database

  || for capture into a SQL Tuning Set (STS) that will be

  || transferred to an Oracle 11g database to demonstrate

  || SQL Plan Baseline seeding.

  */

  -----

  -- Query: SPM_2_1.1

  -- Purpose: Summarize sales activity within Country and Promotion

  -----

  SELECT /*SPM_2_1.1*/

  CTY.country_total_id

  ,PR.promo_total_id

  ,COUNT(S.amount_sold)

  ,SUM(S.amount_sold)

  ,SUM(S.quantity_sold)

  FROM

  sh.sales S

  ,sh.customers C

  ,sh.countries CTY

  ,sh.promotions PR

  WHERE S.cust_id = C.cust_id

  AND C.country_id = CTY.country_id

  AND S.promo_id = PR.promo_id

  GROUP BY

  CTY.country_total_id

  ,PR.promo_total_id

  -----

  -- Query: SPM_2_1.2

  -- Purpose: Summarize sales activity within Country and Promotion

  -----

  SELECT /*SPM_2_1.2*/

  CTY.country_id

  ,CTY.country_subregion_id

  ,CTY.country_region_id

  ,CTY.country_total_id

  ,PR.promo_total_id

  ,COUNT(S.amount_sold)

  ,SUM(S.amount_sold)

  ,SUM(S.quantity_sold)

  FROM

  sh.sales S

  ,sh.customers C

  ,sh.countries CTY

  ,sh.promotions PR

  WHERE S.cust_id = C.cust_id

  AND C.country_id = CTY.country_id

  AND S.promo_id = PR.promo_id

  GROUP BY

  CTY.country_id

  ,CTY.country_subregion_id

  ,CTY.country_region_id

  ,CTY.country_total_id

  ,PR.promo_total_id

  -----

  -- Query: SPM_2_1.3

  -- Purpose: Summarize sales activity within Country, Product,

  -- Channel and Promotion

  -----

  SELECT /*SPM_2_1.3*/

  CTY.country_total_id

  ,P.prod_id

  ,P.prod_subcategory_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  ,COUNT(S.amount_sold)

  ,SUM(S.amount_sold)

  ,SUM(S.quantity_sold)

  FROM

  sh.sales S

  ,sh.customers C

  ,sh.countries CTY

  ,sh.products P

  ,sh.channels CH

  ,sh.promotions PR

  WHERE S.cust_id = C.cust_id

  AND C.country_id = CTY.country_id

  AND S.prod_id = P.prod_id

  AND S.channel_id = CH.channel_id

  AND S.promo_id = PR.promo_id

  GROUP BY

  CTY.country_total_id

  ,P.prod_id

  ,P.prod_subcategory_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  -----

  -- Query: SPM_2_1.4

  -- Purpose: Summarize sales activity within Country, Product,

  -- Channel and Promotion

  -----

  SELECT /*SPM_2_1.4*/

  CTY.country_total_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  ,COUNT(S.amount_sold)

  ,SUM(S.amount_sold)

  ,SUM(S.quantity_sold)

  FROM

  sh.sales S

  ,sh.customers C

  ,sh.countries CTY

  ,sh.products P

  ,sh.channels CH

  ,sh.promotions PR

  WHERE S.cust_id = C.cust_id

  AND C.country_id = CTY.country_id

  AND S.prod_id = P.prod_id

  AND S.channel_id = CH.channel_id

  AND S.promo_id = PR.promo_id

  GROUP BY

  CTY.country_total_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  -----

  -- Query: SPM_2_1.5

  -- Purpose: Summarize sales activity within Country, Product,

  -- Channel and Promotion

  -----

  SELECT /*SPM_2_1.5*/

  CTY.country_id

  ,CTY.country_subregion_id

  ,CTY.country_region_id

  ,CTY.country_total_id

  ,P.prod_id

  ,P.prod_subcategory_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  ,COUNT(S.amount_sold)

  ,SUM(S.amount_sold)

  ,SUM(S.quantity_sold)

  FROM

  sh.sales S

  ,sh.customers C

  ,sh.countries CTY

  ,sh.products P

  ,sh.channels CH

  ,sh.promotions PR

  WHERE S.cust_id = C.cust_id

  AND C.country_id = CTY.country_id

  AND S.prod_id = P.prod_id

  AND S.channel_id = CH.channel_id

  AND S.promo_id = PR.promo_id

  GROUP BY

  CTY.country_id

  ,CTY.country_subregion_id

  ,CTY.country_region_id

  ,CTY.country_total_id

  ,P.prod_id

  ,P.prod_subcategory_id

  ,P.prod_category_id

  ,P.prod_total_id

  ,CH.channel_id

  ,CH.channel_class_id

  ,CH.channel_total_id

  ,PR.promo_total_id

  SPM情景#2:

  /*

  || SFA_Queries.sql

  ||

  || Various queries to produce workload against simulated new

  || Sales Force Administration application

  ||

  */

  -----

  -- Set up and initialize bind variables

  -----

  VARIABLE rgn_abbr VARCHAR2(4);

  VARIABLE rgn_desc VARCHAR2(40);

  VARIABLE cust_id NUMBER;

  BEGIN

  :rgn_abbr := 'NE00';

  :rgn_desc := 'South%';

  :cust_id := 9090;

  END;

  /

  -----

  -- Query: SPM_2.2.1

  -- Purpose: Summarize quantity sold and revenue within U.S. States

  -----

  SELECT /*SPM_2.2.1*/

  C.cust_state_province

  ,SUM(sh.quantity_sold)

  ,SUM(sh.amount_sold)

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章