正文共： 10865字 0图
预计阅读时间： 28分钟
接上文：
《Oracle Sharding Step by Step (1)》
《Oracle Sharding Step by Step (2)》
《Oracle Sharding Step by Step (3)》
《Oracle Sharding Step by Step (4)》
《Oracle Sharding Step by Step (5)》
《Oracle Sharding Step by Step (6)》
《Oracle Sharding Step by Step (7)》
实验5：自定义数据加载应用程序
下载SDB演示应用程序zip文件
要了解有关Oracle Sharded Databases的更多信息，请下载并部署系统管理的SDB演示应用程序。 演示应用程序使用您刚刚创建的SDB环境和架构来模拟在线零售商店的工作负载。 您可以从Master Note for Handling Oracle Sharding - Oracle Database 12.2 Technology（文档ID 2226341.1）中下载最新版本的演示应用程序，以及介绍如何运行和监视它的自述文件。
从下载sdb_demo_app.zip
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2226341.1
将zip文件复制到shard0上的oracle的HOME目录下。 （在这个实验中，我们正在运行sdb0上的演示程序。）
$ cd $HOME
$ . ./catadb.sh
[oracle@sdb0 ~]$ cd scripts
[oracle@sdb0 scripts]$ pwd
/home/oracle/scripts
[oracle@sdb0 scripts]$ ls
sdb_demo_app.zip
$ unzip sdb_demo_app.zip
这将在/home/oracle/scripts下创建sdb_demo_app目录。
设置并配置Sharding演示应用程序
有关演示应用程序和监视工具的设置和配置的信息，请参阅README_SDB_Demo_Application文档（在sdb_demo_app目录中）。
将demo.properties文件修改为以下内容。
name=demo
connect_string=(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(HOST=sdb0)(PORT=1571)(PROTOCOL=tcp)))
monitor.user=dbmonuser
monitor.pass=TEZiPP4MsLLL
#app.service.write=oltp_rw_srvc.cust_sdb.oradbcloud
app.service.write=oltp_rw_srvc.orasdb.oradbcloud
#app.service.readonly=oltp_ro_srvc.cust_sdb.oradbcloud
app.service.readonly=oltp_rw_srvc.orasdb.oradbcloud
app.user=app_schema
app.pass=app_schema
app.threads=7
实验6：跨分片查询
这个动手实验的目的是在一个分片数据库中执行各种交叉分片查询。
注意：所有练习都在分片目录数据库上执行。另外，工作簿中使用的数据样本与您的环境不同，因此您将观察到的输出将会不同（由于数据加载随机化）。
在分片数据库中，数据库客户端使用具有分片键值的连接字符串连接到分片。如果指定了分片密钥，那么在该会话中提交的所有请求将被路由到对应于密钥值的分片。它们被称为单个分片查询（SSQ）。
如果分片密钥不能作为数据库连接字符串的一部分提供，则必须在协调器数据库（catadb）上建立一个会话。从这些会话提交的所有查询原则上都可以触发任何一组分片数据库中的数据。他们被称为交叉碎片查询（CSQ）。
在较高层次上，协调器将每个传入查询Q重写为由两个查询CQ和SQ组成的分配形式，其中SQ（Shard Query）是在每个参与分片上执行的Q的部分并且CQ（协调器查询）是在协调器分片上执行的部分。正式来说：
Q => CQ ( Shard_Iterator( SQ ) )
以下是重写入Q1的聚合查询Q1的一个示例，用于执行分段间执行：
Q1：SELECT COUNT(*) FROM customers
Q1'：SELECT SUM(sc) FROM (Shard_Iterator(SELECT COUNT(*) sc FROM s1))
在这个过程中有两个关键要素：（1）识别相关的分片（也称为参与分片）;（2）将查询重写为分布形式;以及碎片迭代。
在编译协调器数据库的查询期间，查询优化器分析分片键上的谓词并提取可用于标识参与分片的分片，即为查询中引用的分片表贡献行的分片。其他分片被称为修剪分片。在只有一个参与分片被识别的情况下，完整查询被路由到该分片以完全执行，否则查询被重写。重写过程考虑了由查询计算的表达式以及查询形状。本实验室提供的示例将帮助说明重写过程以及参与分片的识别。
从SHARDCAT终端，使用SQL*Plus连接到shardcat数据库：
$ cd home/oracle
$ . ./catadb.sh
$env | grep ORA
ORACLE_SID=catadb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
$sqlplus app_schema/app_schema
运行CSQ查询以将一些示例行加载到表中。
SQL>set termout on
SQL>set linesize 120
SQL>set echo on
SQL>REM
SQL>REM Conventional Insert
SQL>REM
SQL>
SQL>INSERT INTO Customers (CustId, FirstName, LastName, CustProfile, Class, Geo,Passwd) VALUES ('Scott.Tiger@x.bogus', 'Scott', 'Tiger', NULL, 'free', 'west',hextoraw('7d1b00f'));
1 row created.
SQL>
SQL>INSERT INTO Customers (CustId, FirstName, LastName, CustProfile, Class, Geo,Passwd) VALUES ('Mary.Parker@x.bogus', 'Mary', 'Parker', NULL, 'Gold', 'east',hextoraw('8d1c00e'));
1 row created.
SQL>commit;
Commit complete.
现在，让我们运行一个CSQ查询，它使用ORDER BY查询来访问多个碎片，但不是所有的碎片
SQL>set termout on
SQL>set linesize 120 pagesize 200
SQL>set echo on
SQL>column firstname format a20
SQL>column lastname format a20
SQL>REM SELECT with ORDER BY query accessingmultiple shards but not all shards
SQL>explain plan for SELECT FirstName,LastName, geo, class FROM Customers
WHERE CustId in ('Scott.Tiger@x.bogus', 'Mary.Parker@x.bogus') AND class != 'free'ORDER BY geo, class;
Explained.
SQL>set echo off
SQL>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Planhash value: 1622328711
-------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7700 | 1 (100)| 00:00:01| | |
| 1 | SORT ORDER BY | | 100 | 7700 | 1 (100)| 00:00:01| | |
| 2 | VIEW |VW_SHARD_5B3ACD5D | 100 | 7700 | 5 (100)| 00:00:01 | | |
| 3 | SHARD ITERATOR | | | | | | | |
| 4 | REMOTE | | | | | | ORA_S~ | R->S |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN INTO PLAN_TABLE@! FORSELECT
"A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS"FROM "CUSTOMERS" "A1" WHERE
("A1"."CUSTID"='Mary.Parker@x.bogus' OR"A1"."CUSTID"='Scott.Tiger@x.bogus') AND
"A1"."CLASS"<>'free'/* coord_sql_id=gq42axzj3ns5t */ (accessing
'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
21 rows selected.
SQL>REM SELECT with ORDER BY query accessingmultiple shards but not all shards
SQL>REM
SQL>SELECT FirstName,LastName, geo, class FROM Customers
WHERE CustId in ('Scott.Tiger@x.bogus', 'Mary.Parker@x.bogus') AND class != 'free'ORDER BY geo, class;
FIRSTNAME LASTNAME GEO CLASS
---------------------------------------- -------- ----------
Mary Parker east Gold
让我们运行一个连接分片和复制表（加入非分片键）的CSQ查询来获得快速移动的产品（数量> 10）
SQL>set echo on
SQL>column name format a40
SQL>REM Join sharded and duplicated table (join on non sharding key) to get thefast moving products (qty sold > 10)
SQL>explain plan for SELECT name, SUM(qty) qtysold FROM lineitems l, products p
WHERE l.productid = p.productid
GROUP BY name HAVING sum(qty) > 10 ORDER BY qtysold desc;
Explained.
SQL>set echo off
SQL>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2127005259
---------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7900 | 5 (100)| 00:00:01| | |
| 1 | SORT ORDER BY | | 100 | 7900 | 5 (100)| 00:00:01| | |
|* 2 | FILTER | | | | | | | |
| 3 | HASH GROUP BY | | 100 | 7900 | 5 (100)| 00:00:01| | |
| 4 | VIEW | VW_SHARD_372F2D25| 100 | 7900 | 5 (100)| 00:00:01| | |
| 5 | SHARD ITERATOR | | | | | | | |
| 6 | REMOTE | | | | | | ORA_S~ | R->S |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("ITEM_1")>10)
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - EXPLAIN PLAN INTO PLAN_TABLE@! FORSELECT SUM("A2"."QTY"),"A1"."NAME" FROM "LINEITEMS"
"A2","PRODUCTS""A1" WHERE "A2"."PRODUCTID"="A1"."PRODUCTID"GROUP BY "A1"."NAME" *
coord_sql_id=afsk93wt1gxz9 */ (accessing 'ORA_SHARD_POOL@ORA_MULTI_TARGET')
26rows selected.
SQL>REM Join sharded and duplicated table (join on non sharding key) to get thefast moving products (qty sold > 10)
SQL>SELECT name, SUM(qty) qtysold FROM lineitems l, products p
WHERE l.productid = p.productid
GROUP BY name HAVING sum(qty) > 10 ORDER BY qtysold desc;
NAME QTYSOLD
--------------------------------------------------
Starter motor 271
Fastener 242
Distributor 232
Engine block 221
Master cylinder 217
让我们运行一个运行IN子查询的CSQ查询来获取包含价格> 999499的产品的＃订单。
SQL>set echo on
SQL>column name format a20
SQL>REM IN subquery to get # orders thatincludes product with price > 999499.
SQL>explain plan for SELECT COUNT(orderid) FROM orders o
WHERE orderid IN (SELECT orderid FROM lineitems l, products p
WHERE l.productid = p.productid AND o.custid= l.custid AND p.lastprice > 999499);
Explained.
SQL>set echo off
SQL>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2403723386
-------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (100)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | VIEW | VW_SHARD_72AE2D8F| 100 | 1300 | 5 (100)| 00:00:01| | |
| 3 | SHARD ITERATOR | | | | | | | |
| 4 | REMOTE | | | | | | ORA_S~ | R->S |
-------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN INTO PLAN_TABLE@! FORSELECT COUNT(*) FROM "ORDERS" "A1" WHERE
"A1"."ORDERID"=ANY(SELECT "A3"."ORDERID" FROM "LINEITEMS""A3","PRODUCTS" "A2" WHERE
"A3"."PRODUCTID"="A2"."PRODUCTID"AND "A1"."CUSTID"="A3"."CUSTID" AND "A2"."LASTPRICE">999499)
/*coord_sql_id=d3v9w2k0dxcbn */ (accessing'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
20 rows selected.
SQL>REM IN subquery to get # orders thatincludes product with price > 999499.
SQL>SELECT COUNT(orderid) FROM orders o
WHERE orderid IN (SELECT orderid FROM lineitems l, products p
WHERE l.productid = p.productid ANDo.custid = l.custid AND p.lastprice > 999499);
COUNT(ORDERID)
--------------
170
让我们运行一个CSQ查询，根据放置的订单数计算客户分布。
SQL>set echo on
SQL>REM Customer Distribution Query
SQL>explain plan for SELECT ordercount, COUNT(*) as custdist
FROM (SELECT c.custid, COUNT(orderid) ordercount
FROM customers c LEFT OUTER JOIN orders o
ONc.custid = o.custid AND
orderdate BETWEEN sysdate-4 AND sysdateGROUP BY c.custid)
GROUP BY ordercount
ORDER BY custdist desc, ordercount desc;
Explained.
SQL>set echo off
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1140215033
--------------------------------------------------------------------------------------------------------
|Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2600 | 5 (100)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 100 | 2600 | 5 (100)| 00:00:01 | | |
| 2 | HASH GROUP BY | | 100 | 2600 | 5 (100)| 00:00:01 | | |
| 3 | VIEW | VW_SHARD_DB5A5BE0 | 100 | 2600 | 5 (100)| 00:00:01 | | |
| 4 | SHARD ITERATOR | | | | | | | |
| 5 | REMOTE | | | | | | ORA_S~ | R->S |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - EXPLAIN PLAN INTO PLAN_TABLE@! FORSELECT COUNT(*),"A1"."ORDERCOUNT" FROM (SELECT
"A3"."CUSTID""CUSTID",COUNT("A2"."ORDERID")"ORDERCOUNT" FROM "CUSTOMERS""A3","ORDERS" "A2"
WHERE"A3"."CUSTID"="A2"."CUSTID"(+) AND"A2"."ORDERDATE"(+)>=CAST(SYSDATE@!-4 AS TIMESTAMP)
AND"A2"."ORDERDATE"(+)<=CAST(SYSDATE@! AS TIMESTAMP) GROUPBY "A3"."CUSTID") "A1" GROUP BY
"A1"."ORDERCOUNT" /*coord_sql_id=45wf75sj0dpu7 */ (accessing
'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
23 rows selected.
SQL>REM Customer Distribution Query
SQL>SELECT ordercount, COUNT(*) as custdist
FROM (SELECT c.custid, COUNT(orderid) ordercount
FROM customers c LEFT OUTER JOIN orders o
ON c.custid = o.custid AND
orderdate BETWEEN sysdate-4 AND sysdateGROUP BY c.custid)
GROUP BY ordercount
ORDER BY custdist desc, ordercount desc;
ORDERCOUNT CUSTDIST
--------------------
1 10364
2 3433
3 1146
4 379
5 116
6 43
7 16
0 10
8 4
10 3
11 2
9 2
16 1
13 1
