oracle常用SQL（上）

零. SQL和程序优化

1. SQL优化

1.1 便利性SQl

--- 通过SQL创建随机数序列,   日期自增序列
SELECT DBMS_RANDOM.VALUE
  FROM DUAL 
  CONNECT BY LEVEL <= 100;

SELECT SYSDATE - LEVEL
  FROM DUAL 
  CONNECT BY LEVEL <= 100;

--- 时间戳相关操作

SELECT systimestamp,
       SYSDATE,
       CAST(SYSDATE AS TIMESTAMP),
       to_date('2019-12-30 1:00:00', 'yyyy-mm-dd hh24:mi:ss')
  FROM dual;
  
  SELECT dbtimezone, sessiontimezone FROM dual;

---查找所有列名 便利性拼接字符
SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='PB_CUSTOMER';

SELECT 'nvl(t.' || t.column_name || ', -1)  = nvl(st.' || t.column_name || ',-1) and '
  FROM user_tab_columns t
 WHERE lower(t.table_name) = 'temp_tkt_fare_history';

-- 随机抽取数据
SELECT m.*
  FROM (SELECT u.* FROM app_t_check u ORDER BY dbms_random.value()) m
 WHERE rownum < 6;


-- 大表
select * from app_t_check sample(5)  where rownum<6;

333,444,555,666，

2. 程序优化

1. 开启并行

/*+parallel(table_short_name,cash_number)*/
--这个可以加到insert、delete、update、select的后面来使用（和rule的用法差不多，有机会再分享rule的用法）

--开启parallel功能的语句是：
alter session enable parallel dml; --这个语句是DML语句

--开启并行
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8';
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 8';

do_something();
     
EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL QUERY';
EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';



--查看cpu并行数
show parameter cpu;
cpu_count(当前CPU数量)     	                    integer     72
parallel_threads_per_cpu (每个CPU可以的并行数量) integer     2

一. 数据对象操作

1. 查询指定对象在哪里被调用过:

SELECT NAME, TYPE, text
  FROM all_source
 WHERE upper(text) LIKE '%PARTITION_BIGP%'

2. 获取对象的定义

select dbms_metadata.get_ddl('VIEW','V_IFO_DCS_PASSENGER_BASE','HFSERVICE') from dual;

desc obj_name;

3. 同义词查询

SELECT * FROM sys.all_synonyms t WHERE t.owner IN ('HFSERVICE')

二. 锁和Session

1. 查目前系统内的锁的对象

SELECT s.sid,
       s.machine,
       o.object_name,
       l.oracle_username,
       l.locked_mode,
       'ALTER SYSTEM KILL SESSION ' || s.sid || ', ' || s.serial# || ';' command
  FROM v$locked_object l, v$session s, all_objects o
 WHERE l.session_id = s.sid
   AND s.machine = 'SH-wangyanlong'
   AND l.object_id = o.object_id;

2. 查询锁住的对象正在执行的SQL

SELECT *
  FROM v$sql
 WHERE hash_value IN
       (SELECT sql_hash_value
          FROM v$session
         WHERE sid IN (SELECT session_id FROM v$locked_object));

3. 查询对应活动的session正在执行的sql，按cpu执行时间排序

SELECT DISTINCT s.event, s.sql_exec_start, s.sid, s.serial#, st.sql_text, st.cpu_time
  FROM v$session s, v$sql st
 WHERE s.sql_id = st.sql_id
   AND s.schemaname = 'AFXTST_OWNER'
   AND s.status = 'ACTIVE'
 ORDER BY st.cpu_time DESC;

4. 查询活动的session对应的等待事件

  select t2.sid, t2.SERIAL#, *-- 库级唯一定位一个ession*
  t1.SPID OS_PID,   *-- 操作系统的ID*
  t3.SQL_ID, t2.EVENT,*-- SQLID和等待事件*
 *--t2.P1TEXT, t2.P1,  -- 等待事件的1信息*
 *-- t2.p2TEXT, t2.P2,  -- 等待事件的2信息*
   *--t2.p3TEXT, t2.P3,  -- 等待事件的3信息*
   *--t3.SQL_FULLTEXT   -- 被执行的QL完整内容*
   T3.SQL_TEXT
   from v$process t1, v$session t2, v$sql t3
   where t1.ADDR = t2.PADDR
    and t2.STATUS = 'ACTIVE'
    AND T2.SQL_ID=T3.SQL_ID
    *-and t2.sid = ? ;*

5. 回话级临时表锁

SELECT sid,  FROM v$enqueue_lock t WHERE t.type = 'TO';

SELECT sid,
       serial#,
       paddr,
       'ALTER SYSTEM KILL SESSION  ' || '''' || sid || ',' || serial# || '''' || ';'
  FROM v$session
 WHERE sid = '刚才查到的SID'

KILL -9 刚才查出的SPID

查V$PROCESS视图:
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
查V$ACCESS视图:
SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';

三. 系统级优化查询

1. 查询当前数据库的连接数量是否已达峰值

  select b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b
  where a.ADDR = b.PADDR and b.USERNAME is not null
  group by b.MACHINE, b.PROGRAM, b.USERNAME
  order by count(*) desc

2. 查询占用高的sql语句

  SELECT *
    FROM (SELECT v.sql_id,
                 v.child_number,
                 v.sql_text,
                 v.elapsed_time,
                 v.cpu_time,
                 v.disk_reads,
                 RANK() OVER(ORDER BY v.cpu_time DESC) elapsed_rank
            FROM v$sql v
           WHERE first_load_time > TO_CHAR(SYSDATE - 2, 'yyyy-MM-dd/hh:mm:ss')) a
   WHERE a.elapsed_rank <= 10

3. 获取数据库环境的语言编码

select userenv('language') from dual