.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
SQL> desc STORE_LIST
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
STORE_ID NOT NULL NUMBER
COUNTRY VARCHAR2(30)
STORE_TYPE VARCHAR2(19)
REGISTRATION_DATE DATE
SQL>
SQL> exec dbms_stats.gather_table_stats(USER,'STORE_LIST',estimate_percent=>100);
SQL> select num_rows, sample_size, blocks, blocks*8192/1048576 Size_MB from user_tables where table_name = 'STORE_LIST';
PL/SQL procedure successfully completed.
Elapsed: 00:17:43.17
NUM_ROWS SAMPLE_SIZE BLOCKS SIZE_MB
---------- ----------- ---------- ----------
110327040 110327040 507160 3962.1875
select sid, opname, target, sofar, totalwork,
units, to_char(start_time,'HH24:MI:SS') StartTime,
time_remaining, message, username
from v$session_longops
where sofar != totalwork
order by start_time
/
At 06:45:41
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Table Scan
HEMANT.STORE_LIST 49110 507160 Blocks 06:45:09
289
Table Scan: HEMANT.STORE_LIST: 49110 out of 507160 Blocks done
HEMANT
At 06:47:57
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Table Scan
HEMANT.STORE_LIST 272042 507160 Blocks 06:45:09
144
Table Scan: HEMANT.STORE_LIST: 272042 out of 507160 Blocks done
HEMANT
At 06:49:51
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Table Scan
HEMANT.STORE_LIST 453007 507160 Blocks 06:45:09
34
Table Scan: HEMANT.STORE_LIST: 453007 out of 507160 Blocks done
HEMANT
At 06:50:42
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Table Scan
HEMANT.STORE_LIST 63023 507160 Blocks 06:50:21
148
Table Scan: HEMANT.STORE_LIST: 63023 out of 507160 Blocks done
HEMANT
SQL_ID : 5urpb1azwjnwv
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitor
ing no_substrb_pad */count(*), sum(sys_op_opnsize("STORE_ID")), count("COUNTRY"), count(distinct "COUNTRY"), sum(sys_op_opnsize(
"COUNTRY")), substrb(dump(min("COUNTRY"),16,0,32),1,120), substrb(dump(max("COUNTRY"),16,0,32),1,120), count("STORE_TYPE"), coun
t(distinct "STORE_TYPE"), sum(sys_op_opnsize("STORE_TYPE")), substrb(dump(min("STORE_TYPE"),16,0,32),1,120), substrb(dump(max("S
TORE_TYPE"),16,0,32),1,120), count("REGISTRATION_DATE"), count(distinct "REGISTRATION_DATE"), substrb(dump(min("REGISTRATION_DAT
E"),16,0,32),1,120), substrb(dump(max("REGISTRATION_DATE"),16,0,32),1,120) from "HEMANT"."STORE_LIST" t
SQL_ID : g2nvjs2q08rwh
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
kt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_parallel(t) no_parallel_index(t) db
ms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */"STORE_ID" val, ntile(254) o
ver (order by "STORE_ID") bkt from "HEMANT"."STORE_LIST" t where "STORE_ID" is not null) group by val) group by maxbkt order b
y maxbkt
At 06:52:37
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Table Scan
HEMANT.STORE_LIST 457529 507160 Blocks 06:50:21
15
Table Scan: HEMANT.STORE_LIST: 457529 out of 507160 Blocks done
HEMANT
But at 06:53:28 :
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Sort/Merge
15523 166760 Blocks 06:52:52
341
Sort/Merge: : 15523 out of 166760 Blocks done
HEMANT
At 06:53:27 :
SQL_ID : g2nvjs2q08rwh
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
kt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_parallel(t) no_parallel_index(t) db
ms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */"STORE_ID" val, ntile(254) o
ver (order by "STORE_ID") bkt from "HEMANT"."STORE_LIST" t where "STORE_ID" is not null) group by val) group by maxbkt order b
y maxbkt
Again at 06:54:59 :
SQL_ID : g2nvjs2q08rwh
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
kt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_parallel(t) no_parallel_index(t) db
ms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */"STORE_ID" val, ntile(254) o
ver (order by "STORE_ID") bkt from "HEMANT"."STORE_LIST" t where "STORE_ID" is not null) group by val) group by maxbkt order b
y maxbkt
At 06:56:44
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Sort/Merge
167882 166760 Blocks 06:52:52
Sort/Merge: : 167882 out of 166760 Blocks done
HEMANT
At 06:57:35
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Sort/Merge
210688 166760 Blocks 06:52:52
Sort/Merge: : 210688 out of 166760 Blocks done
HEMANT
At 06:58:57
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Sort/Merge
255326 166760 Blocks 06:52:52
Sort/Merge: : 255326 out of 166760 Blocks done
HEMANT
At 07:00:22
SID OPNAME
---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
43 Sort Output
150657 162822 Blocks 06:59:27
4
Sort Output: : 150657 out of 162822 Blocks done
HEMANT
At 07:02:21
SQL_ID : g2nvjs2q08rwh
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
kt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_parallel(t) no_parallel_index(t) db
ms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */"STORE_ID" val, ntile(254) o
ver (order by "STORE_ID") bkt from "HEMANT"."STORE_LIST" t where "STORE_ID" is not null) group by val) group by maxbkt order b
y maxbkt
SQL> l
1 select column_name, num_distinct, num_nulls, histogram, num_buckets from user_tab_col_statistics
2* where table_name = 'STORE_LIST'
SQL> /
COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ---------- --------------- -----------
STORE_ID 306464 0 NONE 1
COUNTRY 42 0 NONE 1
STORE_TYPE 44 0 NONE 1
REGISTRATION_DATE 5896 0 NONE 1
SQL>
SQL> l
1 select to_char(sysdate,'DD-MON HH24:MI:SS') Collection_DateStamp, sid, opname, target, sofar, totalwork,
2 units, to_char(start_time,'HH24:MI:SS') StartTime,
3 time_remaining, message, username
4 from v$session_longops
5 where 1=1
6* order by start_time
SQL> /
COLLECTION_DATESTAMP SID OPNAME
------------------------ ---------- ----------------------------------------------------------------
TARGET SOFAR TOTALWORK UNITS STARTTIM
---------------------------------------------------------------- ---------- ---------- -------------------------------- --------
TIME_REMAINING
--------------
MESSAGE
------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
01-DEC 08:03:44 43 Table Scan
HEMANT.STORE_LIST 507160 507160 Blocks 06:45:09
0
Table Scan: HEMANT.STORE_LIST: 507160 out of 507160 Blocks done
HEMANT
01-DEC 08:03:44 43 Table Scan
HEMANT.STORE_LIST 507160 507160 Blocks 06:50:21
0
Table Scan: HEMANT.STORE_LIST: 507160 out of 507160 Blocks done
HEMANT
01-DEC 08:03:44 43 Sort/Merge
166760 166760 Blocks 06:52:52
0
Sort/Merge: : 166760 out of 166760 Blocks done
HEMANT
01-DEC 08:03:44 43 Sort Output
162822 162822 Blocks 06:59:27
0
Sort Output: : 162822 out of 162822 Blocks done
HEMANT
SQL>