Добрый день! Пытаюсь ускорить отображения списка пользователей, добавляя постраничный вывод (oracle 10g)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select *
from ( select
a.*, ROWNUM rnum
from ( select u.username,u.userfullname,u.usercomment,u.isInner,u.isTeacher,u.isEmployer,u.deleted,
ps_fio(u.personid, 1) teachername,
ps_fio(sr.personid, 1) studentname,
case when u.isInner=1 then '' end Inn,
case when u.isStudent=1 then '' end Stud,
case when u.isTeacher=1 then '' end Teach from AD_Users u
left join fc_studentrecords sr on sr.recordid=u.studentid
order by u.username) a
where ROWNUM <= 120)
where rnum > 60;
Проблема в том что запрос в таком виде выполняется около 9сек, хотя а аналогичный запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select *
from ( select
a.*, ROWNUM rnum
from ( select u.username,u.userfullname,u.usercomment,u.isInner,u.isTeacher,u.isEmployer,u.deleted,
ps_fio(u.personid, 1) teachername,
sr.personid studentname,
case when u.isInner=1 then '' end Inn,
case when u.isStudent=1 then '' end Stud,
case when u.isTeacher=1 then '' end Teach from AD_Users u
left join fc_studentrecords sr on sr.recordid=u.studentid
order by u.username) a
where ROWNUM <= 120)
where rnum > 60;
В таблице ad_users 50 тыс записей, fc_studentrecords 80 тыс записей
функция ps_fio
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
create or replace FUNCTION PS_FIO(PerId PS_PERSONS.PERSONID%Type, FioType NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
result VARCHAR2(70);
gender_int NUMBER ;
BEGIN
select decode(m.message_id,'SEX_MALE',1,0) into gender_int
from ps_persons p
join rb_messages m on m.message_value=p.sex
where personid=PerId;
BEGIN
select case
when fiotype=0 then trim(familyname)||' '||trim(firstname)||' '||trim(secondname)
when fiotype=1 then trim(familyname)||' '||substr(firstname, 1, 1)||'. '||substr(secondname, 1, 1)||'.'
else '' end into result
from ps_persons
where personid=PerId;
EXCEPTION
WHEN no_data_found then result:= '';
END;
return result;
END PS_FIO;
скрипт таблицы fc_studentrecords
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
CREATE TABLE "COPYREAL"."FC_STUDENTRECORDS"
( "RECORDID" NUMBER(10,0),
"PERSONID" NUMBER(10,0) NOT NULL ENABLE,
"BOOKNO" VARCHAR2(15 CHAR),
"BOOKDATE" DATE,
"DIPLOMANO" VARCHAR2(15 CHAR),
"PLANID" NUMBER(10,0) NOT NULL ENABLE,
"DOCDATE" DATE,
"DOCNO" VARCHAR2(15 CHAR),
"DOCSUM" NUMBER(15,2),
"BRANCHID" NUMBER(4,0) DEFAULT 1 NOT NULL ENABLE,
"STATEMENTID" NUMBER(10,0),
"DOCPRIVTYPE" NUMBER(2,0),
"DOCPRIVPER" NUMBER(3,0),
"PREVDOC" VARCHAR2(30 CHAR),
"ENTERED" VARCHAR2(30 CHAR),
"STUDYFORMID" NUMBER(1,0),
"BUDGET" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"PLACE" VARCHAR2(40 CHAR),
"PAID_SUM" NUMBER(15,2),
"FINAL_PAYMENT_DATE" DATE,
"PAID_BY_CAPITAL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"USERNAME" VARCHAR2(25 CHAR),
"INDIV_STUDENTID" NUMBER(10,0),
"CLIENT" VARCHAR2(70 CHAR),
CONSTRAINT "PK_FC_STUDENTRECORDS" PRIMARY KEY ("RECORDID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 720896 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "UK_BOOKNO" UNIQUE ("BOOKNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 917504 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "UK_FC_STYDENTRECORDS" UNIQUE ("PERSONID", "PLANID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_FCDSTUDRECS_RBCOMMONSTFORMS" FOREIGN KEY ("STUDYFORMID")
REFERENCES "COPYREAL"."RB_COMMONSTUDYFORMS" ("FORMID") ENABLE,
CONSTRAINT "FK_STUDENTRECORDS_DEPARTMENTS" FOREIGN KEY ("BRANCHID")
REFERENCES "COPYREAL"."RB_DEPARTMENTS" ("CODE") ENABLE,
CONSTRAINT "FK_PS_PERSONS" FOREIGN KEY ("PERSONID")
REFERENCES "COPYREAL"."PS_PERSONS" ("PERSONID") ENABLE,
CONSTRAINT "FK_FC_STUDENTREC_PL_EDUCPLANS" FOREIGN KEY ("PLANID")
REFERENCES "COPYREAL"."PL_EDUCPLANS" ("PLANID") ENABLE,
CONSTRAINT "FK_FS_STUDREC_FC_PRIVEL" FOREIGN KEY ("DOCPRIVTYPE")
REFERENCES "COPYREAL"."FC_PRIVILEGETYPES" ("PRIVILEGEID") ENABLE,
CONSTRAINT "FK_FC_STUDENTRECORDS_AD_USERS" FOREIGN KEY ("USERNAME")
REFERENCES "COPYREAL"."AD_USERS" ("USERNAME") ENABLE,
CONSTRAINT "FK_FCSTUDRECS_ENSTATEMENTS" FOREIGN KEY ("STATEMENTID")
REFERENCES "COPYREAL"."EN_STATEMENTS" ("STATEMENTID") ENABLE,
CONSTRAINT "FK_FC_STUDREC_IP_STUDENTS" FOREIGN KEY ("INDIV_STUDENTID")
REFERENCES "COPYREAL"."IP_STUDENTS" ("STUDENTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."RECORDID" IS 'id студента';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PERSONID" IS 'id персоны';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."BOOKNO" IS 'номер зачётки';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."BOOKDATE" IS 'дата зачётки';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DIPLOMANO" IS 'номер диплома';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PLANID" IS 'id учебного плана';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DOCDATE" IS 'дата договора';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DOCNO" IS 'номер договора';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DOCSUM" IS 'сумма договора';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."BRANCHID" IS 'код филиала';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."STATEMENTID" IS 'id акта передачи дел';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DOCPRIVTYPE" IS 'тип льготы по оплате';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."DOCPRIVPER" IS 'процент льготы';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PREVDOC" IS 'предыдущий документ об обр.';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."ENTERED" IS 'куда поступил';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."STUDYFORMID" IS 'форма обучения';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."BUDGET" IS '0 - платное обучение, 1 - бюджет, 2 - бесплатное обучение';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PLACE" IS 'территориальная принадлежность процесса обучения';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PAID_SUM" IS 'фактически внесенная сумма';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."FINAL_PAYMENT_DATE" IS 'конечная дата платежа';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."PAID_BY_CAPITAL" IS 'оплачено за счет мат. капитала';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."USERNAME" IS 'пользователь, добавивший договор';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."INDIV_STUDENTID" IS 'Id студента, обучающегося по индивидуальному плану';
COMMENT ON COLUMN "COPYREAL"."FC_STUDENTRECORDS"."CLIENT" IS 'Заказчик';
COMMENT ON TABLE "COPYREAL"."FC_STUDENTRECORDS" IS 'договор со студентом';
CREATE INDEX "COPYREAL"."FC_STUDENTRECORDS_FIO_IDX" ON "COPYREAL"."FC_STUDENTRECORDS" ("COPYREAL"."PS_FIO"("PERSONID",1))
PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."FC_STUDENTRECORDS_FIO_IDX2" ON "COPYREAL"."FC_STUDENTRECORDS" ("COPYREAL"."PS_FIO"("PERSONID",0))
PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FC_STUDENTRECORDS" ON "COPYREAL"."FC_STUDENTRECORDS" ("PERSONID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_FCDSTUDRECS_RBCOMMONSTFO" ON "COPYREAL"."FC_STUDENTRECORDS" ("STUDYFORMID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_FCSTUDRECS_ENSTATEMENTS" ON "COPYREAL"."FC_STUDENTRECORDS" ("STATEMENTID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_FC_STUDENTREC_PL_EDUCPLA" ON "COPYREAL"."FC_STUDENTRECORDS" ("PLANID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_FS_STUDREC_FC_PRIVEL" ON "COPYREAL"."FC_STUDENTRECORDS" ("DOCPRIVTYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_STUDENTRECORDS_DEPARTMEN" ON "COPYREAL"."FC_STUDENTRECORDS" ("BRANCHID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
ad_users
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
CREATE TABLE "COPYREAL"."AD_USERS"
( "USERNAME" VARCHAR2(25 CHAR),
"USERFULLNAME" VARCHAR2(100 CHAR) NOT NULL ENABLE,
"USERCOMMENT" VARCHAR2(200 CHAR),
"PWD" RAW(16),
"PERSONID" NUMBER(10,0),
"ISINNER" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
"ISTEACHER" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"ISSTUDENT" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"STUDENTID" NUMBER(10,0),
"DELETED" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"ISEMPLOYER" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"USERCODE" NUMBER(5,0),
"MOBILE_NUMBER" NUMBER(10,0),
CONSTRAINT "PK_AD_USERS" PRIMARY KEY ("USERNAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "CH_USER_PERSON" CHECK (personid is not null or isTeacher=0) ENABLE,
CONSTRAINT "CH_USER_STUDENT" CHECK (studentid is not null or isStudent=0) ENABLE,
CONSTRAINT "UK_USERCODE" UNIQUE ("USERCODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_USER_PERSON" FOREIGN KEY ("PERSONID")
REFERENCES "COPYREAL"."PS_PERSONS" ("PERSONID") ENABLE,
CONSTRAINT "FK_USER_STUDENT" FOREIGN KEY ("STUDENTID")
REFERENCES "COPYREAL"."FC_STUDENTRECORDS" ("RECORDID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."USERNAME" IS 'Имя пользователя (логин)';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."USERFULLNAME" IS 'Полное имя пользователя';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."USERCOMMENT" IS 'Примечание';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."PWD" IS 'Хеш пароля MD5';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."PERSONID" IS 'id персоны (нужен для преподавателей)';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."ISINNER" IS 'Сотрудник администрации. За таких отвечает администратор системы';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."ISTEACHER" IS 'Преподаватель. За таких отвечает менеджер кафедры';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."ISSTUDENT" IS 'Студент. За таких отвечают сотрудники деканатов.';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."STUDENTID" IS 'id договора со студентом.';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."DELETED" IS '1 - удалён';
COMMENT ON COLUMN "COPYREAL"."AD_USERS"."USERCODE" IS '5-значный код сгенерированный случайным образом в диапазоне [10000;99999]';
COMMENT ON TABLE "COPYREAL"."AD_USERS" IS 'Пользователи системы';
CREATE INDEX "COPYREAL"."AD_USERS_FIO_IDX" ON "COPYREAL"."AD_USERS" ("COPYREAL"."PS_FIO"("PERSONID",1))
PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_USER_PERSON" ON "COPYREAL"."AD_USERS" ("PERSONID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "COPYREAL"."IX_FK_USER_STUDENT" ON "COPYREAL"."AD_USERS" ("STUDENTID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
вывод autotrace в sql developer
+ скриншот
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
buffer is not pinned count 207278
bytes received via SQL*Net from client 880
bytes sent via SQL*Net to client 20975
calls to get snapshot scn: kcmgss 207199
consistent gets 674851
consistent gets - examination 310692
consistent gets from cache 674851
CPU used by this session 890
CPU used when call started 898
cursor authentications 4
DB time 914
enqueue releases 5
enqueue requests 5
execute count 103599
index fetch by key 103590
no work - consistent read gets 260481
opened cursors cumulative 7
OS Block input operations 872
OS Involuntary context switches 62
OS Page faults 2
OS Page reclaims 1714
OS System time used 159
OS User time used 739
OS Voluntary context switches 39
parse count (hard) 5
parse count (total) 7
parse time cpu 1
parse time elapsed 1
recursive calls 103634
recursive cpu usage 687
rows fetched via callback 103512
session logical reads 674851
session pga memory -327680
shared hash latch upgrades - no wait 78
sorts (memory) 3
sorts (rows) 53897
sql area evicted 1
SQL*Net roundtrips to/from client 9
table fetch by rowid 103600
table fetch continued row 78
table scan blocks gotten 260393
table scan rows gotten 1948362
table scans (short tables) 51797
user calls 11
workarea executions - optimal 7
Как можно ускорить запрос ? Индексы уже есть. Заранее спасибо |
