ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация запроса получения прав
8 сообщений из 8, страница 1 из 1
Оптимизация запроса получения прав
    #39637356
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
Добрый день.
Прошу помочь с оптимизацией
Есть запрос, которым я хочу найти все представления, на которые у пользователя нет прав
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT OBJECT_NAME
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = :owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
MINUS
SELECT TABLE_NAME
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :owner
       AND GRANTEE = :user
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )


Запрос работает порядка 15 минут, возвращает 20-40 записей

По отдельности запросы на count отрабатывают достаточно быстро

Код: plsql
1.
2.
3.
4.
5.
SELECT COUNT(1) 
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = ':owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'


2098 записей - 8с

Код: plsql
1.
2.
3.
4.
5.
SELECT count(1)
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :owner
       AND GRANTEE = :user
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )


15640 записей - 3с

 
Профиль
Код: plaintext
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.
169.
170.
171.
172.
173.
174.
175.
        ID PLAN_OPERATION                                           OBJECT_OWNER   OBJECT_NAME                     WAIT_PROFILE
---------- -------------------------------------------------------- -------------- ------------------------------- ----------------------------------------------
         0   SELECT STATEMENT                                                                                      
         1     MINUS                                                                                               
         2       SORT UNIQUE                                                                                       
         3         FILTER                                                                                          
         4           NESTED LOOPS                                                                                  
         5             NESTED LOOPS                                                                                
         6               TABLE ACCESS BY INDEX ROWID                SYS            USER$                           
         7                 INDEX UNIQUE SCAN                        SYS            I_USER1                         
         8               TABLE ACCESS BY INDEX ROWID                SYS            OBJ$                            db file sequential read(1);
         9                 INDEX RANGE SCAN                         SYS            I_OBJ5                          db file sequential read(13); ON CPU(1);
        10                   TABLE ACCESS BY INDEX ROWID            SYS            SUM$                            
        11                     INDEX UNIQUE SCAN                    SYS            I_SUM$_1                        
        12             INDEX RANGE SCAN                             SYS            I_USER2                         
        13           TABLE ACCESS BY INDEX ROWID                    SYS            IND$                            
        14             INDEX UNIQUE SCAN                            SYS            I_IND1                          
        15           FIXED TABLE FULL                               SYS            X$KZSPR                         
        16           FIXED TABLE FULL                               SYS            X$KZSPR                         
        17           NESTED LOOPS                                                                                  
        18             INDEX RANGE SCAN                             SYS            I_OBJAUTH1                      
        19             FIXED TABLE FULL                             SYS            X$KZSRO                         
        20           NESTED LOOPS                                                                                  
        21             NESTED LOOPS                                                                                
        22               NESTED LOOPS                                                                              
        23                 NESTED LOOPS                                                                            
        24                   MERGE JOIN CARTESIAN                                                                  
        25                     INDEX RANGE SCAN                     SYS            I_OBJ5                          
        26                     BUFFER SORT                                                                         
        27                       FIXED TABLE FULL                   SYS            X$KZSRO                         
        28                   INDEX RANGE SCAN                       SYS            I_USER2                         
        29                 INDEX RANGE SCAN                         SYS            I_OBJAUTH1                      
        30               INDEX RANGE SCAN                           SYS            I_DEPENDENCY1                   
        31             TABLE ACCESS BY INDEX ROWID                  SYS            DEPENDENCY$                     
        32           FIXED TABLE FULL                               SYS            X$KZSPR                         
        33           FIXED TABLE FULL                               SYS            X$KZSPR                         
        34           NESTED LOOPS                                                                                  
        35             NESTED LOOPS                                                                                
        36               TABLE ACCESS BY INDEX ROWID                SYS            TRIGGER$                        
        37                 INDEX UNIQUE SCAN                        SYS            I_TRIGGER2                      
        38               INDEX RANGE SCAN                           SYS            I_OBJAUTH1                      
        39             FIXED TABLE FULL                             SYS            X$KZSRO                         
        40           FIXED TABLE FULL                               SYS            X$KZSPR                         
        41           NESTED LOOPS                                                                                  
        42             INDEX RANGE SCAN                             SYS            I_OBJAUTH1                      
        43             FIXED TABLE FULL                             SYS            X$KZSRO                         
        44           FIXED TABLE FULL                               SYS            X$KZSPR                         
        45           FIXED TABLE FULL                               SYS            X$KZSPR                         
        46           FIXED TABLE FULL                               SYS            X$KZSPR                         
        47           NESTED LOOPS                                                                                  
        48             INDEX RANGE SCAN                             SYS            I_OBJAUTH1                      
        49             FIXED TABLE FULL                             SYS            X$KZSRO                         
        50           FIXED TABLE FULL                               SYS            X$KZSPR                         
        51           FIXED TABLE FULL                               SYS            X$KZSPR                         
        52           NESTED LOOPS                                                                                  
        53             NESTED LOOPS                                                                                
        54               TABLE ACCESS BY INDEX ROWID                SYS            TABPART$                        
        55                 INDEX UNIQUE SCAN                        SYS            I_TABPART_OBJ$                  
        56               INDEX RANGE SCAN                           SYS            I_OBJAUTH1                      
        57             FIXED TABLE FULL                             SYS            X$KZSRO                         
        58           FIXED TABLE FULL                               SYS            X$KZSPR                         
        59           NESTED LOOPS                                                                                  
        60             NESTED LOOPS                                                                                
        61               NESTED LOOPS                                                                              
        62                 NESTED LOOPS                                                                            
        63                   MERGE JOIN CARTESIAN                                                                  
        64                     INDEX RANGE SCAN                     SYS            I_OBJ5                          
        65                     BUFFER SORT                                                                         
        66                       FIXED TABLE FULL                   SYS            X$KZSRO                         
        67                   INDEX RANGE SCAN                       SYS            I_USER2                         
        68                 INDEX RANGE SCAN                         SYS            I_OBJAUTH1                      
        69               INDEX RANGE SCAN                           SYS            I_DEPENDENCY1                   
        70             TABLE ACCESS BY INDEX ROWID                  SYS            DEPENDENCY$                     
        71           FIXED TABLE FULL                               SYS            X$KZSPR                         
        72           FIXED TABLE FULL                               SYS            X$KZSPR                         
        73           FIXED TABLE FULL                               SYS            X$KZSPR                         
        74           FIXED TABLE FULL                               SYS            X$KZSPR                         
        75           FIXED TABLE FULL                               SYS            X$KZSPR                         
        76           FIXED TABLE FULL                               SYS            X$KZSPR                         
        77           FIXED TABLE FULL                               SYS            X$KZSPR                         
        78           FIXED TABLE FULL                               SYS            X$KZSPR                         
        79           FIXED TABLE FULL                               SYS            X$KZSPR                         
        80           FIXED TABLE FULL                               SYS            X$KZSPR                         
        81           FIXED TABLE FULL                               SYS            X$KZSPR                         
        82           FIXED TABLE FULL                               SYS            X$KZSPR                         
        83           FIXED TABLE FULL                               SYS            X$KZSPR                         
        84           FIXED TABLE FULL                               SYS            X$KZSPR                         
        85           FIXED TABLE FULL                               SYS            X$KZSPR                         
        86           VIEW                                                                                          
        87             FAST DUAL                                                                                   
        88           NESTED LOOPS                                                                                  
        89             INDEX RANGE SCAN                             SYS            I_OBJAUTH1                      
        90             FIXED TABLE FULL                             SYS            X$KZSRO                         
        91           FIXED TABLE FULL                               SYS            X$KZSPR                         
        92           NESTED LOOPS                                                                                  
        93             INDEX RANGE SCAN                             SYS            I_OBJAUTH1                      
        94             FIXED TABLE FULL                             SYS            X$KZSRO                         
        95           FIXED TABLE FULL                               SYS            X$KZSPR                         
        96           VIEW                                                                                          
        97             SORT GROUP BY                                                                               
        98               NESTED LOOPS                                                                              
        99                 NESTED LOOPS                                                                            
       100                   INDEX UNIQUE SCAN                      SYS            I_OLAP_CUBES$                   
       101                   TABLE ACCESS BY INDEX ROWID            SYS            DEPENDENCY$                     
       102                     INDEX RANGE SCAN                     SYS            I_DEPENDENCY1                   
       103                 INDEX RANGE SCAN                         SYS            I_OBJ1                          
       104           NESTED LOOPS                                                                                  
       105             INDEX SKIP SCAN                              SYS            I_USER2                         
       106             INDEX RANGE SCAN                             SYS            I_OBJ4                          
       107       SORT UNIQUE                                                                                       
       108         CONCATENATION                                                                                   
       109           FILTER                                                                                        
       110             HASH JOIN                                                                                   
       111               NESTED LOOPS                                                                              ON CPU(398);
       112                 HASH JOIN                                                                               ON CPU(164);
       113                   MERGE JOIN CARTESIAN                                                                  
       114                     NESTED LOOPS                                                                        
       115                       NESTED LOOPS                                                                      
       116                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       117                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       118                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       119                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       120                       INDEX FULL SCAN                    SYS            I_TABLE_PRIVILEGE_MAP           
       121                     BUFFER SORT                                                                         
       122                       INDEX FULL SCAN                    SYS            I_USER2                         
       123                   INDEX RANGE SCAN                       SYS            I_OBJ5                          db file sequential read(24); ON CPU(4);
       124                 INDEX RANGE SCAN                         SYS            I_OBJAUTH1                      ON CPU(614); db file sequential read(1);
       125               INDEX FULL SCAN                            SYS            I_USER2                         
       126             NESTED LOOPS                                                                                
       127               INDEX SKIP SCAN                            SYS            I_USER2                         
       128               INDEX RANGE SCAN                           SYS            I_OBJ4                          
       149               INDEX SKIP SCAN                            SYS            I_USER2                         
       150               INDEX RANGE SCAN                           SYS            I_OBJ4                          
       171               INDEX SKIP SCAN                            SYS            I_USER2                         
       172               INDEX RANGE SCAN                           SYS            I_OBJ4                          
       129           FILTER                                                                                        
       130             NESTED LOOPS                                                                                
       131               NESTED LOOPS                                                                              
       132                 NESTED LOOPS                                                                            
       133                   HASH JOIN                                                                             
       134                     NESTED LOOPS                                                                        
       135                       NESTED LOOPS                                                                      
       136                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       137                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       138                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       139                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       140                       TABLE ACCESS BY INDEX ROWID        SYS            OBJAUTH$                        
       141                         INDEX RANGE SCAN                 SYS            I_OBJAUTH2                      db file sequential read(1);
       142                           FIXED TABLE FULL               SYS            X$KZSRO                         
       143                     INDEX FULL SCAN                      SYS            I_TABLE_PRIVILEGE_MAP           
       144                   TABLE ACCESS BY INDEX ROWID            SYS            OBJ$                            db file sequential read(1);
       145                     INDEX RANGE SCAN                     SYS            I_OBJ1                          
       146                 INDEX RANGE SCAN                         SYS            I_USER2                         
       147               INDEX RANGE SCAN                           SYS            I_USER2                         
       148             NESTED LOOPS                                                                                
       151           FILTER                                                                                        
       152             NESTED LOOPS                                                                                
       153               NESTED LOOPS                                                                              
       154                 NESTED LOOPS                                                                            
       155                   HASH JOIN                                                                             
       156                     NESTED LOOPS                                                                        
       157                       NESTED LOOPS                                                                      
       158                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       159                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       160                         TABLE ACCESS BY INDEX ROWID      SYS            USER$                           
       161                           INDEX UNIQUE SCAN              SYS            I_USER1                         
       162                       TABLE ACCESS BY INDEX ROWID        SYS            OBJAUTH$                        
       163                         INDEX RANGE SCAN                 SYS            I_OBJAUTH2                      
       164                           FIXED TABLE FULL               SYS            X$KZSRO                         
       165                     INDEX FULL SCAN                      SYS            I_TABLE_PRIVILEGE_MAP           
       166                   TABLE ACCESS BY INDEX ROWID            SYS            OBJ$                            
       167                     INDEX RANGE SCAN                     SYS            I_OBJ1                          
       168                 INDEX RANGE SCAN                         SYS            I_USER2                         
       169               INDEX RANGE SCAN                           SYS            I_USER2                         
       170             NESTED LOOPS

 
трасса
Код: plaintext
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.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
SELECT OBJECT_NAME
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = :B1
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
MINUS
SELECT TABLE_NAME
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :B1
       AND GRANTEE = :B2
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.28       0.28          0          0          0           0
Fetch        1   1195.54    1237.33       8832    5223839          0          43
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   1195.85    1237.64       8832    5223839          0          43

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        43         43         43  MINUS  (cr=5223839 pr=8832 pw=0 time=1237330509 us)
      2098       2098       2098   SORT UNIQUE (cr=8206 pr=6555 pw=0 time=14769556 us cost=206 size=2844 card=36)
      2098       2098       2098    FILTER  (cr=8206 pr=6555 pw=0 time=588387 us)
      2098       2098       2098     NESTED LOOPS  (cr=8206 pr=6555 pw=0 time=584906 us cost=137 size=79 card=1)
      2098       2098       2098      NESTED LOOPS  (cr=8203 pr=6555 pw=0 time=575882 us cost=136 size=57 card=1)
         1          1          1       TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=20 us cost=1 size=14 card=1)
         1          1          1        INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=10 us cost=0 size=0 card=1)(object id 46)
      2098       2098       2098       TABLE ACCESS BY INDEX ROWID OBJ$ (cr=8201 pr=6555 pw=0 time=574383 us cost=135 size=43 card=1)
      2115       2115       2115        INDEX RANGE SCAN I_OBJ5 (cr=6773 pr=6487 pw=0 time=96925 us cost=72 size=0 card=136)(object id 40)
         0          0          0         TABLE ACCESS BY INDEX ROWID SUM$ (cr=3 pr=0 pw=0 time=32 us cost=1 size=8 card=1)
         3          3          3          INDEX UNIQUE SCAN I_SUM$_1 (cr=2 pr=0 pw=0 time=18 us cost=0 size=0 card=1)(object id 1002)
      2098       2098       2098      INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=5748 us cost=1 size=22 card=1)(object id 47)
         0          0          0     TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)
         0          0          0      INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=50 card=2)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=74 card=1)
         0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=5 size=64 card=1)
         0          0          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=52 card=1)
         0          0          0         MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=1)
         0          0          0          INDEX RANGE SCAN I_OBJ5 (cr=0 pr=0 pw=0 time=0 us cost=3 size=35 card=1)(object id 40)
         0          0          0          BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0           FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0         INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0        INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0       INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0      TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=78 card=2)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=26 card=1)
         0          0          0       TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
         0          0          0        INDEX UNIQUE SCAN I_TRIGGER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 163)
         0          0          0       INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=50 card=2)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=50 card=2)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=70 card=2)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
         0          0          0       TABLE ACCESS BY INDEX ROWID TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
         0          0          0        INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 572)
         0          0          0       INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=74 card=1)
         0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=5 size=64 card=1)
         0          0          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=52 card=1)
         0          0          0         MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=1)
         0          0          0          INDEX RANGE SCAN I_OBJ5 (cr=0 pr=0 pw=0 time=0 us cost=3 size=35 card=1)(object id 40)
         0          0          0          BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0           FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0         INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0        INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0       INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0      TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
         0          0          0      FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=88 card=4)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=2)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=88 card=4)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=2)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
         0          0          0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0     VIEW  (cr=0 pr=0 pw=0 time=0 us cost=8 size=16 card=1)
         0          0          0      SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=8 size=35 card=1)
         0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=175 card=5)
         0          0          0        NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=115 card=5)
         0          0          0         INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 986)
         0          0          0         TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=50 card=5)
         0          0          0          INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0        INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 36)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0      INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0      INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
      7820       7820       7820   SORT UNIQUE (cr=5215633 pr=2277 pw=0 time=1222564097 us cost=231 size=2340 card=20)
     15640      15640      15640    CONCATENATION  (cr=5215633 pr=2277 pw=0 time=1219576441 us)
     15640      15640      15640     FILTER  (cr=5209123 pr=1590 pw=0 time=1219570844 us)
     15640      15640      15640      HASH JOIN  (cr=5209123 pr=1590 pw=0 time=1219559015 us cost=112 size=468 card=4)
     15640      15640      15640       NESTED LOOPS  (cr=5209122 pr=1590 pw=0 time=33927843 us cost=110 size=380 card=4)
 510855050  510855050  510855050        HASH JOIN  (cr=6797 pr=1507 pw=0 time=302644797 us cost=80 size=25282920 card=324140)
       430        430        430         MERGE JOIN CARTESIAN (cr=6 pr=1 pw=0 time=4162 us cost=5 size=15394 card=358)
         2          2          2          NESTED LOOPS  (cr=5 pr=1 pw=0 time=3722 us cost=3 size=78 card=2)
         1          1          1           NESTED LOOPS  (cr=4 pr=0 pw=0 time=31 us cost=2 size=28 card=1)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=16 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 46)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=11 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 46)
         2          2          2           INDEX FULL SCAN I_TABLE_PRIVILEGE_MAP (cr=1 pr=1 pw=0 time=3694 us cost=1 size=22 card=2)(object id 369)
       430        430        430          BUFFER SORT (cr=1 pr=0 pw=0 time=287 us cost=4 size=716 card=179)
       215        215        215           INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=119 us cost=1 size=716 card=179)(object id 47)
   1188035    1188035    1188035         INDEX RANGE SCAN I_OBJ5 (cr=6791 pr=1506 pw=0 time=10017346 us cost=73 size=475335 card=13581)(object id 40)
     15640      15640      15640        INDEX RANGE SCAN I_OBJAUTH1 (cr=5202325 pr=83 pw=0 time=796351798 us cost=0 size=17 card=1)(object id 62)
       215        215        215       INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=114 us cost=1 size=3938 card=179)(object id 47)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0       INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
         0          0          0     FILTER  (cr=6232 pr=664 pw=0 time=2904035 us)
         0          0          0      NESTED LOOPS  (cr=6232 pr=664 pw=0 time=2904033 us cost=35 size=117 card=1)
         0          0          0       NESTED LOOPS  (cr=6232 pr=664 pw=0 time=2904032 us cost=34 size=113 card=1)
         0          0          0        NESTED LOOPS  (cr=6232 pr=664 pw=0 time=2904031 us cost=33 size=91 card=1)
     15814      15814      15814         HASH JOIN  (cr=2349 pr=423 pw=0 time=1101096 us cost=11 size=616 card=11)
    107111     107111     107111          NESTED LOOPS  (cr=2348 pr=423 pw=0 time=1028133 us cost=9 size=4590 card=102)
         1          1          1           NESTED LOOPS  (cr=4 pr=0 pw=0 time=30 us cost=2 size=28 card=1)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=19 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)(object id 46)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=8 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 46)
    107111     107111     107111           TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2344 pr=423 pw=0 time=982423 us cost=7 size=1734 card=102)
    107111     107111     107111            INDEX RANGE SCAN I_OBJAUTH2 (cr=278 pr=219 pw=0 time=89697 us cost=5 size=0 card=102)(object id 63)
         1          1          1             FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=26 us cost=0 size=13 card=1)
         2          2          2          INDEX FULL SCAN I_TABLE_PRIVILEGE_MAP (cr=1 pr=0 pw=0 time=25 us cost=1 size=22 card=2)(object id 369)
         0          0          0         TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3883 pr=241 pw=0 time=1800451 us cost=2 size=35 card=1)
     15814      15814      15814          INDEX RANGE SCAN I_OBJ1 (cr=2648 pr=111 pw=0 time=791400 us cost=1 size=0 card=1)(object id 36)
         0          0          0        INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0       INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
         0          0          0     FILTER  (cr=278 pr=23 pw=0 time=77541 us)
         0          0          0      NESTED LOOPS  (cr=278 pr=23 pw=0 time=77538 us cost=84 size=117 card=1)
         0          0          0       NESTED LOOPS  (cr=278 pr=23 pw=0 time=77536 us cost=83 size=113 card=1)
         0          0          0        NESTED LOOPS  (cr=278 pr=23 pw=0 time=77535 us cost=82 size=91 card=1)
         0          0          0         HASH JOIN  (cr=278 pr=23 pw=0 time=77532 us cost=42 size=1120 card=20)
         0          0          0          NESTED LOOPS  (cr=278 pr=23 pw=0 time=77445 us cost=40 size=7920 card=176)
         1          1          1           NESTED LOOPS  (cr=4 pr=0 pw=0 time=30 us cost=2 size=28 card=1)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=16 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=10 us cost=0 size=0 card=1)(object id 46)
         1          1          1            TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=6 us cost=1 size=14 card=1)
         1          1          1             INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 46)
         0          0          0           TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=274 pr=23 pw=0 time=77414 us cost=38 size=2992 card=176)
         0          0          0            INDEX RANGE SCAN I_OBJAUTH2 (cr=274 pr=23 pw=0 time=77409 us cost=5 size=0 card=2039)(object id 63)
         1          1          1             FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=26 us cost=0 size=13 card=1)
         0          0          0          INDEX FULL SCAN I_TABLE_PRIVILEGE_MAP (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=2)(object id 369)
         0          0          0         TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=35 card=1)
         0          0          0          INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
         0          0          0        INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=22 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0       INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
  db file sequential read                      8832        2.22         38.83
  read by other session                           1        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00
********************************************************************************
...
Рейтинг: 0 / 0
27.04.2018, 12:41
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637366
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
feagor
Код: plsql
1.
       AND GRANTEE = :user

Не падает с ORA-01745?
...
Рейтинг: 0 / 0
27.04.2018, 12:46
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637368
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
AmKad,

не суть, по факту литералы стоят
...
Рейтинг: 0 / 0
27.04.2018, 12:48
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637409
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
Кстати, в режиме RULE работает гораздо быстрее - порядка 48 секунд.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT /*+rule*/OBJECT_NAME
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = :owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
MINUS
SELECT TABLE_NAME
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :owner
       AND GRANTEE = :user
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )


Хочется до 10 секунд
...
Рейтинг: 0 / 0
27.04.2018, 13:12
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637420
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
rownum/materialize пробовал на обе половинки запроса влепить?
...
Рейтинг: 0 / 0
27.04.2018, 13:23
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637424
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
feagorДобрый день.
Прошу помочь с оптимизацией
Есть запрос, которым я хочу найти все представления, на которые у пользователя нет прав
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT OBJECT_NAME
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = :owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
MINUS
SELECT TABLE_NAME
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :owner
       AND GRANTEE = :user
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )


Запрос работает порядка 15 минут, возвращает 20-40 записей

По отдельности запросы на count отрабатывают достаточно быстро
Код: plsql
1.
2.
3.
4.
5.
SELECT COUNT(1) 
FROM   SYS.ALL_OBJECTS
WHERE  OWNER = ':owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'




2098 записей - 8с

Код: plsql
1.
2.
3.
4.
5.
SELECT count(1)
FROM   SYS.ALL_TAB_PRIVS
WHERE  TABLE_SCHEMA = :owner
       AND GRANTEE = :user
       AND PRIVILEGE IN ( 'INSERT', 'UPDATE' )




15640 записей - 3с


В выборке из SYS.ALL_OBJECTS сильно меньше записей, можно попробовать так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT o.OBJECT_NAME
  FROM SYS.ALL_OBJECTS o
  left join SYS.ALL_TAB_PRIVS tp
    on o.OWNER = tp.TABLE_SCHEMA
   AND tp.GRANTEE = :user
   AND tp.PRIVILEGE IN ('INSERT', 'UPDATE')
 WHERE o.OWNER = :owner
   and tp.TABLE_NAME is null
 AND o.OBJECT_TYPE = 'VIEW'
   AND o.STATUS = 'VALID'
...
Рейтинг: 0 / 0
27.04.2018, 13:29
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637439
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
Fogel,

переписал на not exist
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT /*+all_rows*/OBJECT_NAME
FROM   SYS.ALL_OBJECTS o
WHERE  OWNER = :owner
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
       and not exists (
         select 1 
         from   SYS.ALL_TAB_PRIVS p 
         where  p.TABLE_SCHEMA = o.OWNER 
         and    p.TABLE_NAME = o.OBJECT_NAME 
         and    p.GRANTEE = :userr
         and    p.privilege IN ( 'INSERT', 'UPDATE'  )
       );



на каких-то инстанциях вообще мгновенно, но даже на самых тормозных до 12 секунд.
Результат устраивает, спасибо всем
...
Рейтинг: 0 / 0
27.04.2018, 13:42
| Ответить | Цитировать | Написать  
Оптимизация запроса получения прав
    #39637454
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
feagor,
трасса после переписывания на not exists
 
Код: plaintext
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.
SELECT /*+all_rows*/OBJECT_NAME
FROM   SYS.ALL_OBJECTS o
WHERE  OWNER = :b1
       AND OBJECT_TYPE = 'VIEW'
       AND STATUS = 'VALID'
       and not exists (
         select 1
         from   SYS.ALL_TAB_PRIVS p
         where  p.TABLE_SCHEMA = o.OWNER
         and    p.TABLE_NAME = o.OBJECT_NAME
         and    p.GRANTEE = :b2
         and    p.privilege IN ( 'INSERT', 'UPDATE'  )
       )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.06       1.08          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      2.46      12.36       5670      25691          0          43
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.53      13.45       5670      25691          0          43

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        43         43         43  FILTER  (cr=25691 pr=5670 pw=0 time=1788944 us)
        43         43         43   NESTED LOOPS ANTI (cr=25691 pr=5670 pw=0 time=1788718 us cost=144 size=83 card=1)
      2098       2098       2098    NESTED LOOPS  (cr=8203 pr=5650 pw=0 time=847232 us cost=137 size=79 card=1)
      2098       2098       2098     NESTED LOOPS  (cr=8200 pr=5650 pw=0 time=837715 us cost=136 size=57 card=1)
         1          1          1      TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=23 us cost=1 size=14 card=1)
         1          1          1       INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=14 us cost=0 size=0 card=1)(object id 46)
      2098       2098       2098      TABLE ACCESS BY INDEX ROWID OBJ$ (cr=8198 pr=5650 pw=0 time=836211 us cost=135 size=43 card=1)
      2115       2115       2115       INDEX RANGE SCAN I_OBJ5 (cr=6771 pr=5623 pw=0 time=99272 us cost=72 size=0 card=136)(object id 40)
         0          0          0        TABLE ACCESS BY INDEX ROWID SUM$ (cr=3 pr=0 pw=0 time=27 us cost=1 size=8 card=1)
         3          3          3         INDEX UNIQUE SCAN I_SUM$_1 (cr=2 pr=0 pw=0 time=13 us cost=0 size=0 card=1)(object id 1002)
      2098       2098       2098     INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=6100 us cost=1 size=22 card=1)(object id 47)
      2055       2055       2055    VIEW PUSHED PREDICATE  VW_SQ_1 (cr=17488 pr=20 pw=0 time=751954 us cost=7 size=4 card=1)
      2055       2055       2055     FILTER  (cr=17488 pr=20 pw=0 time=749532 us)
      2055       2055       2055      NESTED LOOPS  (cr=17488 pr=20 pw=0 time=744371 us cost=7 size=117 card=1)
    131515     131515     131515       MERGE JOIN CARTESIAN (cr=5154 pr=1 pw=0 time=360245 us cost=7 size=21600 card=216)
      2141       2141       2141        MERGE JOIN CARTESIAN (cr=5151 pr=1 pw=0 time=78684 us cost=6 size=96 card=1)
      2098       2098       2098         NESTED LOOPS  (cr=5148 pr=0 pw=0 time=39841 us cost=5 size=85 card=1)
      2098       2098       2098          NESTED LOOPS  (cr=5145 pr=0 pw=0 time=33094 us cost=4 size=63 card=1)
      2098       2098       2098           NESTED LOOPS  (cr=4204 pr=0 pw=0 time=21058 us cost=2 size=28 card=1)
      2098       2098       2098            TABLE ACCESS BY INDEX ROWID USER$ (cr=2102 pr=0 pw=0 time=10681 us cost=1 size=14 card=1)
      2098       2098       2098             INDEX UNIQUE SCAN I_USER1 (cr=4 pr=0 pw=0 time=4317 us cost=0 size=0 card=1)(object id 46)
      2098       2098       2098            TABLE ACCESS BY INDEX ROWID USER$ (cr=2102 pr=0 pw=0 time=7333 us cost=1 size=14 card=1)
      2098       2098       2098             INDEX UNIQUE SCAN I_USER1 (cr=4 pr=0 pw=0 time=3254 us cost=0 size=0 card=1)(object id 46)
      2098       2098       2098           INDEX RANGE SCAN I_OBJ5 (cr=941 pr=0 pw=0 time=9050 us cost=2 size=35 card=1)(object id 40)
      2098       2098       2098          INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=3671 us cost=1 size=22 card=1)(object id 47)
      2141       2141       2141         BUFFER SORT (cr=3 pr=1 pw=0 time=33882 us cost=5 size=22 card=2)
      4196       4196       4196          INDEX FULL SCAN I_TABLE_PRIVILEGE_MAP (cr=3 pr=1 pw=0 time=11507 us cost=1 size=22 card=2)(object id 369)
    131515     131515     131515        BUFFER SORT (cr=3 pr=0 pw=0 time=205980 us cost=6 size=716 card=179)
    460315     460315     460315         INDEX FULL SCAN I_USER2 (cr=3 pr=0 pw=0 time=169747 us cost=1 size=716 card=179)(object id 47)
      2055       2055       2055       INDEX RANGE SCAN I_OBJAUTH1 (cr=12334 pr=19 pw=0 time=411417 us cost=0 size=17 card=1)(object id 62)
         0          0          0      FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0       INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0       INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)
         0          0          0   TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)
         0          0          0    INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=50 card=2)
         0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=74 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=5 size=64 card=1)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=52 card=1)
         0          0          0       MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=1)
         0          0          0        INDEX RANGE SCAN I_OBJ5 (cr=0 pr=0 pw=0 time=0 us cost=3 size=35 card=1)(object id 40)
         0          0          0        BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0         FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=39 card=1)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=26 card=1)
         0          0          0     TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
         0          0          0      INDEX UNIQUE SCAN I_TRIGGER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 163)
         0          0          0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=25 card=1)
         0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=25 card=1)
         0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=12 card=1)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=35 card=1)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
         0          0          0     TABLE ACCESS BY INDEX ROWID TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)
         0          0          0      INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 572)
         0          0          0     INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=74 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=5 size=64 card=1)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=4 size=52 card=1)
         0          0          0       MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=3 size=48 card=1)
         0          0          0        INDEX RANGE SCAN I_OBJ5 (cr=0 pr=0 pw=0 time=0 us cost=3 size=35 card=1)(object id 40)
         0          0          0        BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0         FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
         0          0          0       INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 47)
         0          0          0      INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 62)
         0          0          0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
         0          0          0    FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
         0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=2)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
         0          0          0    INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=2)(object id 62)
         0          0          0    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
         0          0          0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
         0          0          0   VIEW  (cr=0 pr=0 pw=0 time=0 us cost=8 size=16 card=1)
         0          0          0    SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=8 size=35 card=1)
         0          0          0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8 size=175 card=5)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=3 size=115 card=5)
         0          0          0       INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 986)
         0          0          0       TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=50 card=5)
         0          0          0        INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=5)(object id 106)
         0          0          0      INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 36)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)
         0          0          0    INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)
         0          0          0    INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
  db file sequential read                      5670        0.04         10.08
********************************************************************************
...
Рейтинг: 0 / 0
27.04.2018, 13:53
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимизация запроса получения прав
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39637424&tid=1884078]:
 0ms
get settings:
 12ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 184ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 257ms
total:  534ms
созд. / загр.: 534ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]