I want to update the cache size of an existing sequence and i want to describe a sequence in oracle like table . how to do it ?
and what are all the drawbacks of increasing the cache value of an sequence
Alter sequence seq_name cache 20;
See the docs.
To get the ddl you may use the dbms_metadata package, wich can be used for any object:
select dbms_metadata.get_ddl('SEQUENCE','SEQ_NAME') from dual;
Increasing the cache size is useful when you have massive fetches from sequence. Increasing it has no drawback considering the fact that you use them.
But if you generate 1 milion values at a time and you use only 10, maybe is not a good ideea, because 999990 values are lost. Next session will generate another 1000000 values. I think the engine works to generate them and allocate values for your session.
For example in my opinion, a cache 10 times less than you normally use in a session is ok.
UPDATE: Adding David Aldridge's comment:
The usefullness of a large cache is really related to the rate at which it is used in general, so not just for large selects but for systems with many session all using one value at a time. As background, the performance problem with a small cache is caused by the need for the SEQ$ system table to be modified when the cache is exhausted. It's a small operation but obviously you don't want to be doing it 100 times a second.
So, increasing the cache you'll have fewer concurent sessions on the same resource.