| create table statement [message #469271] |
Wed, 04 August 2010 02:07  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
One script is taking more time near about 30 minutes, i there any alternate way to reduce the time
EXECUTE IMMEDIATE 'CREATE TABLE DGT_ITEMEFFORTDATA_TEMP NOLOGGING AS SELECT * FROM DGT_ITEMEFFORTDATA WHERE
OWNERTYPE = ''Prj'' AND OWNERID NOT IN (SELECT OWNERID FROM NIGHTLY_METRIC_PROJECTS)';
This table DGT_ITEMEFFORTDATA_TEMP will get 7720066 records
after creation
|
|
|
|
|
|
|
|
|
|
| Re: create table statement [message #469300 is a reply to message #469271] |
Wed, 04 August 2010 03:30  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Quote:
But i have to create table inside the proc that is the requirement.
We would like to know why? What compelled you to do so?
Please have a look at the link provided by Michel.This would help you to get your answer.
Few questions:
SELECT * FROM DGT_ITEMEFFORTDATA WHERE
OWNERTYPE = 'Prj' AND OWNERID NOT IN (SELECT OWNERID FROM NIGHTLY_METRIC_PROJECTS)';
Believing statistics upto date,
1.Whats the plan for the above sql?
2. is there any index in owner_id column? What are the index on the tables used here
3. what is the count(*) returning for the above sql?
4. what is the result of SELECT (OWNERID) FROM NIGHTLY_METRIC_PROJECTS
Regards
Ved
[Updated on: Wed, 04 August 2010 03:36] Report message to a moderator |
|
|
|
| Re: create table statement [message #469301 is a reply to message #469282] |
Wed, 04 August 2010 03:39 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This is the plan of this query
explain plan for SELECT * FROM DGT_ITEMEFFORTDATA WHERE
OWNERTYPE = 'Prj' AND OWNERID NOT IN (SELECT OWNERID FROM NIGHTLY_METRIC_PROJECTS)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 1362M| 53238 (
|* 1 | FILTER | | | |
|* 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA | 11M| 1363M| 46159 (
|* 3 | TABLE ACCESS FULL| NIGHTLY_METRIC_PROJECTS | 1 | 5 | 4 (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "NIGHTLY_METRIC_PROJECTS"
"NIGHTLY_METRIC_PROJECTS" WHERE LNNVL("OWNERID"<>:B1)))
2 - filter("OWNERTYPE"='Prj')
3 - filter(LNNVL("OWNERID"<>:B1))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
This is index details
1 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_ACT ACTIVITYCODEID 1
2 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_ITM ITEMTYPE 1
3 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_ITM ITEMID 2
4 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_OWN OWNERTYPE 1
5 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_OWN OWNERID 2
6 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_PHS PHASEID 1
7 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_SOWN SUPEROWNERTYPE 1
8 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_SOWN SUPEROWNERID 2
9 DGT_ITEMEFFORTDATA IDX_DGT_ITEMEFFORTDATA_STG STAGEID 1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
After completing this execution the count(*) of the table DGT_ITEMEFFORTDATA_TEMP is 7720066
Total no of records in NIGHTLY_METRIC_PROJECTS =1200
There is no index on NIGHTLY_METRIC_PROJECTS table
Total no of records in DGT_ITEMEFFORTDATA=13122817
[Updated on: Wed, 04 August 2010 03:43] Report message to a moderator |
|
|
|
|
|
|
|
|
|
 Re: create table statement [message #469408 is a reply to message #469301] |
Wed, 04 August 2010 08:34 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
If you have an index for OWNERID on the NIGHTLY_METRIC_PROJECTS table, you could always try the following:
SELECT *
FROM dgt_itemeffortdata i
WHERE ownertype = 'Prj'
AND NOT EXISTS (SELECT ownerid
FROM nightly_metric_projects m
WHERE m.ownerid = i.ownerid);
PS: Check out the explain plan
[Updated on: Wed, 04 August 2010 08:40] by Moderator Report message to a moderator |
|
|
|
| Re: create table statement [message #469508 is a reply to message #469408] |
Thu, 05 August 2010 01:28 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This is the plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 1362M| 53238 (
|* 1 | FILTER | | | |
|* 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA | 11M| 1363M| 46159 (
|* 3 | TABLE ACCESS FULL| NIGHTLY_METRIC_PROJECTS | 1 | 5 | 4 (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "NIGHTLY_METRIC_PROJECTS"
"NIGHTLY_METRIC_PROJECTS" WHERE LNNVL("OWNERID"<>:B1)))
2 - filter("OWNERTYPE"='Prj')
3 - filter(LNNVL("OWNERID"<>:B1))
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
21 rows selected |
|
|
|
|
|
|
|
|
|
| Re: create table statement [message #469659 is a reply to message #469657] |
Thu, 05 August 2010 07:07 |
cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What, every row in the table always has ownertype = 'Prj'?
Then why does the column exist and why are you referencing it in there where clause.
Regardless I'd be very surprised, given the row counts involved, if oracle would ever use an index for this. This seems to require full table scans.
If you want to speed it up further you're probably going to have to use parallel processing, which we're busy discussing in your other thread.
|
|
|
|
| Re: create table statement [message #469662 is a reply to message #469659] |
Thu, 05 August 2010 07:12 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
yes ,but again the same question whether
Exec dbms_stats.gather_table_stats('my_user','my_tab', degree=>dbms_stats.auto_degree); will give me the degree value , that i can use in query.
|
|
|
|
| Re: create table statement [message #469666 is a reply to message #469662] |
Thu, 05 August 2010 07:28 |
cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So read the documentation to see what that setting does.
Based on what I've seen I doubt it does what you want since it appears to allow oracle to gather stats in parallel and nothing more.
|
|
|
|
| Re: create table statement [message #469669 is a reply to message #469666] |
Thu, 05 August 2010 07:35 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
so if not then i will have to start the testing like this
EXECUTE IMMEDIATE 'CREATE TABLE DGT_ITEMEFFORTDATA_TEMP PARALLEL NOLOGGING AS SELECT /*+ PARALLEL (t1, 1)*/ * FROM DGT_ITEMEFFORTDATA t1 WHERE
OWNERTYPE = ''Prj'' AND OWNERID NOT IN (SELECT OWNERID FROM NIGHTLY_METRIC_PROJECTS)';
and then start incriment with 1 and test again untill i get the optmised one. What you suggest.
|
|
|
|
| Re: create table statement [message #469673 is a reply to message #469669] |
Thu, 05 August 2010 07:41 |
cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
well 1 I believe is effectively not parallel so I'd start with 2.
Alternatively, you can spend some time reading up on parallel processing in the docs and see if that tells you or wait and see if someone else on here knows, but you'd need to answer all of Michel's questions in the other thread.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: create table statement [message #469724 is a reply to message #469719] |
Thu, 05 August 2010 11:47 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
There are 7700000 records and the index detail of this table are
Sr. TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 PROJECT UK_PROJECTCODE PROJECTCODE 1
2 PROJECT PRJ_INDEX ORGANIZATIONID 1
3 PROJECT PROJECT_CURREN_FK_IDX CURRENTPHASEID 1
4 PROJECT PROJECT_SOURCE_FK_IDX SOURCETEMPLATE 1
5 PROJECT PROJECT_ENTERPRISE_FK_IDX ENTERPRISEID 1
6 PROJECT IDX_PROJECT_STATUS STATUS 1
7 PROJECT SYS_C0048506 PROJECTID 1
8 PROJECT PROJ_IDX2 CATEGORY 1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: create table statement [message #469750 is a reply to message #469734] |
Thu, 05 August 2010 12:42 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
It works for me!
SQL> select /* FULL(LRG_TBL) PARALLEL(LRG_TBL, 2) */ SUM(BUCKET) FROM LRG_TBL
2 /
SUM(BUCKET)
-----------
2278646955
Execution Plan
----------------------------------------------------------
Plan hash value: 477856889
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1700 (1)| 00:00:21 | | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 4 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 4 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1579K| 6169K| 1700 (1)| 00:00:21 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| LRG_TBL | 1579K| 6169K| 1700 (1)| 00:00:21 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Please Read The Fine FAQ!
http://www.orafaq.com/wiki/Parallel_Query_FAQ
[Updated on: Thu, 05 August 2010 12:44] Report message to a moderator |
|
|
|
| Re: create table statement [message #469830 is a reply to message #469750] |
Fri, 06 August 2010 01:26 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi this is the explained plan of the below query
select /*+PARALLEL (pt, 2)*/ count(1) from DGT_ITEMEFFORTDATA pt;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29901 (1)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 13M| 29901 (1)| Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA | 13M| 29901 (1)| Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------
One thing is not clear to me is that when i run the query without using the explain plan for and see the output from gv$sql_plan there i can not get the PX COORDINATOR stuff while i run this query select * from table(dbms_xplan.display);
i get the PX COORDINATOR stuff.
[Updated on: Fri, 06 August 2010 02:48] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: create table statement [message #469856 is a reply to message #469847] |
Fri, 06 August 2010 02:39 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
whenever i try to get the outpot from
select * from gv$sql where sql_id=? then i copy and paste the ooutput in side the add code option, but when i see the preview, its not clear.So tell me how can i attach the output.
|
|
|
|
|
|
| Re: create table statement [message #469863 is a reply to message #469750] |
Fri, 06 August 2010 03:06 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I tried both the option parallel and without parallel, but in timing i did not get any difference
here is the plan of both the query
explain plan for select /*+PARALLEL (pt, 2)*/ count(1) from DGT_ITEMEFFORTDATA pt;
PMSMARTDB on 06-AUG-10 at pmsdb >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29901 (1)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 13M| 29901 (1)| Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA | 13M| 29901 (1)| Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
16 rows selected.
explain plan for select /*PARALLEL (pt, 2)*/ count(1) from DGT_ITEMEFFORTDATA pt;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54016 (1)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA | 13M| 54016 (1)|
----------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
12 rows selected.
[u][/u]
Even I incremented the degree value by 1 and reched upto 10 , but the timing was same in every case.
The total no of records are coming is 13191274
|
|
|
|
|
|
| Re: create table statement [message #469867 is a reply to message #469865] |
Fri, 06 August 2010 03:15 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
hey this is the initora parameter value
NAME!12 TYPE!2 VALUE!12 DISPLAY_VALUE!12 ISDEFAULT!12 ISSES_MODIFIABLE!12
parallel_min_servers 3 0 0 TRUE FALSE
parallel_max_servers 3 80 80 TRUE FALSE
parallel_automatic_tuning1 FALSE FALSE TRUE FALSE
See the value of initora parameter , where this value create any problem.
|
|
|
|
|
|
| Re: create table statement [message #469869 is a reply to message #469868] |
Fri, 06 August 2010 03:24 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
because of these parameter setting , system is not using parallel option or what can be other reason not to use paralle option. Even these table is not avilable in my server
select * from v_$pq_sysstat;
SELECT * FROM v_$px_process;
SELECT * FROM v_$px_sesstat;
SELECT * FROM v_$px_process_sysstat;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
