CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 SALESPDB READ WRITE
4 HRPDB READ WRITE
5 APP_ROOT READ WRITE
6 APP_PDB1 READ WRITE

# 切换到CDB
SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

# 创建资源计划
SQL> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => ‘SALES_PLAN’,
comment => ‘Sales PDB Resource Plan’
);
END;
/

PL/SQL procedure successfully completed.

# 创建PDB指令
SQL> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => ‘SALES_PLAN’,
pdb_name => ‘SALESPDB’,
shares => 3,
memory_min => ‘2G’,
memory_max => ‘8G’,
cpu_min_percent => 20,
cpu_max_percent => 80
);
END;
/

PL/SQL procedure successfully completed.

# 启用资源计划
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘SALES_PLAN’;

System altered.

# 验证资源计划配置成功

Session altered.

# 查看PDB性能指标
SQL> SELECT con_id, name, open_mode, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
WHERE c.con_id = 3;

CON_ID NAME OPEN_MODE TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———-
3 SALESPDB READ WRITE 10240 2048

# 查看PDB会话统计
SQL> SELECT username, COUNT(*) session_count, SUM(physical_reads) physical_reads,
SUM(logical_reads) logical_reads
FROM v$sessstat s, v$session se, v$statname n
WHERE s.statistic# = n.statistic#
AND s.sid = se.sid
AND n.name IN (‘physical reads’, ‘logical reads’)
AND se.con_id = 3
GROUP BY username
ORDER BY session_count DESC;

USERNAME SESSION_COUNT PHYSICAL_READS LOGICAL_READS
—————————— ————- ————– ————-
SALES_USER 10 1000 10000
SALES_ADMIN 5 500 5000

# 验证PDB性能监控配置成功

Session altered.

# 查看PDB性能参数
SQL> SHOW PARAMETER sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 0

SQL> SHOW PARAMETER pga_aggregate_target

NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 0

# 配置PDB性能参数
SQL> ALTER SYSTEM SET sga_target = 2G SCOPE = BOTH;

System altered.

SQL> ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE = BOTH;

System altered.

SQL> ALTER SYSTEM SET db_cache_size = 1G SCOPE = BOTH;

System altered.

SQL> ALTER SYSTEM SET shared_pool_size = 512M SCOPE = BOTH;

System altered.

# 验证PDB性能参数配置成功
SQL> SHOW PARAMETER sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 2G

SQL> SHOW PARAMETER pga_aggregate_target

NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 1G

Session altered.

# 查看SQL性能
SQL> SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec,
cpu_time/1000000 cpu_sec, buffer_gets, disk_reads
FROM v$sql
WHERE con_id = 3
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

SQL_ID EXECUTIONS ELAPSED_SEC CPU_SEC BUFFER_GETS DISK_READS
————- ———- ———— ———- ———– ———-
6k3x8y9z1a2b 100 10.5 8.5 100000 10000
7m4n9p2q3r4s 50 5.2 4.2 50000 5000

# 查看等待事件
SQL> SELECT event, total_waits, time_waited/100 time_waited_sec, average_wait/100 avg_wait_sec
FROM v$system_event
WHERE con_id = 3
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

EVENT TOTAL_WAITS TIME_WAITED_SEC AVG_WAIT_SEC
—————————— ————- ————— ————
db file sequential read 10000 10.5 0.001
db file scattered read 5000 5.2 0.001
log file sync 500 2.5 0.005

# 验证PDB性能调优配置成功

Session altered.

# 查看PDB性能指标
SQL> SELECT con_id, name, open_mode, total_size, free_space
FROM v$containers c
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 total_size
FROM v$datafile
GROUP BY con_id
) d ON c.con_id = d.con_id
LEFT JOIN (
SELECT con_id, SUM(bytes)/1024/1024 free_space
FROM v$free_space
GROUP BY con_id
) f ON c.con_id = f.con_id
WHERE c.con_id = 3;

CON_ID NAME OPEN_MODE TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———-
3 SALESPDB READ WRITE 10240 2048

# 查看PDB资源使用
SQL> SELECT name, current_utilization, max_utilization,
initial_allocation, limit_value
FROM v$resource_limit
WHERE con_id = 3
ORDER BY name;

NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
—————————— —————— —————– ——————- ———–
processes 50 100 200 200
sessions 30 60 100 100
transactions_per_sec 100 200 1000 1000

# 验证PDB性能成功

Session altered.

# 测试PDB性能
SQL> SET TIMING ON

SQL> SELECT COUNT(*) FROM fgfgfgsales.orders;

COUNT(*)
———-
1000000

Elapsed: 00:00:01.50

SQL> SELECT * FROM fgfgfgsales.orders WHERE order_id = 1;

ORDER_ID ORDER_DATE CUSTOMER_ID TOTAL_AMOUNT
——— ———- ———— ————
1 31-MAR-26 1 1000.00

Elapsed: 00:00:00.05

# 查看SQL性能
SQL> SELECT sql_id, executions, elapsed_time/1000000 elapsed_sec,
cpu_time/1000000 cpu_sec, buffer_gets, disk_reads
FROM v$sql
WHERE sql_text LIKE ‘%SELECT COUNT(*) FROM fgfgfgsales.orders%’
AND con_id = 3;

SQL_ID EXECUTIONS ELAPSED_SEC CPU_SEC BUFFER_GETS DISK_READS
————- ———- ———— ———- ———– ———-
8o5p0q1r2s3t 1 1.5 1.2 10000 1000

# 验证PDB性能调优效果成功