Home » RDBMS Server » Server Administration » memory consumption - Insert script (10.2.0.1 windows-xp, )
memory consumption - Insert script [message #569941] Sat, 03 November 2012 09:56 Go to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear All,

I ran below script -

SQL> insert into ash_emp select * from ash_emp; -- initially, tables has only 14 rows

SQL> /

114688 rows created.

SQL> /

229376 rows created.

SQL> /

458752 rows created.

SQL> //

917504 rows created.

SQL> /

1835008 rows created.

SQL> /

3670016 rows created.

SQL> /

7340032 rows created.

SQL> /

14680064 rows created.

SQL> /

29360128 rows created.


while seing simultaneously at the dba_free_space table, bytes were fluctuating rapidly. What could be the reason?


SQL> select tablespace_name, sum(bytes/1024) "KB" from dba_free_space
 group by tablespace_name;

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             [b]2048[/b]
SYSAUX                               6528
USERS                                [b]2112[/b]
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                2112
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                2880
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                2880
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                3648
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                3648
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                              896
SYSAUX                               6528
USERS                                4416
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                              448
SYSAUX                               6528
USERS                                5184
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                               [b]64[/b]
SYSAUX                               6528
USERS                                5184
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                5952
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                5952
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                6720
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                6720
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                [b]7488[/b]
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                7488
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8256
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8256
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                1088
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                1856
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                1856
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                2624
SYSTEM                               1024

SQL> ////

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                2624
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                3392
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             1024
SYSAUX                               6528
USERS                                4160
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                              832
SYSAUX                               6528
USERS                                4160
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                              576
SYSAUX                               6528
USERS                                4928
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                              256
SYSAUX                               6528
USERS                                5696
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                               64
SYSAUX                               6528
USERS                                5696
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                6464
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                6464
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             4096
SYSAUX                               6528
USERS                                7232
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                7232
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8000
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8000
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8768
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             3072
SYSAUX                               6528
USERS                                8768
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                [b]1600[/b]
SYSTEM                               1024

SQL> /

TABLESPACE_NAME                        KB
------------------------------ ----------
UNDOTBS1                             2048
SYSAUX                               6528
USERS                                2368
SYSTEM                               1024



User is scott and default permanent tablespace is USERS. Why there is up-down in bytes?

Please make me understand about it?

Regards,
Ishika
Re: memory consumption - Insert script [message #569945 is a reply to message #569941] Sat, 03 November 2012 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But we are unable to understand your test.
We have nothing to relate the 2 parts.
Use "set time on", add the date in your query and remoev the useless tablespaces.

Regards
Michel
Re: memory consumption - Insert script [message #569948 is a reply to message #569945] Sat, 03 November 2012 10:53 Go to previous messageGo to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

While inserting rows in a table, bytes of USERS tablespace should decrease. But, instead of decreasing, its fluctating. My main concern is about USER tablespace (which is default tablespace).

Regards,
Ishika
Re: memory consumption - Insert script [message #569949 is a reply to message #569948] Sat, 03 November 2012 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
Which tablespaces are configured for AUTOEXTEND?
Re: memory consumption - Insert script [message #569950 is a reply to message #569949] Sat, 03 November 2012 12:07 Go to previous messageGo to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear BlackSwan,

Auto extensible for all the tablespaces are YES.
Re: memory consumption - Insert script [message #569951 is a reply to message #569950] Sat, 03 November 2012 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
>Auto extensible for all the tablespaces are YES.
Actually auto extensible applies to data files; not tablespace.
when/after free space drops below some minimum, more free space gets added to the file.
In my opinion, measuring free space on an auto extensible file is an exercise in futility & a meaningless exercise.
Re: memory consumption - Insert script [message #569953 is a reply to message #569951] Sat, 03 November 2012 12:49 Go to previous messageGo to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
You are correct Mr. BlackSwan.
Actually auto extensible applies to data files; not tablespace.


Thanks for clarifying the concept of free space. If you know the link about the concept of free spaces, please share.

Regards,
Ishika
Re: memory consumption - Insert script [message #569956 is a reply to message #569953] Sat, 03 November 2012 13:08 Go to previous message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts

Regards
Michel
Previous Topic: from dictionary to locally
Next Topic: graphical (chart/graph) analysis of dynamic performance views [V$ Views]
Goto Forum:
  


Current Time: Fri Jul 25 06:35:20 CDT 2014

Total time taken to generate the page: 0.13768 seconds