# 示例：分析SQL语句
SQL> SELECT sql_id, sql_text, elapsed_time FROM v$sql WHERE elapsed_time > 1000000 ORDER BY elapsed_time DESC;

# 示例：查看执行计划
SQL> EXPLAIN PLAN FOR SELECT * FROM scott.emp WHERE deptno = 10;
SQL> SELECT * FROM TABLE(dbms_xplan.display);

# 示例：优化SQL语句
— 原SQL
SELECT * FROM scott.emp WHERE deptno = 10;

— 优化后SQL
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM scott.emp
WHERE deptno = 10;

# 示例：创建索引
SQL> CREATE INDEX emp_deptno_idx ON scott.emp(deptno);

# 示例：使用绑定变量
SQL> DECLARE
v_deptno NUMBER := 10;
BEGIN
FOR rec IN (SELECT * FROM scott.emp WHERE deptno = v_deptno)
LOOP
DBMS_OUTPUT.PUT_LINE(rec.ename);
END LOOP;
END;
/

# 示例：避免全表扫描
SQL> SELECT * FROM scott.emp WHERE empno = 7369;
— 使用主键索引，避免全表扫描

# 示例：使用并行执行
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> INSERT /*+ PARALLEL(emp, 4) */ INTO scott.emp SELECT * FROM scott.emp;

# 示例：分析索引使用情况
SQL> SELECT index_name, table_name, uniqueness, status FROM dba_indexes WHERE table_name = ‘EMP’;

# 示例：创建B树索引
SQL> CREATE INDEX emp_ename_idx ON scott.emp(ename);

# 示例：创建位图索引
SQL> CREATE BITMAP INDEX emp_deptno_bmp_idx ON scott.emp(deptno);

# 示例：创建复合索引
SQL> CREATE INDEX emp_deptno_ename_idx ON scott.emp(deptno, ename);

# 示例：创建函数索引
SQL> CREATE INDEX emp_upper_ename_idx ON scott.emp(UPPER(ename));

# 示例：重建索引
SQL> ALTER INDEX emp_deptno_idx REBUILD;

# 示例：收集索引统计信息
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(‘SCOTT’, ‘EMP_DEPTNO_IDX’);

# 示例：删除未使用的索引
SQL> DROP INDEX scott.emp_ename_idx;

# 示例：调整SGA大小
SQL> ALTER SYSTEM SET sga_target = ’16G’ SCOPE=SPFILE;

# 示例：调整PGA大小
SQL> ALTER SYSTEM SET pga_aggregate_target = ‘4G’ SCOPE=SPFILE;

# 示例：优化内存参数
SQL> ALTER SYSTEM SET shared_pool_size = ‘4G’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_cache_size = ‘8G’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET large_pool_size = ‘512M’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET java_pool_size = ‘512M’ SCOPE=SPFILE;

# 示例：监控内存使用
SQL> SELECT * FROM v$sga;
SQL> SELECT * FROM v$pga_target_advice;
SQL> SELECT * FROM v$sgastat WHERE name LIKE ‘%free%’;

# 示例：使用ASM
SQL> SELECT name, state FROM v$asm_diskgroup;

# 示例：优化数据文件布局
SQL> ALTER TABLESPACE users ADD DATAFILE ‘+DATA’ SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;

# 示例：使用分区表
SQL> CREATE TABLE fgfgfgsales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(‘2026-07-01’, ‘YYYY-MM-DD’)),
PARTITION p4 VALUES LESS THAN (TO_DATE(‘2026-10-01’, ‘YYYY-MM-DD’)),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

# 示例：监控存储使用
SQL> SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS used_gb,
SUM(maxbytes)/1024/1024/1024 AS max_gb
FROM dba_data_files
GROUP BY tablespace_name;

# 示例：调整内核参数（Linux）
$ cat /etc/sysctl.conf
# Oracle recommended kernel parameters
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# 示例：优化文件系统
$ mount -t ext4 -o noatime,nodiratime /dev/sda1 /oracle

# 示例：配置网络参数
$ cat /etc/sysctl.conf
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15

# 示例：监控系统资源
$ top
$ vmstat 1
$ iostat -x 1
$ free -h