Queries, query

Oracle Database
1

Архивлог аль цагуудад хичнээн ширэх үүсэж байгааг харах

set lines 325 
col "DG Date" for a10
col 12AM for a10 
col 01AM for a10 
col 02AM for a10 
col 03AM for a10 
col 04AM for a10 
col 05AM for a10 
col 06AM for a10 
col 07AM for a10 
col 08AM for a10 
col 09AM for a10 
col 10AM for a10 
col 11AM for a10 
col 12PM for a10 
col 1PM for a10 
col 2PM for a10 
col 3PM for a10 
col 4PM for a10 
col 5PM for a10 
col 6PM for a10 
col 7PM for a10 
col 8PM for a10 
col 9PM for a10 
col 10PM for a10 
col 11PM for a10 
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM", 
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM" 
FROM V$LOG_HISTORY 
GROUP BY TRUNC(FIRST_TIME) 
ORDER BY TRUNC(FIRST_TIME) DESC 
/
2 Likes
2

Full table scan хийж буй sql үүдийг харах.

select sql_id,object_owner,object_name from V$SQL_PLAN where 
operation='TABLE ACCESS' and 
options='FULL' and 
object_owner=upper('&schema');
3

Үр дүн нь ямаршуу юм байна?

4

ажлуулаад үзээрэй. зүгээр байхаар гээд оруулчихлаа…

1 Like
5

#blocking_session

SELECT 
s.inst_id, 
s.blocking_session, 
s.sid, 
s.serial#, 
s.seconds_in_wait 
FROM 
gv$session s 
WHERE 
blocking_session IS NOT NULL;
6

#long_running_operations

select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining 
from gv$session_longops 
where totalwork<>sofar 
/
7

#database_size

col "Database Size" format a20 
col "Free space" format a20 
col "Used space" format a20 
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" 
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" 
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" 
from (select bytes 
from v$datafile 
union all 
select bytes 
from v$tempfile 
union all 
select bytes 
from v$log) used 
, (select sum(bytes) as p 
from dba_free_space) free 
group by free.p 
/
8

@Багш. Table Full Scan-н сул талуудыг дэлгэрэнгүй тайлбарлаад өгөөч

9

1-100 мэдээлэл бүхий хүснэгт байлаа гэж бодъё. Тухайн үед 60-65 дахь хэсэг хэрэгтэй гэж үзье.
Уг мэдээллийг татаж харуулах комманд full table scan ашиглавал 1-100 хүртэлх бүхий л мэдээллийг шалгаад буцааж өгөх мэдээллийг харуулнаа гэсэн үг. индекс ашиглавал түүнээс харьцангуй бага мэдээллийг шалгана. магадгүй ихдээ 99 ч юм уу… тухайн нөхцөл байдал тохиргоо, индексээс шалтгаална.

Тиймээс Full table scan нь илүү цаг зарцуулна, илүү i/o хийгдэнэ, системдээ илүү ачаалал өгнө. 1,2 зэргийн үйлдэлд гайгүй ч 1000,10000 үйлдэл зэрэг хийгдвэл яах вэ гэдэг асуудал тарина… гэх мэт…

10

#sql_tuning_advisor

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&&sql_id',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'st &&sql_id',
description => '$uning task for statement &&sql_id');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Execute tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'st &&sql_id');

-- Generate report

SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('st &&sql_id') AS recommendations FROM dual;
SET PAGESIZE 24
1 Like